用PostgreSQL数据库做地理位置app应用
项目中用到了postgreSQL中的earthdistance()函数功能计算地球上两点之间的距离，中文的资料太少了，我找到了一篇英文的、讲的很好的文章 ，特此翻译，希望能够帮助到以后用到earthdistance的同学。
做一个GEO应用从来都不是一件容易的事。但是用一些身边的开源项目就可以在几分钟内轻松解决这个问题。PostgreSQL有许多特性。是我的首选，它能够把数据库平台提升到另一个层次。
一、两种可用的选择
当我们想用Postgres作为GEO函数使用时，我们通常有2中选择（据我所知）:
PostGIS：为postgreSQL提供了高级GEO函数功能。我用了它一段时间，但是它对于我的需求来说太笨重了。
Cube和Earthdistance：这两个拓展为轻量级的Geo关系实体提供了简单、快速的实现方法。
二、为什么在数据库服务器端做计算
这是件非常明显的事。服务器存储了所有的数据，服务器拓展是用C/C++实现的，非常快。为数据表做索引也能加快计算速度。
三、使用我的选择--CubeandEarthDistance
作为开始，你应该先建一个数据库（我想你知道该怎么做），然后使它们能用我们的架构。执行：
然后执行：
上面的命令创建了大约40个函数，以后我们做数据查询的时候就可以用了。
在我们的例子中，我创建了名为events的表，字段有：id(serial),name(varchar255),lat(double),lng(double)。（别忘了~~）
四、计算2个坐标之间的距离
计算2个坐标之间的距离，我们要用到earthdistance(lltoearth($latlngcube),lltoearth($latlng_cube))这个函数。earthdistance()函数接受2组坐标值，返回值一个以米为单位的的数值。
这能用于很多场景，比如根据某一位置找到离其最近的发生的新闻事件的列表。数据库的操作可能就像下面这样：
SELECTevents.idevents.name,eaerthdiatance(lltoearth({currentuserlat},{currentuserlng}),llto_earth(events.lat,events.lng))
asdistancefromcurrentlocationFROMevents
ORDERBYdistancefromcurretnlocationASC;
这将给我们一个很nice的新闻事件列表,按他们的离我们当前位置的距离由近到远排序。第一个是离我们最近的。
五、找到某个半径范围内的记录
Cube和Earthdiatance拓展提供的另一个伟大的函数是earthbox(lltoearch($latlngcub),$radiusinmetres)。这个函数通过简单的比较就能到找到某个半径范围内的所有记录。它是靠返回2点之间的“大圆距离”实现的。
【译者注】大圆距离（Greatcircledisstance）指的是从球面的一点A出发到达球面上另一点B，所经过的最短路径的长度。一般说来，球面上任意两点A和B都可以与球心确定唯一的大圆，这个大圆被称为黎曼圆，而在大圆上连接这两点的较短的一条弧的长度就是大圆距离。如果想了解更多，请看wiki：大圆距离
它能用于查询我们城市中所有的新闻事件：
这条查询语句仅仅会返回在radius_in_metres指定的半径范围内的记录，非常简单吧！
六、提高查询速度
你可能会发现上面的查询有不小的开销。以我的经验，最好对一些字段建立索引。（下面这条语句假定你又events表，同时events表有字段lat和lng）
CREATEINDEX${nameofindex}oneventsUSINGgits(lltoearth(lat,lng));
七、数据类型
我的应用比较简单，所以我把经纬度（lat和lng）都设成了double类型。这使得我用Node.js开发起来更加快速，而不用再去自己定制针对GIST类型的解决方案。
八、就这些!
很神奇，对么？！？我们仅仅用常用的数据类型（double）就足以去用一些GEO函数创建基于地理位置的社交app
九、我使用的postgreSQL语句总结（使用实例）：
/*创建表*/ /*插入记录*/ /*插入记录*/ /*选择记录*/ /*更新记录*/ /*对经纬度列创建索引*/ /*根据半径（1000米）选择记录*/ /*选择距离当前用户的距离*/ /* -- SELECTsec_to_gc(0)::numeric(20,5); SELECTgc_to_sec(sec_to_gc(2*earth()))::numeric(20,5); SELECTcube_ll_coord(ll_to_earth(360,360),1)::numeric(20,5), SELECTlatitude(ll_to_earth(45,0))::numeric(20,10); -- SELECTlongitude(ll_to_earth(45,0))::numeric(20,10); SELECTearth_distance(ll_to_earth(0,0),ll_to_earth(0,180))::numeric(20,5); -- SELECTgeo_distance("(0,0)"::point,"(180,0)"::point)::numeric(20,5); SELECT("(0,0)"::point<@>"(180,0)"::point)::numeric(20,5); SELECTearth_box(ll_to_earth(0,0), SELECTearth_box(ll_to_earth(0,0),
*postgreSQL之earthdistance学习笔记
*author:wusuopubupt
*date:2013-03-31
*/
CREATETABLEpicture(
idserialPRIMARYKEY,
p_uidchar(12)NOTNULL,
p_keychar(23)NOTNULL,
latrealnotnull,
lngrealNOTNULL,
upintNOTNULL,
downintNOTNULL,
ipvarchar(15)DEFAULTNULL,
addressvarchar(256)DEFAULTNULL
);
INSERTINTOpicture(p_uid,p_key,lat,lng,up,down,ip,address)
VALUES("aaaabbbbcccc","2014032008164023279.png",40.043945,116.413668,0,0,"","");
INSERTINTOpicture(p_uid,p_key,lat,lng,up,down,ip,address)
VALUES("xxxxccccmmmm","2014032008164023111.png",40.067183,116.415230,0,0,"","");
SELECT*FROMpicture;
UPDATEpictureSETaddress="LiShuiqiao"WHEREid=1;
UPDATEpictureSETaddress="TianTongyuan"WHEREid=2;
CREATEINDEXll_idxonpictureUSINGgist(ll_to_earth(lat,lng));
SELECT*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
ORDERBYdisASC;
*以下内容是网上的一篇教程
*地址：http://www.cse.iitb.ac.in/dbms/Data/Courses/CS631/PostgreSQL-Resources/postgresql-9.2.4/contrib/earthdistance/expected/earthdistance.out
*/
--
-- Testearthdistanceextension
--
--Inthisfilewealsodosometestingofextensioncreate/dropscenarios.
--That"sreallyexercisingthecoredatabase"sdependencylogic,soideally
--we"ddoitinthecoreregressiontests,butwecan"tforlackofsuitable
--guaranteed-availableextensions. earthdistanceisagoodtestcasebecause
--ithasadependencyonthecubeextension.
--
CREATEEXTENSIONearthdistance; --fail,mustinstallcubefirst
ERROR: requiredextension"cube"isnotinstalled
CREATEEXTENSIONcube;
CREATEEXTENSIONearthdistance;
--
--TheradiusoftheEarthweareusing.
--
SELECTearth()::numeric(20,5);
earth
---------------
6378168.00000
(1row)
--Convertstraightlinedistancestogreatcircledistances.把直线距离转成大圆距离
--
SELECT(pi()*earth())::numeric(20,5);
numeric
----------------
20037605.73216
(1row)
sec_to_gc
-----------
0.00000
(1row)
--
--Convertgreatcircledistancestostraightlinedistances.
--
SELECTgc_to_sec(0)::numeric(20,5);
gc_to_sec
-----------
0.00000
(1row)
gc_to_sec
----------------
12756336.00000
(1row)
--
--Setcoordinatesusinglatitudeandlongitude.
--Extracteachcoordinateseparatelysowecanroundthem.
--
SELECTcube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),
cube_ll_coord(ll_to_earth(0,0),2)::numeric(20,5),
cube_ll_coord(ll_to_earth(0,0),3)::numeric(20,5);
cube_ll_coord|cube_ll_coord|cube_ll_coord
---------------+---------------+---------------
6378168.00000| 0.00000| 0.00000
(1row)
cube_ll_coord(ll_to_earth(360,360),2)::numeric(20,5),
cube_ll_coord(ll_to_earth(360,360),3)::numeric(20,5);
cube_ll_coord|cube_ll_coord|cube_ll_coord
---------------+---------------+---------------
6378168.00000| 0.00000| 0.00000
(1row)
--
--Testgettingthelatitudeofalocation.
--
SELECTlatitude(ll_to_earth(0,0))::numeric(20,10);
latitude
--------------
0.0000000000
(1row)
latitude
---------------
45.0000000000
(1row)
--Testgettingthelongitudeofalocation.
--
SELECTlongitude(ll_to_earth(0,0))::numeric(20,10);
longitude
--------------
0.0000000000
(1row)
longitude
--------------
0.0000000000
(1row)
--
--Forthedistanceteststhefollowingissomereallifedata.
--
--Chicagohasalatitudeof41.8andalongitudeof87.6.
--Albuquerquehasalatitudeof35.1andalongitudeof106.7.
--(Notethatlatitudeandlongitudearespecifieddifferently
--inthecubebasedfunctionsthanforthepointbasedfunctions.)
--
--
--Testgettingthedistancebetweentwopointsusingearth_distance.
--
SELECTearth_distance(ll_to_earth(0,0),ll_to_earth(0,0))::numeric(20,5);
earth_distance
----------------
0.00000
(1row)
earth_distance
----------------
20037605.73216
(1row)
--Testgettingthedistancebetweentwopointsusinggeo_distance.
--
SELECTgeo_distance("(0,0)"::point,"(0,0)"::point)::numeric(20,5);
geo_distance
--------------
0.00000
(1row)
geo_distance
--------------
12436.77274
(1row)
--
--Testgettingthedistancebetweentwopointsusingthe<@>operator.
--
SELECT("(0,0)"::point<@>"(0,0)"::point)::numeric(20,5);
numeric
---------
0.00000
(1row)
numeric
-------------
12436.77274
(1row)
--
--Testforpointsthatshouldbeinboundingboxes.
--
SELECTearth_box(ll_to_earth(0,0),
earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*1.00001)@>
ll_to_earth(0,1);
?column?
----------
t
(1row)
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*1.00001)@>
ll_to_earth(0,0.1);
?column?
----------
t
(1row)
--
--Testforpointsthatshouldn"tbeinboundingboxes.Notethatweneed
--tomakepointswayoutside,sincesomepointsclosemaybeinthebox
--butfurtherawaythanthedistancewearetesting.
--
SELECTearth_box(ll_to_earth(0,0),
earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*.57735)@>
ll_to_earth(0,1);
?column?
----------
f
(1row)
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*.57735)@>
ll_to_earth(0,0.1);
?column?
----------
f
(1row)
相关文章