Definition
ST_Overlaps takes two geometry objects and returns 1 (Oracle and SQLite) or t (PostgreSQL) if the intersection of the objects results in a geometry object of the same dimension but not equal to either source object; otherwise, it returns 0 (Oracle and SQLite) or f (PostgreSQL).
Syntax
Oracle and PostgreSQL
sde.st_overlaps (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
SQLite
st_overlaps (geometry1 geometryblob, geometry2 geometryblob)
Return type
Boolean
Example
The county supervisor needs a list of sensitive areas that overlap the buffered radius of hazardous waste sites. The sensitive_areas table contains several columns that describe the threatened institutions in addition to the shape column, which stores the institutions' ST_Polygon geometries.
The hazardous_sites table stores the identity of the sites in the id column, while the actual geographic location of each site is stored in the site point column.
The sensitive_areas and hazardous_sites tables are joined by the ST_Overlaps function, which returns the ID for all sensitive_areas rows that contain polygons that overlap the buffered radius of the hazardous_sites points.
Oracle
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)
);
SELECT UNIQUE (hs.id)
FROM HAZARDOUS_SITES hs, SENSITIVE_AREAS sa
WHERE sde.st_overlaps (sde.st_buffer (hs.site, .001), sa.shape) = 1;
ID
4
5
PostgreSQL
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)
);
SELECT DISTINCT (hs.id) AS "Hazardous Site ID"
FROM hazardous_sites hs, sensitive_areas sa
WHERE sde.st_overlaps (sde.st_buffer (hs.site, .001), sa.shape) = 't';
id
1
2
SQLite
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,
site_name varchar(30)
);
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_name, site) VALUES (
'Kemlabs',
st_geometry ('point (.60 .60)', 4326)
);
INSERT INTO hazardous_sites (site_name, site) VALUES (
'Medi-Waste',
st_geometry ('point (.30 .30)', 4326)
);
SELECT DISTINCT (hs.site_name) AS "Hazardous Site"
FROM hazardous_sites hs, sensitive_areas sa
WHERE st_overlaps (st_buffer (hs.site, .001), sa.shape) = 1;
Hazardous Site
Kemlabs
Medi-Waste