[GIS] Simplifying multipolygon using PostGIS

polygonpostgisqgis-2

I am using PostGIS 2.0.

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?

Best Answer

The Postgis docu suggest to wrap a ST_Multi around it to get a multi-geometry:

SELECT stusps,
       ST_Multi(ST_Union(f.the_geom)) as singlegeom
     FROM sometable As f
GROUP BY stusps
Related Question