I have a table in PostGIS with a quite fine partition of a region, which I'd like to dissolve at two higher levels by means of two foreign keys (e.g. municipality and state). The partition is a real one, i.e. the areas don't overlap, they just touch each other. I thought it'd be nice to do that in views, to follow the "don't insert into the database something which can be calculated from other existing data". However, a simple

SELECT id_municipality, ST_Union(the_geom)
FROM areas
GROUP BY id_municipality

takes around 22s on a core i7 for "just" around 36000 areas with a POLYGON geometry field. I indexed the geometry field with a GIST index, but with no luck.

I am now thinking that the only practical way would be to insert a geometry column in the municipalities table and calculate it beforehand.

Unfortunately, I need to share my results to less technical people, so installing experimental packages (like topological extensions) is not practical.

2 Answers 2


When we're dissolving geometries, we use ST_Multi(ST_BuildArea(ST_Buffer(ST_collect(geom), 0))).

ST_collect groups the geometry. ST_Buffer(geom, 0) 1. cleans up the geometry (removing potential duplicative points) and 2. provides only the exterior ring. ST_BuildArea(geom) turns the buffer output into a polygon.

Then ST_Multi(geom) turns it into a multipolygon. I don't know why we're doing that, but I'm assuming we have a sane reason which is currently eluding me ;) (Edit: The geometries we're dissolving aren't necessarily touching. It might have something to do with that.)

I don't know how this compares speedwise to ST_Union. My technical lead, sitting next to me, indicates it is faster and more reliable than ST_Union was when it was called geomunion.

  • And... I believe this is actually a bad solution. In ~2009 this was apparently faster, but according to the couple of queries I just ran, ST_Union seems to be take the cake. I'm guessing you're going to have to calculate it beforehand...
    – canisrufus
    Aug 2, 2011 at 21:46

If you want to precalculate, one way of getting the job done cleanly would be to use the function dissolve in Quantum GIS. You will have a single geometry created for each region made of municipality, not a set of geometry. In order to do that, use the municipality table that must have a foreign key that is the identifier of the area. Then in Quantum GIS user Vector--> Geoprocessing Tools --> Dissolve. It will probably take a long time, but you will have the processing done once for all. Dissolving geometry is an ressource-consumming process anyway. Once the job is done, export the new layer in PostGIS and use the identifier in order to associate your geometry with the table describing your regions.

  • That's exactly what the query above does, so there'd be no need of going back and forth from PostGIS. I was looking for a way of computing it in reasonable time (3-4s at most).
    – Metiu
    Aug 2, 2011 at 15:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.