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)

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