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.
I tried using:
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?
Best Answer
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:
or, for a large amount of points as blade:
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:
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 linespoints_table
with the theST_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)