1

I have a question about spatial indexes. I'm using SQLite with VirtualSpatialIndex.

I have a main table that contains millions of polygons. Let's say every 4 months I have to get a completely new snapshot of the polygons from another party.

Meaning, I should delete all the records of the polygons from the main table (i.e., truncate main table), and then insert all the records from the new snapshot. That's why I need to re-build the spatial index.

How should I do it?

Spatialite Functions list reference After checking the SpatiaLite functions list I came with the following solution.

  1. Truncate main table

  2. Insert new snapshot to main table

  3. Use RecoverSpatialIndex('MainTable','MyGeoCol',1) to rebuild The RTree from scratch.

What do you think? Is this right?

1 Answer 1

0

I would do the task with these steps

  • DisableSpatialIndex
  • Truncate table
  • Insert new data
  • RecoverSpatialIndex

If the spatial index is disabled the index does not need to be updated due to truncate. Also new data can be inserted without updating the spatial index by the same. Recovering the whole spatial index at the end of the process is faster than doing updates transaction by transaction. Until very recently building and updating the spatial index has taken at least as much time as it takes to insert data into the main table. Read https://www.gaia-gis.it/gaia-sins/rtree-benchmark.html about three times faster spatial index creation in the current development version of SpatiaLite.

When you insert the new data use big transactions. In SQLite it is expensive to begin and end a transaction.

Your Answer

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

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