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

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
  • My geometry does not merge with either query – Luffydude Nov 21 '17 at 9:59
  • @Luffydude care to provide any more info? It's possible that the geometry you're working with is not aligned such that it is possible for it to be merged – Grant Humphries Nov 29 '17 at 2:35
  • This worked beautifully for me to merge a road dataset based on the road id. This handled the roads with gaps beautifully (no lines across the gap sections). I'm not OP, but this is a great answer. This should be the accepted answer. Thanks. – jbalk May 15 '19 at 17:47
  • This is the best answer! – aborruso Sep 30 '19 at 19:47

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"

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

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.