ST_Crosses

Release 9.3
Note: This topic was updated for 9.3.1.

Definition

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

Syntax

Oracle

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

PostgreSQL

``st_crosses (g1 st_geometry, g2 st_geometry)``

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 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,
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,
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
``````SELECT DISTINCT (ww.name) AS "River or stream", hs.name AS "Hazardous sites"