7

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?

Improve this question
5
  • 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, 2012 at 10:29
  • In this case, Is the query correct? Or can I improve the efficiency?
    – Alexandre Neto
    Oct 8, 2012 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, 2012 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, 2012 at 14:40
  • 2
    Perhaps this would be worth posting on the spatialite mailing list: groups.google.com/group/spatialite-users
    – BradHards
    Oct 8, 2012 at 21:36

1 Answer 1

Reset to default
11

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;
Improve this answer

Your Answer

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

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