5

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?

Improve this question
1
  • 1
    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.
    – John Powell
    Commented May 15, 2015 at 8:02

2 Answers 2

Reset to default
6
+100

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

Improve this answer
1
  • 1
    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;
    – Lunar Sea
    Commented Jul 5, 2015 at 20:32
0

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
;
Improve this answer
2
  • 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));
    – Lunar Sea
    Commented Jul 4, 2015 at 19:54
  • ok, so replace my lines table with your river table and my columns with your columns.
    – Michal Zimmermann
    Commented Jul 4, 2015 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.