ST_Crosses

Definition

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

Syntax

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

Return type

Boolean

Example

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.

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 sde.st_crosses (sde.st_buffer (hs.location, .01), ww.water) = 't';

River or stream			   Hazardous sites

Keshequa					   Bad Water

11/18/2013