Categories:: Geospatial functions

ST_DISTANCE¶

Returns the minimum geodesic distance between two GEOGRAPHY or the minimum Euclidean distance between two GEOMETRY objects.

Syntax¶

ST_DISTANCE( <geography_or_geometry_expression_1> , <geography_or_geometry_expression_2> )

Copy

Arguments¶

geography_or_geometry_expression_1: The argument must be of type GEOGRAPHY or GEOMETRY.
geography_or_geometry_expression_2: The argument must be of type GEOGRAPHY or GEOMETRY.

Returns¶

Returns a REAL value, which represents the distance:

For GEOGRAPHY input values, the distance is in meters.
For GEOMETRY input values, the distance is computed with the same units used to define the input coordinates.

Usage notes¶

Returns NULL if one or more input points are NULL.

For GEOMETRY objects, the function reports an error if the two input GEOMETRY objects have different SRIDs.

Examples¶

GEOGRAPHY examples¶

This shows the distance in meters between two points 1 degree apart along the equator (approximately 111 kilometers or 69 miles).

WITH d AS
    ( ST_DISTANCE(ST_MAKEPOINT(0, 0), ST_MAKEPOINT(1, 0)) )
SELECT d / 1000 AS kilometers, d / 1609 AS miles;
+---------------+--------------+
|    KILOMETERS |        MILES |
|---------------+--------------|
| 111.195101177 | 69.108204585 |
+---------------+--------------+

Copy

This shows use of the ST_DISTANCE function with NULL values:

SELECT ST_DISTANCE(ST_MAKEPOINT(0, 0), ST_MAKEPOINT(NULL, NULL));
+-----------------------------------------------------------+
| ST_DISTANCE(ST_MAKEPOINT(0, 0), ST_MAKEPOINT(NULL, NULL)) |
|-----------------------------------------------------------|
|                                                      NULL |
+-----------------------------------------------------------+

Copy

GEOMETRY examples¶

The following example compares the distance calculated for GEOGRAPHY and GEOMETRY input objects.

SELECT ST_DISTANCE(TO_GEOMETRY('POINT(0 0)'), TO_GEOMETRY('POINT(1 1)')) AS geometry_distance,
       ST_DISTANCE(TO_GEOGRAPHY('POINT(0 0)'), TO_GEOGRAPHY('POINT(1 1)')) AS geography_distance;

Copy

+-------------------+--------------------+
| GEOMETRY_DISTANCE | GEOGRAPHY_DISTANCE |
|-------------------+--------------------|
|       1.414213562 |   157249.628092508 |
+-------------------+--------------------+

Copy