3

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.

Marty

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
  • thanks for the comment. ST_Multi produces the same result i.e. the new singlegeom column is mostly empty apart from where a country only had one geometry to begin with... – marty_c Jan 27 '14 at 15:00
0

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

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.