Join intersecting lines with PostGIS

by Nathan W   Last Updated May 15, 2019 20:22 PM

This is something that I'm almost ashamed to ask but I can't seem to get it to work for the life of me.

I have a road layer with segments, each segment has a Road ID and a segment type.

I would like to join all the segments together, for each Road ID into one linestring but only when they are the same type and are touching (all the lines are snaped together).

enter image description here

Road ID - Type
   1       L
   1       L
   1       T
   1       L
Tags : postgis sql

Answers 3

Seems this works

SELECT a."Road_ID",a."Road_Type", ST_LineMerge(ST_Collect(a.the_geom))
FROM "RoadCentreLines" as a 
LEFT JOIN "RoadCentreLines" as b ON 
    AND a."Road_Type" = b."Road_Type" 
    AND a."Road_ID" = b."Road_ID"
GROUP BY ST_Touches(a.the_geom,b.the_geom), a."Road_Type", a."Road_ID"
Nathan W
Nathan W
November 09, 2011 02:03 AM

Just spit-balling but I can think of a few other solutions. I don't know if they're better or worse, just that they're other.

First, if there are only a few road types you can go type-by-type with something like:

WITH type As (SELECT "Road_ID" As id WHERE "Road_Type" = 'type')
SELECT ST_LineMerge (ST_Collect(the_geom))
FROM "RoadCentreLines"
WHERE ST_StartPoint(the_geom) && ST_EndPoint(the_geom) IS TRUE
AND "Road_ID" IN (SELECT id FROM type);

You could also use most of the above with Road_Type as the variable in a FOR loop if there are a bunch of types.

My last thought involved merging all the geometries, then calling out road types with the ST_Line_Substring function (Link-Link) but that won't work at all.

Best of luck with it, Rob

November 09, 2011 05:42 AM

I believe the code below is a little cleaner solution than the selected answer for a couple of reasons. First no table joins are necessary and thus an addendum to the 'ON' clause is not needed for each of the street attributes, and second the above methodology can potentially produce multi-linestrings if there are multiple, non-contiguous clusters of streets that have all of the same attribute values, whereas ST_Dump takes care of that issue in this solution.

SELECT (ST_Dump(geom)).geom AS geom, "road_id", "road_type"
    SELECT ST_LineMerge(ST_Union(geom)) AS geom, "road_id", "road_type"
    FROM "RoadCentreLines"
    GROUP BY "road_id", "road_type"
) AS street_union
Grant Humphries
Grant Humphries
December 11, 2013 00:55 AM

Related Questions

Updated February 20, 2017 03:22 AM

Updated October 04, 2018 22:22 PM

Updated October 05, 2018 22:22 PM

Updated December 11, 2018 13:22 PM

Updated February 01, 2019 21:22 PM