Use postgreSQL for geo-location-based Apps

The earthdistance () function of postgreSQL is used in the project to calculate the distance between two points on the earth. There are too few Chinese materials, I have found a good English article, and I want to translate it to help my classmates who will use earthdistance in the future. I. Two available options when we want to use S as the GEO Function

The earthdistance () function of postgreSQL is used in the project to calculate the distance between two points on the earth. There are too few Chinese materials, I have found a good English article, and I want to translate it to help my classmates who will use earthdistance in the future. I. Two available options when we want to use S as the GEO Function

The earthdistance () function of postgreSQL is used in the project to calculate the distance between two points on the earth. There are too few Chinese materials, I have found a good English article, and I want to translate it to help my classmates who will use earthdistance in the future.

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

1. PostGIS: Provides advanced GEO functions for postgreSQL. I used it for a while, but it is too heavy for my needs.

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

?

1

2

Create extension cube;

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 earth_distance (ll_to_earth ($ latlngcube), ll_to_earth ($ latlng_cube) function. The earth_distance () 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.

Note: (for more information, see :)

Table F-3. Cube-based earthdistance functions

FunctionReturnsDescription

Earth () float8Returns the assumed radius of the Earth.

Sec_to_gc (float8) float8Converts the normal straight line (secant) distance between two points on the surface of the Earth to the great circle distance between them.

Gc_to_sec (float8) float8Converts the great circle distance between two points on the surface of the Earth to the normal straight line (secant) distance between them.

Ll_to_earth (float8, float8) earthReturns the location of a point on the surface of the Earth given its latitude (argument 1) and longdistance (argument 2) in degrees.

Latitude (earth) float8Returns the latitude in degrees of a point on the surface of the Earth.

Longpolling (earth) float8Returns the longpolling in degrees of a point on the surface of the Earth.

Earth_distance (earth, earth) float8Returns the great circle distance between two points on the surface of the Earth.

Earth_box (earth, float8) cubeReturns a box suitable for an 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 from the location, so a second check using earth_distance shocould be stored in the query.

Database operations may be like the following:

?

1

2

3

SELECT events. id events. name, eaerthdiatance (ll_to_earth ({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 earth_box (ll_to_earch ($ 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:

?

1

2

SELECT events. id, events. name FROM events

WHERE earth_box ({currentuserlat}, {currentuserlng}, {radiusinmetres}) @> ll_to_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)

?

1

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 the commonly used data type (double) to create a social app based on geographical location with some GEO functions (A answer on zhihu )!

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

The English level is limited. If you have any poor translation skills, please give me some advice!