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:
- 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.
- 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
- 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:
- The result see the lines cut not only on the points geometry, but also every time a line crosses a line
- 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