前言:项目中用到了postgreSQL中的earthdistance()函数功能计算地球上两点之间的距离,中文的资料太少了,我找到了一篇英文的、讲的很好的文章,特此翻译,希望能够帮助到以后用到earthdistance的同学。 一、两种可用的选择 当我们想用Postgres作为GEO函数使用时,我们通常有2中选择(据我所知):
1.PostGIS: 为postgreSQL提供了高级GEO函数功能。我用了它一段时间,但是它对于我的需求来说太笨重了。 2.Cube和Earthdistance: 这两个拓展为轻量级的Geo关系实体提供了简单、快速的实现方法。
二、为什么在数据库服务器端做计算 这是件非常明显的事。服务器存储了所有的数据,服务器拓展是用C/C++实现的,非常快。为数据表做索引也能加快计算速度。 三、使用我的选择--Cube and EarthDistance 作为开始,你应该先建一个数据库(我想你知道该怎么做),然后使它们能用我们的架构。 执行:
- CREATEEXTENSIONcube;
- 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_distance should be included in the query. |
数据库的操作可能就像下面这样:
SELECTevents.idevents.name,eaerthdiatance(ll_to_earth({currentuserlat},{currentuserlng}),llto_earth(events.lat,events.lng))
- asdistancefromcurrentlocationFROMevents
- ORDERBYdistancefromcurretnlocationASC;
这将给我们一个很nice的新闻事件列表,按他们的离我们当前位置的距离由近到远排序。第一个是离我们最近的。
五、找到某个半径范围内的记录 Cube和Earthdiatance拓展提供的另一个伟大的函数是earth_box(ll_to_earch($latlngcub),$radiusinmetres)。 这个函数通过简单的比较就能到找到某个半径范围内的所有记录。它是靠返回2点之间的“大圆距离”实现的。
【译者注】大圆距离(Great circle disstance)指的是从球面的一点A出发到达球面上另一点B,所经过的最短路径的长度。一般说来,球面上任意两点A和B都可以与球心确定唯一的大圆,这个大圆被称为黎曼圆,而在大圆上连接这两点的较短的一条弧的长度就是大圆距离。如果想了解更多,请看wiki:大圆距离 它能用于查询我们城市中所有的新闻事件:
SELECTevents.id,events.nameFROMevents
- 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语句总结:
/*
- *postgreSQL之earthdistance学习笔记
- *author:wusuopubupt
- *date:2013-03-31
- */
-
- /*创建表*/
- TABLEpicture(
- idserialPRIMARYKEY,
- p_uidchar(12)NOTNULL,
- 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
- );
-
- /*插入记录*/
- INSERTINTOpicture(p_uid,p_key,lat,lng,up,down,ip,address)
- VALUES('aaaabbbbcccc','2014032008164023279.png',40.043945,116.413668,'','');
- VALUES('xxxxccccmmmm','2014032008164023111.png',40.067183,116.415230,248)"> /*选择记录*/
- SELECT*FROMpicture;
- /*更新记录*/
- UPDATEpictureSETaddress='LiShuiqiao'WHEREid=1;
- SETaddress='TianTongyuan'WHEREid=2;
- /*对经纬度列创建索引*/
- INDEXll_idxonpictureUSINGgist(ll_to_earth(lat,lng));
- /*根据半径(1000米)选择记录*/
- FROMpicturewhereearth_box(ll_to_earth(40.059286,116.418773),1000)@>ll_to_earth(picture.lat,picture.lng);
- /*选择距离当前用户的距离*/
- SELECTpicture.id,earth_distance(ll_to_earth(picture.lat,picture.lng),ll_to_earth(40.059286,116.418773))
- ASdisFROMpicture
- BYdisASC;
- /*
- *以下内容是网上的一篇教程
- *地址:http://www.cse.iitb.ac.in/dbms/Data/Courses/CS631/PostgreSQL-Resources/postgresql-9.2.4/contrib/earthdistance/expected/earthdistance.out
-
-
- --Inthisfilewealsodosometestingofextensioncreate/dropscenarios.
- --That'sreallyexercisingthecoredatabase'sdependencylogic,soideally
- --we'ddoitinthecoreregressiontests,butwecan'tforlackofsuitable
- --guaranteed-availableextensions.earthdistanceisagoodtestcasebecause
- --ithasadependencyonthecubeextension.
- CREATEEXTENSIONearthdistance;
- ERROR:requiredextension"cube"isnotinstalled
- CREATEEXTENSIONearthdistance;
- --
- --TheradiusoftheEarthweareusing.
- SELECTearth()::numeric(20,5);
- earth
- ---------------
- 6378168.00000
- (1row)
- --Convertstraightlinedistancestogreatcircledistances.把直线距离转成大圆距离
- SELECT(pi()*earth())::numeric
- ----------------
- 20037605.73216
- (1row)
- SELECTsec_to_gc(0):: sec_to_gc
- -----------
- 0.00000
- --Convertgreatcircledistancestostraightlinedistances.
- SELECTgc_to_sec(0):: gc_to_sec
- SELECTgc_to_sec(sec_to_gc(2*earth())):: 12756336.00000
- --Setcoordinatesusinglatitudeandlongitude.
- --Extracteachcoordinateseparatelysowecanroundthem.
- 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
- ---------------+---------------+---------------
- 6378168.00000|0.00000|0.00000
- 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.
- SELECTlatitude(ll_to_earth(0,0)):: latitude
- --------------
- 0.0000000000
- 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
- --Testgettingthelongitudeofalocation.
- SELECTlongitude(ll_to_earth(0,10);
- longitude
- --------------
- 0.0000000000
- SELECTlongitude(ll_to_earth(45,0); background-color:inherit">--Forthedistanceteststhefollowingissomereallifedata.
- --Chicagohasalatitudeof41.8andalongitudeof87.6.
- --Albuquerquehasalatitudeof35.1andalongitudeof106.7.
- --(Notethatlatitudeandlongitudearespecifieddifferently
- --inthecubebasedfunctionsthanforthepointbasedfunctions.)
- --Testgettingthedistancebetweentwopointsusingearth_distance.
- SELECTearth_distance(ll_to_earth(0,ll_to_earth(0,248)"> earth_distance
- --Testgettingthedistancebetweentwopointsusinggeo_distance.
- SELECTgeo_distance('(0,0)'::point,'(0,0)'::point):: geo_distance
- 0.00000
- '(180,108); list-style:decimal-leading-zero outside; color:inherit; line-height:18px; margin:0px!important; padding:0px 3px 0px 10px!important"> 12436.77274
- --Testgettingthedistancebetweentwopointsusingthe<@>operator.
- SELECT('(0,0)'::point<@>'(0,153); font-weight:bold; background-color:inherit">numeric
- ---------
- ::point<@>'(180,0); background-color:inherit">-------------
- --Testforpointsthatshouldbeinboundingboxes.
- 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)@>
- ll_to_earth(0,1);
- ?column?
- ----------
- t
- --Testforpointsthatshouldn'tbeinboundingboxes.Notethatweneed
- --tomakepointswayoutside,sincesomepointsclosemaybeinthebox
- --butfurtherawaythanthedistancewearetesting.
- f
- (1row)
(编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|