用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;

四、计算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

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

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)

（编辑：李大同）

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