Étude du connecteur Oracle Spatial de QGis🔗

Spatial est le nom du cartouche spatial du SGBDR Oracle édité par la société éponyme. Pour ceux qui viennent du monde libre, Spatial correspond à la couche PostGIS de PostgreSQL. Si cela fait bien longtemps que QGis sait se connecter à une base PostGIS, ce n'est pas la même chose en ce qui concerne Oracle. En effet, c'est seulement depuis la version 2.0 de QGis qu'il existe un connecteur natif permettant de se connecter directement à un serveur Oracle Spatial. Cet ajout est issu d'un (gros) commit sur le code de QGis.

Comme toute nouveauté, il est important d'en faire le tour sachant que le service proposé a forcément des limites qu'il s'agit d'explorer et de bien comprendre. Faisons-donc le tour du connecteur Oracle Spatial, de ses limites et de son impact sur un entrepôt de données géographiques. Le contenu de cet article se base sur la version 2.2 de QGis sortie le 21 février 2014. L'article se veut aller au fond des choses, il sera donc volumineux et long à lire !

L'accès aux bases Oracle est assez simple et il est complètement intégré à l'interface graphique de QGis. Dans la barre des connecteurs, il existe une icône dont voici la représentation:

Voyons maintenant ce que nous propose le connecteur. La boîte de dialogue qui s'ouvre après avoir cliqué sur l'icône du connecteur Oracle permet de lister les tables d'une base Oracle. Pour la faire fonctionner, il faut créer une connexion. Le paramétrage de cette dernière sera conservé dans la configuration globale de QGis ce qui vous permettra de la réutiliser après avoir fermé Qgis. Si vous avez plus d'un serveur ou si vous avez un seul serveur avec des utilisateurs différents qui disposent de droits distincts, vous pourrez enregistrer ces connexions pour les réutiliser plus tard, ce qui est bien pratique.

Voici la boîte de dialogue qui permet de configurer une connexion Oracle Spatial:

Pour créer une nouvelle connexion, vous pouvez appuyer sur le bouton "Nouveau". Avant de créer une connexion, il est indispensable que vous disposiez d'un client Oracle correctement installé. Sous MS-Windows, vous devrez définir la variable d'environnement TNS_ADMIN pour qu'elle pointe vers le répertoire qui contient les définitions de connexion Oracle. En effet, en règle générale, pour se connecter, le client Oracle (et toute application qui se base dessus) utilise le fichier tnsnames.ora. Ce fichier fait le lien entre le nom du serveur Oracle, le nom de la base et le port. Il permet de définir des SID qui sont des raccourcis de connexion à la base. Pour que QGis puisse se connecter à une base Oracle Spatial, vous aurez besoin de disposer du fichier tnsnames.ora, qu'il soit accessible via la variable TNS_ADMIN et de connaître le SID qui vous permettra de vous connecter à la bonne base de données du bon serveur sur le bon port.

J'ai toutefois pu noter quelques différences sur deux postes de travail différents. Le premier sous MS-Windows XP n'utilisait pas forcément le SID du fichier tnsnames.ora. Le second, sous MS-Windows 7, ne fonctionnait qu'en renseignant le SID. Lorsque vous créez une connexion Oracle Spatial dans QGis, vous devez renseigner les champs suivants:

• Nom: c'est le nom de la connexion. Vous pouvez mettre ce que vous voulez, c'est le nom qui sera affiché dans QGis pour repérer la connexion.
• Base de données: si vous êtes sous MS-Windows XP, vous devez renseigner le nom de la base de données. Sous MS-Windows 7, il suffit de mettre le nom du SID tel que défini dans le fichier tnsnames.ora.
• Hôte: Sous MS-Windows XP, vous pouvez renseigner le FQDN ou l'IP du serveur. Sous MS-Windows 7, il ne faut rien mettre, le SID suffit.
• Port: sous MS-Windows XP, vous pouvez renseigner le port du listener Oracle. Sous MS-Windows 7, il ne faut rien mettre non plus.
• Nom d'utilisateur: Le nom de l'utilisateur avec lequel on va faire la connexion.
• Mot de passe: Un mot de passe qui peut se connecter à cette base.

D'une manière générale, si vous avez une erreur ORA-12154, c'est que vous avez rempli le nom d'hôte du serveur et que QGis utilise en fait le SID. Dans ces conditions, videz le champ hôte et refaîtes un test de connexion (avec le bouton adéquat).

À la suite de ces éléments de configuration, QGis nous propose quelques options qu'il me faut détailler:

• Enregistrer le nom de l'utilisateur permet de stocker le login dans les paramètres de la connexion Oracle, pour la réutiliser plus tard. Si vous vous connectez toujours avec le même compte, n'hésitez pas à l'utiliser. De plus, la base de données peut être gérée pour affecter certains schémas à certains utilisateurs. Vous pouvez donc sauvegarder plusieurs connexions différentes avec un nom d'utilisateur différent sur la même base pour accéder plus efficacement aux données du schéma auquel l'utilisateur a droit.
• Sauvegarder le mot de passe permet de stocker le mot de passe. L'information est stockée en clair dans un fichier de configuration de QGis sous GNU/Linux et dans la base de registre sous MS-Windows (HKEY_CURRENT_USER\Software\QGIS\QGIS2\Oracle\connections). Je vous recommande de ne pas utiliser cette option: taper un mot de passe lors de la connexion est trivial et c'est beaucoup plus sécurisé.
• Chercher uniquement dans la table de métadonnées est un mécanisme qui permet de lister plus rapidement les tables géographiques. Si la case est décochée, QGis parcoure la liste des tables et vérifie, une par une, si elles disposent d'une colonne géométrique. Si la case est cochée, QGis utilise les mécanismes d'Oracle pour afficher plus rapidement la liste des couches géographiques. En effet, comme sous PostGIS, Oracle maintient une liste des couches géographiques dans une table spécifique. C'est cette table qui est interrogée pour retourner en une seule requête la liste des tables géographiques. Cette table de métadonnées (qui est en fait une vue) se nomme ALL_SDO_GEOM_METADATA et elle est stockée dans le schéma MDSYS de la base Oracle. On y trouve les colonnes suivantes:
• TABLE_NAME: le nom de la couche géographique.
• COLUMN_NAME: le nom de la colonne qui porte la géométrie de la couche.
• DIMINFO: une donnée présente sous forme d'un tableau (SDO_DIM_ARRAY) contenant les dimensions de la colonne géométrie. Sous Oracle Spatial, une dimension est déterminée en indiquant ses bornes spatiales ainsi que sa résolution via un objet SDO_DIM_ELEMENT.
• SRID: le système de projection de la couche.
• Chercher uniquement les tables de l'utilisateur permet de consulter uniquement la vue USER_SDO_GEOM_METADATA. Cette vue liste les tables géographiques de la base Oracle à la manière de la vue ALL_SDO_GEOM_METADATA mais elle extrait uniquement les tables que peut consulter l'utilisateur qui fait la connexion vers le serveur. Si vous avez un catalogue important, cette option permettra de limiter le nombre de couches retournées.
• Lister les tables sans géométrie est une option pour afficher le maximum de tables en listant également celles qui ne sont pas géographiques.
• Utiliser la table de meta-données estimées est un mécanisme qui permet de déterminer plus rapidement le type géométrique de chaque couche. Quand cette option est activée, QGis ne cherche le type de géométrie que sur les 100 premières lignes de la couche. Si vous avez des couches contenant de nombreux objets géographiques, il vaut mieux activer cette option. Par nombreux objets j'entends supérieur à 200000 objets géographiques dans une couche. En deçà, on ne peut pas vraiment faire la différence entre les temps de requêtes. L'autre intérêt de cette option c'est qu'elle permet à QGis de déterminer plus rapidement la bounding-box de la couche plutôt que de la calculer en fonction du contenu de la couche.
• Seulement les types de géométrie existants permet de ne gérer que les types de données géométriques reconnus de QGis. En effet, Oracle Spatial peut gérer d'autres types d'objets géographiques comme des arcs ou des rectangles qui ont des définitions bien particulières (on stocke uniquement les coordonnées nécessaires à leur représentation soit deux points pour le rectangle au lieu de 3 pour un polygone rectangulaire). Lorsque la case est décochée, on ajoute dans les types de géométrie de la couche, la valeur WKBUnknown ce qui vous permet de choisir dans le connecteur Oracle quel sera le type géométrique de la couche que vous allez ouvrir. À vous de choisir la bonne valeur. Dans la pratique, je vous recommande de cocher cette case pour être sûr que QGis délaisse les couches contenant des objets qu'il ne sait pas gérer.

Au-delà de l'utilisation du connecteur, j'ai remarqué un bug assez ennuyeux si vous utilisez plusieurs connexions simultanées sur la même base mais avec un utilisateur différent sans enregistrer le mot de passe dans les paramètres de la connexion (ce qui est peu sécurisé). En effet, Qgis réutilise le nom de l'utilisateur qui s'est connecté en dernier à la base Oracle. En résumé, une fois que vous avez ouvert une connexion vers une base, impossible de changer d'utilisateur à moins d'avoir le mot de passe en dur dans la définition de la connexion. J'ai fait un rapport de bug sur ce point afin de faire évoluer la situation. Donc retenez qu'en cas de connexions multiples avec des utilisateurs différents, il faut stocker les mots de passe en dur.

Une fois que vous avez initié la connexion, Qgis affiche un arbre (qui ressemble assez à une liste) des couches suivant les schémas Oracle.

Voici le descriptif des colonnes:

• Propriétaire: c'est le nom du schéma Oracle dans lequel est situé la couche.
• Nom de la couche: c'est le nom de la table dans le serveur Oracle.
• Type de géométrie: QGis affiche une icône qui indique le type de géométrie de la couche, tel qu'il a pu le repérer. Si vous avez décoché la case Seulement les types de géométrie existants, vous aurez pour chaque couche un doublon avec une icône en forme de raster qui vous permet de sélectionner le type de géométrie. Par ailleurs, si votre couche contient plusieurs types géométriques, elle sera listée plusieurs fois.
• Colonne géométrique: Le nom de la colonne géométrique.
• SRID: c'est le système de projection de la couche. Si QGis n'a pas pu le déterminer, ce sera à vous de renseigner une valeur avant de pouvoir sélectionner une couche.
• Clef primaire: C'est la clef primaire de la table telle qu'elle a été déterminée par QGis.
• Select At ID: Cette case est censée activer un cache pour la table attributaire mais j'ai l'impression que ce cache est toujours actif, même si on décoche la case. Je n'ai pas décelé de comportement particulier.
• SQL: c'est la clause WHERE de la requête. Dès que vous saisissez des critères dans cette partie, toutes les requêtes vers Oracle Spatial auront cette clause WHERE. Cela permet d'être beaucoup plus efficace et de limiter le périmètre de vos recherches à ce qui vous intéresse tout en améliorant les temps de réponse du serveur qui ne sera pas obligé de remonter toutes les lignes. Pour rédiger plus efficacement une clause WHERE, double-cliquez sur la ligne de la couche pour faire apparaître la boîte de dialogue.

QGis propose également un mode qui sera sans doute plus utile si vous désirez afficher plusieurs couches pour voir leur contenu. En effet, une fois que vous avez fermé la fenêtre du connecteur Oracle, la liste des couches de la base de données Oracle est vidée. Chaque fois que vous ouvrez le connecteur, il vous sera imposé de re-sélectionner une connexion et de cliquer sur le bouton "Connecter" ce qui aura pour effet de relancer les requêtes d'interrogation du catalogue Oracle, opération souvent coûteuse en temps. C'est particulièrement pénible si votre catalogue est imposant.

Heureusement, il est possible de conserver en mémoire la liste des couches d'une connexion grâce à l'onglet Parcourir.

Dans ce mode, apparaît un raccourci Oracle qui liste les connexions que vous avez configurées au niveau du connecteur Oracle Spatial. Pour les activer il suffit de déplier l'arbre en cliquant sur le "+" situé à gauche du nom de la connexion. Cette action entraîne une interrogation du catalogue Oracle (selon les paramètres de la connexion). Néanmoins, cette fois, les résultats restent présents dans l'arbre. On peut donc ajouter une couche, passer à l'onglet "Couches", travailler avec Qgis (en stylant la couche), puis revenir sur l'onglet "Parcourir" et retrouver la liste des couches disponibles intacte.

Si vous devez vraiment ajouter beaucoup de couches issues de différents horizons, je vous recommande à la place d'utiliser la mini-fenêtre "Navigateur". Cette mini-fenêtre se place en dessous de la liste des couches. Elle est donc immédiatement disponible pour vous aider à naviguer dans votre entrepôt de données. Pour l'activer, il est nécessaire d'activer le panneau "Navigateur (2)" à partir du menu Vue → Panneau. Vous pouvez également l'activer via un clic droit sur une barre d'outils et cocher la case "Navigateur (2)".

Ce panneau est vraiment intéressant car outre ses fonctions de navigation rapide, il est toujours actif. Par exemple, pendant que vous faites un inventaire des couches spatiales d'Oracle, vous pouvez vous balader dans l'arborescence de fichiers de vos rasters. D'ailleurs une fois que vous l'avez activé, je vous recommande de désactiver l'onglet parcourir en le décochant depuis le menu Vue → Panneau.

Je vous recommande donc d'utiliser ce mode si vous souhaitez parcourir un peu votre catalogue pour y trouver les couches dont vous avez besoin et être plus efficace avec QGis.

Après avoir montré comment utiliser le connecteur pour se connecter à une base Oracle, voyons ce qui se passe sous le capot.

Un petit coup de Wireshark peut nous révéler plusieurs instructions précieuses. Voici ce que j'ai pu découvrir après analyse.

Voici la requête SQL qu'on peut voir passer lorsqu'on se connecte à la base pour lister les couches disponibles en activant l'interrogation de la table de métadonnées et en limitant le périmètre de la recherche aux couches que possède l'utilisateur connecté:

SELECT user AS owner,c.table_name,c.column_name,c.srid,o.object_type AS type
FROM user_sdo_geom_metadata c
JOIN user_objects o ON c.table_name=o.object_name AND o.object_type IN ('TABLE','VIEW','SYNONYM');

Cette requête permet d'interroger la vue USER_SDO_GEOM_METADATA pour récupérer la liste des tables et des vues géographiques. Qgis récupère ce dont il a besoin pour gérer la fenêtre de dialogue: l'utilisateur de la table, le nom de la table, la colonne qui stocke la géométrie, le système de projection ainsi que le type d'objet: table, vue ou synonym qui est un alias d'une table ou d'une vue. A noter qu'une vue matérialisée est vue comme une table.

Si vous avez décoché la case Chercher uniquement les tables de l'utilisateur, c'est la requête suivante qui est jouée:

SELECT c.owner,c.table_name,c.column_name,c.srid,o.object_type AS type
FROM all_sdo_geom_metadata c
JOIN all_objects o ON c.table_name=o.object_name
  AND o.object_type IN ('TABLE','VIEW','SYNONYM')
  AND c.owner=o.owner;

On voit que dans cette configuration, on attaque la table ALL_SDO_GEOM_METADATA. Tout dépend de la manière dont vous avez organisé vos tables. En règle générale, il est bon qu'un utilisateur spécifique n'accède qu'aux tables qu'il possède. Cela permet de réduire fortement le temps d'attente avant de disposer de la liste des couches. Dans mon cas, l'ordre de grandeur pour afficher 2500 couches cataloguées est de l'ordre d'une trentaine de secondes.

Si on a décoché la case Chercher uniquement dans la table de métadonnées, QGis lance alors la requête suivante:

SELECT user AS owner,c.table_name,c.column_name, NULL AS srid, o.object_type AS type
FROM user_tab_columns c
JOIN user_objects o ON c.table_name=o.object_name
  AND o.object_type IN ('TABLE','VIEW','SYNONYM')
WHERE c.data_type='SDO_GEOMETRY';

La requête est proche de la précédente mais on voit qu'elle se focalise sur toutes les tables de la base. Si vous avez de nombreuses tables réparties dans des schémas distincts, cette requête peut prendre du temps…

Ensuite, suivant le résultat de ces requêtes d'inventaire, QGis va essayer d'en savoir plus sur les couches. Ainsi, pour chaque table retournée par une des requêtes précédentes, QGis va lancer la requête suivante:

SELECT DISTINCT t."COLONNE_GEOMETRIQUE".SDO_GTYPE,t."COLONNE_GEOMETRIQUE".SDO_SRID
FROM (SELECT "GEOM" FROM "SCHEMA"."TABLE_A_INTERROGER" WHERE "COLONNE_GEOMETRIQUE" IS NOT NULL AND rownum<=100) t
WHERE NOT t."COLONNE_GEOMETRIQUE" IS NULL;

Cette requête sélectionne les 100 premières lignes de la table concernée (TABLE_A_INTERROGER dans mon exemple) contenant des géométries et va récupérer deux informations:

• Le type de géométrie (Point/Multipoint/Lignes/Multilignes). S'il y a plusieurs types géométriques de détectés, il y aura plusieurs couches qui auront le même nom.
• Le SRID des objets géographiques de la couche.

Si vous avez décoché la case "Utiliser la table de metadonnées estimée", QGis fait la requête sur toute la table, au lieu de sélectionner les 100 premières lignes. Si vous avez des couches volumineuses (par exemple, le bâti du cadastre d'un département), il vaudra mieux activer cette option, sous peine de se retrouver avec des temps de requête très longs.

Enfin, à la suite de cette requête unitaire, s'ajoute une deuxième requête par couche:

SELECT column_name
FROM all_tab_columns
WHERE owner='USER' 
AND table_name='TABLE_A_INTERROGER' ORDER BY column_id;

Cette table liste les colonnes de la couche concernée. QGis lance cette requête pour récupérer ce qui pourrait faire office de clef primaire.

Pour résumer, lorsque vous vous connectez sur une base Oracle Spatial et que vous demandez un inventaire via le connecteur, QGis va:

• Récupérer la liste des couches.
• Déterminer la géométrie de chaque couche.
• Lister les champs pour déterminer la clef primaire de la table.

En conséquence, afficher la liste des couches peut prendre un temps non négligeable. Ce temps est lié essentiellement au contenu de votre entrepôt de données. Plus vous aurez de tables, plus l'inventaire sera long. Néanmoins, nous verrons par la suite qu'il existe certaines astuces pour faciliter cette étape d'inventaire.

On aurait pu penser que la partie dénommée "Options de recherche" ait une influence sur ce temps d'interrogation du catalogue. En effet, il existe de nombreuses options qui permettent d'affiner la recherche et de n'afficher, par exemple, que les tables qui commencent par 'QGIS_'. Néanmoins, ces options permettent de filtrer les résultats uniquement sur ce qui est retourné par le serveur Oracle, elles n'influencent pas du tout les requêtes sur le catalogue. Qu'on le veuille ou non, le parcours du catalogue prendra du temps…

Après avoir étudié le comportement du connecteur, il est temps d'observer le comportement de QGis lors de l'ouverture d'une table Oracle. Comme pour le connecteur, une série de requête est lancée pour déterminer le type de données disponibles, le SRID et un tas d'autres détails avant de récupérer les données proprement dites. Voyons cela en détails.

SELECT user FROM dual;

La première requête lancée est triviale, elle sert à récupérer le nom de l'utilisateur connecté à la base de données. Pour cela, on utilise la table DUAL d'Oracle qui est une table spéciale à une seule colonne qui contient le résultat de notre sélection basique. Dans notre cas, on veut juste le nom de l'utilisateur (user).

SELECT srid FROM mdsys.all_sdo_geom_metadata WHERE owner='USER' AND table_name='TABLE' AND column_name='GEOM';

La deuxième requête tente de déterminer le SRID de la couche en interrogeant la table de métadonnées d'Oracle Spatial (ALL_SDO_GEOM_METADATA). Dans mon exemple précis, la requête ne renvoit rien, QGis procède à une autre requête pour trouver le SRID.

SELECT DISTINCT t."GEOM".sdo_gtype FROM "USER"."TABLE" t WHERE rownum<=2;

Cette troisième requête tente de récupérer le type de géométrie de la couche en interrogeant le champ de géométrie des deux premières lignes de la table. À moins d'avoir une table vide, il ne devrait pas y avoir de problème.

SELECT comments FROM all_tab_comments WHERE owner='USER' AND table_name='TABLE';

Une fois le SRID récupéré, QGis récupère le commentaire de la table. A noter que la table qui est intérrogée pour récupérer les commentaires est ALL_TAB_COMMENTS. Cela signifie que QGis ne peut pas afficher les commentaires présents sur une vue matérialisée. J'ai déposé un rapport de bug dans l'outil de ticketing de QGis dans ce sens, on verra s'il est pris en compte pour la prochaine version.

SELECT column_name, comments FROM all_col_comments t WHERE t.owner='USER' AND
  t.table_name='TABLE' AND t.column_name<>'GEOM';

Une fois le commentaire de table récupéré, on récupère les commentaires de chaque champ de la table pour nourrir le commentaire dans l'onglet champs des propriétés de la couche dans QGis.

SELECT t.column_name,
	   CASE WHEN t.data_type_owner IS NULL THEN t.data_type ELSE t.data_type_owner||'.'||t.data_type END,
	   t.data_precision,
	   t.data_scale,
	   t.char_length,
	   t.char_used,
	   t.data_default
FROM all_tab_columns t
WHERE t.owner='USER' AND t.table_name='TABLE' AND t.column_name<>'GEOM'
ORDER BY t.column_id;

Cette requête d'apparence plus complexe ne fait que récupérer les types de chaque champ. Rien de bien complexe ici…

SELECT i.index_name, i.domidx_opstatus
FROM all_indexes i
	 JOIN all_ind_columns c ON
	 i.owner=c.index_owner
	 AND i.index_name=c.index_name
	 AND c.column_name='GEOM'
WHERE i.table_owner='USER'
	  AND i.table_name='TABLE'
	  AND i.ityp_owner='MDSYS'
	  AND i.ityp_name='SPATIAL_INDEX';

La requête ci-dessus s'occupe de récupérer le nom et la validité de l'index spatial de la table. On voit que cet index doit travailler sur la colonne géométrique de la table. Si la table (ou la vue (matérialisée ou non) dispose d'un index spatial, QGis récupère ici son nom.

SELECT * FROM "USER"."TABLE" WHERE 1=0;

Cette simple requête permet de récupérer les noms des champs pour traitement interne de QGis. C'est à partir de cette requête qui ne renvoie aucune donnée (sauf les noms des champs) que QGis fait l'appariement Nom du champ/Type de données/Commentaire.

SELECT column_name
FROM all_ind_columns a
	 JOIN all_constraints b
	 ON a.index_name=constraint_name
		AND a.index_owner=b.owner
WHERE b.constraint_type='P'
	  AND b.owner='USER'
	  AND b.table_name='TABLE';

Une fois les champs obtenus, il reste à récupérer le nom de la clef primaire. Pour cela, on va lister les index de la table qui ont une contrainte de type 'P' (contrainte de clef primaire).

SELECT 1 FROM all_tables WHERE owner='USER' AND table_name='TABLE';

La requête précédente est toujours dans la boucle de vérification de la clef primaire. Elle est implémentée pour vérifier que la table existe bien.

SELECT coalesce(auth_name,'EPSG'), auth_srid, wktext FROM mdsys.cs._srs WHERE srid=0;

Après en avoir terminé avec les champs, les index, la clef primaire, QGis détermine le SRID de la couche. Si aucun SRID n'a été renseigné dans la table de métadonnées, c'est le SRID n°0 qui sera utilisé. QGis vérifie comment ce SRID est déclaré dans la table de référence des systèmes de projection d'Oracle. Dans notre cas, aucune donnée ne correspond au SRID 0, donc QGis va poser la question à l'utilisateur final avec une boîte de dialogue dédiée.

SELECT SDO_TUNE.EXTENT_OF('USER.TABLE','GEOM') FROM dual;

Maintenant, c'est la bounding-box qu'on essaye de récupérer. La colonne SDO_TUNE.EXTENT_OF permet de récupérer cette information. On utilise la table dual pour récupérer un seul résultat (seule la colonne est retournée). Cette requête prend forcément du temps car Oracle Spatial va analyser toutes les géométries pour définir le rectangle qui les englobe toutes. On peut avoir des temps de l'ordre de la dizaine de secondes pour une centaine d'objets. Heureusement, cette requête n'est jouée qu'à l'ouverture de la table. Un moyen d'aller plus vite sur ce point est de cocher la case Utiliser la table de metadonnées estimées et de renseigner correctement les informations d'extent dans le catalogue Oracle Spatial (ALL_SDO_GEOM_METADATA). A noter que si vous n'avez pas d'index spatial sur la table, la requête sera différente. Elle utilisera la fonction SDO_AGGR_MBR qui est encore plus longue.

SELECT "GEOM","CHAMP_1",…, "CHAMP_N"
FROM "USER"."TABLE" "featureRequest"
WHERE sdo_filter("GEOM",
                 mdsys.sdo_geometry(2003, NULL, NULL,
                                    mdsys.sdo_elem_info_array(1,1003,3),
                                    mdsys.sdo_ordinate_array(288332.71326791675528511,
                                                             249155.39441391587024555,
                                                             322322.672240078031318262,
                                                             262604.34770948911318555)))
	  ='TRUE';

Enfin, QGis lance une requête pour récupérer les données dans la bounding-box calculée précédemment. On peut noter que la requête géographique se déroule en utilisant la fonction sdo_filter. Pour faire simple, sdo_filter permet de savoir si deux géométries interagissent au niveau spatial (en gros, si la première est contenue ou touche la seconde). Si c'est le cas, sdo_filter retourne la valeur TRUE, sinon FALSE. SDO_FILTER utilise l'index spatial, quand il est disponible, pour faire la requête ce qui accélère grandement le calcul. Dans notre cas, on ne sélectionne que les géométries de la colonne "GEOM" de la table "TABLE" qui interagissent avec une autre géométrie dont la définition se fait avec la fonction mdsys.sdo_geometry.

Si vous vous référez à la documentation Oracle, vous pouvez voir que sdo_geometry sert à construire une géométrie donnée, grâce aux éléments suivants:

sdo_geometry(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES).

• SDO_GTYPE indique le type de géométrie. Dans notre cas, on a 2003, 2 pour un objet dans un espace à deux dimensions, 3 pour un polygone.
• SDO_SRID indique le SRID utilisé, NULL dans notre cas: QGis a normalement déjà calculé les dimensions de la bounding-box dans le bon référentiel.
• SDO_POINT sert uniquement si l'objet est un point (il en définit les coordonnées). Dans notre cas, on a un polygone donc, la valeur de SDO_POINT est nulle.
• SDO_ELEM_INFO est un attribut qui permet de gérer le contenu de SDO_ORDINATES:
• La première valeur indique à quel offset de SDO_ORDINATES le calcul de l'objet doit être fait.
• La seconde valeur (SDO_ETYPE) précise le type d'objet décrit dans SDO_ORDINATES. Dans notre cas, 1003 correspond à un polygone extérieur.
• La dernière valeur indique comment on doit interpréter les coordonnées décrites dans SDO_ORDINATE. Dans notre cas, 3 correspond à un objet de type rectangle qui se construit en utilisant seulement deux points.
• SDO_ORDINATE qui est un tableau de valeurs qui sont les coordonnées à interpréter. Dans notre cas, ce sont les deux points (limite supérieure gauche et limite inférieure droite) qui sont consignés.

Cette requête récupère donc l'ensemble des objets qui sont situés dans le rectangle de l'emprise de la couche.

SELECT 1 FROM v$option WHERE parameter='Spatial' AND value='TRUE';

Cette requête permet de savoir si Oracle dispose de l'option Spatial. Elle retourne 1 dans le cas positif. La table v$option est une table spéciale d'Oracle qui permet de lister les options d'installation de la base ainsi que les fonctionnalités installées. Cette requête est lancée par QGis pour contrôler que Oracle Spatial est bien installé. Elle est lancée à chaque constitution de la classe d'itération qui sert à QGis en interne pour parcourir les objets géographiques.

Si on résume les étapes d'ouverture d'une couche QGis, on obtient la liste suivante: - On récupère le nom de l'utilisateur. - On tente de retrouver le SRID de la couche via la table de métadonnée Oracle Spatiale. - Si ce n'est pas possible, on interroge les données de la couche pour le déterminer. - On récupère les commentaires de la couche. - On récupère les commentaires des champs de la couche. - On récupère les types de données de chaque champ. - On récupère le nom de l'index spatial s'il existe. - On récupère le nom des champs. - On détermine le champ de clef primaire. - On vérifie l'existence du SRID. - On calcule les coordonnées de l'enveloppe de la couche. - On fait la requête géographique pour récupérer les valeurs des champs et des objets géographiques.

En règle générale, seule la requête de calcul de l'enveloppe de la couche peut prendre du temps. Bien entendu, selon le volume des données à transférer, la requête finale sera plus ou moins longue.

Après avoir abordé la question de l'ouverture d'une couche, il reste à analyser le comportement de QGis lorsqu'on travaille avec une couche. Je vais illustrer les cas d'utilisation suivants:

• Zoom sur la couche.
• Sélection d'un objet avec l'outil d'information.
• Sélection d'un objet avec le requêteur d'expression.
• Gestion du cache.
• Affichage des données attributaires.
• Ajout d'une deuxième couche.

De ce côté, on peut dire que QGis connaît des points à améliorer. Pour commencer, sachez qu'il n'existe aucun mécanisme de cache de données, que ce soit sur Oracle Spatial ou sur PostGIS. Chaque fois que vous vous déplacez dans une couche, QGis lance une requête du même type que celle que j'ai présentée juste au dessus, dans la partie consacrée à l'ouverture d'une couche. Pour information, mon entrepôt de données Oracle Spatial est capable de retourner environ 5000 objets polygonaux à la seconde (avec les attributs également). Mon ressenti est donc basé sur cette expérience.

Pour contrecarrer un peu les performances, sachez que si vous disposez d'un index spatial, QGis utilise la fonction sdo_filter sur l'emprise de la fenêtre carte. Dans le cas contraire, QGis requête toute la table ! Si vous avez une table conséquente, ça peut prendre du temps et même si vous travaillez à une grande échelle, il faudra quand même tout recharger. En conclusion: l'index spatial est un pré-requis pour toute utilisation sérieuse d'Oracle Spatial.

Lorsque vous sélectionnez un objet, QGis va lancer une requête un peu plus intelligente que pour l'affichage global. En effet, il va effectuer un select sur l'emprise de l'objet sélectionner. Ce mécanisme permet d'être sûr de disposer de la dernière version de l'objet avant l'affichage sur l'écran. De plus, il est peu gourmand si vous avez un index spatial puisque la requête porte uniquement sur l'emprise de l'objet considéré. Néanmoins, à la suite de la sélection d'objet, QGis rafraîchi la vue d'ensemble ce qui prend encore du temps !

Mon conseil pour mieux gérer ces temps d'accès est d'utiliser un seuil de zoom pour ne rendre visible la donnée volumineuse que dans une gamme d'échelles pertinentes afin d'éviter d'afficher (et de requêter) trop d'objets à la fois.

Je vous recommande également de ne pas utiliser l'outil de sélection par expression. En effet, ce dernier va demander à rapatrier l'ensemble des données de la couche sur une requête globale qui sélectionne tous les champs , y compris ceux de la géométrie. A la place, je vous suggère de dupliquer la couche dans laquelle vous souhaitez faire une sélection selon un ou plusieurs champs puis d'utiliser l'outil de filtre sur la couche initiale. Vous aurez deux couches: la première contiendra tous les objets de la couche, la seconde, uniquement votre sélection qui sera bien plus rapide à obtenir puisque les résultats seront limités à la clause WHERE de la requête.

De même, quand vous ouvrez la table attributaire, QGis va lancer une requête sur tous les objets de la table (y compris les géométries). Ensuite, il va réaliser autant de requêtes de sélection (sans la géométrie cette fois) pour rapatrier ligne à ligne toutes les lignes visibles de la table attributaire. Ce mécanisme paraît assez absurde: la première requête retourne tout ce qu'il faut, y compris des géométries dont on n'a pas besoin puisqu'on souhaite uniquement avoir les attributs. Tout ceci peut être assez long si vous avez de nombreuses lignes dans votre table. Ce comportement spécifique permet toutefois de ne charger que ce qui est visible dans la fenêtre d'attributs. Si vous n'avez pas encore atteint la fin de cette fenêtre, QGis effectuera une requête sur chaque clef primaire de l'objet qui doit s'afficher dans la table d'attributs. En revanche, une fois que tout le contenu de la table est récupéré (vous avez scrollé jusqu'en bas de la table), QGis ne charge plus rien. Chaque fois que vous sélectionnez un objet dans la table d'attributs, QGis ré-intérroge la table spatiale en utilisant l'emprise de la fenêtre d'affichage des cartes.

Mon conseil pour la gestion de la table attributaire est de modifier le comportement par défaut de QGis qui consiste à afficher les attributs de tous les objets de la table. Pour mieux gérer ce problème, il faut modifier les options de la table attributaire et sélectionner de n'afficher que les objets visibles à l'écran. Cela permettra, combiné au seuil de zoom, de réduire fortement les temps de requête sur le serveur Oracle. De même, vous pouvez également désactiver le rendu de la couche dans la fenêtre carte pour effectuer des opérations de sélection dans la table d'attributs: c'est le rendu des objets sélectionnés qui prend du temps.

Lorsque vous ajoutez une nouvelle couche à votre liste de couches, QGis ré-interroge également toutes les couches pour extraire les objets qui se situent dans l'emprise de la fenêtre.

On comprend aisément que ces mécanismes d'interrogation sans cache mettent les performances de QGis à genoux dès que vous avez un grand nombre d'objets à gérer. Si vous vous trouvez dans un environnement contraint, c'est-à-dire que si le serveur Oracle n'est pas sur votre machine, vous aurez intérêt à développer certains réflexes:

• Faire en sorte que vous couches disposent toutes d'un index spatial.
• Ajouter une nouvelle couche uniquement sur un zoom à grande échelle.
• Sélectionner un objet uniquement sur un zoom à grande échelle.

Il existe une demande d'amélioration qui porte sur le cache mais elle n'a pas été prise en compte malgré l'existence d'un patch. Il faut quand même noter que la prochaine version de QGis a fait un grand pas en avant dans le traitement des couches volumineuses. De plus, QGis v2.4 apportera la gestion multi-threadée des couches (une couche= un thread) ce qui devrait améliorer sensiblement les temps de réponse de l'interface qui ne devrait plus être bloquée pendant le chargement d'une couche comme c'est encore le cas actuellement. Maintenant que toutes les contraintes sur la gestion des couches volumineuses locales est géré, peut-être que les développeurs vont se concentrer à rendre QGis plus frugal avec les serveurs de bases de données spatiales.

Une solution de contournement pourrait consister à utiliser le plugin d'édition offline. Son principe est simple: on stocke le contenu d'une couche distante dans une base de données locale SpatiaLite. Lors de l'édition de cette couche, on peut ensuite resynchroniser avec la couche d'origine, une fois les modifications validées. Néanmoins, ce plugin ne fonctionne plus avec les dernières versions de QGis.

Nous avons abordé pour l'instant les performances de lecture pour l'affichage de données issues d'un entrepôt de données sous Oracle Spatial. Mais QGis sert également à modifier des données. Il permet d'ajouter des objets géographiques et également de modifier les attributs d'une table. Il est donc important, pour faire le tour complet de la question de s'interesser également à ces points bien particuliers.

Voici ce que nous allons faire:

• Créer une couche spatiale vide, dans les règles de l'art.
• Importer de la données dedans.
• Modifier un champ attributaire.
• Ajouter un objet géographique dans QGis.
• Modifier la structure de la table en modifiant un champ.
• Modifier la structure de la table en ajoutant/supprimant un champ.
• Créer un index spatial depuis QGIs.

Pour créer une couche dans les règles de l'art de QGis il faut bien prendre en compte les paramètres qui suivent:

• Mettre des commentaires là où c'est possible.
• Mettre obligatoire un SRID dans la couche de métadonnées.
• Mettre obligatoirement les valeurs X et Y de l'emprise dans la couche de métadonnées.
• Mettre obligatoirement une clef primaire.
• Créer obligatoirement un index spatial.
• Ajouter des éléments de contrainte de géométrie.

On obtient un script SQL de ce type:

DROP TABLE TEST_QGIS_S;

-- Création de la table:
CREATE TABLE TEST_QGIS_S (
	GID NUMBER(10),
	DESCRIPTION varchar2(40),
	GEOM MDSYS.SDO_GEOMETRY
);

-- Ajout des commentaires
COMMENT ON TABLE TEST_QGIS_S IS 'Table de test d''écriture pour QGIS';
COMMENT ON COLUMN TEST_QGIS_S.GID IS 'Clef primaire de base';
COMMENT ON COLUMN TEST_QGIS_S.DESCRIPTION IS 'Champ de description de l''objet';

-- Contrainte de clef primaire
ALTER TABLE TEST_QGIS_S ADD CONSTRAINT TEST_QGIS_S_IDX PRIMARY KEY (GID) USING INDEX TABLESPACE MMET_INDEX;

-- Contrainte de Géométrie: Polygone
ALTER TABLE TEST_QGIS_S ADD CONSTRAINT TEST_QGIS_S_GCHECK CHECK ( GEOM.SDO_GTYPE = 2003 );

-- Enregistrement table de métadonnée Oracle Spatial
DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'TEST_QGIS_S';
INSERT INTO USER_SDO_GEOM_METADATA ( TABLE_NAME, COLUMN_NAME, DIMINFO, SRID )
	VALUES ('TEST_QGIS_S', 'GEOM',
	        MDSYS.SDO_DIM_ARRAY(
			  MDSYS.SDO_DIM_ELEMENT('X',276000,322000,0.005),
			  MDSYS.SDO_DIM_ELEMENT('Y',239000,271000,0.005)),
		    27562);

-- Création de l'index spatial
DROP INDEX GI_TEST_QGIS_S FORCE;

CREATE INDEX GI_TEST_QGIS_S ON TEST_QGIS_S(GEOM)
	INDEXTYPE IS MDSYS.SPATIAL_INDEX
	PARAMETERS ('layer_gtype=POLYGON TABLESPACE=USER_INDEX SDO_DML_BATCH_SIZE=1');

COMMIT;

Il existe maintenant une couche TEST_QGIS_S de disponible pour l'édition. Première attention, si vous avez coché la case Seulement les types de géométrie existants dans la configuration du connecteur Oracle de QGis, votre couche n'apparaîtra pas. En effet, QGis lance une requête sur le contenu de la couche pour déterminer le type de géométrie. Si l'option est activée, QGis est plus strict en ce qui concerne la géométrie et n'affiche pas la couche dans la liste. Si la case est décochée, QGis vous propose de sélectionner le type de données. Dans notre cas, nous allons choisir le type Polygone.

Par ailleurs, si vous utilisez l'onglet Parcourir ou le panneau de navigation, QGis ne vous listera pas la couche. Pour ouvrir une couche géographique Oracle Spatial vide, il faut impérativement passer par la boîte de dialogue du connecteur Oracle. Autre effet de bord, vous serez également obligé d'indiquer manuellement le SRID de la couche car celui-ci est récupéré via une requête qui scanne les objets géographiques de la couche. Ce comportement est typiquement un bug que j'ai remonté.

Maintenant que nous disposons d'une couche vide, il faut la remplir.

Voyons maintenant le cas de la modification d'un objet dans la couche. Le constat est assez intéressant: dès qu'on active le mode d'édition, QGis va jouer systèmatiquement deux requêtes en préalable à toute action (mise à jour de l'emprise de la fenêtre carte, modification d'un objet, déplacement d'un point d'un polygone, sélection d'un objet, suppression d'un objet, modification du contenu d'un attribut, etc.):

• D'abord, on constate que QGis rappatrie des objets à l'aide d'une requêt globale: SELECT "GEOM","GID","DESCRIPTION" FROM "MMET"."TEST_QGIS_S" "featureRequest"; Cette requête peut faire peur car elle est censée tout rapatrier. Néanmoins, QGis limite ce qui est récupéré à ce qui lui est utile. J'ai pu constater que le contenu de cette requête est digéré par QGis selon le zoom (et le nombre d'objets qui sont présents). Je ne suis pas parvenu à voir comment QGis faisait pour gérer ce qu'il rapatrie et quand il décide de mettre fin à la requête. Dans tous les cas, le volume de données retourné par la requête est fonction du zoom.
• Ensuite, QGis rafraîchit son affichage et il relance une requête tenant compte de l'index:

SELECT "GEOM","GID","DESCRIPTION" FROM "MMET"."TEST_QGIS_S" "featureRequest"
WHERE sdo_filter(
  "GEOM",
  mdsys.sdo_geometry(2003,27572,NULL,
    mdsys.sdo_elem_info_array(1,1003,3),
    mdsys.sdo_ordinate_array(302522.88477424852317199,
                             256408.67948694800725207,
                             305644.892545554557116702,
                             258634.18810979597037658)))='TRUE';

La deuxième requête est contenue car elle ne concerne que l'emprise de la fenêtre carte. La première est gérée par QGis qui l'arrête en cas de besoin. Dans la pratique, le poids des deux requêtes est assez proche. Ce qui fait qu'on peut dire que les temps de réponse en mode édition sont globalement deux fois plus longs que ceux du mode de consultation.

Lorsqu'on sélectionne un objet, QGis effectue une requête spécifique qui rapatrie uniquement tous les attributs (y compris géométrique) de l'objet. Cette requête est suivie du rafraîchissement de la fenêtre carte. En mode édition, la sélection d'un objet ajoute une requête de plus: la requête globale maîtrisée par QGis. Concrètement, en mode édition, le temps de sélection est deux fois plus long que le temps en mode consultation.

Je vous conseille, pour les couches volumineuses, de travailler à une échelle adaptée qui limitera le nombre d'objets affichés en même temps.

Lorsqu'on supprime un objet, QGis lance une requête de rafraichissement de l'écran qui conduit à rejouer nos deux requêtes du mode édition. Ces deux requêtes s'ajoutent à la sélection de votre objet que vous avez du effectuer avant de lancer sa suppression. Pour propager la suppression, il faut soit sortir du mode d'édition, soit enregistrer les changements. Cette propagation se traduit par une requête DELETE (très légère), suivie d'un autre rafraîchissement (qui lui est forcément plus long).

Globalement, on peut dire que si on travaille avec un nombre d'objets raisonnable, les temps de réponse sont bons (<1 seconde pour 5000 objets visibles dans mon cas).

Dès qu'on déplace un noeud d'un objet, QGis rafraîchit l'affichage et lance les deux requêtes du mode édition. Là encore, même pour cette opération simple, il faudra travailler à une échelle raisonnable.

Comme dans le cas de la suppression, QGis ne va lancer les requêtes de modification des lignes de la table concernée (via un UPDATE) que lorsqu'on quitte le mode édition ou qu'on lance une sauvegarde de ses modifications (l'icône en forme de disquette). On peut distinguer deux types de modification:

• Modification de la géométrie.
• Modification uniquement des attributs.

Sur ce point, QGis se révèle assez intelligent: suivant l'un ou l'autre des cas, il n'effectue les requêtes d'UPDATE que sur les champs qui ont été modifiés. Si vous avez modifié deux attributs sur cinq, il y aura une seule requête UPDATE qui s'occupera de mettre à jour les deux attributs modifiés. Si vous avez modifié la géométrique, une requête UPDATE sera jouée sur le champ de géométrie de l'objet. Si vous avez modifiés attributs et géométrie, il y aura deux requêtes UPDATE, l'une pour les attributs, l'autre pour la géométrie.

Bien sûr, cette action entraîne le rafraîchissement de la couche dans QGis…

Si vous avez les droits suffisants sur la couche, QGis permet de changer la structure de la couche, dans les limites permises par l'outil. On ne peut pas modifier le nom ou le type d'un champ déjà existant. En revanche, il est possible à la fois de supprimer n'importe quel champ et également d'une ajouter un ou plusieurs. Cela peut être assez pratique pour caler votre modélisation et ajouter un champ rapidement pour y injecter des données. Sur ce point, QGis lance des requêtes ALTER TABLE. On peut indiquer des commentaires sur le champ et QGis les intègre directement au niveau Oracle Spatial.

Néanmoins, pour que vos changements soient propagés dans la couche sous QGis, il faudra l'ouvrir à nouveau manuellement.

En termes de bugs, j'ai juste pu noter que l'inclusion des champs VARCHAR2 et CHAR ne fonctionnait pas bien. En dehors de ça, pas de surprises…

D'une manière générale, le cœur du problème du comportement de QGis en édition est constitué par les deux requêtes qui sont lancées dès qu'une opération de rafraîchissement est lancée. Or, QGis lance souvent des requêtes de rafraichissement; en fait dès qu'une intervention graphique sur la fenêtre de carte a lieu. Le poids de ce rafraichissement est deux fois plus lourd qu'en mode consultation. Il faut donc tenir compte de cette contrainte supplémentaire pour travailler à des échelles n'affichant qu'un nombre raisonnable d'objets.

Au delà de cette contrainte, QGis donne toute satisfaction en édition. Il est capable de faire toutes les opérations de modification de géométrie et d'attributs dans des conditions correctes et efficaces.

Pour ma part, dans ma configuration de travail, je ramène environ 5000 objets par seconde (pour du parcellaire cadastral) à une échelle de 1/10000. Mon échelle de travail sur cette couche ne doit donc pas dépasser 1/15000 sous peine d'avoir des temps de latence trop longs et incompatible avec des conditions de travail. 1/15000 est une échelle assez large pour travailler sur du cadastre. Je ne serai donc pas obligé de trop zoomer pour pouvoir travailler correctement sur cette couche.

Après avoir étudié le comportement de QGis avec Oracle, il convient, pour avoir de bons éléments de comparaison, de faire la même étude mais avec le connecteur PostGIS. Ce dernier est en effet implémenté depuis plus longtemps et on peut donc penser qu'il sera plus complet et qu'il sera plus performant. Mais encore faut-il le vérifier. C'est l'objet de ce passage.

Lors du lancement du connecteur PostGIS, des requêtes sont lancées pour obtenir la liste des couches.

Voici la première:

SELECT l.f_table_name,l.f_table_schema,l.f_geometry_column,upper(l.type),l.srid,c.relkind
FROM geometry_columns l,pg_class c,pg_namespace n
WHERE c.relname=l.f_table_name
  AND l.f_table_schema=n.nspname
  AND n.oid=c.relnamespace
  AND has_schema_privilege(n.nspname,'usage')
  AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select')
ORDER BY n.nspname,c.relname,l.f_geometry_column

Cette requête permet de lister les tables géographiques listées dans la vue geometry_columns. Pour savoir ce que l'utilisateur qui fait la connexion a le droit d'ouvrir, on utilise quelques fonctions, notamme has_table_privilege. Par ailleurs, cette requête utilise également le catalogue pg_class qui permet de déterminer le type de table: table normale, vue, vue matérialisée, etc. Elle est en général assez rapide à effectuer.

Ensuite, pour chaque table, QGis va lancer une requête permettant de lister les attributs:

SELECT attname,
	   CASE WHEN typname = ANY(ARRAY['geometry','geography','topogeometry']) THEN 1 ELSE null END AS isSpatial
FROM pg_attribute
  JOIN pg_type ON atttypid=pg_type.oid
WHERE attrelid=regclass('"schema"."TABLE_GEOGRAPHIQUE"')

Cela permet de lister les attributs de chaque table et de déterminer la colonne spatiale. En règle générale, c'est une requête assez rapide même si dans certains cas, il faudra la multiplier par le nombre de tables dans l'entrepôt de données géographiques.

Une fois la liste des attributs récupérée, QGis lance une autre requête globale:

SELECT l.f_table_name,l.f_table_schema,l.f_geography_column,upper(l.type),l.srid,c.relkind
FROM geography_columns l,pg_class c,pg_namespace n
WHERE c.relname=l.f_table_name
  AND l.f_table_schema=n.nspname
  AND n.oid=c.relnamespace
  AND has_schema_privilege(n.nspname,'usage')
  AND has_table_privilege('"'||n.nspname||'"."'||c.relname||'"','select')
ORDER BY n.nspname,c.relname,l.f_geography_column

Et non, ce n'est pas la même que la première. En effet, celle-ci interroge la vue geography_columns au lieu de geometry_columns. La différence tient au nouveau type de données géographiques que PostGIS a mis en œuvre. Le premier type se dénomme geometry. Pour ces objets, la plus courte distante est exprimée sur un plan. Donc le chemin le plus court entre deux objets "geometry" est une droite. Pour les objets "geography", c'est un arc de cercle car dans ce mode, la plus courte distance est exprimée sur une sphère. Ces objets sont utilisés pour avoir plus de précision par rapport aux calculs réalisés.

Ensuite, c'est le même topo, pour chacune de ces couches, on va récupérer la liste des attributs.

Enfin, QGis fait une requête pour lister les rasters et pour chaque raster on récupère les attributs.

Lorsqu'on ouvre une couche, QGis va lancer une série de requêtes sur cette dernière dont voici la première:

SELECT * FROM "schema"."TABLE_GEOGRAPHIQUE" LIMIT 1

Elle permet de récupérer la liste des attributs. Vient ensuite une petite vérification:

SELECT pg_is_in_recovery()

Elle permet de se renseigner sur l'état du serveur PostgreSQL et de savoir si il est en opération de recovery.

On poursuit avec la gestion des droits de l'utilisateur:

SELECT has_table_privilege('"schema"."TABLE_GEOGRAPHIQUE"','DELETE'),
	   has_any_column_privilege('"schema"."TABLE_GEOGRAPHIQUE"','UPDATE'),
	   has_column_privilege('"schema"."TABLE_GEOGRAPHIQUE"','geom','UPDATE'),
	   has_table_privilege('"schema"."TABLE_GEOGRAPHIQUE"','INSERT'),
	   current_schema()

Cette requête essaye de voir si on a le droit de modifier, d'ajouter ou de supprimer des données sur la table sélectionnée (TABLE_GEOGRAPHIQUE dans l'exemple).

Vient ensuite une série de requêtes qui tentent d'en savoir un peu plus sur la couche. On commence par récupérer l'oid de la table (dans l'exemple il vaudra 17734):

SELECT regclass('"public"."CADASTRE_PARCELLE_S"')::oid;

Muni de cet oid, on récupère la description de la table (le commentaire):

SELECT description FROM pg_description WHERE objoid=17734 AND objsubid=0;

Ensuite, QGis relance une lecture du nom des attributs de la table:

SELECT * FROM "schema"."TABLE_GEOGRAPHIQUE" LIMIT 0

Maintenant, pour chaque attribut, QGis va lancer une série de 3 requêtes:

SELECT typname,typtype,typelem,typlen FROM pg_type WHERE oid=23
SELECT attnum,pg_catalog.format_type(atttypid,atttypmod) FROM pg_attribute
 WHERE attrelid=17734 AND attname='ATTRIBUT1'
SELECT description FROM pg_description WHERE objoid=17734 AND objsubid=1

La première va permettre de déterminer le type PostgreSQL supposé de l'attribut tel que QGis l'a repéré. Par exemple, l'oid 23 de pg_type correspond à un type int4. QGis connaît les types des attributs grâce aux requêtes précédentes. La deuxième requête permet de déterminer le numéro d'attribut (dans l'ordre de la création de la table) ainsi que son type PostgreSQL. Enfin, on récupère le commentaire du champ.

On va maintenant déterminer si la couche dispose d'un index de clef primaire et on va récupérer son oid:

SELECT indexrelid
FROM pg_index
WHERE indrelid='"schema"."TABLE_GEOGRAPHIQUE"'::regclass
  AND (indisprimary OR indisunique)
ORDER BY CASE WHEN indisprimary THEN 1 ELSE 2 END
LIMIT 1

Muni de cet index, QGis demande sur quel attribut il s'applique permettant de déterminer la clef primaire:

SELECT attname FROM pg_index,pg_attribute
  WHERE indexrelid=17738 AND indrelid=attrelid
    AND pg_attribute.attnum=any(pg_index.indkey)

Ensuite, QGis essaye de déterminer s'il existe bien une seule colonne géométrique:

SELECT count(*) FROM pg_stats
WHERE schemaname='schema' AND tablename='TABLE_GEOGRAPHIQUE' AND attname='geom'

Par la suite, QGis détermine le nombre d'objets géographiques de la couche via la requête suivante:

SELECT reltuples::int FROM pg_catalog.pg_class
WHERE oid=regclass('"schema"."TABLE_GEOGRAPHIQUE"')::oid

Maintenant qu'on dispose des métadonnées de la couche, de sa clef primaire, de sa colonne géométrique, il faut calculer la bounding-box. QGis tente de le faire avec deux modes:

SELECT st_estimatedextent('schema','TABLE_GEOGRAPHIQUE','geom')

Cette première requête utilise la fonction PostGIS d'estimation de la bouding-box de la couche. Cette estimation est calculées à chaque opération de VACCUUM. S'il n'y a rien de disponible, QGis calcule la bouding-box de manière traditionnelle:

SELECT st_extent("geom") FROM "schema"."TABLE_GEOGRAPHIQUE"

Maintenant, nous avons tout ce qu'il faut pour rapatrier les données. QGis va le faire avec un curseur.

DECLARE qgisf0_0 BINARY CURSOR FOR
  SELECT st_asbinary(st_snaptogrid("geom",28.3257),'NDR'),
		 "id"
  FROM "schema"."TABLE_GEOGRAPHIQUE"
  WHERE "geom" && st_makeenvelope(276970.7717500000144355,
                                  236370.84200633913860656,
                                  321654.49825000000419095,
                                  274043.9679936608299613,
                                  27562)

Ici, on déclare un curseur nommé qgisf0_0 qui va se nourrir d'une requête qui récupère la géométrie (en binaire) ainsi que l'attribut id des objets qui intersectent (opérateur &&) l'emprise courante. Ensuite, QGis "fetch" par paquet de 2000:

FETCH FORWARD 2000 FROM qgisf0_0

Quand QGis a fini, il ferme le curseur:

CLOSE qgisf0_0

Pour résumer, lorsque QGis ouvre une couche, on a les étapes suivantes:

• Sélection de la première ligne de la table
• Etat de PostgreSQL
• Récupération des droits de l'utilisateur sur la table
• Récupération de l'oid de la table pour…
• …pouvoir récupérer les commentaires de la table (visibles dans l'onglet métadonnées de la couche dans QGis).
• Récupération des noms de colonnes.
• Pour chaque attribut, on récupère:
• la définition de son type présumé
• son ordre dans la table et son type avéré
• les commentaires de la colonne.
• Récupération de la clef primaire
• Vérification de la colonne géométrique
• Détermination du nombre d'objets de la couche
• Récupération de la bounding-box (avec deux techniques)
• Ouverture d'un curseur
• Récupération des données géométrique et de clef primaire (donc pas la totalité des données) par paquets de 2000.
• Fermeture du curseur.

La seule requête lourde est celle de la fin (avec le curseur). Néanmoins, seuls les données utiles sont rapatriées: la géométrie et la clef primaire. Le calcul de la bounding-box peut également prendre du temps par la commande st_extent. Il vaut donc mieux effectuer des VACUUMS régulièrement pour que cette emprise soit recalculée lors de cette opération de maintenance et non à chaque ouverture de la couche.

Lorsqu'on se déplace dans une couche PostGIS, QGis déclare un curseur avec la méthode présentée au dessus. A chaque fois que la fenêtre d'extent se modifie, un nouveau curseur est défini et les données sont à nouveau rapatriées.

Il n'y a pas de mécanisme de cache mais comparativement à ce qui se passe avec le connecteur Oracle, QGis récupère uniquement ce qui est utile: les géométries et la clef primaire. Les autres attributs ne sont pas concernés. Ce type de comportement se déclenche à chaque refresh de la fenêtre.

Lorsqu'on réalise une interrogation d'objet, la requête est un peu modifiée:

DECLARE qgisf1_8 BINARY CURSOR FOR
  SELECT st_asbinary("geom",'NDR'),
		 "ATTRIBUT1",
		 "ATTRIBUT2",
		 …
		 "ATTRIBUTN"
 FROM "schema"."TABLE_GEOGRAPHIQUE"
 WHERE "geom" && st_makeenvelope(296689.76752788928570226,
                                 256669.62860945626744069,
                                 296757.21800251881359145,
                                 256737.07908408585353754,
                                 27562)
   AND st_intersects("geom",st_makeenvelope(296689.76752788928570226,
                                                256669.62860945626744069,
                                                296757.21800251881359145,
                                                256737.07908408585353754,
                                                27562))

Elle récupère les attributs et restreint la sélection à l'objet qui se trouve dans l'emprise que QGis connaît de l'objet sélectionné. Il n'y a pas de refresh.

Lorsqu'on effectue une simple sélection, le comportement est le même que le refresh sauf qu'on restreint la requête à l'emprise de l'objet sélectionné. Après chaque sélection, il y a un refresh ce qui peut être long si vous avez beaucoup d'objets affichés en même temps.

La sélection par expression affiche les mêmes timings catastrophiques que sous Oracle. En effet, tous les objets sont alors rapatriés et la sélection se fait par les mécanismes internes de QGis.

Enfin, lorsqu'on demande le décompte des entités, QGis effectue une sélection sur tous les objets mais ne rappatrie que les attributs non géométriques ce qui limite le volume récolté (mais qui est loin d'être une requête du type count).

On procède de la même méthode du curseur que pour les sélections d'objet sauf qu'il n'y a plus d'emprise et que tous les attributs sont récupérés y compris la géométrie (à quoi bon ?). En conséquence, QGis récupère toutes les données. Ensuite, pour chaque ligne visible, QGis va lancer une requête de sélection limitée à la clef primaire de l'objet qui doit être affiché:

DECLARE qgisf1_8 BINARY CURSOR FOR
  SELECT st_asbinary("geom",'NDR'),
		 "ATTRIBUT1",
		 "ATTRIBUT2",
		 …
		 "ATTRIBUTN"
  FROM "schema"."TABLE_GEOGRAPHIQUE"
  WHERE "ATTRIBUT1"=123456

Néanmoins, il y a un mécanisme de cache: QGis ne récupère que les lignes qui n'ont pas encore été affichées. Le nombre de lignes en cache est limité à 100000. Par rapport, à Oracle, il n'y a pas vraiment de différences et on constate le même comportement bizarre de récupération de la géométrie.

On retrouve quasiment le même comportement qu'avec Oracle. Lors du passage en mode édition, QGis ajoute une requête globale qu'il gère comme un grand. Cette requête globale n'a aucun poids: le curseur est déclaré mais aucun Fetch n'est lancé et le curseur est fermé. A chaque sélection d'un objet, QGis requête l'objet comme pour une sélection et fait un refresh.

Lorsqu'on déplace un noeud, QGis réalise juste un refresh. En fonction du nombre d'objets affichés, ce sera plus ou moins long.

Attention, lorsque vous avez sélectionné le décompte des entités de la couche, le comportement de QGis pose problème. En effet, à chaque modification, ce dernier récupère la totalité des champs attributaires de toute la table, puis il fait un refresh ! En conséquence, la moindre modification d'objets sur une table volumineuse prend beaucoup de temps. Ce comportement me semble relever d'un bug !

Il manque encore des choses dans QGis pour faire aussi bien que ce qui existe avec PostGIS. Par exemple, le gestionnaire de base de données ne gère pas les bases Oracle. Impossible de créer des tables à la volée, de faire des requêtes intermédiaires. Je pense qu'il ne manque pas grand chose pour intégrer ce type de SGBDRS(patial) dans cet outil qui me semble incontournable pour QGis.

Pendant que nous sommes en train d'étudier le comportement du connecteur Oracle, parlons un bref moment de performances…

J'ai importé dans SpatiaLite, (dans une base de données sur un disque local), une couche Oracle Spatial de plus de 200000 objets polygoniaux (du cadastre). L'import prend près de 5 minutes mais reste mesuré en termes de consommation mémoire, si l'on compare avec un import par copier/coller des entités depuis Qgis. A partir de cette couche, j'ai généré une couche au format MapInfo TAB (l'export dure environ 30 secondes). J'ai dupliqué cette couche MapInfo sur un espace réseau sur du LAN à 100Mbits/s juste pour voir…

Je me suis amusé à mesurer les temps d'affichage à différents seuils de zoom en croisant avec les temps de MapInfo comparé à ceux de QGis. Toute est résumé dans le tableau suivant:

L'analyse du tableau révèle les éléments suivants:

• D'abord, au niveau de la connexion Oracle Spatial, QGis est deux fois plus performant (globalement) que MapInfo 10 (sans son mécanisme de cache).
• Ensuite, QGis connecté à Oracle Spatial a environ les mêmes performances que MapInfo connecté à un lecteur réseau.
• Spatialite en local est bien meilleur, il est environ deux fois plus performant qu'avec Oracle Spatial.
• MapInfo sait bien gérer ses accès réseau. Le mécanisme d'accès à la donnée au sein des fichiers .DAT et .MAP est sans doute mieux codé que la bibliothèque GDAL qui offre l'ouverture des fichiers TAB dans QGis.
• Dès qu'on revient à un stockage local des fichiers TAB, les performances de QGis redeviennent honnêtes.

Ce test met en œuvre une couche lourde à gérer. Le nombre d'objets est assez important et les échelles utilisées permettent d'afficher un grand nombre de ces objets en même temps.

La tableau ci-dessous fait un petit comparatif des temps d'ouverture avec des rasters (ECW de 170Mo et GeoTiff de 550Mo):

Pour les dalles ECW, il n'y a pas de problème de temps d'ouverture. On voit que MapInfo comme QGis lisent uniquement ce qui leur est utile et affichent les données directement. Je n'ai donc pas effectué de test en local tellement les temps d'accès sont faibles. Pour d'autres formats, les chiffres peuvent être un peu différents. C'est le cas notamment du format GéoTiff comme le montrent les chiffres du tableau ci-dessous:

On voit que les temps d'accès aux fichiers rasters sont du même ordre de grandeur entre QGis 2.2 et MapInfo 10. Au niveau accès réseau, le temps de 28 secondes correspond à la moitié du temps pour ouvrir la dalle raster en totalité, c'est à dire qu'il faut que QGis (ou MapInfo) rappatrie l'intégralité du fichier. Copier le fichier avec l'explorateur de fichiers depuis cet emplacement réseau donne un temps de lecture deux fois plus long. Pour pallier à ce problème, deux solutions s'offrent à nous:

• Réduire la taille du fichier en utilisant les bonnes options de compression.
• Utiliser le système des pyramides pour présenter des vues intermédiaires.

QGis propose une boite de dialogue assez performante pour générer des GéoTiffs bien compressés avec pyramides. On y accède en faisant un clic-droit sur la couche raster et faisant sauvegarde sous…. J'ai réalisé un test de compression sur le fichier raster cité en exemple et j'arrive à le compresser à 240 Mo (soit deux fois moins que le fichier d'origine sans perte liée à la compression) en ayant un temps d'ouverture quasi-constant, situé à 6 secondes comparé aux 28 secondes initiales.

Histoire d'être un peu complet sur le sujet, faisons un tour du développement de la connexion vers Oracle Spatial dans QGis. C'est Jürgen Fischer qui s'occupe de ce développement. Le code de QGis étant gérer sur la plate-forme GitHub, il est facile d'avoir le déroulé des évolutions en consultant l'historique. Le premier commit date de janvier 2013. Ca fait donc près d'un an et demi que le code de QGis contient de quoi se connecter à Oracle Spatial.

Très rapidement, quelques corrections ont eu lieu. On peut citer dans l'ordre:

• Une meilleure gestion des systèmes de projection par l'ajout de certains SRID dans la table de référence Oracle (sdo_coord_ref_system) lors de la création d'une nouvelle couche vide si le système n'est pas connu dans cette même table (commit).
• Une meilleure gestion des tables sans géométrie récupérées depuis Oracle (commit).
• L'accélération du calcul de la bounding-box de la couche si vous avez coché la case "Utiliser la table de métadonnées estimées", à la condition que les champs de définition des dimensions soient bien définis (voir dans le point consacré aux conseils de catalogage)(commit).
• Une meilleure gestion des champs date et time dans Oracle (commit).
• Réécriture des requêtes de listing des champs d'une table avec une bien meilleure rapidité (commit).

Dans les dernières améliorations, en nouveauté pour la version 2.4, on peut noter la mise en cache de la liste des couches d'une base Oracle. Sur de gros entrepôts de données, l'interrogation de la liste des couches hébergées peut prendre plusieurs minutes. Cette interrogation se fait quasiment à chaque fois que vous ouvrez le connecteur QGis. C'est particulièrement pénible si vous avez oublié d'ajouter une couche à votre projet. Pour éviter ce problème, ce commit permet de stocker le résultat de l'interrogation d'une connexion Oracle Spatial dans un cache local de QGis qui se trouve être une base de données QSLite dédiée. C'est une idée plutôt opérationnelle car attendre indéfiniment le rechargement de la liste des tables d'une base volumineuse est assez pénible dans la pratique. En annexe à cette possibilité, on trouve l'ajout d'une case à cocher pour que la boîte de dialogue du connecteur Oracle reste ouverte après avoir appuyé sur le bouton "Ajout".

Enfin, une bonne partie des bugs que j'ai remonté sont déjà corrigés dans le code et devraient impacter la version 2.4 de QGis qui devrait ouvrir les couches un peu plus rapidement qu'avant. De ce côté, on peut dire que le développement est vraiment actif.

Le premier conseil que je peux vous donner est de vous assurer que la table de métadonnées Oracle (ALL_SDO_GEOM_METADATA) est bien à jour et que notamment, elle ne référence pas des tables qui n'existent plus. Dans le cas contraire, QGis va générer un message d'erreur et faire des requêtes intermédiaires inutiles. De plus, chaque requête qui échoue déclenche l'écriture (au moins en mémoire) d'un log de problème et qui viendra gonfler la table des erreurs de QGis qu'on peut consulter via le bouton adéquat.

Ensuite, il serait bon de penser à ajouter systématiquement le SRID (système de projection) dans ALL_SDO_GEOM_METADATA. Si ce n'est pas le cas, QGis interroge les objets géométriques de la couche pour le déterminer. Cette seconde requête ne fonctionne que si la couche n'est pas vide. Si une couche ne dispose pas de SRID, QGis demande impérativement qu'un SRID soit attribué à la couche et ouvre une boîte de dialogue adéquate. Si vous avez 20 couches à ouvrir, vous aurez 20 fois la question… Quelle perte de temps ! De plus, si vous avez plusieurs systèmes de projection, ça peut vite devenir complexe de gérer manuellement à la couche. Dans les bonnes pratiques de stockage de la donnée géographique, assurez-vous de mettre TOUT LE TEMPS le SRID dans la table de métadonnée (que ce soit pour Oracle ou PostGIS d'ailleurs).

Par ailleurs, veillez également à mettre des données qui sont toutes du même type géométrique dans une table. Les bonnes pratiques de stockage de l'information géographique recommandent de ne pas mixer les types de données et de disposer de couches mono-type, et ce même si la majorité des logiciels de SIG savent gérer une couche avec des types de géométries multiples. Mais, à priori, ce n'est pas le cas de QGis. Si vous avez des données ponctuelles dans une couche qui contient également des lignes, QGis va vous demander de choisir. Au niveau du connecteur Oracle, lorsqu'une couche contient plus d'un type de géométrie, QGis affiche deux couches au nom identique mais avec un type différent. Cela peut être facilement destabilisateur pour l'utilisateur final (quelle est la bonne couche ?). Je vous renvoie au point "Sous le capot" qui fait mention de la requête de détermination du type de géométrie. De ce côté, PostGIS est plus contraignant par défaut car en règle générale, lors de la création d'une couche géographique, on indique toujours une contrainte sur le type de géométrie de la couche.

En regardant le code du connecteur Oracle on peut constater que QGis peut récupérer les commentaires des noms de champs de la couche ouverte, il me paraît donc intéressant de les rajouter directement dans la table afin que les utilisateurs de la couche puissent avoir plus d'informations sur les attributs de cette dernière. D'ailleurs, si vous avez les droits d'écriture, QGis permet d'ajouter des commentaires aux champs que vous créez (QGis ne peut pas modifier les champs actuels).

N'hesitez pas à créer des index spatiaux sur vos couches. Lorsque vous ouvrez une couche avec QGis, celui-ci indique le message d'avertissement suivant: "No spatial index on column USER.TABLE.COLONNE_GEOMETRIQUE found - expect poor performance.". C'est vraiment ce qui va arriver car sans index spatial, QGis ramène la couche complète avec tous les objets ! Donc, une utilisation sérieuse d'un entrepôt de données Oracle Spatial impose de créer un index spatial. Franchement, c'est loin d'être complexe, il y a juste une ligne de SQL à rajouter pour améliorer fortement les performances de QGis… Pourquoi s'en priver ?

Un point important si vous souhaitez calculer rapidement l'emprise des couches est de bien renseigner vos champs de SDO_DIM_ELEMENT dans la table ALL_SDO_GEOM_METADATA. Pour que QGis puisse les prendre en compte, il faut qu'ils soient nommés respectivement 'X' et 'Y'. Sinon la requête de calcul du boudning-box se fera en mode lent: compter environ 10 secondes pour une couche avec une centaine d'objet (estimation non linéaire qui dépend de vos géométries). Pensez également à cocher la case Utiliser la table de métadonnées estimées pour vous servir de l'emprise déclarée dans ALL_SDO_GEOM_METADATA. Une fois ces pré-requis établis, l'ouverture d'une couche dans QGis devrait prendre beaucoup moins de temps car la requête de calcul d'emprise qui peut être très longue ne sera pas lancée.

Ce tour d'horizon du connecteur Oracle Spatial montre bien que QGis est prêt à utiliser des bases de données relationnelles spatiales avec ce type de technologie. Certes le passé de développement du connecteur est beaucoup moins long que celui de PostGIS mais l'essentiel est là. Vous devez néanmoins prendre des précautions quand vous élaborez votre entrepôt de données, notamment avec la gestion de la table de métadonnées et des SRID ainsi que des index spatiaux. Mais globalement ces conseils font partie des bonnes pratiques de gestion d'un entrepôt de données géographiques.

Comme avec le connecteur PostGIS, le connecteur Oracle Spatial souffre un peu du comportement de QGis qui fait souvent deux fois la même requête pour rien, c'est notamment le cas lors de l'affichage de la table attributaire. Une bonne remise à plat de ces tentatives de connexion pour rendre QGis plus frugal sur le plan du requêtage serait un bon travail sachant que le niveau de technique pour la gestion des couches volumineuses est maintenant achevé. Enfin, l'ajout d'une gestion de cache permettrait de minimiser les appels au serveur de base de données avec à la clef, un travail plus facile sur des couches complexes ou disposant de nombreux objets.

Pour terminer et être tout à fait complet sur le sujet, il manque encore au moins un élément dans QGis pour faire aussi bien que ce qui existe avec PostGIS. En effet, le gestionnaire de base de données (plugin DBManager) de QGis ne gère pas les bases Oracle. Impossible de créer des tables à la volée, de faire des requêtes intermédiaires. Je pense qu'il ne manque pas grand chose pour intégrer ce type de SGBDRS(patial) dans cet outil qui me semble incontournable pour QGis. D'ailleurs une demande d'évolution a déjà été déposée. Lorsqu'il sera prêt, QGis aura une nouvelle corde à son arc et pourra se targuer de se connecter à la majorité des SGBDRS du marché.

Je tiens également à noter que cet article ne se focalise que sur la partie vectorielle. En effet, à l'instar de PostGIS à partir de la version 2.0, Oracle est également capable de stocker des rasters.

En attendant, je souhaite bon courage à l'équipe de développement de QGis pour nous mener dans la bonne direction quant aux performances améliorables de Qgis et je salue déjà le travail sérieux qui a été mené…