6

I have a road network of center lines of MultiLinestrings. They have nodes at every intersection. I try to understand how I can merge roads together that have the same category as attribute (there are about 80 different categories). Roads with the same category are not always connected, so I guess I need to have the result back as a MultiLineString again but grouped on attribute value.

Is there a way to do this?

Edit: I played around some more and almost got the result I wanted. Simply using 

SELECT  "category", ((ST_Collect(geom))) as geom FROM s.road GROUP BY "category"

But this seem to be returned as a Geometrycollection of Multilinestrings and cannot be loaded into QGIS. Any advice?

Edit 28.07

Final solution:

SELECT  "category", ((ST_Union(geom))) as geom FROM s.road GROUP BY "category"

Using ST_Union instead of ST_Collect gave me the results I wanted. For beginners in SQL like myself can I explain why. ST_Union dissolve overlapping and overlapping regions whereas Collect does not. The consequences using ST_collect in this case with MultiLineString is that the result is a Geometry collection that is not a supported type for QGIS to load.

Improve this question

1 Answer 1

Reset to default
4 
SELECT category, ST_Union(geom) geom FROM s.road GROUP BY category;

You might want to use ST_Dump() afterwards not to have multilinestrings in the result.

Improve this answer
3
  • Thanks I tried, but it did not work for me. I Have however got it into Linestrings (10k<) and now need to get the Linestring with the same category to create a MultiLineString. Never done these things before, am I completely out?
    – geogrow
    Commented Jul 27, 2015 at 13:58
  • What happens with ST_Union?
    – user30184
    Commented Jul 27, 2015 at 15:47
  • Use ST_Multi() to promote linestrings to multilinestrings.
    – Michal Zimmermann
    Commented Jul 27, 2015 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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