Wednesday 17 June 2015

sql - Join intersecting lines with PostGIS


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

Answer



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"

No comments:

Post a Comment

arcpy - Changing output name when exporting data driven pages to JPG?

Is there a way to save the output JPG, changing the output file name to the page name, instead of page number? I mean changing the script fo...