[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.

[SpatiaLite] Mettre à jour une table avec jointure (UPDATE SET JOIN)

SpatiaLite, encore et toujours !
Pour les bases, je rappelle que c'est ici que ça se passe:
http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook-fr/

Aujourd'hui, nous allons apprendre comment mettre à jour une table en utilisant une relation (jointure). IL ne s'agit pas d'une syntaxe spécifique à SpatiaLite, mais à SQLite en général.

Données:
-parcelles (id, surf_lib, geometrie) -type:polygon-
-parcelles_maj(id, surf_lib)

Mise en situation:
Vous gerez un domaine agricole, composé de plusieurs parcelles. Vous disposez d'une table parcelles datant de l'an dernier, et recensant les surfaces libres (disponibles) sur chacune des parcelles. Vous decidez d'actualiser cette table en envoyant un stagiaire réaliser une verification de certaines parcelles. Il reviens vous voir avec une table parcelles_maj . Comment mettre à jour votre table parcelle à partir des informations présentes dans la table du stagiaire ?

Solution:
UPDATE parcelles
SET surf_lib = ( SELECT a.surf_lib FROM parcelles_maj AS a WHERE a.id=parcelles.id)

Quelques explications:
SQLite n'autorise pas l'utilisation de la clause JOIN dans des reqêtes de type UPDATE...SET...
La syntaxe ci dessus en revanche est acceptée et marche correctement.

Une fois le principe compris, il est facile de mettre à jour une table en fonction de plusieurs tables, autant avec des relations attributaires que spatiales.

En cas de soucis, n'hésitez pas à poser des questions,

[SpatiaLite] Soustraction vectorielle

Aujourd'hui nous allons réaliser une soustraction vectorielle avec SpatiaLite

Données:
- table1 (id,geometry) - type: polygone -
- table2 (id,geometry) + Index Spatial R*Tree - type: polygone -

Objectif: Soustraction vectorielle simple: table3=table1-table2
(l'ordre est important: dans notre cas, on va soustraire à la table 2 à la table)

Mise en situation:
Imaginons que:
- Notre table1 représente des territoires à explorer
- Notre table 2 représente des zones inaccessibles (fossés, lacs, etc...)
Notre objectif serait ici de determiner parmis les territoires à explorer (table1), les zones accessibles.

Solution basique:
CREATE TABLE table3 AS SELECT
t1.id AS id1
st_multi( st_difference( t1.geometry,t2.geometry) ) AS geometry
FROM table1 AS t1
JOIN table2 AS t2 ON (
GeometryType( st_multi(st_difference( t1.geometry,t2.geometry) ) ) = "MULTIPOLYGON")

Quelques explications:
Ici, on créée la table3 contenant le resultat de la soustraction vectorielle via la fonction st_difference(). L'utilisation de st_multi() force le type de géométrie créée en multi: ceci est recommandé pour la validation ulterieure de la colonne géométrique via la fonction RecoverGeometryColumn(). De plus, le critère de jointure GeomtryType()="MULTIPOLYGON" permet de ne garder que les résultats de soustraction de type MULTIPOLYGON, écartant ainsi les géométries NULLes (objets de table1 entièrement contenu dans objets de table2) et les lignes (POLYLINESTRING).
Ainsi, la table3 contiendra uniquement des objets de type MULTIPOLYGON. La fonction RecoverGeometryColumn() permet ainsi d'authentifier correctement la colonne geometry.

Cette requête basique marche, mais son fonctionnement n'est pas optimal et les temps de calcul risquent d'être très longs pour des gros jeux de donnée. Essayons de l'optimiser un peu ...

Solution optimisée:

CREATE TABLE table3 AS
SELECT
t1.id AS id1
st_multi( st_difference( t1.geometry,t2.geometry) ) AS geometry
FROM table1 AS t1
JOIN table2 AS t2 ON (
st_intersects( t1.geometry,t2.geometry)
AND
GeometryType( st_multi(st_difference( t1.geometry,t2.geometry) ) ) = "MULTIPOLYGON"
AND
t2.rowid IN (
SELECT pkid FROM idx_table2_geometry
WHERE pkid MATCH RTreeIntersects(
MBRminX(t1.geometry),
MBRmaxY(t1.geometry),
MBRminX(t1.geometry),
MBRmaxY(t1.geometry)
))
)
UNION
SELECT
t1.id AS id1,
st_multi(t1.geometry) AS geometry
FROM table1 AS t1, table2 AS t2
WHERE
NOT st_intersects( t1.geometry,t2.geometry)

Quelques explications:

L'astuce consiste à filtrer au préalable les objets s'intersectant, avant de réaliser le géotraitement (soustraction vectorielle) uniquement sur ces objets:

Dans un premier temps, on selectionne tous les objets de table1 qui intersectent les objets de table2 via la fonction st_intersects() (l'utilisation du R*Tree en rouge). Pour chacun de ces objets, on réalise une soustraction vectorielle via la fonction spatiale st_difference(obj1,obj2).

Dans un second temps, on ajoute au résultat les objets de table1 qui n'intersectent pas ceux de table2. La clause UNION va permettre de fusionner les résultats des deux SELECT en un seul résultat. Ces objets ne sont pas modifiés.

A vous de jouer !

N'hesitez pas à laisser des commentaires en cas de soucis, je tâcherais de vous aider du mieux que je peux.