ST_Distance

ST_Distance 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, measured in the default or given units.

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, it will be converted to the other spatial reference system.

Note: After you apply APAR PM92224, the results that are returned by the ST_Distance function might be different than before the APAR was applied, unless the function uses point data exclusively.

Syntax

Parameter

geometry1: A value of one of the seven distinct spatial data types that represents the geometry that is used to compute the distance to geometry2.
geometry2: A value of one of the seven distinct spatial data types 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 DB2GSE.ST_UNITS_OF_MEASURE catalog view.

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.

If the geometry is in a geographic coordinate system, you can specify a linear unit as the value.

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.


SET CURRENT PATH = CURRENT PATH, db2gse; 

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_GEOMETRY(ST_Point('point(100 100)', 1) ));

INSERT INTO sample_geometries1(id, spatial_type, geometry)
VALUES
    (10, 'ST_LineString', ST_GEOMETRY(ST_LineString('linestring(125 125, 
                          125 175)', 1) ));

INSERT INTO sample_geometries1(id, spatial_type, geometry)
VALUES
    (20, 'ST_Polygon', ST_GEOMETRY(ST_Polygon('polygon
                       ((50 50, 50 150, 150 150, 150 50, 50 50))', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (101, 'ST_Point', ST_GEOMETRY(ST_Point('point(200 200)', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (102, 'ST_Point', ST_GEOMETRY(ST_Point('point(200 300)', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (103, 'ST_Point', ST_GEOMETRY(ST_Point('point(200 0)', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (110, 'ST_LineString', ST_GEOMETRY(ST_LineString('linestring(200 100, 
                           200 200)', 1) ));

INSERT INTO sample_geometries2(id, spatial_type, geometry)
VALUES
    (120, 'ST_Polygon', ST_GEOMETRY(ST_Polygon('polygon
                        ((200 0, 200 200, 300 200, 300 0, 200 0))', 1) ));

Example 2

The following SELECT statement calculates the distance between the various geometries in the SAMPLE_GEOMETRIES1 and SAMPLE_GEOMETRIES2 tables.


SELECT   sg1.id AS sg1_id, sg1.spatial_type AS sg1_type,
         sg2.id AS sg1_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;

Results:


SG1_ID SG1_TYPE      SG1_ID SG2_TYPE      DISTANCE
------ ------------- ------ ------------- ----------
     1 ST_Point         101 ST_Point        141.4213
     1 ST_Point         102 ST_Point        223.6067
     1 ST_Point         103 ST_Point        141.4213
     1 ST_Point         110 ST_LineString   100.0000
     1 ST_Point         120 ST_Polygon      100.0000
    10 ST_LineString    101 ST_Point         79.0569
    10 ST_LineString    102 ST_Point        145.7737
    10 ST_LineString    103 ST_Point        145.7737
    10 ST_LineString    110 ST_LineString    75.0000
    10 ST_LineString    120 ST_Polygon       75.0000
    20 ST_Polygon       101 ST_Point         70.7106
    20 ST_Polygon       102 ST_Point        158.1138
    20 ST_Polygon       103 ST_Point         70.7106
    20 ST_Polygon       110 ST_LineString    50.0000
    20 ST_Polygon       120 ST_Polygon       50.0000

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 sg1_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;

Results:


SG1_ID SG1_TYPE      SG1_ID SG2_TYPE      DISTANCE
------ ------------- ------ ------------- ----------
     1 ST_Point         110 ST_LineString   100.0000
     1 ST_Point         120 ST_Polygon      100.0000
    10 ST_LineString    101 ST_Point         79.0569
    10 ST_LineString    110 ST_LineString    75.0000
    10 ST_LineString    120 ST_Polygon       75.0000
    20 ST_Polygon       101 ST_Point         70.7106
    20 ST_Polygon       103 ST_Point         70.7106
    20 ST_Polygon       110 ST_LineString    50.0000
    20 ST_Polygon       120 ST_Polygon       50.0000

Example 4

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


SAMPLE_GEOMETRIES1 and SAMPLE_GEOMETRIES2 tables.
SELECT   sg1.id AS sg1_id, sg1.spatial_type AS sg1_type,
         sg2.id AS sg1_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;

Results:


SG1_ID SG1_TYPE      SG1_ID SG2_TYPE      DISTANCE
------ ------------- ------ ------------- ------------
     1 ST_Point         101 ST_Point        12373.2168
     1 ST_Point         102 ST_Point        16311.3816
     1 ST_Point         103 ST_Point         9809.4713
     1 ST_Point         110 ST_LineString    1707.4463
     1 ST_Point         120 ST_Polygon      12373.2168
    10 ST_LineString    101 ST_Point         8648.2333
    10 ST_LineString    102 ST_Point        11317.3934
    10 ST_LineString    103 ST_Point        10959.7313
    10 ST_LineString    110 ST_LineString    3753.5862
    10 ST_LineString    120 ST_Polygon      10891.1254
    20 ST_Polygon       101 ST_Point         7700.5333
    20 ST_Polygon       102 ST_Point        15039.8109
    20 ST_Polygon       103 ST_Point         7284.8552
    20 ST_Polygon       110 ST_LineString    6001.8407
    20 ST_Polygon       120 ST_Polygon      14515.8872