Use postgreSQL for geo-location-based Apps

Preface:The earthdistance () function in postgreSQL is used in the project to calculate the distance between two points on the earth. There is too little Chinese information. I found a good English article, hope to help those who will use earthdistance in the future.

One or two available options
When we want to use s as a GEO function, we usually have 2 options (as far as I know ):

1. PostGIS: Provides advanced GEO functions for postgreSQL. I used it for a while, but it is too heavy for my needs.
2. Cube and Earthdistance: These two extensions provide a simple and fast implementation method for lightweight Geo relational entities.

Ii. Why computing on the database server
This is very obvious. The server stores all the data, and the server expansion is implemented in C/C ++, which is very fast. Indexing a data table can speed up computation.

3. Use my options-Cube and EarthDistance
In the beginning, you should first create a database (I think you know how to do it) and then make them use our architecture. Run:

CREATE EXTENSION cube;CREATE EXTENSION earthdistance;

The above command creates about 40 functions, which can be used for data query in the future.
In our example, I created a table named events with the following fields: id (serial), name (varchar 255), lat (double), and lng (double ). (Don't forget ~~)

4. Calculate the distance between two coordinates

To calculate the distance between two coordinates, we need to useEarth_distance (ll_to_earth ($ latlngcube), ll_to_earth ($ latlng_cube ))This function. The earth_distance () function accepts two sets of coordinate values, and returns a value in meters. This can be used in many scenarios, such as finding the list of news events closest to it based on a specific location.

Here are several important functions: (reference: 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 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 longdistance (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 longlines 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 usingearth_distanceShocould be encoded in the query.

Database operations may be like the following:

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;

This will give us a nice news event list, sorted by their distance from our current location from near to far. The first one is closest to us.

5. Find records within a certain radius
Another great function provided by Cube and Earthdiatance expansion isEarth_box (ll_to_earch ($ latlngcub), $ radiusinmetres). Through a simple comparison, this function can find all records within a certain radius. It is achieved by returning the "large circle distance" between two points.

[Note] Great circle disstance refers to the length of the shortest path that passes through from one vertex A on the sphere to another vertex B on the sphere. Generally, any two points A and B on the sphere can be uniquely identified with the ball center. This large circle is called A column-related circle, the shorter arc connecting the two points on a large circle is the distance between the large circle. For more information, see Wikipedia: large circle distance.

It can be used to query all news events in our city:

SELECT events.id, events.name FROM events WHERE earth_box({currentuserlat}, {currentuserlng}, {radiusinmetres}) @> ll_to_earth(events.lat, events.lng);

This query statement only returns records within the radius specified by radius_in_metres. It is very simple!

6. improve query speed

You may find that the preceding query has a large cost. In my experience, it is best to index some fields. (The following statement assumes that you have an events table, and the events table has the fields lat and lng)

CREATE INDEX ${nameofindex} on events USING gits(lltoearth(lat, lng));

VII. Data Types

My application is relatively simple, so I set the longitude and latitude (lat and lng) to double type. This makes it faster for me to use Node. js, instead of customizing the GIST-type solution.

8. That's all!

Amazing, right ?!? We only use the commonly used data type (double) to create a social app based on geographical location with some GEO functions (A answer on zhihu )!

---------------------------
The English level is limited. If you have any poor translation skills, please give me some advice!

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

Update:

Summary of my postgreSQL statements:

/** Earthdistance of postgreSQL Study Notes * author: wusuopubupt * date: 2013-03-31 * // * create table */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 record */insert into picture (p_uid, p_key, lat, lng, up, down, ip, address) VALUES ('aaa Abbbbcccc ', '2014032008164023279.png', 40.043945, 116.413668, 0, 0, '','');/* INSERT record */insert into picture (p_uid, p_key, lat, lng, up, down, ip, address) VALUES ('xxxxccmmmmmm', '2014032008164023111.png ', 40.067183, 116.415230, 0, 0 ,'',''); /* SELECT record */SELECT * FROM picture;/* UPDATE record */UPDATE picture SET address = 'lishuiqiao 'WHERE id = 1; UPDATE picture SET address = 'tianygyuany' WHERE id = 2;/* CREATE an index for the longitude and latitude columns */CREATE INDEX ll_idx on picture USING gist (ll_to_earth (lat, lng);/* SELECT a record based on the radius (1000 meters) */SELECT * FROM picture where earth_box (ll_to_earth (40.059286, 116.418773), 1000) @> ll_to_earth (picture. lat, picture. lng);/* SELECT the distance from the current user */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;/** the following is an online tutorial * address: 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-avai Lable 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 (6378168.00000); earth --------------- (1 row) ---- Conver T straight line distances to great circle distances. convert the straight line distance to a large circle distance -- SELECT (pi () * earth (): numeric (20, 5); numeric -------------- 20037605.73216 (1 row) SELECT sec_to_gc (0 ):: numeric (0.00000); sec_to_gc ----------- (1 row) ---- Convert great circle distances to straight line distances. -- SELECT gc_to_sec (0): numeric (0.00000); gc_to_sec ----------- (1 row) SELECT gc_to_sec (sec_to_gc (2 * earth (): nu Meric (12756336.00000); gc_to_sec ---------------- (1 row) ---- Set coordinates using latitude and longpolling. -- Extract each coordinate separately so we can round them. -- SELECT cube_ll_coord (ll_to_earth (), 1): numeric (), cube_ll_coord (ll_to_earth (), 2): numeric ), cube_ll_coord (ll_to_earth (0, 0), 3): numeric (20, 5); 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 (360,360), cube_ll_coord (ll_to_earth (), 2 ):: numeric (360,360), round (ll_to_earth (6378168.00000), 3): numeric (0.00000); Round | round ----------------- + ------------- + --------------------- 0.00000 | (1 row) ---- Test getting the latitude 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 longpolling of a location. -- SELECT longpolling (ll_to_earth (0.0000000000): numeric (); longpolling -------------- (1 row) SELECT longpolling (ll_to_earth (): numeric ); Longpolling -------------- 0.0000000000 (1 row) ---- For the distance tests the following is some real life data. ---- Chicago has a latitude of 41.8 and a longpolling of 87. 6. -- Albuquerque has a latitude of 35.1 and a longpolling of 106. 7. -- (Note that latitude and longpolling are specified differently -- in the cube based functions than for the point based functions .) ------ Test getting the distance betwe En two points using earth_distance. -- SELECT earth_distance (ll_to_earth (0.00000), ll_to_earth (): numeric (); earth_distance limit (1 row) SELECT earth_distance (ll_to_earth ), ll_to_earth (0,180): numeric (20037605.73216); earth_distance -------------- (1 row) ---- Test getting the distance between two points using geo_distance. -- SELECT geo_distance ('(0, 0)': point, '(0, 0)': p Oint): numeric (0.00000); geo_distance -------------- (1 row) SELECT geo_distance ('()': point ):: numeric (12436.77274); geo_distance -------------- (1 row) ---- Test getting the distance between two points using the <@> operator. -- SELECT ('(0.00000)': point <@> '()': point): numeric (); numeric --------- (1 row) SELECT ('(0, 0)': point <@> '(180,0)': point): numeric (2 12436.77274); numeric ------------- (1 row) ---- Test for points that shocould be in bounding boxes. -- SELECT earth_box (ll_to_earth (1.00001), earth_distance (ll_to_earth (), ll_to_earth () *) @> ll_to_earth );? Column? ---------- T (1 row) SELECT earth_box (ll_to_earth (0.1), earth_distance (ll_to_earth (1.00001), ll_to_earth (0,) *) @> 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 (), earth_distance (ll_to_earth (), ll_to_earth ))*. 57735) @> ll_to_earth );? Column? ---------- F (1 row) SELECT earth_box (ll_to_earth (0.1), earth_distance (ll_to_earth (), ll_to_earth (0 ))*. 57735) @> ll_to_earth (0, 0.1 );? Column? ---------- F (1 row)