Simplifying multipolygon using PostGIS

Question

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?

Answer 1

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

Answer 2

I looks like you are on the right track. I can't tell why it is not working but I have some modest suggestions to help you debug this. (Incidentally, if possible, try and load your data using lower case names - I think it makes the SQL more readable).

First, make sure that this command returns what you expect:

SELECT 
  a.name,
  a.geom  
FROM 
  public."africa_ALL" as a
WHERE 
 name = "Mali";

If the data is structured as you describe, it should return something like this:

name | geom
Mali | a23123h12312
Mali | asd2234234

If it does not return this for a country with multiple geometries, then something else is wrong.

Then, @AndreJoost's reply (just reorganizing it a tiny bit) should work:

SELECT 
 a.name,
 ST_Multi(ST_Union(a.geom)) as country_geom
FROM 
 public."africa_ALL" as a
GROUP BY 
 a.name