Split lines with points, the PostGIS way

The problem:

Given a layer of lines and one of points (the latter not necessarily already over the lines one) , split the lines using the points.

Five years after a feature request on the QGIS bug tracker

https://hub.qgis.org/issues/5040

this tool is not yet available as native QGIS tool and also an (easy) alternative is missing within the other providers in the QGIS Processing toolbox.

Here following a pure SQL/PostGIS solution. Not sure is the most elegant or fast one, but seems to work as expected.

Workflow and notes:

• As the input points are away from the lines, get the closest place on the line (likely on a segment) using the st_closestpoint function
• Input lines are densified with the st_segmentize: this will later allow to snap the input points -now over the segments- over a node/vertex, a really close one depending on how much the lines were densified
• Extract the nodes/vertexes of densified lines with a custom function:

CREATE OR REPLACE FUNCTION ST_AsMultiPoint(geometry) RETURNS geometry AS
'SELECT ST_Union((d).geom) FROM ST_DumpPoints($1) AS d;'
LANGUAGE sql IMMUTABLE STRICT COST 10;

• Snap points (the ones over lines segments) over the nearest node/vertex of the densified lines using the st_snap function
• Split the lines using snapped points thanks to the st_split function
• The above process is necessary as points can be used to split lines only if they are exactly over a line node/vertex (st_split won’t work in the points are over a line segment)

Step1: input points on nearest place over lines segments

CREATE TABLE points_over_lines
AS SELECT a.id,ST_ClosestPoint(ST_Union(b.geom), a.geom)::geometry(POINT,3763) AS geom
FROM points a, lines b
GROUP BY a.geom,a.id;

Step2: densify lines and extract nodes as 1 unique multipoint geometry

CREATE TABLE lines_nodes_densified AS
SELECT 1 AS id, ST_Union(ST_AsMultiPoint(st_segmentize(geom,1)))::geometry(MULTIPOINT,3763) AS geom 
FROM lines;

Step3: snap points over lines nodes/vertexes

CREATE TABLE points_snapped AS
SELECT b.id, ST_snap(ST_Union(b.geom),a.geom, ST_Distance(a.geom,b.geom)*1.01)::geometry(POINT,3763) AS geom 
FROM lines_nodes_densified a, points_over_lines b
GROUP BY a.geom, b.geom, b.id;

Step4: split lines

CREATE TABLE lines_split AS
SELECT a.id, (ST_Dump(ST_split(st_segmentize(a.geom,1),ST_Union(b.geom)))).geom::geometry(LINESTRING,3763) AS geom 
FROM lines a, points_snapped b
GROUP BY a.id;

With one unique query statement:

CREATE TABLE lines_split AS
WITH 
temp_table1 AS (SELECT a.id,ST_ClosestPoint(ST_Union(b.geom), a.geom)::geometry(POINT,3763) AS geom FROM points a, lines b GROUP BY a.geom,a.id),
temp_table2 AS (SELECT 1 AS id, ST_Union(ST_AsMultiPoint(st_segmentize(geom,1)))::geometry(MULTIPOINT,3763) AS geom FROM lines),
temp_table3 AS (SELECT b.id, ST_snap(ST_Union(b.geom),a.geom, ST_Distance(a.geom,b.geom)*1.01)::geometry(POINT,3763) AS geom 
FROM temp_table2 a, temp_table1 b
GROUP BY a.geom, b.geom, b.id)
SELECT a.id, (ST_Dump(ST_split(st_segmentize(a.geom,1),ST_Union(b.geom)))).geom::geometry(LINESTRING,3763) AS geom FROM lines a, temp_table3 b
GROUP BY a.id;