Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question

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
share|improve this answer
up vote 4 down vote accepted

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"
share|improve this answer

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

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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