ThinkGeo.com    |     Documentation    |     Premium Support

Spatial Index Issue

#1

Hi,

I’ve recently run into an issue when viewing objects from a SqliteFeatureLayer where some features are not displayed on the map even though they are in the database.

The best I can figure is that the Spatial Index has gotten corrupt.

When we write to the database object table, we also make a write to the spatial index in the format (id, minx, maxx, miny, maxy) where id is the corresponding id in the object table. I assume you then use this index when displaying objects on the map for increased speed.

I saw an old topic that mentioned recreating this index using SqliteFeatureSource.CreateSpatialIndex; however, this seems to be creating it from scratch and not just recreating the data. Not only that, how would the indexes become realigned if somehow they got out of line? Do you have a way to test if my index is corrupt and if so a way to fix it?

Regards,
Damian

#2

There is no built-in method to recreate the index. (The CreateView() method creates an index for the current contents of the view, but is awkward to use when you have just a table.)

To check the index table for basic consistency, execute SELECT COUNT(*) FROM MyTable and SELECT COUNT(*) FROM idx_MyTable_geometry. To search for missing IDs, use a query like this:

SELECT * FROM MyTable WHERE id NOT IN (SELECT id FROM idx_MyTable_geometry)

These queries will not find index entries with wrong bounds.

The most reliable way to keep the index table consistent would be to put INSERT/UPDATE/DELETE triggers on the geometry table, but that would require custom functions to get the bounds out of the geometry blob.

#3

I have an update on this.

I created a query that has an inner join on id fields from my object table and the index and then checked that the extents of the index matched up with the extents of the bounding box of the object. I was able to see the mismatch although still no idea why it occurred. I also observed that the difference between them was static (systematic shift) for all objects.

I was also able to fix the issue on the fly by updating the min and max values in the index.

This process was somewhat expensive on one of my tables, so I am still wondering if there is a more efficient way to detect the problem and then to fix it.

Regards,
Damian

#4

Thanks Clemens.

I think the trigger idea is interesting, but handling the blob through sqlite syntax sounds daunting.

Your queries are useful as I also noticed that there were actually more entries in the index than in the object table and I guess the opposite could also happen especially if there are errors on the data loading.

Question: Do you think using NOT IN is more efficient than a left join?

Regards,
Damian

#5

Damian,
I think the “id” is index. NOT IN shouldn’t have any performance issue.

Thanks

Frank