-- The county supervisor needs a list of hazardous waste sites
-- whose five-mile radius overlaps sensitive areas.
-- 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);

-- 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 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)
);

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 sensitive_areas and hazardous_sites tables are joined by the
-- ST_Overlaps function, which returns t (TRUE) for all sensitive_areas
-- rows whose zone polygons overlap the buffered five-mile radius of
-- the hazardous_sites location points.

SELECT hs.name hazardous_site, sa.name sensitive_area
   FROM hazardous_sites hs, sensitive_areas sa
   WHERE ST_Overlaps(ST_Buffer(hs.location,(26400)),sa.zone);