用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個函數，以後我們做資料查詢的時候就可以用了。
在我們的例子中，我建立了名為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)	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)	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)	float8	Returns the latitude in degrees of a point on the surface of the Earth.
longitude(earth)	float8	Returns the longitude in degrees of a point on the surface of the Earth.
earth_distance(earth, earth)	float8	Returns the great circle distance between two points on the surface of the Earth.
earth_box(earth, float8)	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 using earth_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;

這將給我們一個很nice的新聞事件列表,按他們的離我們當前位置的距離由近到遠排序。第一個是離我們最近的。

五、找到某個半徑範圍內的記錄
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);

這條查詢語句僅僅會返回在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（【譯者注】知乎上的一個回答）!

---------------------------
英語水平有限，如有翻譯不周之處，請您指點！

------------------------------

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, 0, 0, '', '');/*插入記錄*/INSERT INTO picture(p_uid, p_key, lat, lng, up, down, ip, address) VALUES('xxxxccccmmmm', '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 firstERROR:  required extension "cube" is not installedCREATE 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)