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

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 2

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