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)
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.
In Oracle and PostgreSQL, units are defined in the projection system you're using. In these examples, that is decimal degrees. In SQLite, you can specify the units. In the SQLite example, kilometer is specified; therefore, the distance is returned in kilometers.
Oracle
--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)
);
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
--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)
);
--
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
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)
);
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