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_expression_1> , <geography_expression_2> )

Arguments

geography_expression_1

The argument must be an expression of type GEOGRAPHY.

geography_expression_2

The argument must be an expression of type GEOGRAPHY.

Returns

Returns a value of type REAL that represents distance in meters.

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 |
+---------------+--------------+

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 |
+-----------------------------------------------------------+

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;
+-------------------+--------------------+
| GEOMETRY_DISTANCE | GEOGRAPHY_DISTANCE |
|-------------------+--------------------|
|       1.414213562 |   157249.628092508 |
+-------------------+--------------------+
Back to top