5 - SQL - Bases de données

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

Construisons un index spatial sur la table TRONCON_HYDROGRAPHIQUE, ainsi que sur la table PONCTUEL_HYDROGRAPHIQUE.

Dans les anciennes versions de spatialite, on pouvait utiliser des fonctions Rtree (RtreeIntersects, RTreeWithin, RtreeContain).

Ce sont ces fonctions qui sont implémentées dans l'assistant SQL de Qspatialite (version 7.0.2 - Août 2014) :

En appuyant sur 'use Spatial Index' Qspatiale rajoute automatiquement une syntaxe dans la clause where.

AND"PONCTUEL_HYDROGRAPHIQUE".ROWID IN (

SELECT pkid FROM "idx_PONCTUEL_HYDROGRAPHIQUE_Geometry" WHERE pkid MATCH RTreeIntersects(

MBRminX("TRONCON_HYDROGRAPHIQUE".'Geometry'),MBRminY("TRONCON_HYDROGRAPHIQUE".'Geometry'),MBRmaxX("TRONCON_HYDROGRAPHIQUE".'Geometry'),MBRmaxY("TRONCON_HYDROGRAPHIQUE".'Geometry')))

Cette syntaxe n'est plus recommandée dans les dernières versions de spatialite (> 4.0.1) pour laquelle, il y a eu des travaux d'optimisation.

Elle peut conduire au message d'erreur suivant si le module geocallback n'est pas présent :

'no such function : RtreeIntersects' (module geocallbacks non présent)

Désormais, pour utiliser explicitement les index spatiaux on peut construire une requête copiant la syntaxe indiquée ici :

SELECT p.'Geometry' AS Geometry,

p.'TYPE',

p.'NATURE',

p.'TOPONYME',

p.'COTE',

t.'TOPONYME' as COUR_D_EAU

FROM 'PONCTUEL_HYDROGRAPHIQUE' p, 'TRONCON_HYDROGRAPHIQUE' t

WHERE st_intersects(p.'Geometry',t.'Geometry') and p.'ROWID' in ( select rowid from spatialIndex where f_table_name = 'PONCTUEL_HYDROGRAPHIQUE' AND search_frame = t.'Geometry')

nb : On utilise pour plus de concision les alias de table ; p pour 'PONCTUEL_HYDROGRAPHIQUE' et t pour 'TRONCON_HYDROGRAPHIQUE'

Sans rentrer trop dans les détails une sous-requête est ici utilisée (ordre select dans la clause Where de la requête de base relançant une sous-requête). 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).

Cela donne :

Ici le traitement s'exécute en 26 ms...(au lieu de 45 ms sans l'utilisation explicite des index spatiaux) le gain de temps n'est semble-t-il pas en rapport avec l'effort intellectuel consenti ! 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