How to delete a broken index in SpatiaLite?

Question

I managed to screw up an index in my SpatiaLite database. The index was created using spatialite-gui. In the meantime, I deleted the original table and three out of four index files: idx_tablename_Geometry is still left while idx_tablename_Geometry_node, idx_tablename_Geometry_parent and idx_tablename_Geometry_rowid have been deleted too.

Whenever I start the GUI or refresh the list of tables, I'll get the error message: No such table main.idx_tablename_Geometry_node. Trying to drop the table idx_tablename_Geometry using either SQL or the GUI results in the same error message and nothing is deleted.

The database is still usable in my GIS, but it's annoying to have this error pop up every minute when working with spatialite-gui. Any ideas how to fix it?

Answer 1

Have you tried disabling the spatial index first?

SELECT DisableSpatialIndex('TableName', 'geom');

DROP TABLE idx_tablename_Geometry;

-- check to see if it is now gone
.tables

-- if it is gone, vacuum to clean up your db
VACUUM;

Answer 2

I had a similar problem: After deleting the spatial index on one of my tables, I received the error message whenever I refreshed the list of tables. I had a view that referenced the index, which turned out to be the problem. After deleting the view, the error messages stopped. So, my suggestion: make sure that no views rely on the index that you're trying to delete.