Indexation et optimisation
Dans le cas d'une grosse base de données, les requêtes Sql peuvent être coûteuses en temps de calcul, a fortiori les requêtes spatiales qui utilisent la géométrie des objets.
Créer des index (spatiaux ou non) peut permettre d'améliorer les temps de traitement. Ce n'est cependant pas une recette miracle. Dans SpatiaLite, un index spatial ne peut accélérer les calculs que dans le cas où le résultat appartient à une petite portion du jeu de données. Quand les résultats incluent une grande partie du jeu de données, l'index spatial ne permet pas de gains de performance.
Dans un SGBD élaboré comme PostGIS le planificateur de requête choisit de façon adaptée d'utiliser ou non les index et l'index spatial (de type Gist que nous verrons plus tard) est primordial. La seule restriction d'utilisation est celle des tables avec de très gros objets en petit nombre (ex : tache urbaine départementale répartie en 10 périodes soit 10 enregistrements).
On trouvera quelques explications sur le principe de l'algorithme R-Tree utilisé par spatialite ici et sur les index Gist ici
Remarque : Clef primaire
Pour des raisons expliquées ici il est indispensable sous spatialite que la table dispose d'une clef primaire avant de créer un index spatial. Une clef primaire est une colonne qui représente un identifiant unique pour chaque enregistrement.
Les clefs primaires apparaissent soulignés dans la description des champs dans l'onglet Info de DBManager :
Normalement (exemple sous PostGIS) il est possible d'ajouter une clef primaire en passant par le menu Table -> Editer une table -> onglet contraintes.
Malheureusement SqLite n'autorise pas (encore?) la création d'une clef primaire après coup, (pour les anglicistes voir les limites de ALTER TABLE) il faut re-créer une autre table. Contacter l'assistance interne si vous êtes confronté à ce type de problèmes.
Index spatial
Si aucun index spatial n'existe DBManager le signale et permet de le créer directement :
Il est également possible de passer par le menu Table -> editer une table -> onglet index -> Ajouter un index spatial
Construire un index spatial sur la table TRONCON_HYDROGRAPHIQUE, ainsi que sur la table PONCTUEL_HYDROGRAPHIQUE.
Avec l'assistant SQL, construisons maintenant une requête utilisant l'index spatial
En appuyant sur 'Utiliser l'index spatial' l'assistant ajoute automatiquement une syntaxe dans la clause where.
AND "PONCTUEL_HYDROGRAPHIQUE".ROWID IN (
SELECT ROWID FROM SpatialIndex WHERE f_table_name='PONCTUEL_HYDROGRAPHIQUE' AND search_frame="TRONCON_HYDROGRAPHIQUE"."geometry")
Sans rentrer trop dans les détails une sous-requête est ici utilisée (ordre SELECT dans le IN). Cette sous-requête utilise les colonnes f_table_name et search_frame de la table système SpatialIndex (cette table n'est pas interrogeable).
Une requête optimisée utilisant l'index spatial donne :
La différence de temps de traitement n'est pas significative dans notre cas (gain en millisecondes), Mais dans d'autres cas cette petite gymnastique qui après quelques essais n'est pas si difficile à mettre en œuvre peut faire gagner beaucoup de temps.
Quelques exemples de gains sont données ici
Complément :
Les possibilités de manipulation spatiale sont très grandes... voici quelques références supplémentaires pour Spatialite :
Quelques exercices et astuces classiques
Ne pas hésitez à consulter à partir de cette page le 'Spatial SQL functions reference guide' qui est la liste de référence des fonctions disponibles dans la dernière version de spatialite (attention ce n'est pas forcement celle de votre version de QGIS). Pour aller plus loin, on pourra en particulier regarder avec intérêt les fonctions 'GEOS Advanced', ainsi que les fonctions 'LWGEOM'
(Sur SQL d'une façon générale on pourra consulter un cours en ligne ou ce site de référence en français