-- The fire marshal must obtain the areas of the hospitals,
-- schools, and nursing homes intersected by the radius of
-- a possible hazardous waste contamination.
-- The sensitive areas are stored in the sensitive_areas table
-- that is created with the CREATE TABLE statement that follows.
-- The zone column defined as a polygon stores the outline of each
-- of the sensitive areas.

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 are stored in the hazardous_sites table created
-- with the CREATE TABLE statement that follows. The location column,
-- defined as a point, stores a location that is the geographic center
-- of each hazardous site.

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_Intersection function
-- generates polygons from the intersection of the buffered hazardous
-- waste sites and the sensitive areas. The ST_Area function returns
-- the intersection polygons' area, which is summarized for all hazardous
-- sites by the sum function. The group by clause directs the query to
-- aggregate the intersection areas by hazardous waste site ID.

SELECT hs.site_id, SUM(ST_Area(ST_Intersection(sa.zone,
       ST_Buffer(hs.location,(5 * 5280)))::ST_MultiPolygon))
   FROM sensitive_areas sa, hazardous_sites hs
   GROUP BY hs.site_id;