大佬教程收集整理的这篇文章主要介绍了用postgreSQL做基于地理位置的app,大佬教程大佬觉得挺不错的,现在分享给大家,也给大家做个参考。
前言:项目中用到了postgresql中的earthdistance()函数功能计算地球上两点之间的距离,中文的资料太少了,我找到了一篇英文的、讲的很好的文章 ,特此翻译,希望能够帮助到以后用到earthdistance的同学。
一、两种可用的选择
当我们想用Postgres作为GEO函数使用时,我们通常有2中选择(据我所知):
1.PostGIS: 为postgresql提供了高级GEO函数功能。我用了它一段时间,但是它对于我的需求来说太笨重了。
2.Cube和Earthdistance: 这两个拓展为轻量级的Geo关系实体提供了简单、快速的实现方法。
二、为什么在数据库服务器端做计算
这是件非常明显的事。服务器存储了所有的数据,服务器拓展是用C/C++实现的,非常快。为数据表做索引也能加快计算速度。
三、使用我的选择--Cube and Earthdistance
作为开始,你应该先建一个数据库(我想你知道该怎么做),然后使它们能用我们的架构。 执行:
CREATE EXTENSION cube; CREATE EXTENSION earthdistance;上面的命令创建了大约40个函数,以后我们做数据查询的时候就可以用了。
四、计算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,255); 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. |
数据库的操作可能就像下面这样:
SELECT events.ID events.name,eaerthdiatance(ll_to_earth({currentuserlat},{currentuserlng}),llto_earth(events.lat,events.lng)) as distancefromcurrentlocation FROM events ORDER BY distancefromcurretnlocation ASC;
五、找到某个半径范围内的记录
Cube和Earthdiatance拓展提供的另一个伟大的函数是earth_Box(ll_to_earch($latlngcub),$radiusinmetres)。 这个函数通过简单的比较就能到找到某个半径范围内的所有记录。它是靠返回2点之间的“大圆距离”实现的。
【译者注】大圆距离(Great circle disstance)指的是从球面的一点A出发到达球面上另一点B,所经过的最短路径的长度。一般说来,球面上任意两点A和B都可以与球心确定唯一的大圆,这个大圆被称为黎曼圆,而在大圆上连接这两点的较短的一条弧的长度就是大圆距离。如果想了解更多,请看wiki:大圆距离
它能用于查询我们城市中所有的新闻事件:
SELECT events.ID,events.name FROM events WHERE earth_Box({currentuserlat},{currentuserlng},{radiusinmetres}) @> ll_to_earth(events.lat,events.lng);
CREATE INDEX ${nameofindex} on events USING gits(lltoearth(lat,lng));
------------------------------
update:
我使用的postgreSQL语句总结:
/* * postgresql之earthdistance学习笔记 * author: wusuopubupt * date: 2013-03-31 */ /*创建表*/ CREATE table picture ( ID serial PRIMARY KEY,p_uID char(12) NOT NulL,p_key char(23) NOT NulL,lat real not null,lng real NOT NulL,up int NOT NulL,down int NOT NulL,ip varchar(15) DEFAulT NulL,address varchar(256) DEFAulT NulL ); /*插入记录*/ INSERT INTO picture(p_uID,p_key,lat,lng,up,down,ip,address) VALUES('aaaabbbbcccc','2014032008164023279.png',40.043945,116.413668,'',''); /*插入记录*/ INSERT INTO picture(p_uID,address) VALUES('xxxxccccmmmm','2014032008164023111.png',40.067183,116.415230,''); /*选择记录*/ SELECT * FROM picture; /*更新记录*/ UPDATE picture SET address='liShuiqiao' WHERE ID=1; UPDATE picture SET address='TianTongyuan' WHERE ID=2; /*对经纬度列创建索引*/ CREATE INDEX ll_IDx on picture USING gist(ll_to_earth(lat,lng)); /*根据半径(1000米)选择记录*/ SELECT * FROM picture where earth_Box(ll_to_earth(40.059286,116.418773),1000) @> ll_to_earth(picture.lat,picture.lng); /*选择距离当前用户的距离*/ SELECT picture.ID,earth_distance(ll_to_earth(picture.lat,picture.lng),ll_to_earth(40.059286,116.418773)) AS dis FROM picture ORDER BY dis ASC; /* * 以下内容是网上的一篇教程 * 地址:http://www.cse.iitb.ac.in/dbms/Data/Courses/CS631/Postgresql-Resources/postgresql-9.2.4/contrib/earthdistance/expected/earthdistance.out */ -- -- Test earthdistance extension -- -- In this file we also do some testing of extension create/drop scenarios. -- That's really exercising the core database's dependency logic,so IDeally -- we'd do it in the core regression tests,but we can't for lack of suitable -- guaranteed-available extensions. earthdistance is a good test case because -- it has a dependency on the cube extension. -- CREATE EXTENSION earthdistance; -- fail,must install cube first ERROR: required extension "cube" is not installed CREATE EXTENSION cube; CREATE EXTENSION earthdistance; -- -- The radius of the Earth we are using. -- SELECT earth()::numeric(20,5); earth --------------- 6378168.00000 (1 row) -- -- Convert straight line distances to great circle distances.把直线距离转成大圆距离 -- SELECT (pi()*earth())::numeric(20,5); numeric ---------------- 20037605.73216 (1 row) SELECT sec_to_gc(0)::numeric(20,5); sec_to_gc ----------- 0.00000 (1 row) -- -- Convert great circle distances to straight line distances. -- SELECT gc_to_sec(0)::numeric(20,5); gc_to_sec ----------- 0.00000 (1 row) SELECT gc_to_sec(sec_to_gc(2*earth()))::numeric(20,5); gc_to_sec ---------------- 12756336.00000 (1 row) -- -- Set coordinates using latitude and longitude. -- Extract each coordinate separately so we can round them. -- SELECT cube_ll_coord(ll_to_earth(0,0),1)::numeric(20,5),cube_ll_coord(ll_to_earth(0,2)::numeric(20,3)::numeric(20,5); cube_ll_coord | cube_ll_coord | cube_ll_coord ---------------+---------------+--------------- 6378168.00000 | 0.00000 | 0.00000 (1 row) SELECT cube_ll_coord(ll_to_earth(360,360),cube_ll_coord(ll_to_earth(360,5); cube_ll_coord | cube_ll_coord | cube_ll_coord ---------------+---------------+--------------- 6378168.00000 | 0.00000 | 0.00000 (1 row) -- -- Test getting the latitude of a location. -- SELECT latitude(ll_to_earth(0,0))::numeric(20,10); latitude -------------- 0.0000000000 (1 row) SELECT latitude(ll_to_earth(45,10); latitude --------------- 45.0000000000 (1 row) -- -- Test getting the longitude of a location. -- SELECT longitude(ll_to_earth(0,10); longitude -------------- 0.0000000000 (1 row) SELECT longitude(ll_to_earth(45,10); longitude -------------- 0.0000000000 (1 row) -- -- For the distance tests the following is some real life data. -- -- Chicago has a latitude of 41.8 and a longitude of 87.6. -- Albuquerque has a latitude of 35.1 and a longitude of 106.7. -- (Note that latitude and longitude are specifIEd differently -- in the cube based functions than for the point based functions.) -- -- -- Test getting the distance between two points using earth_distance. -- SELECT earth_distance(ll_to_earth(0,ll_to_earth(0,5); earth_distance ---------------- 0.00000 (1 row) SELECT earth_distance(ll_to_earth(0,180))::numeric(20,5); earth_distance ---------------- 20037605.73216 (1 row) -- -- Test getting the distance between two points using geo_distance. -- SELECT geo_distance('(0,0)'::point,'(0,0)'::point)::numeric(20,5); geo_distance -------------- 0.00000 (1 row) SELECT geo_distance('(0,'(180,5); geo_distance -------------- 12436.77274 (1 row) -- -- Test getting the distance between two points using the <@> operator. -- SELECT ('(0,0)'::point <@> '(0,5); numeric --------- 0.00000 (1 row) SELECT ('(0,0)'::point <@> '(180,5); numeric ------------- 12436.77274 (1 row) -- -- Test for points that should be in bounding Boxes. -- SELECT earth_Box(ll_to_earth(0,earth_distance(ll_to_earth(0,1))*1.00001) @> ll_to_earth(0,1); ?column? ---------- t (1 row) SELECT earth_Box(ll_to_earth(0,0.1))*1.00001) @> ll_to_earth(0,0.1); ?column? ---------- t (1 row) -- -- Test for points that shouldn't be in bounding Boxes. Note that we need -- to make points way outsIDe,since some points close may be in the Box -- but further away than the distance we are testing. -- SELECT earth_Box(ll_to_earth(0,1))*.57735) @> ll_to_earth(0,1); ?column? ---------- f (1 row) SELECT earth_Box(ll_to_earth(0,0.1))*.57735) @> ll_to_earth(0,0.1); ?column? ---------- f (1 row)
以上是大佬教程为你收集整理的用postgreSQL做基于地理位置的app全部内容,希望文章能够帮你解决用postgreSQL做基于地理位置的app所遇到的程序开发问题。
如果觉得大佬教程网站内容还不错,欢迎将大佬教程推荐给程序员好友。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。