Functions for geodata

Functions for geospatial data and GIS tools.

ST_Point()

Description

Creates a point of type GEOMETRY.
Usage

ST_Point(num, num)

Creates a point of type GEOMETRY from two numbers of type DOUBLE. The arguments can be, for example, geographic coordinates given in degrees as a decimal fraction.

Some usage examples are described in this script.

See example

Create a table with names of organisations and coordinates of their offices. Calculate the distance from zero kilometre of Moscow (55.75579845052788, 37.617679973467204) to the offices of these organisations and display this information in the form of a table:

CREATE TABLE demo.table (organization VARCHAR,
                        latitude DOUBLE,
                        longitude DOUBLE);

INSERT INTO demo.table VALUES
    ('Postgres Professional LLC', 55.69189394353437, 37.564623398131985),
    ('Oracle Corporation', 30.243622717202587, -97.72199761339736);

SELECT
    organization,
    round(ST_Distance_Sphere(ST_Point(latitude, longitude),
                             ST_Point(55.75579845052788, 37.617679973467204)
                            )/1000)
    AS "distance from moscow, km"
FROM demo.table;
+---------------------------+--------------------------+
| organisation | distance from moscow, km |
+---------------------------+--------------------------+
| Postgres Professional LLC | 8 |
+---------------------------+--------------------------+
| Oracle Corporation | 9557 |
+---------------------------+--------------------------+

ST_Distance()

Description

Calculates the distance between two points in the plane.
Usage

ST_Distance(GEOMETRY, GEOMETRY)

Calculates the distance in the plane between two points of type GEOMETRY.

See example 
SELECT
    ST_Distance('POINT (0 0)'::GEOMETRY, 'POINT (5 12)'::GEOMETRY)
        AS distance;
+----------+
| distance |
+----------+
| 13       |
+----------+

ST_Distance_Sphere()

Description

Calculates the distance between two points on the sphere.
Usage

ST_Distance_Sphere(GEOMETRY, GEOMETRY)

Calculates the haversine (great circle of the sphere) distance between two points of type GEOMETRY.

Returns the distance in metres. Input data must be in coordinates given in degrees as a decimal fraction (WGS84 format, EPSG:4326) with axis order: latitude, longitude.

Some usage examples are described in this script.

See example

Create a table with names of organisations and coordinates of their offices. Calculate the distance from zero kilometre of Moscow (55.75579845052788, 37.617679973467204) to the offices of these organisations and display this information in the form of a table:

CREATE TABLE demo.table (organization VARCHAR,
                        latitude DOUBLE,
                        longitude DOUBLE);

INSERT INTO demo.table VALUES
    ('Postgres Professional LLC', 55.69189394353437, 37.564623398131985),
    ('Oracle Corporation', 30.243622717202587, -97.72199761339736);

SELECT
    organization,
    round(ST_Distance_Sphere(ST_Point(latitude, longitude),
                             ST_Point(55.75579845052788, 37.617679973467204)
                            )/1000)
    AS "distance from moscow, km"
FROM demo.table;
+---------------------------+--------------------------+
| organisation | distance from moscow, km |
+---------------------------+--------------------------+
| Postgres Professional LLC | 8 |
+---------------------------+--------------------------+
| Oracle Corporation | 9557 |
+---------------------------+--------------------------+

See also