The earthdistance() function in postgreSQL was used in the project to calculate the distance between two points on the earth. There is too little Chinese information. I found an English-speaking article. I will translate it here. I hope it can help students who use earthdistance in the future.
Making a GEO application is never easy. But using some open source projects around you can easily solve this problem in a few minutes. PostgreSQL has many features. It is my first choice, it can take the database platform to another level.
1. Two available options
When we want to use Postgres as a GEO function, we usually have 2 choices (as far as I know):
PostGIS: Provides advanced GEO function functionality for postgreSQL. I've used it for a while, but it's too bulky for my needs.
Cube and Earthdistance: These two expansions provide a simple and fast implementation method for lightweight Geo relational entities.
2. Why do calculations on the database server side
This is a very obvious thing. The server stores all the data, and server expansion is implemented in C/C++, which is very fast. Indexing data tables can also speed up calculations.
3. Use my choice--Cube and EarthDistance
To start, you should build a database (I think you know what to do) and then make them work with our architecture. implement:
Then execute:
The above command creates about 40 functions, which can be used when we do data queries in the future.
In our example, I created a table named events with fields: id(serial), name(varchar 255), lat(double), lng(double). (Don't forget~~)
4. Calculate the distance between two coordinates
To calculate the distance between two coordinates, we need to use the function earthdistance(lltoearth($latlngcube), lltoearth($latlng_cube)). 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 a list of news events that are closest to it based on a location. The operation of the database may look like this:
SELECT , eaerthdiatance(lltoearth({currentuserlat}, {currentuserlng}), llto_earth(, ))
as distancefromcurrentlocation FROM events
ORDER BY distancefromcurretnlocation ASC;
This will give us a very nice list of news events, sorted from near to far by their distance from our current location. The first one is the closest to us.
5. Find records within a certain radius
Another great function provided by Cube and Earthdiatance extensions is earthbox(lltoearch($latlngcub), $radiusinmetres) . This function can find all records within a certain radius through simple comparison. It is achieved by returning the "big circle distance" between 2 points.
[Translator's Note] The Great circle distance refers to the length of the shortest path through which it takes from point A of the sphere to point B on the sphere. Generally speaking, any two points A and B on the sphere can determine the only great circle with the center of the sphere. This great circle is called the Riemann circle, and the length of the shorter arc connecting these two points on the sphere is the great circle distance. If you want to know more, please see the wiki: Great Circle Distance
It can be used to query all news events in our city:
This query statement will only return records within the radius specified by radius_in_metres, which is very simple!
6. Improve query speed
You may find that the above query has considerable overhead. In my experience, it is best to index some fields. (The following statement assumes that you have events table again, and the events table has 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 both lat and longitude (lat and lng) to double types. This makes me develop faster without having to customize solutions for GIST types by myself.
8. That’s all!
Very magical, right? ! ? We just use common data types (double) to create a geographic social app using some GEO functions
9. Summary of postgreSQL statements I use (using example):
* postgreSQL earthdistance 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('aaaabbbbcccc', '', 40.043945, 116.413668, 0, 0, '', '');
/*Insert record*/
INSERT INTO picture(p_uid, p_key, lat, lng, up, down, ip, address)
VALUES('xxxxccccmmmm', '', 40.067183, 116.415230, 0, 0, '', '');
/*Select record*/
SELECT * FROM picture;
/*Update history*/
UPDATE picture SET address='LiShuiqiao' WHERE id=1;
UPDATE picture SET address='TianTongyuan' WHERE id=2;
/*Create indexes for latitude and longitude columns*/
CREATE INDEX ll_idx on picture USING gist(ll_to_earth(lat, lng));
/*Select record based on radius (1000 meters)*/
SELECT * FROM picture where earth_box(ll_to_earth(40.059286,116.418773),1000) @> ll_to_earth(, );
/*Select the distance from the current user*/
SELECT , earth_distance(ll_to_earth(, ), ll_to_earth(40.059286,116.418773))
AS dis FROM picture
ORDER BY dis ASC;
/*
* The following is a tutorial online
* Address: /dbms/Data/Courses/CS631/PostgreSQL-Resources/postgresql-9.2.4/contrib/earthdistance/expected/
*/
--
-- 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.
--
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)