Definición
ST_Distance devuelve la distancia entre dos geometrías. La distancia se mide desde el vértice más cercano de las dos geometrías.
Sintaxis
Oracle y 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)
Los nombres de unidad válidos son los siguientes:
Milímetro | Pulgada | Yarda | Vínculo |
Centímetro | Inch_US | Yard_US | Link_US |
Decímetro | Pie | Yard_Clarke | Link_Clarke |
Metro | Foot_US | Yard_Sears | Link_Sears |
Meter_German | Foot_Clarke | Yard_Sears_1922_Truncated | Link_Sears_1922_Truncated |
Kilómetro | Foot_Sears | Yard_Benoit_1895_A | Link_Benoit_1895_B |
50_Kilometers | Foot_Sears_1922_Truncated | Yard_Indian | Cadena |
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 | Braza inglesa | 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 |
Tipo de devolución
Precisión doble
Ejemplo
Se crean y se completan dos tablas, study1 y zones. A continuación, la función ST_Distance se usa para determinar la distancia entre el límite de cada subárea y los polígonos de la tabla de área study1 que tienen el código de uso 400. Puesto que hay tres zonas con esta forma, se deben devolver tres registros.
Si no especifica unidades, ST_Distance utiliza las unidades del sistema de proyección de los datos. En el primer ejemplo, se usan grados decimales. En los últimos dos ejemplos, se especifica el kilómetro; por tanto, la distancia se devuelve en kilómetros.
Oracle y 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