Definition
ST_Intersects returns 1 (Oracle and SQLite) or t (PostgreSQL) if the intersection of two geometries doesn't result in an empty set; otherwise, it returns 0 (Oracle and SQLite) or f (PostgreSQL).
Syntax
Oracle and PostgreSQL
sde.st_intersects (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
SQLite
st_intersects (geometry1 geometryblob, geometry2 geometryblob)
Return type
Boolean
Example
The fire marshal wants a list of sensitive areas within a radius of a hazardous waste site.
The sensitive areas are stored in the sensitive_areas table. The shape column, defined as a polygon, stores the outline of each of the sensitive areas.
The hazardous sites are stored in the hazardous_sites table. The site column, defined as a point, stores a location that is the geographic center of each hazardous site.
The SELECT query creates a buffer radius around each hazardous site and returns a list of sensitive areas that intersect the hazardous sites buffers.
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