Practical way of managing polygons and unions of them

Question

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.

Answer 1

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.

Answer 2

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.