-- For a special report, the county supervisor must determine the area
-- of sensitive areas and five-mile hazardous site radii that aren't
-- intersected.
-- 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.

CREATE TABLE sensitive_areas (id    integer,
                              name  varchar(128),
                              size  float,
                              type  varchar(10),
                              zone  ST_Polygon);

INSERT INTO sensitive_areas VALUES(
   1, 'Johnson County Hospital', 102281.91, 'hospital',
   ST_PolyFromText('polygon ((22000 45000,22000 58000,28000 58000,28000 42000,25000 42000,25000 45000,22000 45000))',1000)
);

INSERT INTO sensitive_areas VALUES(
   2, 'Rydale Nursing Home', 53926.54, 'nursing hm',
   ST_PolyFromText('polygon ((22000 18000,28000 18000,28000 12000,22000 12000,22000 18000))',1000)
);

INSERT INTO sensitive_areas VALUES(
   3, 'Summerhill Elementary School', 67920.64, 'school',
   ST_PolyFromText('polygon ((42000 18000,48000 18000,48000 13000,42000 13000,42000 18000))',1000)
);

-- The hazardous_sites table stores the identity of the sites in the
-- site_id and name columns, while the actual geographic location of
-- each site is stored in the location point column.

CREATE TABLE hazardous_sites (site_id   integer,
                              name      varchar(128),
                              location  ST_Point);

INSERT INTO hazardous_sites VALUES(
   102,
   'W. H. Kleenare Chemical Repository',
   ST_PointFromText('point (7000 47000)',1000)
);

INSERT INTO hazardous_sites VALUES(
   59,
   'Landmark Industrial', 
   ST_PointFromText('point (48000 39000)',1000)
);

-- The ST_Buffer function generates a five-mile buffer surrounding the
-- hazardous waste site locations. The ST_Union function generates polygons
-- from the union of the buffered hazardous waste site polygons and the 
-- sensitive areas. The ST_Area function returns the unioned polygon's area.

SELECT sa.name sensitive_area, hs.name hazardous_site,
       ST_Area(ST_Union(ST_Buffer(hs.location,(5 * 5280)),sa.zone)::ST_MultiPolygon) area
   FROM hazardous_sites hs, sensitive_areas sa;