Definition
ST_Distance gibt die Entfernung zwischen zwei Geometrien zurück. Die Entfernung wird von den nächsten Stützpunkten der beiden Geometrien gemessen.
Syntax
Oracle und 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)
Die folgenden Einheitennamen sind gültig:
Millimeter | Zoll | Yard | Link |
Zentimeter | Inch_US | Yard_US | Link_US |
Dezimeter | Fuß | 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 | Kette |
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 |
Rückgabetyp
Doppelte Genauigkeit
Beispiel
Es werden zwei Tabellen, "study1" und "zones", erstellt und gefüllt. Die Funktion "ST_Distance" wird anschließend verwendet, um die Entfernung zwischen der Grenze jedes Teilgebiets und den Polygonen in der Flächentabelle "study1" zu ermitteln, die einen Nutzungscode von 400 aufweisen. Da sich drei Zonen auf dem Shape befinden, sollten drei Datensätze zurückgegeben werden.
In Oracle und PostgreSQL werden Einheiten in dem Projektionssystem definiert, das Sie verwenden. In diesen Beispielen sind es Dezimalgrad. In SQLite können Sie die Einheiten angeben. Da im Beispiel für SQLite Kilometer angegeben sind, wird die Entfernung in Kilometern zurückgegeben.
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