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.

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.

  • 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 Jul 27 '15 at 13:58
  • What happens with ST_Union? – user30184 Jul 27 '15 at 15:47
  • Use ST_Multi() to promote linestrings to multilinestrings. – Michal Zimmermann Jul 27 '15 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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