Определение
ST_Distance возвращает расстояние между двумя геометриями. Расстояние измеряется от наиближайших вершин до двух геометрий.
Синтаксис
Oracle и 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)
Допустимыми являются следующие наименования единиц измерения:
Миллиметр | Дюйм | Ярд | Линк |
Сантиметр | Inch_US | Yard_US | Link_US |
Дециметр | Фут | Yard_Clarke | Link_Clarke |
Метр | Foot_US | Yard_Sears | Link_Sears |
Meter_German | Foot_Clarke | Yard_Sears_1922_Truncated | Link_Sears_1922_Truncated |
Километр | Foot_Sears | Yard_Benoit_1895_A | Link_Benoit_1895_B |
50_Kilometers | Foot_Sears_1922_Truncated | Yard_Indian | Чейн |
150_Kilometers | Foot_Benoit_1895_A | Yard_Indian_1937 | Chain_US |
Vara_US | Foot_1865 | Yard_Indian_1962 | Chain_Clarke |
Смут | Foot_Indian | Yard_Indian_1975 | Chain_Sears |
Foot_Indian_1937 | Морская сажень | Chain_Sears_1922_Truncated | |
Foot_Indian_1962 | Mile_US | Chain_Benoit_1895_A | |
Foot_Indian_1975 | Statute_Mile | Род | |
Foot_Gold_Coast | Nautical_Mile | Rod_US | |
Foot_British_1936 | Nautical_Mile_US | ||
Nautical_Mile_UK |
Тип возвращаемого значения
Двойная точность
Пример
Созданы и заполнены две таблицы – study1 и zones. Функция ST_Distance используется для определения расстояния между границей каждой подзоны и полигонами таблицы study1, имеющими код 400. Поскольку там содержатся три зоны, будут возвращены три записи.
Если не указаны единицы измерения, ST_Distance использует единицы системы координат проекции данных. В первом примере применяются десятичные градусы. В последних двух указаны километры, поэтому расстояние возвращается в километрах.
Oracle и 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