ST_Crosses takes two 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).

Oracle

sde.st_crosses (g1 sde.st_geometry, g2 sde.st_geometry)

PostgreSQL

st_crosses (g1 st_geometry, g2 st_geometry)

Boolean

The county government is considering a new regulation stating that all hazardous waste storage facilities within the county may not be in a 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.

Oracle

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)
);

PostgreSQL

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

CREATE TABLE hazardous_sites (site_id integer, 
name varchar(40),
location st_geometry);

INSERT INTO waterways VALUES (
2,
'Zanja', 
st_geometry ('linestring (40 50, 50 40)', 0)
);

INSERT INTO waterways VALUES (
3, 
'Keshequa',
st_geometry ('linestring (20 20, 60 60)', 0)
);

INSERT INTO hazardous_sites VALUES (
4,
'Bad Water',
st_point ('point (60 60)', 0)
);

INSERT INTO hazardous_sites VALUES (
5,
'Glowing Pools',
st_point ('point (30 30)', 0)
);

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.

Oracle

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

PostgreSQL

SELECT DISTINCT (ww.name) AS "River or stream", hs.name AS "Hazardous sites"
FROM waterways ww, hazardous_sites hs 
WHERE st_crosses (st_buffer (hs.location, .01), ww.water) = 't';

River or stream			   Hazardous sites

Keshequa					   Bad Water