More

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 http://postgis.net/docs/manual-2.0/ST_LineMerge.html

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.id, l1.wkb_geometry COUNT(1) FROM lines l1 JOIN lines l2 ON ST_Intersects(l1.wkb_geometry, l2.wkb_geometry) AND l1.id <> l2.id GROUP BY l1.id, l1.wkb_geometry ) SELECT ST_Dump(ST_Union(i.wkb_geometry)).geom wkb_geometry FROM intersections i WHERE count = 2 ;