ST_Crosses takes two ST_Geometry objects and returns 1 (Oracle) or t (PostgreSQL) if their intersection results in a geometry object whose dimension is one less than the maximum dimension of the source objects. The intersection object must contain points that are interior to both source geometries and are not equal to either of the source objects. Otherwise, it returns 0 (Oracle) or f (PostgreSQL).
sde.st_crosses (g1 sde.st_geometry, g2 sde.st_geometry)
The county government is considering a new regulation stating that all hazardous waste storage facilities within the county may not be in a specific radius of any waterway. The county GIS manager has an accurate representation of rivers and streams stored as linestrings 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 sde.st_geometry); CREATE TABLE hazardous_sites (site_id integer, name varchar(40), location sde.st_geometry); INSERT INTO waterways VALUES ( 2, 'Zanja', sde.st_geometry ('linestring (40 50, 50 40)', 0) ); INSERT INTO waterways VALUES ( 3, 'Keshequa', sde.st_geometry ('linestring (20 20, 60 60)', 0) ); INSERT INTO hazardous_sites VALUES ( 4, 'Bad Water', sde.st_point ('point (60 60)', 0) ); INSERT INTO hazardous_sites VALUES ( 5, 'Glowing Pools', sde.st_point ('point (30 30)', 0) );
To determine whether 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 points with waterways, returning only those records where the waterway crosses over the county's proposed regulated radius.
SELECT UNIQUE (ww.name) "River or stream", hs.name "Hazardous sites" FROM WATERWAYS ww, HAZARDOUS_SITES hs WHERE sde.st_crosses (sde.st_buffer (hs.location, .01), ww.water) = 1; River or stream Hazardous sites Keshequa Bad Water
SELECT DISTINCT (ww.name) AS "River or stream", hs.name AS "Hazardous sites" FROM waterways ww, hazardous_sites hs WHERE sde.st_crosses (sde.st_buffer (hs.location, .01), ww.water) = 't'; River or stream Hazardous sites Keshequa Bad Water