Efficient Dissolution of Polygons Using Spatialite 3.0

Question

I have polygon table called "tableA" with 72000 records. It has a geometry column called "geometry", and a text column called "field1". I want to aggregate\dissolve all polygons with the same "field1" value.

I have a Spatial Index in Geometry and an index in field1.

I tried this:

Select f.field1 as field1, st_union(f.geometry) as geometry
From tableA as f
Group by field1;

and its taking too long, I had to cancel it after being processing for 1 hour. Using Arcgis it toke me 5 minutes, so I must be doing something wrong.

So, is there a better way to preform this operation using spatialite? Is the Spatial Index being used this way?

Answer 1

After asking in several different forums and mailing list, I found my answer in the Spatialite dedicated google group, as posted by @BradHards.

In fact St_Union is not working as expected as sandro furieri have tested and explained here.

And this is the beauty of Open source, the issue is already taken care of, and will be available in Spatialite 4.0.0.

So while waiting for Spatialite 4.0.0 the workaround solution is:

Select f.field1 as field1, st_unaryunion(st_collect(f.geometry)) as geometry
From tableA as f
Group by field1;