-- The fire marshal wants a list of sensitive areas within a five-mile
-- radius of a hazardous waste site.
-- The sensitive areas are stored in the sensitive_areas table
-- created below. The zone column is defined as a polygon and stores
-- the outline 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 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 query returns a list of sensitive area and hazardous site names
-- for sensitive areas that intersect the five-mile buffer radius
-- of the hazardous sites.

SELECT sa.name, hs.name
   FROM sensitive_areas sa, hazardous_sites hs
   WHERE ST_Intersects(ST_Buffer(hs.location,(5 * 5280)),sa.zone);