ST_Intersects

Definition

ST_Intersects gibt 1 (Oracle und SQLite) oder t (PostgreSQL) zurück, wenn die Überschneidung von zwei Geometrien keine leere Menge ergibt. Andernfalls wird 0 (Oracle und SQLite) oder f (PostgreSQL) zurückgegeben.

Syntax

Oracle und PostgreSQL

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

SQLite

st_intersects (geometry1 geometryblob, geometry2 geometryblob)

Rückgabetyp

Boolesch

Beispiel

Der Kreisbrandmeister möchte eine Liste der empfindlichen Bereiche, die innerhalb des Radius einer Sondermülldeponie liegen.

Die empfindlichen Bereiche werden in der Tabelle "sensitive_areas" gespeichert. Die als Polygon definierte Spalte "shape" enthält den Umriss der einzelnen empfindlichen Bereiche.

Die Sondermülldeponien werden in der Tabelle "hazardous_sites" gespeichert. Die als Punkte definierte Spalte "site" enthält eine Position, die den geographischen Mittelpunkt der einzelnen Sondermülldeponien darstellt.

Die SELECT-Abfrage erstellt einen Pufferradius um jede Sondermülldeponie und gibt eine Liste der empfindlichen Bereiche zurück, die den Pufferradius der Sondermülldeponien schneiden.

Oracle

--Create and populate tables.
CREATE TABLE sensitive_areas (
 id integer,
 shape sde.st_geometry
); 

CREATE TABLE hazardous_sites (
 id integer,
 site sde.st_geometry
);

INSERT INTO sensitive_areas VALUES (
 1,
 sde.st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);

INSERT INTO sensitive_areas VALUES (
 2,
 sde.st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);

INSERT INTO sensitive_areas VALUES (
 3,
 sde.st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);

INSERT INTO hazardous_sites VALUES (
 4,
 sde.st_geometry ('point (60 60)', 4326)
);

INSERT INTO hazardous_sites VALUES (
 5,
 sde.st_geometry ('point (30 30)', 4326)
);

--Create a buffer around the hazardous sites, then find the hazardous site buffers that intersect sensitive areas.
SELECT sa.id SA_ID, hs.id HS_ID
 FROM SENSITIVE_AREAS sa, HAZARDOUS_SITES hs
 WHERE sde.st_intersects (sde.st_buffer (hs.site, .1), sa.shape) = 1
 ORDER BY sa.id;

SA_ID      HS_ID

    1          5
    2          5
    3          4

PostgreSQL

--Create and populate tables.
CREATE TABLE sensitive_areas (
 id serial,
 shape sde.st_geometry
); 

CREATE TABLE hazardous_sites (
 id serial,
 site sde.st_geometry
);

INSERT INTO sensitive_areas (shape) VALUES (
 sde.st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);

INSERT INTO sensitive_areas (shape) VALUES (
 sde.st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);

INSERT INTO sensitive_areas (shape) VALUES (
 sde.st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);

INSERT INTO hazardous_sites (site) VALUES (
 sde.st_geometry ('point (60 60)', 4326)
);

INSERT INTO hazardous_sites (site) VALUES (
 sde.st_geometry ('point (30 30)', 4326)
);

--Create a buffer around the hazardous sites, then find the hazardous site buffers that intersect sensitive areas.
SELECT sa.id AS sid, hs.id AS hid
 FROM sensitive_areas sa, hazardous_sites hs
 WHERE sde.st_intersects (sde.st_buffer (hs.site, .1), sa.shape) = 't'
 ORDER BY sa.id;

sid     hid

  1       2
  2       2
  3       1

SQLite

--Create and populate tables.
CREATE TABLE sensitive_areas (
 id integer primary key autoincrement not null
); 

SELECT AddGeometryColumn (
 NULL,
 'sensitive_areas',
 'shape',
 4326,
 'polygon',
 'xy',
 'null'
);

CREATE TABLE hazardous_sites (
 id integer primary key autoincrement not null
);

SELECT AddGeometryColumn (
 NULL,
 'hazardous_sites',
 'site',
 4326,
 'point',
 'xy',
 'null'
);

INSERT INTO sensitive_areas (shape) VALUES (
 st_geometry ('polygon ((20 30, 30 30, 30 40, 20 40, 20 30))', 4326)
);

INSERT INTO sensitive_areas (shape) VALUES (
 st_geometry ('polygon ((30 30, 30 50, 50 50, 50 30, 30 30))', 4326)
);

INSERT INTO sensitive_areas (shape) VALUES (
 st_geometry ('polygon ((40 40, 40 60, 60 60, 60 40, 40 40))', 4326)
);

INSERT INTO hazardous_sites (site) VALUES (
 st_geometry ('point (60 60)', 4326)
);

INSERT INTO hazardous_sites (site) VALUES (
 st_geometry ('point (30 30)', 4326)
);

--Create a buffer around the hazardous sites, then find the hazardous site buffers that intersect sensitive areas.
SELECT sa.id AS "sid", hs.id AS "hid"
 FROM sensitive_areas sa, hazardous_sites hs
 WHERE st_intersects (st_buffer (hs.site, .1), sa.shape) = 1
 ORDER BY sa.id;

sid     hid

  1       2
  2       2
  3       1