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?

  • 1
    The SQLite query planner does not take advantage of the spatial index- you must explicitly use it in your query. However, a spatial index will not be useful for st_union. – Scro Oct 8 '12 at 10:29
  • In this case, Is the query correct? Or can I improve the efficiency? – Alexandre Neto Oct 8 '12 at 11:40
  • I tried this and found that even without any indexes, the table scan and grouping happens very quickly. It is the sptiaLite aggregate function that is running slowly. I hope somebody can comment on this. – Scro Oct 8 '12 at 13:55
  • Indeed, with the same query but using sum(st_area(geometry)) instead of St_Union(geometry), the query is solved in 5 sec. – Alexandre Neto Oct 8 '12 at 14:40
  • 2
    Perhaps this would be worth posting on the spatialite mailing list: groups.google.com/group/spatialite-users – BradHards Oct 8 '12 at 21:36

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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