Use PostgreSQL database for geographic location app applications

The earthdistance() function in postgreSQL was used in the project to calculate the distance between two points on the earth. There is too little information in Chinese, so I found an English article  , which is well spoken, and I hereby translate it, hoping to help those students who use earthdistance in the future.

Making an GEO app has never been easy. But you can easily solve this problem in a few minutes with some open source projects around you. PostgreSQL has many features. It's my first choice to take the database platform to another level.

One or two available options are

When we want to use Postgres as the GEO function, we usually have 2 choices (as far as I know) :
PostGIS: advanced GEO functions are provided for postgreSQL. I used it for a while, but it was too heavy for my needs.
Cube and Earthdistance: these two extensions provide an easy and fast way to implement lightweight Geo relationship entities.

ii. Why do calculations on the database server side

It's a pretty obvious thing. The server stores all the data, and the server expansion is implemented with C/C++, which is very fast. Indexing tables can also speed up calculations.

3. Use my choice --Cube and EarthDistance

To start, you should build a database (I think you know how to do this) and then make them work with our architecture. Execution:

CREATE EXTENSION cube;

Then execute:

CREATE EXTENSION earthdistance;

The above command creates about 40 functions that we can use for future data queries.
In our example, I created a table named events with fields id(serial), name(varchar 255), lat(double), lng(double). (don't forget ~~)

Calculate the distance between the two coordinates

To calculate the distance between the two coordinates, we use the function earthdistance(lltoearth($latlngcube), lltoearth($latlng_cube)). The earthdistance() function takes two sets of coordinates and returns a value in meters.

This can be used in a number of scenarios, such as finding a list of news events closest to a location. The database operation might look like this:

SELECT events.id events.name, eaerthdiatance(lltoearth({currentuserlat}, {currentuserlng}), llto_earth(events.lat, events.lng)) 
as distancefromcurrentlocation FROM events 
ORDER BY distancefromcurretnlocation ASC;

This will give us a list of news events in nice, sorted from near to far from our current location. The first is nearest to us.

5. Find records within a certain radius
Another great function provided by the Cube and Earthdiatance extensions is earthbox(lltoearch($latlngcub), $radiusinmetres). This function simply compares to find all records within a certain radius. It is achieved by returning the "great circle distance" between two points.

The great circle distance (Great circle disstance) is the length of the shortest path from A at one point on the sphere to B at another point on the sphere. In general, any two points on a sphere, A and B, can be identified with the center of the sphere as a unique great circle, which is called a Riemann circle, and the length of a shorter arc connecting the two points on the great circle is the distance of the great circle. For more information, see wiki: great circle distance

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

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

This query only returns records within the radius specified by radius_ in_ metres.

6, improve the query speed

You may find the above query expensive. In my experience, it is best to index some fields. The following statement assumes that you have events, and events has the fields lat and lng CREATE INDEX ${nameofindex} on events USING gits(lltoearth(lat, lng));

7. Data type

My application is relatively simple, so I set the latitude and longitude (lat and lng) to double. This allowed me to develop Node.js more quickly without having to customize my own GIST type of solution.

That's all!

Amazing, right? ! ? Just using the usual data types (double) is enough to create location-based social app
with some GEO functions
Nine, I use postgreSQL statement summary (use example) :

/*
* postgreSQL the earthdistance Learning notes 
* author: wusuopubupt
* date: 2013-03-31
*/
/* Create a 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 records */
INSERT INTO picture(p_uid, p_key, lat, lng, up, down, ip, address) 
VALUES('aaaabbbbcccc', '2014032008164023279.png', 40.043945, 116.413668, 0, 0, '', '');
/* Insert records */
INSERT INTO picture(p_uid, p_key, lat, lng, up, down, ip, address) 
VALUES('xxxxccccmmmm', '2014032008164023111.png', 40.067183, 116.415230, 0, 0, '', '');
/* Select records */
SELECT * FROM picture;
/* Update record */
UPDATE picture SET address='LiShuiqiao' WHERE id=1;
UPDATE picture SET address='TianTongyuan' WHERE id=2;
/* Index the latitude and longitude columns */
CREATE INDEX ll_idx on picture USING gist(ll_to_earth(lat, lng));
/* According to the radius ( 1000 M) select records */
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-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. Let's turn the straight line distance into the big circle distance 
--
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)