ST_Union returns an ST_Geometry object that is the union of two source objects.
Oracle
sde.st_union (g1 sde.st_geometry, g2 sde.st_geometry)
PostgreSQL
st_union (g1 st_geometry, g2 st_geometry)
The sensitive_areas table stores the IDs of 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 ST_Buffer function generates a buffer surrounding the hazardous waste site locations. The ST_Union function generates polygons from the union of the buffered hazardous waste sites and sensitive area polygons. The ST_Area function returns the area of these polygons.
Oracle
SELECT sa.id SA_ID, hs.id HS_ID,
sde.st_area (sde.st_union (sde.st_buffer (hs.location, .01), sa.zone)) UNION_AREA
FROM hazardous_sites hs, sensitive_areas sa;
SA_ID HS_ID UNION_AREA
1 4 100.000314
2 4 400.000314
3 4 400.000235
1 5 100.000235
2 5 400.000235
3 5 400.000314
PostgreSQL
SELECT sa.id AS SA_ID, hs.id AS HS_ID,
st_area (st_union (st_buffer (hs.location, .01), sa.zone)) AS UNION_AREA
FROM hazardous_sites hs, sensitive_areas sa;
sa_id hs_id union_area
1 4 100.000314
2 4 400.000314
3 4 400.000235
1 5 100.000235
2 5 400.000235
3 5 400.000314