Syntax

ST_Distance(g1 ST_Geometry, g2 ST_Geometry)
ST_Distance(g1 ST_Geometry, g2 ST_Geometry, linear_uom varchar(128))Копировать

The linear_uom parameter converts the result to the specified unit of measure. To calculate the distance if the geometries are in a geographic coordinate system where the coordinates are in an angular unit of measure, you must specify a linear unit of measure with the linear_uom parameter. Angular units of measure are converted to linear units of measure by great-circle calculations. If the geometries are in a projected coordinate system that has a unit of measure that is different from the unit of measure that is specified by the linear_uom parameter, then the returned value is converted to the unit of measure that is specified by the linear_uom parameter. The linear_uom parameter must be the name of a linear unit of measure from the unit_name column of the st_units_of_measure table.

Return type

DOUBLE PRECISION

Example: List buildings within a foot of a lot line

The city engineer needs a list of all buildings within one foot of any lot line.

The building_id column of the buildingfootprints table uniquely identifies each building. The lot_id column identifies the lot each building belongs to. The footprints multipolygon stores the geometry of each building’s footprint:

CREATE TABLE buildingfootprints (building_id  integer,
                                 lot_id       integer,
                                 footprint    ST_MultiPolygon);Копировать

The lots table stores the lot_id that uniquely identifies each lot and the lot ST_MultiPolygon that contains the lot geometry:

CREATE TABLE lots (lot_id  integer,
                   lot     ST_MultiPolygon);Копировать

The following query returns a list of building IDs that are within one foot of their lot lines. The ST_Distance() function performs a spatial join on the footprints and lot ST_MultiPolygon columns. However, the equijoin between bf.lot_id and lots.lot_id ensures that only the ST_MultiPolygons belonging to the same lot are compared by the ST_Distance() function:

SELECT bf.building_id
   FROM buildingfootprints bf, lots
   WHERE bf.lot_id = lots.lot_id
   AND ST_Distance(footprint,lot) <= 1.0;Копировать

Examples: Distance between two points

The following query returns the distance between two points in meters:

EXECUTE FUNCTION round(
        ST_Distance(
                '32608 point(576100 15230)'::st_point,
                '32608 point(576102 15230)'::st_point,
                'meter'),
        2);

    (expression)

2.00000000000000

1 row(s) retrieved.Копировать

The following query returns the distance between two points in feet:

EXECUTE FUNCTION round(
        ST_Distance(
                '32608 point(576100 15230)'::st_point,
                '32608 point(576102 15230)'::st_point,
                'foot'),
        2);

    (expression)

6.56000000000000

1 row(s) retrieved.Копировать

Examples: Find the distance between two points that have angular units

These examples are based on the angular coordinate system WGS 84, which has SRID 4326. They calculate the distance between the following latitude and longitude values for New York and Los Angeles:

  • Latitude and longitude of New York: 73.94000 W, 40.67000 N
  • Latitude and longitude of Los Angles: 118.25000 W, 34.05000 N

The following statement returns the distance between New York and Los Angeles in US miles:

EXECUTE FUNCTION ST_Distance('4326 point(-73.94000 40.67000)', 
               '4326 point(-118.25000 34.05000)', 'mile_us');

  (expression)

2454.991002988

1 row(s) retrieved.Копировать

The following statement returns the distance between New York and Los Angeles in kilometers:

EXECUTE FUNCTION ST_Distance('4326 point(-73.94000 40.67000)', 
              '4326 point(-118.25000 34.05000)', 'kilometer');

  (expression)

3950.932942578

1 row(s) retrieved.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Любишь мемасики?

Подпишись на мой телеграм-канал!

Открыть
Закрыть