用PostgreSQL数据库做地理位置app应用
项目中用到了postgreSQL中的earthdistance()函数功能计算地球上两点之间的距离，中文的资料太少了，我找到了一篇 英文的、讲的很好的文章 ，特此翻译，希望能够帮助到以后用到earthdistance的同学。
做一个GEO应用从来都不是一件容易的事。但是用一些身边的开源项目就可以在几分钟内轻松解决这个问题。 PostgreSQL有许多特性。是我的首选，它能够把数据库平台提升到另一个层次。
一、两种可用的选择
当我们想用Postgres作为GEO函数使用时，我们通常有2中选择（据我所知）:
PostGIS： 为postgreSQL提供了高级GEO函数功能。我用了它一段时间，但是它对于我的需求来说太笨重了。
Cube和Earthdistance： 这两个拓展为轻量级的Geo关系实体提供了简单、快速的实现方法。
二、为什么在数据库服务器端做计算
这是件非常明显的事。服务器存储了所有的数据，服务器拓展是用C/C++实现的，非常快。为数据表做索引也能加快计算速度。
三、使用我的选择–Cube and EarthDistance
作为开始，你应该先建一个数据库（我想你知道该怎么做），然后使它们能用我们的架构。 执行：
上面的命令创建了大约40个函数，以后我们做数据查询的时候就可以用了。
在我们的例子中，我创建了名为events的表，字段有：id(serial), name(varchar 255), lat(double), lng(double)。（别忘了~~）
四、计算2个坐标之间的距离
计算2个坐标之间的距离，我们要用到 earthdistance(lltoearth($latlngcube), lltoearth($latlng_cube)) 这个函数。 earthdistance()函数接受2组坐标值，返回值一个以米为单位的的数值。
这能用于很多场景，比如根据某一位置找到离其最近的发生的新闻事件的列表。数据库的操作可能就像下面这样：
SELECT events.id events.name, eaerthdiatance(lltoearth({currentuserlat}, {currentuserlng}), llto_earth(events.lat, events.lng))
as distancefromcurrentlocation FROM events
ORDER BY distancefromcurretnlocation ASC;
这将给我们一个很nice的新闻事件列表,按他们的离我们当前位置的距离由近到远排序。第一个是离我们最近的。
五、找到某个半径范围内的记录
Cube和Earthdiatance拓展提供的另一个伟大的函数是 earthbox(lltoearch($latlngcub), $radiusinmetres) 。 这个函数通过简单的比较就能到找到某个半径范围内的所有记录。它是靠返回2点之间的“大圆距离”实现的。
【译者注】大圆距离（Great circle disstance）指的是从球面的一点A出发到达球面上另一点B，所经过的最短路径的长度。一般说来，球面上任意两点A和B都可以与球心确定唯一的大圆，这个大圆被称为黎曼圆，而在大圆上连接这两点的较短的一条弧的长度就是大圆距离。如果想了解更多，请看wiki： 大圆距离
它能用于查询我们城市中所有的新闻事件：
这条查询语句仅仅会返回在radius_ in_ metres指定的半径范围内的记录，非常简单吧！
六、提高查询速度
你可能会发现上面的查询有不小的开销。以我的经验，最好对一些字段建立索引。 （下面这条语句假定你又events表， 同时events表有字段lat和lng）
CREATE INDEX ${nameofindex} on events USING gits(lltoearth(lat, lng));
七、数据类型
我的应用比较简单，所以我把经纬度（lat和lng）都设成了double类型。这使得我用Node.js开发起来更加快速，而不用再去自己定制针对GIST类型的解决方案。
八、就这些!
很神奇，对么？！？我们仅仅用常用的数据类型（double）就足以去用一些GEO函数创建基于地理位置的社交app
九、我使用的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, 0, 0, ”, ”);
/*插入记录*/
INSERT INTO picture(p_uid, p_key, lat, lng, up, down, ip, address)
VALUES(‘xccccmmmm’, ‘2014032008164023111.png’, 40.067183, 116.415230, 0, 0, ”, ”);
/*选择记录*/
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,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
(1 row)
SELECT cube_ll_coord(ll_to_earth(360,360),1)::numeric(20,5),
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
(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,0))::numeric(20,10);
latitude
—————
45.0000000000
(1 row)
—
— Test getting the longitude of a location.
—
SELECT longitude(ll_to_earth(0,0))::numeric(20,10);
longitude
————–
0.0000000000
(1 row)
SELECT longitude(ll_to_earth(45,0))::numeric(20,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,0),ll_to_earth(0,0))::numeric(20,5);
earth_distance
—————-
0.00000
(1 row)
SELECT earth_distance(ll_to_earth(0,0),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,0)’::point,'(180,0)’::point)::numeric(20,5);
geo_distance
————–
12436.77274
(1 row)
—
— Test getting the distance between two points using the <@> operator.
—
SELECT (‘(0,0)’::point <@> ‘(0,0)’::point)::numeric(20,5);
numeric
———
0.00000
(1 row)
SELECT (‘(0,0)’::point <@> ‘(180,0)’::point)::numeric(20,5);
numeric
————-
12436.77274
(1 row)
—
— Test for points that should be in bounding boxes.
—
SELECT earth_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
(1 row)
SELECT earth_box(ll_to_earth(0,0),
earth_distance(ll_to_earth(0,0),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,0),
earth_distance(ll_to_earth(0,0),ll_to_earth(0,1))*.57735) @>
ll_to_earth(0,1);
?column?
———-
f
(1 row)
SELECT earth_box(ll_to_earth(0,0),
earth_distance(ll_to_earth(0,0),ll_to_earth(0,0.1))*.57735) @>
ll_to_earth(0,0.1);
?column?
———-
f
(1 row)
