Using PostgreSQL to do location-based apps

Preface: The Project uses the Earthdistance () function function in PostgreSQL to calculate the distance between two points on the Earth, the Chinese information is too few, I found an English, speak very good article, hereby translates, Hope to be able to help later use to earthdistance classmate.

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):

1.PostGIS: Provides advanced geo function functionality for PostgreSQL. I used it for a while, but it was too unwieldy for my needs.

2.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:

?

1 2	CREATE EXTENSION Cube; 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 Earth_distance (Ll_to_earth ($latlngcube), Ll_to_earth ($latlng _cube) function. The Earth_distance () 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.

"Translator note" Here to mention a few 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 between two points on the surface of the great CI Rcle distance between them.
Gc_to_sec (FLOAT8)	Float8	Converts the great circle distance between two points on the surface of the "Earth" to "normal straight line (secant) dis Tance 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 longitude (argument 2) in Degrees.
Latitude (Earth)	Float8	Returns the latitude in degrees's a point on the surface of the Earth.
Longitude (Earth)	Float8	Returns the longitude in degrees's a point on the surface of the Earth.
Earth_distance (Earth and Earth)	Float8	Returns the great circle distance between two points on the "Earth".
Earth_box (Earth, FLOAT8)	Cube	Returns a box suitable for a 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 to the location, so a second check using EA Rth_distance should is included in the query.

The operation of the database may be as follows:

?

1 2 3

SELECT events.id Events.name, Eaerthdiatance (Ll_to_earth ({Currentuserlat}, {currentuserlng}), Llto_earth (Events.lat , EVENTS.LNG)) as distancefromcurrentlocation from the 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 Earth_box (Ll_to_earch ($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:

?

1 2	SELECT events.id, events.name from Events WHERE Earth_box ({Currentuserlat}, {currentuserlng}, {radiusinmetres}) @> ll _to_earth (Events.lat, EVENTS.LNG);

This query will only return records within the radius specified by radius_in_metres, very simple!

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)

?

1	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, isn't it?!? We can use only the usual data types (double) to create a geographically based social app with some geo functions (a "translator" knows the answer)!

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

English Proficiency is limited, if there is a poor translation, please advise!