Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

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.

share|improve this answer
    
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 '11 at 21:46
add comment

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.

share|improve this answer
    
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 '11 at 15:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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