3

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?

  • 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 May 15 '15 at 8:02
4
+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.

  • 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 Jul 5 '15 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
;
  • 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 Jul 4 '15 at 19:54
  • ok, so replace my lines table with your river table and my columns with your columns. – Michal Zimmermann Jul 4 '15 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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