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)