Definición
ST_Intersects devuelve 1 (Oracle y SQLite) o t (PostgreSQL) si la intersección de dos geometrías no genera un conjunto vacío; de lo contrario, devuelve 0 (Oracle y SQLite) o f (PostgreSQL).
Sintaxis
Oracle y PostgreSQL
sde.st_intersects (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
SQLite
st_intersects (geometry1 geometryblob, geometry2 geometryblob)
Tipo de devolución
Booleano
Ejemplo
El jefe de bomberos quiere una lista de áreas sensibles dentro de un radio de un sitio de residuos peligrosos.
Las áreas sensibles se almacenan en la tabla sensitive_areas. La columna de forma, que se define como un polígono, almacena el contorno de cada una de las zonas sensibles.
Los emplazamientos peligrosos se almacenan en la tabla hazardous_sites. La columna de sitio, que se define como un punto, almacena una ubicación que es el centro geográfico de cada sitio peligroso.
La consulta SELECT crea un radio de zona de influencia alrededor de cada emplazamiento peligroso y devuelve una lista de las áreas sensibles que se intersecan con las zonas de influencia de los emplazamientos peligrosos.
Oracle
--Create and populate tables.
CREATE TABLE sensitive_areas (
id integer,
shape sde.st_geometry
);
CREATE TABLE hazardous_sites (
id integer,
site sde.st_geometry
);
INSERT INTO sensitive_areas VALUES (
1,
sde.st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);
INSERT INTO sensitive_areas VALUES (
2,
sde.st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);
INSERT INTO sensitive_areas VALUES (
3,
sde.st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);
INSERT INTO hazardous_sites VALUES (
4,
sde.st_geometry ('point (60 60)', 4326)
);
INSERT INTO hazardous_sites VALUES (
5,
sde.st_geometry ('point (30 30)', 4326)
);
--Create a buffer around the hazardous sites, then find the hazardous site buffers that intersect sensitive areas.
SELECT sa.id SA_ID, hs.id HS_ID
FROM SENSITIVE_AREAS sa, HAZARDOUS_SITES hs
WHERE sde.st_intersects (sde.st_buffer (hs.site, .1), sa.shape) = 1
ORDER BY sa.id;
SA_ID HS_ID
1 5
2 5
3 4
PostgreSQL
--Create and populate tables.
CREATE TABLE sensitive_areas (
id serial,
shape sde.st_geometry
);
CREATE TABLE hazardous_sites (
id serial,
site sde.st_geometry
);
INSERT INTO sensitive_areas (shape) VALUES (
sde.st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);
INSERT INTO sensitive_areas (shape) VALUES (
sde.st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);
INSERT INTO sensitive_areas (shape) VALUES (
sde.st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);
INSERT INTO hazardous_sites (site) VALUES (
sde.st_geometry ('point (60 60)', 4326)
);
INSERT INTO hazardous_sites (site) VALUES (
sde.st_geometry ('point (30 30)', 4326)
);
--Create a buffer around the hazardous sites, then find the hazardous site buffers that intersect sensitive areas.
SELECT sa.id AS sid, hs.id AS hid
FROM sensitive_areas sa, hazardous_sites hs
WHERE sde.st_intersects (sde.st_buffer (hs.site, .1), sa.shape) = 't'
ORDER BY sa.id;
sid hid
1 2
2 2
3 1
SQLite
--Create and populate tables.
CREATE TABLE sensitive_areas (
id integer primary key autoincrement not null
);
SELECT AddGeometryColumn (
NULL,
'sensitive_areas',
'shape',
4326,
'polygon',
'xy',
'null'
);
CREATE TABLE hazardous_sites (
id integer primary key autoincrement not null
);
SELECT AddGeometryColumn (
NULL,
'hazardous_sites',
'site',
4326,
'point',
'xy',
'null'
);
INSERT INTO sensitive_areas (shape) VALUES (
st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);
INSERT INTO sensitive_areas (shape) VALUES (
st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);
INSERT INTO sensitive_areas (shape) VALUES (
st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);
INSERT INTO hazardous_sites (site) VALUES (
st_geometry ('point (60 60)', 4326)
);
INSERT INTO hazardous_sites (site) VALUES (
st_geometry ('point (30 30)', 4326)
);
--Create a buffer around the hazardous sites, then find the hazardous site buffers that intersect sensitive areas.
SELECT sa.id AS "sid", hs.id AS "hid"
FROM sensitive_areas sa, hazardous_sites hs
WHERE st_intersects (st_buffer (hs.site, .1), sa.shape) = 1
ORDER BY sa.id;
sid hid
1 2
2 2
3 1