-- The GIS technician has been asked by his boss to provide a list
-- of all sewer lines whose endpoints 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.

CREATE TABLE sewerlines (sewer_id  integer,
                         class     integer,
                         sewer     ST_LineString);

INSERT INTO sewerlines VALUES(
   1, 1,
   ST_LineFromText('linestring (42000 10000,46000 14000)',1000)
);

INSERT INTO sewerlines VALUES(
   2, 2,
   ST_LineFromText('linestring (43000 11000,44000 10000)',1000)
);

INSERT INTO sewerlines VALUES(
   3, 2,
   ST_LineFromText('linestring (42000 12000,43000 11000)',1000)
);

INSERT INTO sewerlines VALUES(
   4, 2,
   ST_LineFromText('linestring (44000 12000,45000 11000)',1000)
);

INSERT INTO sewerlines VALUES(
   5, 2,
   ST_LineFromText('linestring (43000 13000,44000 12000)',1000)
);

INSERT INTO sewerlines VALUES(
   6, 2,
   ST_LineFromText('linestring (45000 13000,46000 12000)',1000)
);

INSERT INTO sewerlines VALUES(
   7, 2,
   ST_LineFromText('linestring (44000 14000,45000 13000)',1000)
);

-- The query returns an ordered list of sewer_ids that touch one another.

SELECT s1.sewer_id, s2.sewer_id
   FROM sewerlines s1, sewerlines s2
   WHERE ST_Touches(s1.sewer, s2.sewer);