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