I have a data set of national boundaries for the African continent. These are in a multipolygon table in postgis. Each country has multiple entries (rows) such that some of the countries in my table have 20 or 30 rows with individual geometries. I want to combine them multiple rows for each country into a single polygon (if possible).
The main reason I want to do this is because I'm creating a map in QGIS with this as my base layer and want to label the countries. At the moment however I get 20 to 30 labels per country - I just want one!
I have tried ST_Union which I thought would combine all of my individual geometries into one (per Country) But it only produced geometries for the countries where there was only one geom to begin with:( This is my query:
SELECT "africa_ALL".name, ST_Union(geom) FROM public."africa_ALL" GROUP BY name ORDER BY name;
Can anyone help out at all, my sql leaves a lot to be desired and I'm desperately trying to get this map finished!
All help greatly appreciated.