Merging linestrings in QGIS using PostgreSQL and PostGIS

Question

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.

enter image description here 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?

• John Powell: There is not, directly. ST_Union will combine all intersecting geometries, but you will need to build a list of all linestrings with only one intersection on each end, which you can get via ST_StartPoint and ST_EndPoint and a count of the intersections.

Answer 1

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 MultiLineString ST_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().

• Lunar Sea: CREATE VIEW river_merge AS SELECT row_number() over() AS gid, g.* FROM (SELECT (ST_Dump(ST_LineMerge(ST_Multi(St_Collect(geom))))).geom::geometry(linestring, SRID) AS geom FROM river) AS g;

Answer 2

Not tested, but it might give you what you want. With intersections you get those ids and geometries that intersect with others. And the SELECT statement will ST_Union all of these records that intersect exactly with two other geometries. ST_Dump makes 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
;

• Lunar Sea: I'm sorry, I don't unterstand this. There is only one PostGIS table: CREATE TABLE river (gid serial NOT NULL, geom geometry(linestring, SRID), CONSTRAINT river_pkey PRIMARY KEY (gid));
• Michal Zimmermann: ok, so replace my lines table with your river table and my columns with your columns.