2

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...

unusual subset results

I have this now... I have this now

I want this... I want this

How do I perform this task in PostGIS?

  • @thingumabob can you help here? Can I hire you to finish a small task? – wtgeographer Aug 18 at 20:24
  • Possible duplicate? gis.stackexchange.com/questions/112282/… (cannot vote to close bountied questions) – alphabetasoup Aug 18 at 23:59
  • This is not a duplicate. I have tried every other related post to accomplish this simple task and nothing has worked. Ive offered a bounty for this task. If you have knowledge here please divulge. – wtgeographer Aug 19 at 0:54
  • I'm not available right now for extra work, or for anything else than answering a question in my coffee break. – ThingumaBob Aug 19 at 10:33
3
+50

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)
  • note that, if this is about splitting lines at their intersection, better use ST_Node. – ThingumaBob Aug 19 at 11:17
  • @wtgeographer did this work? have I overlooked sth. in my answer...? – ThingumaBob Aug 20 at 20:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.