-- An insurance company wants to assess the insurance coverage for the town's
-- hospital, nursing homes, and schools. Part of this process includes
-- determining the threat the hazardous waste sites pose to each institution.
-- At this time the insurance company wants to consider only those
-- institutions that are not at risk of contamination.  The GIS consultant
-- hired by the insurance company has been commissioned to locate all
-- institutions that are outside a five-mile radius of a hazardous waste
-- storage facility. The sensitive_areas table contains several columns that
-- describe the threatened institutions in addition to the zone column,
-- which stores the institutions' 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 select statement lists the names of all sensitive areas that are
-- outside the five-mile radius of a hazardous waste site.

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

-- Tip: You can rewrite the query to use the ST_Intersects function instead,
-- as shown here.  ST_Intersects and ST_Disjoint return opposite results,
-- but ST_Intersects can use an index to determine the answer more quickly,
-- while ST_Disjoint must perform a full table scan.

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