Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

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;
share|improve this answer
2  
If there is some sort of corruption and you really can't drop the index table, you may want to create a new db and transfer the valid contents to it. –  DavidF Aug 27 '10 at 13:53
    
While DisableSpatialIndex() returned 1, dropping the table still isn't possible. Guess I'll have to create a new db. –  underdark Aug 27 '10 at 14:58
1  
Not a direct solution, but I would highly recommend posting your issue to the Spatialite Users list. groups.google.com/group/spatialite-users Alessandro, the primary developer of Spatialite fixes bugs almost faster than you can post them... –  DavidF Aug 27 '10 at 17:03
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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