Exploding multi-geometries with PostGIS

# Posted on Wednesday, 27th April 2011 , under PostGIS Natural Earth

I recently needed a bit of geoprocessing for which PostGIS turned out to be the perfect tool. I'll share my findings in case they are useful to somebody. But first of all, here goes some context: I had imported the countries dataset from Natural Earth to PostGIS (piece of cake with ogr2ogr), and I needed to obtain the extent or bounding box of each country.

Getting the extents

The most obvious choice seemed ST_Extent, but there is a better choice: ST_Envelope. ST_Extent is an aggregate function, so you need to group by one of the table's columns, and it returns a bbox3d_extent object instead of an actual geometry. ST_Envelope will return a geometry (a polygon in our case):

CREATE TABLE world_extents AS 
SELECT ST_Envelope(mpoly) AS the_geom,name 
FROM world_worldborders;

The result of the previous statement was not as exciting as expected when rendered in QGIS:

Messy extents

The Natural Earth borders dataset has one multi-polygon per country, which includes all the land polygons which pertain to it. In consequence, countries like the United States had extents stretching the whole world, from Hawaii to the Bering Strait islands. I needed a previous step to obtain one separate polygon for each land mass or island.


GIS users will be familiar with the operation known as explode. It is used to obtain individual features from a multipart feature. So instead of having a single polygon for all Japan, after exploding it you will get an individual polygon for each of its islands.


There are different ways of performing it in desktop GIS applications, but in can also be done in PostGIS using a couple of different functions. I adapted this statment from the slides of the "Tips for the PostGIS power user" by Paul Ramsey, presented in last year's edition of FOSS4G (The slides are a must read for any PostGIS enthusiast). Let's have a look at the final statement and discuss it later:

CREATE TABLE world_worldborders_2 AS 
SELECT id,name,the_geom 
FROM (SELECT id,name,ST_GeometryN(mpoly,generate_series(1,ST_NumGeometries(mpoly))) AS the_geom
FROM world_worldborders
) AS foo;
  • ST_GeometryN will return the geometry at the provided index in a geometry collection or multipart geometry.
  • generate_series is a set returning function (it returns more than one row). It generates a series of values from the start to the end values provided.
  • ST_NumGeometries does what it says on the tin, returning the number of geometries in a collection or multigeometry.

There is a faster approach using ST_Dump, which returns a set of the individual geometries that make up a multi one. Notice that it returns a composite type with a path and geom properties, so you need to use the dot notation to access the returned geometry:

CREATE TABLE world_worldborders_3 AS
SELECT id,name,
FROM world_worldborders;

The result

The combined statement produced a more nice looking layer, with extents following more closely the actual outline of the countries:

CREATE TABLE world_extents_2 AS 
SELECT id,name,ST_Envelope(the_geom) AS the_geom
FROM (SELECT id,name,ST_GeometryN(mpoly,generate_series(1,ST_NumGeometries(mpoly))) AS the_geom
FROM world_worldborders
) AS foo;
Nice extents


Jorge Gustavo Rocha
# Tuesday, 17th May 2011, 21:43

Thanks, Adrià. Very useful.

In my case, I've imported a large KML with ogr2ogr. Only 8 records were created, by each one with a very large collection of line segments. So I've exploded that 8 records in 44948. Much better!

ogr2ogr -overwrite -skipfailures -f "PostgreSQL" PG:"host=localhost user=geobox dbname=osm password=geobox" Vias.kml

And then:

CREATE TABLE vias.road AS SELECT ogc_fid, name, (ST_Dump(wkb_geometry)).geom FROM vias.kml;


# Thursday, 21st June 2012, 13:15

Hello Adriá, i have a question, as u know you can import from kml to postgis with ogr, and i want to know, if it's possible, import data from a kml that has a multiple types of geometries (line, point and polygon) with this tools, just like with a single geometry type kml....

New comment:

(optional and not stored, used for gravatar)
  • Links will be automatically created for you.
  • You can use Markdown syntax (**bold**,_em_, bullet points for lists. Complete reference).
  • Please do not use html tags.