Using PostgreSQL database to do location app application _postgresql

Project used in the PostgreSQL earthdistance () function to calculate the distance between two points on the Earth, the Chinese information is too little, I found an English, speak a very good article, I hereby translate, hope to be able to help later use to earthdistance classmate.

It's never easy to do a geo application. But with some open source projects around you can easily solve the problem in a matter of minutes. PostgreSQL has many characteristics. Is my first choice, it can upgrade the database platform to another level.

One or two available options

When we want to use Postgres as the GEO function, we usually have 2 choices (as far as I know):
PostGIS: Provides advanced geo function functionality for PostgreSQL. I used it for a while, but it was too unwieldy for my needs.
Cube and earthdistance: These two extensions provide a simple, fast way to implement lightweight geo relational entities.

Second, why do calculations on the database server side

This is a very obvious thing. The server stores all the data, the server expansion is implemented with C/s + +, very fast. Indexing data tables can also speed up the calculation.

Iii. use my choice--cube and earthdistance

As a start, you should build a database (I think you know what to do) and then make them work with our architecture. Perform:

code as follows:

CREATE EXTENSION Cube;

Then execute:

code as follows:

CREATE EXTENSION earthdistance;

The above command creates about 40 functions, which we can use when we do the data query.
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 it ~ ~)

Four, calculate the distance between 2 coordinates

To calculate the distance between the 2 coordinates, we use the Earthdistance (Lltoearth ($latlngcube), Lltoearth ($latlng _cube) function. The Earthdistance () function accepts 2 sets of coordinate values, and returns a value in meters.

This can be used in a number of scenarios, such as finding a list of recent news events that occur from a location. The operation of the database may be as follows:

 code as follows:

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 very nice list of news events, sorted by their distance from our current position from near to far. The first one is closest to us.

v. Find a record within a radius

The other great function of cube and earthdiatance extension is Earthbox (Lltoearch ($latlngcub), $radiusinmetres). This function can be used to find all the records within a radius by a simple comparison. It is achieved by returning a "great circle distance" between 2 points.

"Translator note" The Great Circle distance (great circle disstance) refers to the length of the shortest path that is passed from a point a of the sphere to the other point B on the sphere. In general, any two points A and B on the sphere can be identified with the center of the circle, which is called Riemann, and the shorter arc connecting the two points on the great circle is a great distance. For more information, see the wiki: great distances

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

 code as follows:

SELECT events.id, events.name from Events WHERE Earthbox ({Currentuserlat}, {currentuserlng}, {radiusinmetres}) @> Llto_earth (Events.lat, EVENTS.LNG);

This query simply returns records in the radius specified by the Radius_ in_ metres, very simply!

Vi. improve the speed of query

You may find that the above query has a very small overhead. In my experience, it's best to index some fields. (The following statement assumes that you are in the events table and that the Events table has fields lat and LNG)
CREATE INDEX ${nameofindex} on Events USING Gits (Lltoearth (lat, LNG));

Vii. Types of data

My application is simpler, so I set the latitude (Lat and LNG) into double types. This allows me to develop faster with node.js without having to customize the solution for the gist type.

Eight, that's all!

It's amazing, right?!? We can use only the usual data types (double) to create a geographically based social app with some GEO functions

Nine, I use the PostgreSQL statement summary (use instance):

 code as follows:

/*
* PostgreSQL's Earthdistance learning notes
* Author:wusuopubupt
* DATE:2013-03-31
*/

/* CREATE TABLE/*
CREATE TABLE Picture (
ID serial PRIMARY KEY,
P_uid Char (a) not NULL,
P_key char (not NULL),
Lat Real is not NULL,
LNG Real is not NULL,
Up int is not NULL,
Down int is not NULL,
IP varchar () DEFAULT NULL,
Address varchar (256) DEFAULT NULL
);

/* Insert Record * *
INSERT into the picture (P_uid, P_key, lat, LNG, up, down, IP, address)
VALUES (' AAAABBBBCCCC ', ' 2014032008164023279.png ', 40.043945, 116.413668, 0, 0, ', ');

/* Insert Record * *
INSERT into the picture (P_uid, P_key, lat, LNG, up, down, IP, address)
VALUES (' xxxxccccmmmm ', ' 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= ' Tiantongyuan ' WHERE id=2;

/* CREATE index on latitude column * *
CREATE INDEX ll_idx on the picture USING gist (Ll_to_earth (lat, LNG));

* * According to the radius (1000 meters) Select Record * *
SELECT * from the picture where Earth_box (Ll_to_earth (40.059286,116.418773), 1000) @> Ll_to_earth (Picture.lat, PICTURE.LNG);

/* Select 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 the picture
Order by Dis ASC;

/*
* The following content is a tutorial on the Web
* 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 ' 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
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. Turn a straight distance into a great circle
--
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)

--
--the 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) '::p oint, ' (0,0) '::p oint):: Numeric (20,5);
Geo_distance
--------------
0.00000
(1 row)

SELECT geo_distance (' (0,0) '::p oint, ' (180,0) '::p oint):: Numeric (20,5);
Geo_distance
--------------
12436.77274
(1 row)

--
--Test getting the distance between two points using the <@> operator.
--
SELECT (' (0,0) '::p oint <@> ' (0,0) '::p oint):: Numeric (20,5);
Numeric
---------
0.00000
(1 row)

SELECT (' (0,0) '::p oint <@> ' (180,0) '::p oint):: Numeric (20,5);
Numeric
-------------
12436.77274
(1 row)

--
--Test for points that should is 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)

--
--The Test for points that shouldn ' t is in bounding boxes. Note that we need
--to make points way outside, since some points
--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)