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)

Boolean

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