ST_Overlaps takes two ST_Geometry objects and returns 1 (Oracle) or t (PostgreSQL) if the intersection of the objects results in an ST_Geometry object of the same dimension but not equal to either source object; otherwise, it returns 0 (Oracle) or f (PostgreSQL).
Oracle
sde.st_overlaps (g1 sde.st_geometry, g2 sde.st_geometry)
PostgreSQL
st_overlaps (g1 st_geometry, g2 st_geometry)
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 zone 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 location point column.
Oracle
CREATE TABLE sensitive_areas (id integer,
zone sde.st_geometry);
CREATE TABLE hazardous_sites (id integer,
location sde.st_geometry);
INSERT INTO sensitive_areas VALUES (
1,
sde.st_polygon ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 0)
);
INSERT INTO sensitive_areas VALUES (
2,
sde.st_polygon ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 0)
);
INSERT INTO sensitive_areas VALUES (
3,
sde.st_polygon ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
);
INSERT INTO hazardous_sites VALUES (
4,
sde.st_point ('point (60 60)', 0)
);
INSERT INTO hazardous_sites VALUES (
5,
sde.st_point ('point (30 30)', 0)
);
PostgreSQL
CREATE TABLE sensitive_areas (id integer,
zone st_geometry);
CREATE TABLE hazardous_sites (id integer,
location st_geometry);
INSERT INTO sensitive_areas VALUES (
1,
st_polygon ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 0)
);
INSERT INTO sensitive_areas VALUES (
2,
st_polygon ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 0)
);
INSERT INTO sensitive_areas VALUES (
3,
st_polygon ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
);
INSERT INTO hazardous_sites VALUES (
4,
st_point ('point (60 60)', 0)
);
INSERT INTO hazardous_sites VALUES (
5,
st_point ('point (30 30)', 0)
);
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 zone polygons that overlap the buffered radius of the hazardous_sites location points.
Oracle
SELECT UNIQUE (hs.id)
FROM hazardous_sites hs, sensitive_areas sa
WHERE sde.st_overlaps (sde.st_buffer (hs.location, .01), sa.zone) = 1;
ID
4
5
PostgreSQL
SELECT DISTINCT (hs.id)
FROM hazardous_sites hs, sensitive_areas sa
WHERE st_overlaps (st_buffer (hs.location, .01), sa.zone) = 't';
ID
4
5