[SpatiaLite] corruption de l'index spatial R*Tree : soyons prudents !

Aujourd'hui, il est question de corruption d'index R*Tree dans vos BDD SpatiaLite. En effet, bien que très utile pour optimiser les requêtes, il nécessite quelques précautions d'usage.

Ce post est s'inspire du document suivant : http://www.gaia-gis.it/spatialite-2.4.0-5/SpatialIndex-Update.pdf (écrit par le développeur de SpatiaLite)

Voici comment réussir à corrompre un index Spatial R*Tree:

D'abord, quelques rappels pour mieux comprendre le fonctionnement de R*Tree:

• Tout Index Spatial R*Tree est simplement une table (virtual table)
• Le moteur interne de SQLite ne gère pas de façon native les relations liant l'index à la colonne géométrique de la table correspondante.
• Spatialite dispose d'un système de TRIGGERS afin d'assurer la synchronisation de l'index avec la colonne géométrique de la table correspondante.
• Le lien entre l'index et la colonne géométrique repose sur les valeurs de ROWID
• Pour une table SQLite donnée, chaque ligne est identifiée de façon unique par son ROWID
• Si la table contient une clef primaire (PRIMARY KEY), alors le ROWID est totalement lié aux valeurs de la clé primaire.
• Si la table ne contient pas de clef primaire, alors le ROWID correspond simplement au numéro de ligne et n'est lié avec la table que de façon relative

Dans quels cas l'index peut-il être corrompu ?

Considérons une table SANS clé primaire, ayant un index spatial R*Tree. Maintenant, supprimons une ligne de cette table, et compactons la base de donnée (VACUUM): vous venez de corrompre votre index spatial...

• La commande VACUUM compacte la base de donnée et par conséquent réassigne les ROWID des tables
• Pendant un VACUUM, les triggers sont désactivés ( fonctionnement interne de SQLite). Ainsi, la synchronisation avec l'Index Spatial n'est pas possible.
• On se retrouve dès lors avec une table ayant des ROWID modifiés et un index Spatial non modifié: le lien entre la table (colonne géométrique) et l'index spatial est rompu !
• Les requêtes basées sur l'index spatial ne sont dès lors plus valides....

La seule solution a ce problème: Toujours affecter une clé primaire à vos tables avant de mettre en place un index spatial R*Tree !

• La clé primaire va permettre s'assurer que les ROWID ne soient pas réassignés lors du VACCUUM et donc garantir la synchronisation avec l'index spatial R*Tree

BILAN: Méthodologie à suivre pour créer de manière sûre une table avec clé primaire et index spatial:

ex: matable(pk_uid,nom,geometry)

#Création de la table et mise en place de la clé primaire
CREATE TABLE ma table
( pk_uid INTEGER PRIMARY KEY , nom TEXT NOT NULL )

#Création de la colonne géométrique
SELECT addGeometryColumn( "matable" , "geometry", srid , type , dimension )

#Insertion des données
INSERT INTO matable ( pk_uid,nom,geometry) SELECT ..... FROM .....

#Création de l'Index Spatial
SELECT createSpatialIndex("matable", "geometry")

En procédant ainsi, vous êtes sûr de ne jamais corrompre votre index spatial.