Merging linestrings in QGIS using PostgreSQL and PostGIS

Merging linestrings in QGIS using PostgreSQL and PostGIS

I've to merge all linestrings from a PostgreSQL table "river" where not more than two start-/endpoints are overlapping. In the illustration below I use a "M" to mark all linestrings that needs to be merged.

Unfortunately I'm new to PostGIS. To my knowledge QGIS needs an id (gid) and a geometry (geom) column to load a table or view. But I'm not sure which PostGIS function will do the job. Can anyone please help me with the SQL query?

There is ST_LineMerge function

You could try to serve all your rivers network as one MultiLineStringST_LineMerge(ST_Multi(St_Collect(geometry)))The result is also a MultiLineString with segments sewed together. So after ST_LineMerge you could get sewed segments via ST_Dump.

Not tested, but it might give you what you want. Withintersectionsyou get those ids and geometries that intersect with others. And theSELECTstatement willST_Unionall of these records that intersect exactly with two other geometries.ST_Dumpmakes sure you don't get MultiLinestrings as a result.

WITH intersections AS ( SELECT, l1.wkb_geometry COUNT(1) FROM lines l1 JOIN lines l2 ON ST_Intersects(l1.wkb_geometry, l2.wkb_geometry) AND <> GROUP BY, l1.wkb_geometry ) SELECT ST_Dump(ST_Union(i.wkb_geometry)).geom wkb_geometry FROM intersections i WHERE count = 2 ;