Definition
ST_Distance returns the distance between two geometries. The distance is measured from the closest vertices of the two geometries.
Syntax
Oracle and PostgreSQL
sde.st_distance (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
sde.st_distance (geometry1 sde.st_geometry, geometry2 sde.st_geometry, unit_name text)
SQLite
st_distance (geometry1 geometryblob, geometry2 geometryblob)
st_distance (geometry1 geometryblob, geometry2 geometryblob, unit_name text)
Valid unit names are as follows:
Millimeter | Inch | Yard | Link |
Centimeter | Inch_US | Yard_US | Link_US |
Decimeter | Foot | Yard_Clarke | Link_Clarke |
Meter | Foot_US | Yard_Sears | Link_Sears |
Meter_German | Foot_Clarke | Yard_Sears_1922_Truncated | Link_Sears_1922_Truncated |
Kilometer | Foot_Sears | Yard_Benoit_1895_A | Link_Benoit_1895_B |
50_Kilometers | Foot_Sears_1922_Truncated | Yard_Indian | Chain |
150_Kilometers | Foot_Benoit_1895_A | Yard_Indian_1937 | Chain_US |
Vara_US | Foot_1865 | Yard_Indian_1962 | Chain_Clarke |
Smoot | Foot_Indian | Yard_Indian_1975 | Chain_Sears |
Foot_Indian_1937 | Fathom | Chain_Sears_1922_Truncated | |
Foot_Indian_1962 | Mile_US | Chain_Benoit_1895_A | |
Foot_Indian_1975 | Statute_Mile | Rod | |
Foot_Gold_Coast | Nautical_Mile | Rod_US | |
Foot_British_1936 | Nautical_Mile_US | ||
Nautical_Mile_UK |
Return type
Double precision
Example
Two tables—study1 and zones—are created and populated. The ST_Distance function is then used to determine the distance between the boundary of each subarea and the polygons in the study1 area table that have a use code of 400. Since there are three zones on this shape, three records should be returned.
If you do not specify units, ST_Distance uses the units of the projection system of the data. In the first example, that is decimal degrees. In the last two examples, kilometer is specified; therefore, the distance is returned in kilometers.
Oracle and PostgreSQL
--Create tables and insert values.
CREATE TABLE zones (
sa_id integer,
usecode integer,
shape sde.st_geometry
);
CREATE TABLE study1 (
code integer unique,
shape sde.st_geometry
);
INSERT INTO zones (sa_id, usecode, shape) VALUES (
1,
400,
sde.st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
INSERT INTO zones (sa_id, usecode, shape) VALUES (
2,
400,
sde.st_polygon ('polygon ((12 3, 12 6, 15 6, 15 3, 12 3))', 4326)
);
INSERT INTO zones (sa_id, usecode, shape) VALUES (
3,
400,
sde.st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);
INSERT INTO zones (sa_id, usecode, shape) VALUES (
4,
402,
sde.st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);
INSERT INTO study1 (code, shape) VALUES (
400,
sde.st_polygon ('polygon ((-1 -1, -1 11, 11 11, 19 11, 31 11, 31 -1, 19 -1, 11 -1, -1 -1))', 4326)
);
INSERT INTO study1 (code, shape) VALUES (
402,
sde.st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 4326)
);
--Oracle SELECT statement without units
SELECT UNIQUE s.code, z.sa_id, sde.st_distance(z.shape, sde.st_boundary(s.shape)) DISTANCE
FROM zones z, study1 s
WHERE z.usecode = s.code AND s.code = 400
ORDER BY DISTANCE;
CODE SA_ID DISTANCE
---------- ---------- ----------
400 1 1
400 3 1
400 3 4
--PostgreSQL SELECT statement without units
SELECT DISTINCT s.code, z.sa_id, sde.st_distance(z.shape, sde.st_boundary(s.shape))
AS Distance
FROM zones z, study1 s
WHERE z.usecode = s.code AND s.code = 400
ORDER BY Distance;
code sa_id distance
400 1 1
400 3 1
400 2 4
--Oracle SELECT statement with values returned in kilometers
SELECT UNIQUE s.code, z.sa_id, sde.st_distance(z.shape, sde.st_boundary(s.shape), 'kilometer') DISTANCE
FROM zones z, study1 s
WHERE z.usecode = s.code AND s.code = 400
ORDER BY DISTANCE;
CODE SA_ID DISTANCE
---------- ---------- ----------
400 1 109.639196
400 3 109.639196
400 2 442.300258
--PostgreSQL SELECT statement with values returned in kilometers
SELECT DISTINCT s.code, z.sa_id, sde.st_distance(z.shape, sde.st_boundary(s.shape), 'kilometer')
AS Distance
FROM zones z, study1 s
WHERE z.usecode = s.code AND s.code = 400
ORDER BY Distance;
code sa_id distance
400 1 109.63919620267
400 3 109.63919620267
400 2 442.300258454087
SQLite
--Create tables, add geometry columns, and insert values.
CREATE TABLE zones (
sa_id integer primary key autoincrement not null,
usecode integer
);
SELECT AddGeometryColumn (
NULL,
'zones',
'shape',
4326,
'polygon',
'xy',
'null'
);
CREATE TABLE study1 (
code integer unique
);
SELECT AddGeometryColumn (
NULL,
'study1',
'shape',
4326,
'polygon',
'xy',
'null'
);
INSERT INTO zones (usecode, shape) VALUES (
400,
st_polygon ('polygon ((0 0, 0 10, 10 10, 10 0, 0 0))', 4326)
);
INSERT INTO zones (usecode, shape) VALUES (
400,
st_polygon ('polygon ((12 3, 12 6, 15 6, 15 3, 12 3))', 4326)
);
INSERT INTO zones (usecode, shape) VALUES (
400,
st_polygon ('polygon ((20 0, 20 10, 30 10, 30 0, 20 0))', 4326)
);
INSERT INTO zones (usecode, shape) VALUES (
402,
st_polygon ('polygon ((40 0, 40 10, 50 10, 50 0, 40 0))', 4326)
);
INSERT INTO study1 (code, shape) VALUES (
400,
st_polygon ('polygon ((-1 -1, -1 11, 11 11, 19 11, 31 11, 31 -1, 19 -1, 11 -1, -1 -1))', 4326)
);
INSERT INTO study1 (code, shape) VALUES (
402,
st_polygon ('polygon ((39 -1, 39 11, 51 11, 51 -1, 39 -1))', 4326)
);
--SQLite SELECT statement without units
SELECT DISTINCT s.code, z.sa_id, st_distance(z.shape, st_boundary(s.shape))
AS "Distance(km)"
FROM zones z, study1 s
WHERE z.usecode = s.code AND s.code = 400
ORDER BY "Distance(km)";
code sa_id distance
400 1 1
400 3 1
400 2 4
--SQLite SELECT statement with units
SELECT DISTINCT s.code, z.sa_id, st_distance(z.shape, st_boundary(s.shape), "kilometer")
AS "Distance(km)"
FROM zones z, study1 s
WHERE z.usecode = s.code AND s.code = 400
ORDER BY "Distance(km)";
code sa_id Distance(km)
400 1 109.63919620267
400 3 109.63919620267
400 2 442.30025845408