ST_Touches returns 1 (Oracle) or t (PostgreSQL) if none of the points common to both geometries intersect the interiors of both geometries; otherwise, it returns 0 (Oracle) or f (PostgreSQL). At least one geometry must be an ST_LineString, ST_Polygon, ST_MultiLineString, or ST_MultiPolygon.
Oracle
sde.st_touches (g1 sde.st_geometry, g2 sde.st_geometry)
PostgreSQL
st_touches (g1 st_geometry, g2 st_geometry)
The GIS technician has been asked by his boss to provide a list of all sewer lines that possess endpoints that intersect another sewer line.
The sewerlines table is created with three columns. The first column, sewer_id, uniquely identifies each sewer line. The integer class column identifies the type of sewer line generally associated with the line's capacity. The sewer ST_Linestring column stores the sewer line's geometry.
Oracle
CREATE TABLE sewerlines (sewer_id integer,
sewer sde.st_geometry);
INSERT INTO sewerlines VALUES (
1,
sde.st_mlinefromtext ('multilinestring ((20 30, 30 30, 30 40, 20 40, 20 30))', 0)
);
INSERT INTO sewerlines VALUES (
2,
sde.st_mlinefromtext ('multilinestring ((30 30, 30 50, 50 50, 50 30, 30 30))', 0)
);
INSERT INTO sewerlines VALUES (
3,
sde.st_mlinefromtext ('multilinestring ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
);
INSERT INTO sewerlines VALUES (
4,
sde.st_linestring ('linestring (60 60, 70 70)', 0)
);
INSERT INTO sewerlines VALUES (
5,
sde.st_linestring ('linestring (30 30, 60 60)', 0)
);
PostgreSQL
CREATE TABLE sewerlines (sewer_id integer,
sewer st_geometry);
INSERT INTO sewerlines VALUES (
1,
st_multilinestring ('multilinestring ((20 30, 30 30, 30 40, 20 40, 20 30))', 0)
);
INSERT INTO sewerlines VALUES (
2,
st_multilinestring ('multilinestring ((30 30, 30 50, 50 50, 50 30, 30 30))', 0)
);
INSERT INTO sewerlines VALUES (
3,
st_multilinestring ('multilinestring ((40 40, 40 60, 60 60, 60 40, 40 40))', 0)
);
INSERT INTO sewerlines VALUES (
4,
st_linestring ('linestring (60 60, 70 70)', 0)
);
INSERT INTO sewerlines VALUES (
5,
st_linestring ('linestring (30 30, 60 60)', 0)
);
The query returns an ordered list of sewer IDs that touch one another.
Oracle
SELECT s1.sewer_id, s2.sewer_id
FROM sewerlines s1, sewerlines s2
WHERE sde.st_touches (s1.sewer, s2.sewer) = 1;
SEWER_ID SEWER_ID
1 5
3 4
4 3
4 5
5 1
5 3
5 4
PostgreSQL
SELECT s1.sewer_id, s2.sewer_id
FROM sewerlines s1, sewerlines s2
WHERE st_touches (s1.sewer, s2.sewer) = 't';
SEWER_ID SEWER_ID
1 5
3 4
4 3
4 5
5 1
5 3
5 4