Use a PostgreSQL database for geo-location app

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.

It is never easy to make a GEO application. However, with some open-source projects around you, you can easily solve this problem within a few minutes. PostgreSQL has many features. Is my first choice. It can promote the database platform to another level.

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 ):
PostGIS: Provides advanced GEO functions for postgreSQL. I used it for a while, but it is too heavy for my needs.
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:
Copy codeThe Code is as follows: create extension cube;
Then execute:
Copy codeThe Code is as follows: 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, use the earthdistance (lltoearth ($ latlngcube) and lltoearth ($ latlng_cube) functions. The earthdistance () 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. Database operations may be like the following:
Copy codeThe Code is 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 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 is earthbox (lltoearch ($ 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:
Copy codeThe Code is as follows: SELECT events. id, events. name FROM events WHERE earthbox ({currentuserlat}, {currentuserlng}, {radiusinmetres}) @> llto_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 common data types (double) to create GEO-based social apps.

9. Summary of the postgreSQL statements I use (using instances ):

Copy codeThe Code is as follows :/*
* PostgreSQL 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 record */
Insert into picture (p_uid, p_key, lat, lng, up, down, ip, address)
VALUES ('aaaabbbbcccccc', '2014032008164023279.png ', 40.043945, 116.413668, 0, 0 ,'','');

/* Insert record */
Insert into picture (p_uid, p_key, lat, lng, up, down, ip, address)
VALUES ('xxxxccmmmm', '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 = 'tianygyua' 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-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. converts 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 (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 longpolling.
-- 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 (), 2): numeric ),
Cube_ll_coord (ll_to_earth (), 3): numeric );
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 ),
Cube_ll_coord (ll_to_earth (360,360), 2): numeric ),
Cube_ll_coord (ll_to_earth (360,360), 3): numeric );
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 longpolling of a location.
--
SELECT longpolling (ll_to_earth (0, 0): numeric (20, 10 );
Longpolling
--------------
0.0000000000
(1 row)

SELECT longpolling (ll_to_earth (45, 0): numeric (20, 10 );
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 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 ('()': point, '()': point): numeric );
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 ('()': point <@> '()': point): numeric );
Numeric
-------------
12436.77274
(1 row)

--
-- Test for points that shoshould be in bounding boxes.
--
SELECT earth_box (ll_to_earth (0, 0 ),
Earth_distance (ll_to_earth (1.00001), ll_to_earth () *) @>
Ll_to_earth (0, 1 );
? Column?
----------
T
(1 row)

SELECT earth_box (ll_to_earth (0, 0 ),
Earth_distance (ll_to_earth (0.1), ll_to_earth (0, 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 (57735), ll_to_earth () *.) @>
Ll_to_earth (0, 1 );
? Column?
----------
F
(1 row)

SELECT earth_box (ll_to_earth (0, 0 ),
Earth_distance (ll_to_earth (0.1), ll_to_earth (0, 57735) *.) @>
Ll_to_earth (0, 0.1 );
? Column?
----------
F
(1 row)