Definition
ST_Crosses takes two ST_Geometry objects and returns 1 (Oracle and SQLite) 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 and SQLite) or f (PostgreSQL).
Syntax
sde.st_crosses (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
Oracle and PostgreSQL
sde.st_crosses (geometry1 sde.st_geometry, geometry2 sde.st_geometry)
SQLite
st_crosses (geometry1 geometryblob, geometry2 geometryblob)
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.
To determine whether he must alert the county supervisor to any existing facilities that would violate the proposed regulation, the GIS manager must 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
--Define tables and insert values.
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)', 4326)
);
INSERT INTO waterways VALUES (
3,
'Keshequa',
sde.st_geometry ('linestring (20 20, 60 60)', 4326)
);
INSERT INTO hazardous_sites VALUES (
4,
'StorIt',
sde.st_point ('point (60 60)', 4326)
);
INSERT INTO hazardous_sites VALUES (
5,
'Glowing Pools',
sde.st_point ('point (30 30)', 4326)
);
--Buffer hazardous waste sites and find if any buffers cross a waterway.
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 StorIt
Keshequa Glowing Pools
PostgreSQL
--Define tables and insert values.
CREATE TABLE waterways (
id serial,
name varchar(128),
water sde.st_geometry
);
CREATE TABLE hazardous_sites (
site_id integer,
name varchar(40),
location sde.st_geometry
);
INSERT INTO waterways (name, water) VALUES (
'Zanja',
sde.st_geometry ('linestring (40 50, 50 40)', 4326)
);
INSERT INTO waterways (name, water) VALUES (
'Keshequa',
sde.st_geometry ('linestring (20 20, 60 60)', 4326)
);
INSERT INTO hazardous_sites (name, location) VALUES (
'StorIt',
sde.st_point ('point (60 60)', 4326)
);
INSERT INTO hazardous_sites (name, location) VALUES (
'Glowing Pools',
sde.st_point ('point (30 30)', 4326)
);
--Buffer hazardous waste sites and find if any buffers cross a waterway.
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 StorIt
Keshequa Glowing Pools
SQLite
--Define tables and insert values.
CREATE TABLE waterways (
id integer primary key autoincrement not null,
name varchar(128)
);
SELECT AddGeometryColumn(
NULL,
'waterways',
'water',
4326,
'linestring',
'xy',
'null'
);
CREATE TABLE hazardous_sites (
site_id integer primary key autoincrement not null,
name varchar(40)
);
SELECT AddGeometryColumn(
NULL,
'hazardous_sites',
'location',
4326,
'point',
'xy',
'null'
);
INSERT INTO waterways (name, water) VALUES (
'Zanja',
st_geometry ('linestring (40 50, 50 40)', 4326)
);
INSERT INTO waterways (name, water) VALUES (
'Keshequa',
st_geometry ('linestring (20 20, 60 60)', 4326)
);
INSERT INTO hazardous_sites (name, location) VALUES (
'StorIt',
st_point ('point (60 60)', 4326)
);
INSERT INTO hazardous_sites (name, location) VALUES (
'Glowing Pools',
st_point ('point (30 30)', 4326)
);
--Buffer hazardous waste sites and find if any buffers cross a waterway.
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) = 1;
River or stream Hazardous sites
Keshequa StorIt
Keshequa Glowing Pools