Converting individual line segments into line using PostGIS?

Question

I have groups of individual linestrings segments with spatially coincident start & end points. I want to convert them into individual linestrings as seen in the picture:

This seems like it should be very easy but I cannot figure out how to do it. ST_Union is just returning all features in the entire set, i.e. every single feature from both groups in the picture becoming a single multilinestring.

I don't have a common identifier field for the groups of linestrings, the only thing that indicates that they belong together is the fact that they touch each other at their endpoints.

Am I missing something obvious here?

Answer 1

I think you want ST_LineMerge. I don't have a PostGIS handy right now, but here it is in Spatialite, combining three line segments into one longer segment and a detached short segment:

SELECT ST_AsText(ST_LineMerge(
   ST_GeomFromText('MULTILINESTRING ((4 4, 3 7), (1 3, 2 4), (1 3, 3 6))')));
MULTILINESTRING((2 4, 1 3, 3 6), (4 4, 3 7))

you may have to ST_UNION your features to get a MULTILINESTRING to feed it.

PostGIS LineMerge doc here: https://postgis.net/docs/ST_LineMerge.html

With integer coordinates this obviously works well, but look out for floating point arithmetic resulting in non-joining lines - I'm not sure what the tolerance is for snapping segments together with this.