Split Lines with Points PostGIS

Question

I have a line table and a points table in PostGIS. I need to split the line table with the points table and return all geometries.

Tried...

CREATE TABLE lines_split AS (
SELECT
a.seg_name, ((ST_DUMP(ST_SPLIT(a.geom,b.geom))).geom) as geom
FROM lines_table a
INNER JOIN points_table b
ON ST_INTERSECTS(a.geom, b.geom)
);

This runs successfully with unusual results. It returns a subset (pink lines) of the lines which are not split by the points...

I have this now...

I want this...

How do I perform this task in PostGIS?

Answer 1

The core issue with your query is that you split every line by every point individually, as a result of the table join; if one line is to be split by two points, that same original line will be split once for each point instead of twice!

Also, you need to explicitly select those geometries that are not split; I just updated the queries to include them.

You need to split each line by all points:

WITH
  blade AS (
    SELECT ST_Collect(geom) AS geom
    FROM   points_table
  )

SELECT a.seg_name,
       (ST_Dump(ST_Split(a.geom, b.geom)).geom
FROM   lines_table AS a,
       blade AS b
UNION ALL
SELECT seg_name,
       geom
FROM   lines_table AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM   points_table AS b
  WHERE  ST_Intersects(a.geom, b.geom)
);

or, for a large amount of points as blade:

SELECT seg_name,
       (ST_Dump(ST_Split(
         a.geom,
         (SELECT ST_Collect(b.geom) AS geom FROM points_table AS b WHERE ST_Intersects(a.geom, b.geom)
       )).geom
FROM   lines_table AS a
UNION ALL
SELECT seg_name,
       geom
FROM   lines_table AS a
WHERE NOT EXISTS (
  SELECT 1
  FROM   points_table AS b
  WHERE  ST_Intersects(a.geom, b.geom)
);

---

A core issue with your data may be that, if your points are not directly derived off the lines, the chance that they don't match is very high (PostGIS stores up to 15 decimal places of coordinate precision...plenty of room for mismatches)!

If some of your lines are not split at all, you have to make the points snap to the lines. Two ways of doing this:

• use ST_Snap with a tiny tolerance to match each segment to the blade (snapping the blade to the segments would result in one point of the blade being snapped to the segment only), which results in slightly misplaces split lines
• update the points_table with the the ST_ClosestPoint within a Nearest Neighbor search (see e.g. my answer here); that way you guarantee that points will be snapped to their nearest segment. This might lead to mismatched points in edge cases (a point close to two segments e.g. at an intersection, might lead to that point being located on the wrong segment)