-- The County Supervisor needs a list of hazardous sites whose five-mile
-- radius overlaps sensitive areas such as schools, hospitals, and nursing
-- homes.  The sensitive areas are stored in the table sensitive_areas that
-- is created with the CREATE TABLE statement below. The zone column, defined
-- as a ST_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);


-- The hazardous sites are stored in the hazardous_sites table created below.
-- The location column, defined as a point, stores the geographic center of
-- each hazardous site.

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 point.

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