Split Lines at intersections

split_lines.sql

-- Sample data
CREATE TABLE pipes (id serial, geom geometry(LineString));
INSERT INTO pipes(geom) VALUES (ST_MakeLine(ST_MakePoint(1,1), ST_MakePoint(4,1)));
INSERT INTO pipes(geom) VALUES (ST_MakeLine(ST_MakePoint(1,0), ST_MakePoint(3,0)));

CREATE TABLE connections (id serial, geom geometry(LineString));
INSERT INTO connections(geom) VALUES (ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(1,1)));
INSERT INTO connections(geom) VALUES (ST_MakeLine(ST_MakePoint(0,2), ST_MakePoint(1,1)));
INSERT INTO connections(geom) VALUES (ST_MakeLine(ST_MakePoint(2,2), ST_MakePoint(2,1)));
INSERT INTO connections(geom) VALUES (ST_MakeLine(ST_MakePoint(4,0), ST_MakePoint(4,1)));
INSERT INTO connections(geom) VALUES (ST_MakeLine(ST_MakePoint(3,2), ST_MakePoint(4,2)));

-- Split "pipes" query should return 2 linestrings
WITH splits AS (
  SELECT p.*, (ST_Dump(ST_Split(p.geom,c.geom))).geom AS dump 
    FROM pipes p, connections c
) 
SELECT id, ST_AsText(dump) FROM splits 
  WHERE NOT ST_Equals(geom,dump);
  
-- Result
 id |      st_astext      
----+---------------------
  1 | LINESTRING(1 1,2 1)
  1 | LINESTRING(2 1,4 1)
(2 rows)

-- Assumptions:
-- (1) Connections split pipes (returns pipes)
-- (2) Pipes are split at least once (unsplit pipes will not appear in the query result)
-- (3) Connections touch or cross pipes (no split tolerance)
-- (4) This is not tested with MultiGeometries