analytics/informix/extend/spatial.8.22.FC2/examples/sql/st_crosses.sql

-- The county government is considering a new regulation stating that all
-- hazardous waste storage facilities within the county may not be within
-- five miles of any waterway. The county GIS manager has an accurate
-- representation of rivers and streams stored as multilinestrings in the
-- waterways table but he only has a single point location for each of the
-- hazardous waste storage facilities.

CREATE TABLE waterways (id     integer,
                        name   varchar(128),
                        water  ST_MultiLineString);


CREATE TABLE hazardous_sites (site_id   integer,
                              name      varchar(128),
                              location  ST_Point);

INSERT INTO waterways VALUES(
   829, 'Fedders creek',
   ST_MLineFromText('multilinestring ((5000 0,2000 12000,17000 6000,8000 19000,21000 11000,23000 13000,25000 16000,29000 19000,31000 21000,39000 27000,45000 31000, 51000 31000, 59000 29000, 66000 29000, 75000 32000),(29000 19000,32500 26000,34000 31000,35000 39000,36000 41000,39000 48000,42000 51000,49000 58000,51000 59000,57000 62000))',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)
);

-- To determine if he must alert the county supervisor to any existing
-- facilities that would violate the proposed regulation the GIS manager
-- will have to buffer the hazardous_sites locations to see if any rivers
-- or streams cross the buffer polygons. The cross predicate compares the
-- buffered hazardous_sites with waterways, returning only those records
-- where the waterway crosses over the county's proposed regulated radius.

SELECT ww.name waterway, hs.name hazardous_site
   FROM waterways ww, hazardous_sites hs
   WHERE ST_Crosses(ST_Buffer(hs.location,(5 * 5280)),ww.water);