PostGIS 2.3 - Union of lines if not starting/ending on a point table

Question

I have a table with some lines (that are in fact multi-linestrings, even if it is not really needed, but that's another problem) that sometimes start and end on the location of a point table, sometimes not, like if what could be a single line what split in different lines. Here is how it could look like, I purposely drew the lines in different colors so we can see how each line is.

I would like to get the couple of points that are connected by the lines, and in order to do so I suppose the way to go is to first get a simpler table that union the lines in order to have only lines between the points, it would give something like that :

The way I see it, there are 3 different cases:

• the line start and end on a point, so there is nothing to be done
• the lines start or end on a point, and touches another line, in that case these two lines must be unioned
• a line doesn't nor start nor end on a point, but touches two other lines that possibly do start or end on a point (even though I have some cases where there are more than one line with no starting or end on a point, but are on the same segment)

I try to conceptualize how to do so, I am guessing:

1. it would be possible to use plpgsql to iterate through the lines and create a new table with the proper geometry. But maybe there is way to do I don't know about with the PostGIS functions.
2. it would be needed, through some CTE, to first detect how many points a line intersects (or touches, but st_intersects is faster than st_touches isn't it?), so the three cases are segregated
3. it will be needed to consider that if the touching point of two lines are on a point, then this point should be excluded, if not all the lines would be merged regardless of the situation (in the second picture, the red and blue lines would just be one line since the middle point is a junction between two lines in the second case).

But from there I cannot figure the way to go, especially that it is possible that lines are intersecting without need to be considered (they intersects geometrically but not in the reality). Here is the piece of script I put together, but since I use a ST_Intersection, it also considers these not needed intersections, and anyway I am stuck with only possibility to merge a maximum of two lines:

WITH points AS (
SELECT id, geom, st_buffer(point.geom, 2) buff FROM point),
WITH lines AS (
SELECT id, st_linemerge(line.geom) geom, count(st_intersection(st_linemerge(line.geom),points.buff)) ngeom FROM line
LEFT JOIN points ON st_intersects(st_linemerge(line.geom), points.buff),
GROUP BY id, st_linemerge(line.geom))
SELECT a.id, b.id, st_union(a.geom, b.geom) FROM lines a
LEFT JOIN lines b ON st_touches(a.geom, b.geom) AND a.id != b.id
LEFT JOIN points ON st_intersects(a.geom, points.geom)
WHERE a.ngeom < 2 AND b.ngeom < 2 AND a.id < b.id
AND st_intersects(a.geom, b.geom)
AND st_intersects(st_intersection(a.geom, b.geom), points.buff) IS FALSE

Once again, my final need is to get the couples of points linked by lines, so in this example I would get 3 couples (I am saying this because it is always possible that my conceptualization of the problem is not the one to go).

EDIT: I looked into the approach proposed by GHRF which propose to merge all lines and split the result by the points.

The thing is that some of my lines are crossing each other, ending in two things:

1. The result see the lines cut not only on the points geometry, but also every time a line crosses a line
2. Merging the lines gives a network as a result, and cutting by the points then can only results in some kind of multilines, which nullyfies the process, given my need

Answer 1

Here is another approach to the problem :

• Merge all of the lines, regardless of whether or not they intersect an endpoint. Once done, you get a single line which intersects all of the endpoints.

• Split the single line using the endpoints, the result being a set of lines starting/ending on those endpoints.

You might have to snap the points to the lines (temporarily) for it to properly work, as these kind of operations require the points to be precisely intersecting the lines. (Which might seem to be the case, but isn't always the case)

I hope this helps!

---

EDIT : Using ST_Collect instead of ST_Union

While browsing the documentation (for another, completely unrelated reason), I stumbled upon ST_Collect, with a note for people who want to use ST_Union instead. Read it here

The bottom line being :

ST_Union will also split linestrings at node intersections, whereas will never split linestrings and in turn just return as MULTILINESTRING.

Therefore, I tried some things, taking into account the fact that lines may intersect.

This is our starting point :

The white squares being the endpoints. The coloured lines are what we want to have as a continuous lines, and each coloured square is a point at which the lines are currently split. So our goal here is to get rid of those small squares.

Step 1 : Collect all the lines :

Create table t1 as 
    Select ST_LineMerge(ST_CollectionExtract(ST_Collect(geom), 2)) as geom 
    From lines;

Had we used ST_Union here, we would have obtained a lot of lines that do not intersect one another. But since we're using ST_Collect, what we get is a single Multilinestring.

Step 2 : Collect all the points :

Create table t2 as
    Select ST_CollectionExtract(ST_Collect(ST_Snap(endpoints.geom, lines.geom, <tolerance>)), 1) as geom 
    From endpoints, lines;

The ST_Snap is optional, but it is here to ensure that the points we'll be using to split the lines later on are actually intersecting those lines. Choosing a tolerance may be tricky, as you can see below, if the tolerance is too high, the result will contain unwanted artefacts :

We want all the red crosses inside the white squares. This is currently not the case. Therefore, you should make sure that the tolerance is just right for the points to be precisely snapped onto the right vertices.

Step 3 : Splitting the lines :

Create table t4 as
    Select (ST_Dump(ST_Split(lines.geom, endpoints.geom))).geom as geom 
    From t1 lines, t2 endpoints;

Using the (optianally) snapped endpoints to split the merged lines, we get what should be precisely what we wanted.

I hope this time around, the problem is actually solved ! It's the first time that I go this deep in trying to solve a problem here :)