加入收藏 | 设为首页 | 会员中心 | 我要投稿 李大同 （https://www.lidatong.com.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 百科 > 正文

用postgreSQL做基于地理位置的app

发布时间：2020-12-13 18:13:50 所属栏目：百科 来源：网络整理
导读：原文地址：http://blog.csdn.net/wusuopubupt/article/details/21621477 前言： 项目中用到了postgreSQL中的earthdistance()函数功能计算地球上两点之间的距离，中文的资料太少了，我找到了一篇英文的、讲的很好的文章，特此翻译，希望能够帮助到以后用到ear

原文地址：http://blog.csdn.net/wusuopubupt/article/details/21621477


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

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

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


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

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

[sql] view plain copy
  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：大圆距离

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
        站长推荐
        热点阅读

          【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

          建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

          Copygight © 2008-2022 https://www.lidatong.com.cn/ All Rights Reserved. 李大同