Join intersecting lines with PostGIS

Question

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

Road ID - Type
   1       L
   1       L
   1       T
   1       L

Answer 1

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"
FROM (
    SELECT ST_LineMerge(ST_Union(geom)) AS geom, "road_id", "road_type"
    FROM "RoadCentreLines"
    GROUP BY "road_id", "road_type"
) AS street_union

Answer 2

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 
ST_Touches(a.the_geom,b.the_geom) 
    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"

Answer 3

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