QGIS » QGIS Application

The issue is real but how to improve the current situation needs some thinking because spatial index in SpatiaLite/GeoPackage is not an index but a table with some well known triggers.

The list of true indexes from SQLite can be obtained with a PRAGMA command

PRAGMA index_list('table_name')

I suppose that the Indexes tab in Layer properties makes exactly that request.

From SpatiaLite databases created with recent versions the existence of the spatial index is supposed to be checked from the geometry columns table which has a column "spatial_index_enabled".

SELECT ROWID, "f_table_name", "f_geometry_column", "type", "coord_dimension", "srid", "spatial_index_enabled"
FROM "geometry_columns"
ORDER BY ROWID

If user is pressing "Create spatial index" button and spatial index already exists then the application should perhaps make a question:
"Table xxx has already a spatial index. Do you want to recover it?" If user says "Yes" then RecoverSpatialIndex function would be performed.

For the GeoPackage case I suppose that if spatial index exists or not should be checked with the test that is included in the standard, on page 75:

@A.3.1.3 Spatial Indexes
A.3.1.3.1 Data
A.3.1.3.1.1 Spatial Indexes Implementation

...snip...

3) For each row table_name, column_name from step 1
a. SELECT sql FROM sqlite_master WHERE tbl_name = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name
b. Not testable if result set is empty
c. Fail if returned sql != ‘CREATE VIRTUAL TABLE rtree_’ ’ || result_set_table_name || ‘_’ || result_set_column_name || USING rtree(id, minx, maxx, miny, maxy)
d. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND tname = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_insert’
e. Fail if returned sql != result of populating insert triggers template in Annex L using result_set_table_name for <t> and result_set_column_name for <c>
f. SELECT sql FROM sqlite_master WHERE type = ‘trigger’ AND name LIKE ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_update%’
g. Fail if returned sql != result of populating 4 update triggers templates in Annex L using result_set_table_name for <t> and result_set_column_name for <c>
h. SELECT sql FROM sqlite_master WHERE type=’trigger’ AND name = ‘rtree_’ || result_set_table_name || ‘_’ || result_set_column_name || ‘_delete’
i. Fail if returned sql != result of populating delete trigger template in Annex L using result_set_table_name for <t> and result_set_column_name for <c>
j. Log pass otherwise
4) Pass if logged pass and no fails@