ST_Crosses

定义

ST_Crosses 以两个 ST_Geometry 对象作为输入，如果这两个对象的交集生成的几何对象的维度小于两个源对象中的最大维度，则返回 1（Oracle 和 SQLite）或 t (PostgreSQL)。交集对象所包含的点必须在两个源几何的内部，并且不等于其中任何一个源对象。否则，返回 0（Oracle 和 SQLite）或 f (PostgreSQL)。

语法

sde.st_crosses (geometry1 sde.st_geometry, geometry2 sde.st_geometry)

Oracle 和 PostgreSQL

sde.st_crosses (geometry1 sde.st_geometry, geometry2 sde.st_geometry)

SQLite

st_crosses (geometry1 geometryblob, geometry2 geometryblob)

返回类型

布尔型

示例

县政府正在考虑制定一项新条例，规定县内所有危险废弃物存储设施不能位于任何水道周围指定半径范围内。该县的 GIS 管理人员拥有河流和溪流的精确数据表示，以线串形式存储在 waterways 表中，但他只拥有每个危险废弃物存储设施的单个点位置。

为确定是否必须警告县监管人员现有设施有可能违反提出的条例，GIS 管理人员需要创建 hazardous_sites 位置的缓冲区，以了解是否有河流或溪流穿过缓冲区面。cross 谓词将已建立缓冲区的 hazardous_sites 点与水道进行比较，仅返回水道穿过县提议条例规定半径的记录。

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