用postgreSQL做基于地理位置的app

前端之家收集整理的这篇文章主要介绍了用postgreSQL做基于地理位置的app前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

前言:项目中用到了postgresql中的earthdistance()函数功能计算地球上两点之间的距离,中文的资料太少了,我找到了一篇英文的、讲的很好的文章,特此翻译,希望能够帮助到以后用到earthdistance的同学。

一、两种可用的选择
当我们想用Postgres作为GEO函数使用时,我们通常有2中选择(据我所知):

1.PostGIS: 为postgresql提供了高级GEO函数功能。我用了它一段时间,但是它对于我的需求来说太笨重了。
2.Cube和Earthdistance: 这两个拓展为轻量级的Geo关系实体提供了简单、快速实现方法


二、为什么在数据库服务器端做计算
这是件非常明显的事。服务器存储了所有的数据,服务器拓展是用C/C++实现的,非常快。为数据表做索引也能加快计算速度。

三、使用我的选择--Cube and EarthDistance
作为开始,你应该先建一个数据库(我想你知道该怎么做),然后使它们能用我们的架构。 执行:

  1. CREATEEXTENSIONcube;
  2. CREATEEXTENSIONearthdistance;
上面的命令创建了大约40个函数,以后我们做数据查询的时候就可以用了。
在我们的例子中,我创建了名为events的表,字段有:id(serial),name(varchar 255),lat(double),lng(double)。(别忘了~~)

四、计算2个坐标之间的距离

计算2个坐标之间的距离,我们要用到earth_distance(ll_to_earth($latlngcube),ll_to_earth($latlng_cube))这个函数。 earth_distance()函数接受2组坐标值,返回值一个以米为单位的的数值。这能用于很多场景,比如根据某一位置找到离其最近的发生的新闻事件的列表。


【译者注】这里要提下几个重要的函数:(参考:http://www.postgresql.org/docs/8.3/static/earthdistance.html

Table F-3. Cube-based earthdistance functions

Function Returns Description
earth() float8 Returns the assumed radius of the Earth.
sec_to_gc(float8) Converts the normal straight line (secant) distance between between two points on the surface of the Earth to the great circle distance between them.
gc_to_sec(float8) Converts the great circle distance between two points on the surface of the Earth to the normal straight line (secant) distance between them.
ll_to_earth(float8,float8) earth Returns the location of a point on the surface of the Earth given its latitude (argument 1) and longitude (argument 2) in degrees.
latitude(earth) Returns the latitude in degrees of a point on the surface of the Earth.
longitude(earth) Returns the longitude in degrees of a point on the surface of the Earth.
earth_distance(earth,earth) Returns the great circle distance between two points on the surface of the Earth.
earth_Box(earth,223); padding:0.5ex">cube Returns a Box suitable for an indexed search using the cube@>operator for points within a given great circle distance of a location. Some points in this Box are further than the specified great circle distance from the location,so a second check usingearth_distanceshould be included in the query.

数据库的操作可能就像下面这样:

    SELECTevents.idevents.name,eaerthdiatance(ll_to_earth({currentuserlat},{currentuserlng}),llto_earth(events.lat,events.lng))
  1. asdistancefromcurrentlocationFROMevents
  2. ORDERBYdistancefromcurretnlocationASC;

这将给我们一个很nice的新闻事件列表,按他们的离我们当前位置的距离由近到远排序。第一个是离我们最近的。


五、找到某个半径范围内的记录

Cube和Earthdiatance拓展提供的另一个伟大的函数earth_Box(ll_to_earch($latlngcub),$radiusinmetres)。 这个函数通过简单的比较就能到找到某个半径范围内的所有记录。它是靠返回2点之间的“大圆距离”实现的。


【译者注】大圆距离(Great circle disstance)指的是从球面的一点A出发到达球面上另一点B,所经过的最短路径的长度。一般说来,球面上任意两点A和B都可以与球心确定唯一的大圆,这个大圆被称为黎曼圆,而在大圆上连接这两点的较短的一条弧的长度就是大圆距离。如果想了解更多,请看wiki:@L_404_6@

它能用于查询我们城市中所有的新闻事件:

    SELECTevents.id,events.nameFROMevents
  1. WHEREearth_Box({currentuserlat},{currentuserlng},{radiusinmetres})@>ll_to_earth(events.lat,events.lng);

这条查询语句仅仅会返回在radius_in_metres指定的半径范围内的记录,非常简单吧!



六、提高查询速度

你可能会发现上面的查询有不小的开销。以我的经验,最好对一些字段建立索引。 (下面这条语句假定你又events表, 同时events表有字段lat和lng)
    CREATEINDEX${nameofindex}oneventsUSINGgits(lltoearth(lat,lng));

七、数据类型

我的应用比较简单,所以我把经纬度(lat和lng)都设成了double类型。这使得我用Node.js开发起来更加快速,而不用再去自己定制针对GIST类型的解决方案。

八、就这些!

很神奇,对么?!?我们仅仅用常用的数据类型(double)就足以去用一些GEO函数创建基于地理位置的社交app(【译者注】 知乎上的一个回答 )!

---------------------------
英语水平有限,如有翻译不周之处,请您指点!


------------------------------

update:

我使用的postgresql语句总结:

    /*
  1. *postgresql之earthdistance学习笔记
  2. *author:wusuopubupt
  3. *date:2013-03-31
  4. */
  5. /*创建表*/
  6. TABLEpicture(
  7. idserialPRIMARYKEY,
  8. p_uidchar(12)NOTNULL,
  9. p_keychar(23) latrealnotnull,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> lng upint down ipvarchar(15)DEFAULT addressvarchar(256)NULL
  10. );
  11. /*插入记录*/
  12. INSERTINTOpicture(p_uid,p_key,lat,lng,up,down,ip,address)
  13. VALUES(@H_509_502@'aaaabbbbcccc',@H_509_502@'2014032008164023279.png',40.043945,116.413668,@H_509_502@'',@H_509_502@'');
  14. VALUES(@H_509_502@'xxxxccccmmmm',@H_509_502@'2014032008164023111.png',40.067183,116.415230,248)"> /*选择记录*/
  15. SELECT*FROMpicture;
  16. /*更新记录*/
  17. UPDATEpictureSETaddress=@H_509_502@'LiShuiqiao'WHEREid=1;
  18. SETaddress=@H_509_502@'TianTongyuan'WHEREid=2;
  19. /*对经纬度列创建索引*/
  20. INDEXll_idxonpictureUSINGgist(ll_to_earth(lat,lng));
  21. /*根据半径(1000米)选择记录*/
  22. FROMpicturewhereearth_Box(ll_to_earth(40.059286,116.418773),1000)@>ll_to_earth(picture.lat,picture.lng);
  23. /*选择距离当前用户的距离*/
  24. SELECTpicture.id,earth_distance(ll_to_earth(picture.lat,picture.lng),ll_to_earth(40.059286,116.418773))
  25. ASdisFROMpicture
  26. BYdisASC;
  27. /*
  28. *以下内容是网上的一篇教程
  29. *地址:http://www.cse.iitb.ac.in/dbms/Data/Courses/CS631/Postgresql-Resources/postgresql-9.2.4/contrib/earthdistance/expected/earthdistance.out
  30. --
  31. --Testearthdistanceextension
  32. --Inthisfilewealsodosometestingofextensioncreate/dropscenarios.
  33. --That'sreallyexercisingthecoredatabase'sdependencylogic,soideally
  34. --we'ddoitinthecoreregressiontests,butwecan'tforlackofsuitable
  35. --guaranteed-availableextensions.earthdistanceisagoodtestcasebecause
  36. --ithasadependencyonthecubeextension.
  37. CREATEEXTENSIONearthdistance;--fail,mustinstallcubefirst
  38. ERROR:requiredextension@H_509_502@"cube"isnotinstalled
  39. CREATEEXTENSIONearthdistance;
  40. --
  41. --TheradiusoftheEarthweareusing.
  42. SELECTearth()::numeric(20,5);
  43. earth
  44. ---------------
  45. 6378168.00000
  46. (1row)
  47. --Convertstraightlinedistancestogreatcircledistances.把直线距离转成大圆距离
  48. SELECT(pi()*earth())::numeric
  49. ----------------
  50. 20037605.73216
  51. (1row)
  52. SELECTsec_to_gc(0):: sec_to_gc
  53. -----------
  54. 0.00000
  55. --Convertgreatcircledistancestostraightlinedistances.
  56. SELECTgc_to_sec(0):: gc_to_sec
  57. SELECTgc_to_sec(sec_to_gc(2*earth())):: 12756336.00000
  58. --Setcoordinatesusinglatitudeandlongitude.
  59. --Extracteachcoordinateseparatelysowecanroundthem.
  60. SELECTcube_ll_coord(ll_to_earth(0,0),1):: cube_ll_coord(ll_to_earth(0,2):: cube_ll_coord(ll_to_earth(0,3):: cube_ll_coord|cube_ll_coord|cube_ll_coord
  61. ---------------+---------------+---------------
  62. 6378168.00000|0.00000|0.00000
  63. SELECTcube_ll_coord(ll_to_earth(360,360),108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> cube_ll_coord(ll_to_earth(360,248)"> cube_ll_coord(ll_to_earth(360,0); background-color:inherit">--Testgettingthelatitudeofalocation.
  64. SELECTlatitude(ll_to_earth(0,0)):: latitude
  65. --------------
  66. 0.0000000000
  67. SELECTlatitude(ll_to_earth(45,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> 45.0000000000
  68. --Testgettingthelongitudeofalocation.
  69. SELECTlongitude(ll_to_earth(0,10);
  70. longitude
  71. --------------
  72. 0.0000000000
  73. SELECTlongitude(ll_to_earth(45,0); background-color:inherit">--Forthedistanceteststhefollowingissomereallifedata.
  74. --Chicagohasalatitudeof41.8andalongitudeof87.6.
  75. --Albuquerquehasalatitudeof35.1andalongitudeof106.7.
  76. --(Notethatlatitudeandlongitudearespecifieddifferently
  77. --inthecubebasedfunctionsthanforthepointbasedfunctions.)
  78. --Testgettingthedistancebetweentwopointsusingearth_distance.
  79. SELECTearth_distance(ll_to_earth(0,ll_to_earth(0,248)"> earth_distance
  80. --Testgettingthedistancebetweentwopointsusinggeo_distance.
  81. SELECTgeo_distance(@H_509_502@'(0,0)'::point,@H_509_502@'(0,0)'::point):: geo_distance
  82. 0.00000
  83. 502@'(180,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> 12436.77274
  84. --Testgettingthedistancebetweentwopointsusingthe<@>operator.
  85. SELECT(@H_509_502@'(0,0)'::point<@>@H_509_502@'(0,153); font-weight:bold; background-color:inherit">numeric
  86. ---------
  87. ::point<@>@H_509_502@'(180,0); background-color:inherit">-------------
  88. --TestforpointsthatshouldbeinboundingBoxes.
  89. SELECTearth_Box(ll_to_earth(0,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> earth_distance(ll_to_earth(0,1))*1.00001)@>
  90. ll_to_earth(0,1);
  91. ?column?
  92. ----------
  93. t
  94. --Testforpointsthatshouldn'tbeinboundingBoxes.Notethatweneed
  95. --tomakepointswayoutside,sincesomepointsclosemaybeintheBox
  96. --butfurtherawaythanthedistancewearetesting.
  97. f
  98. (1row)

猜你在找的Postgre SQL相关文章