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?
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.