ST_Distance function

The ST_Distance function takes two geometries and, optionally, a unit as input parameters and returns the shortest distance between any point in the first geometry to any point in the second geometry.

If any of the two geometries is null or is empty, null is returned.

If the second geometry is not represented in the same spatial reference system as the first geometry and uses the same underlying datum, it will be converted to the other spatial reference system.

Syntax

Parameter

geometry1: A value of type ST_Geometry that represents the geometry that is used to compute the distance to geometry2.
geometry2: A value of type ST_Geometry that represents the geometry that is used to compute the distance to geometry1.
unit: VARCHAR(128) value that identifies the unit in which the result is measured. The supported units of measure are listed in the SYSGEO.ST_UNITS_OF_MEASURE catalog view.
intersectsTest: Set this flag to 1 to check whether geometry1 and geometry2 intersect instead of calculating the distance. When intersectsTest is specified, the function returns 1 if the geometries intersect and 0 if they do not.

If the unit parameter is omitted, the following rules are used to determine the unit of measure used for the result:

If geometry1 is in a projected or geocentric coordinate system, the linear unit associated with this coordinate system is the default.
If geometry1 is in a geographic coordinate system, the angular unit associated with this coordinate system is the default.

Restrictions on unit conversions: An error (SQLSTATE 38SU4) is returned if any of the following conditions occur:

The geometry is in an unspecified coordinate system and the unit parameter is specified.
The geometry is in a projected coordinate system and an angular unit is specified.

Return type

DOUBLE

Examples

Example 1

The following SQL statements create and populate the SAMPLE_GEOMETRIES1 and SAMPLE_GEOMETRIES2 tables.



CREATE TABLE sample_geometries1(id SMALLINT, spatial_type varchar(13),
   geometry ST_GEOMETRY)

CREATE TABLE sample_geometries2(id SMALLINT, spatial_type varchar(13),
   geometry ST_GEOMETRY)

INSERT INTO sample_geometries1(id, spatial_type, geometry)
VALUES
  ( 1, 'ST_Point', ST_Point('point(100 10)', 1)),
  (10, 'ST_LineString', ST_LineString('linestring(125 25, 125 75)', 1)),
  (20, 'ST_Polygon', ST_Polygon('polygon
                       ((50 50, 50 75, 150 75, 150 50, 50 50))', 1))

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
  (101, 'ST_Point', ST_Point('point(20 20)', 1)),
  (102, 'ST_Point', ST_Point('point(20 30)', 1)),
  (103, 'ST_Point', ST_Point('point(20 0)', 1)),
  (110, 'ST_LineString', ST_LineString('linestring(20 10, 20 20)', 1)),
  (120, 'ST_Polygon', ST_Polygon('polygon
                       ((20 0, 20 20, 30 20, 30 0, 20 0))', 1))

Example 2

The following SELECT statement calculates the distance between the various geometries in the SAMPLE_GEOMTRIES1 and SAMPLE_GEOMTRIES2 tables.


SELECT   sg1.id AS sg1_id, sg1.spatial_type AS sg1_type,
         sg2.id AS sg2_id, sg2.spatial_type AS sg2_type,
         cast(ST_Distance(sg1.geometry, sg2.geometry) 
         AS Decimal(8, 4)) AS distance 
FROM     sample_geometries1 sg1, sample_geometries2 sg2
ORDER BY sg1.id, sg2.id

Results:


SG1_ID SG1_TYPE      SG2_ID SG2_TYPE      DISTANCE
------ ------------- ------ ------------- ----------
     1 ST_Point         101 ST_Point         80.6225
     1 ST_Point         102 ST_Point         82.4621
     1 ST_Point         103 ST_Point         80.6225
     1 ST_Point         110 ST_LineString    80.0000
     1 ST_Point         120 ST_Polygon       70.0000
    10 ST_LineString    101 ST_Point        105.1189
    10 ST_LineString    102 ST_Point        105.0000
    10 ST_LineString    103 ST_Point        107.9351
    10 ST_LineString    110 ST_LineString   105.1189
    10 ST_LineString    120 ST_Polygon       95.1314
    20 ST_Polygon       101 ST_Point         42.4264
    20 ST_Polygon       102 ST_Point         36.0555
    20 ST_Polygon       103 ST_Point         58.3095
    20 ST_Polygon       110 ST_LineString    42.4264
    20 ST_Polygon       120 ST_Polygon       36.0555
   
15 record(s) selected.

Example 3

The following SELECT statement illustrates how to find all the geometries that are within a distance of 100 of each other.


SELECT   sg1.id AS sg1_id, sg1.spatial_type AS sg1_type,
         sg2.id AS sg2_id, sg2.spatial_type AS sg2_type,
         cast(ST_Distance(sg1.geometry, sg2.geometry) 
         AS Decimal(8, 4)) AS distance
FROM     sample_geometries1 sg1, sample_geometries2 sg2
WHERE    ST_Distance(sg1.geometry, sg2.geometry)  <= 100
ORDER BY distance desc, sg1_id, sg2_id

Results:


SG1_ID SG1_TYPE      SG2_ID SG2_TYPE      DISTANCE
------ ------------- ------ ------------- ----------
    10 ST_LineString    120 ST_Polygon       95.1314
     1 ST_Point         102 ST_Point         82.4621
     1 ST_Point         101 ST_Point         80.6225
     1 ST_Point         103 ST_Point         80.6225
     1 ST_Point         110 ST_LineString    80.0000
     1 ST_Point         120 ST_Polygon       70.0000
    20 ST_Polygon       103 ST_Point         58.3095
    20 ST_Polygon       101 ST_Point         42.4264
    20 ST_Polygon       110 ST_LineString    42.4264
    20 ST_Polygon       102 ST_Point         36.0555
    20 ST_Polygon       120 ST_Polygon       36.0555

11 record(s) selected.

Example 4

The following SELECT statement calculates the distance in kilometers between the various geometries.


SELECT   sg1.id AS sg1_id, sg1.spatial_type AS sg1_type,
         sg2.id AS sg2_id, sg2.spatial_type AS sg2_type,
         cast(ST_Distance(sg1.geometry, sg2.geometry, 'KILOMETER')
         AS DECIMAL(10, 4)) AS distance
FROM     sample_geometries1 sg1, sample_geometries2 sg2
ORDER BY sg1.id, sg2.id

Results:


SG1_ID SG1_TYPE      SG2_ID SG2_TYPE      DISTANCE
------ ------------- ------ ------------- ------------
     1 ST_Point         101 ST_Point         8604.4231
     1 ST_Point         102 ST_Point         8506.1994
     1 ST_Point         103 ST_Point         8922.0124
     1 ST_Point         110 ST_LineString    8604.4231
     1 ST_Point         120 ST_Polygon       7561.4960
    10 ST_LineString    101 ST_Point         8291.6805
    10 ST_LineString    102 ST_Point         7224.3487
    10 ST_LineString    103 ST_Point        10431.9415
    10 ST_LineString    110 ST_LineString    8291.6805
    10 ST_LineString    120 ST_Polygon       8012.7545
    20 ST_Polygon       101 ST_Point         4253.2601
    20 ST_Polygon       102 ST_Point         3350.0057
    20 ST_Polygon       103 ST_Point         6231.7636
    20 ST_Polygon       110 ST_LineString    4253.2601
    20 ST_Polygon       120 ST_Polygon       3769.5441

15 record(s) selected.