[QGIS 2.14 – SQL – QSpatialite (ou pas)] Gérer les relations de 1 à n (1 à plusieurs)

Note du 1er mars 2016: il est depuis aujourd’hui avec la version QGIS 2.14 possible de faire la requête directement dans QGIS sans passer par spatialite grâce à la création d’un virtual layer… voir en fin de billet

Objectif : Rechercher et afficher les Faits archéologiques (trous de poteaux) contenant un négatif de poteau. Pour cela nous disposons au départ d’un shapefile des Faits et d’un inventaire des US (Unité Stratigraphique).

L’intérêt d’un SIG est notamment de pouvoir gérer des relations entre des éléments spatialisés –ici les Faits archéologiques- et les données qui s’y rapportent issues de l’enregistrement de terrain ou de l’étude en post-fouille.

1.Introduction

 La relation la plus commune est la jointure ou relation  de 1 à 1 : un Fait pourra être relié aux données qui le concernent dans un tableau d’inventaire par exemple. Il faut dans ce cas que l’on puisse déterminer pour chaque Fait –avec un numéro unique- une ligne correspondante dans le tableau d’inventaire contenant une colonne avec le même identifiant unique –le numéro de Fait.

Un exemple en image :

Un exemple de relation de 1 à 1 ou « jointure spatiale »

On a effectué ici une relation de 1 à 1 entre le shapefile Faits et le tableau d’inventaire des Faits (format .xls ou .dbf). Cette relation de 1 à 1,  appelée jointure attributaire a été faite sur l’identifiant unique Num_Fait c’est-à-dire qu’à chaque numéro de fait du shapefile correspond un et un seul numéro de Fait dans l’inventaire. On peu ainsi faire des requêtes sur la datation des Faits ou leur profondeur par exemple et les afficher dans QGis.

Note : la jointure attributaire dans QGis se fait grâce à clic droit sur la couche d’origine => Propriétés => Onglet Jointure

La relation de 1 à n n’est par contre pas disponible nativement dans les versions officielles de QGis (ici la version 1.7.4 ou 1.8) alors qu’elle l’est dans ArcGis. L’intérêt de ce type de relation est pourtant important, notamment dans notre discipline. En effet, si le Fait archéologique (ou structure appelez la comme vous voulez selon la région ou la base de données avec laquelle vous travaillez 😉 ) est couramment utilisé comme entité de base dans un SIG archéologique à l’échelle de l’opération, les inventaires utilisent souvent une unité plus petite, généralement l’US. Il est donc important de pouvoir interroger ses inventaires d’US ou de céramique par exemple et de représenter le résultat de nos requêtes graphiquement.

Un exemple de relation 1 à n en image :

Un exemple de relation de 1 à plusieurs ou de 1 à n.

Comme vous le voyez on cherche ici à relier les Unité Stratigraphiques aux Faits, or il y a plusieurs US pour 1 Fait => C’est donc une relation Faits – US de 1 à plusieures.

Note : Il faut bien entendu que dans la table Faits il y est un numéro d’identifiant unique –le numéro de Fait- et qu’il existe un champs avec le même identifiant (non unique) dans la table des US.

Rappelons l’objectif de ce tutoriel à la lumière de ce que nous venons de voir : nous voulons rechercher et afficher les Faits qui ont une US de type « négatif de poteau ».

Nous allons pour cela utiliser l’extension QSpatiaLite qui est la version de SpatiaLite (dont nous nous sommes déjà servi pour résoudre un problème dans un précédent  tutoriel) intégrable à QGis …et peut être complètement intégré dans les futures versions de notre logiciel préféré !

Note : L’extension QSpatiaLite version 5.0.3 s’installe dans QGis 1.7.4 grâce à l’installateur d’extension via le dépôt « QGIS Contributed Repository ». Dans QGis 1.8 il n’existe plus qu’un seul dépôt : officiel dans lequel on trouve la version 6.0.4 de QSpatiaLite

Après installation de l’extension un icône apparait dans la barre d’outils « Extensions » de QGis

Nous disposons au départ :

–          d’un shapefile de polygones pour les Faits archéologique avec un identifiant unique : le numéro de Fait => Num_Fait

–          d’une table Excel (ou dbf)  qui correspond à l’inventaire des US avec comme identifiant unique le numéro d’US (Num_US), le numéro du Fait auxquelles elles appartiennent (Num_Fait) et le type d’US (Type_US) qui est de type texte et qui contient les informations qui nous intéressent, c’est-à-dire si c’est une US de comblement, de creusement ou un négatif de poteau.

Ce shapefile et cette table ont été ajoutées dans QGis via le Menu « Couche » => « ajouter une nouvelle couche vecteur »

 

2.Importer des couches QGis dans QSpatiaLite

 Nous allons donc passer par QSpatiaLite afin de faire une requête SQL entre notre shapefile de Faits et notre table d’US.

Clic sur l’icône de QSpatiaLite => une fenêtre s’ouvre et nous demande de créer une nouvelle base de données avec pour nom par défaut myDB.sqlite

Ceci étant fait, nous allons importer notre shapefile de Faits et notre Table_US via le bouton dédié, ici l’icône avec le logo de QGis . Qui permet d’importer les éléments présents dans notre projet QGis, en l’occurrence notre shapefile et notre table…

Puis, dans la fenêtre qui s’ouvre => Clic sur le bouton « Sélectionner tout » => OK

Note : ATTENTION cette manipulation ne marche pas chez moi avec QGis 1.8, je n’arrive pas à importer le shapefile alors que pour la table Excel je n’ai pas de problème ??? pour contourner le problème j’ai importé le shapefile dans ma base de données sqlite (myDB.sqlite) via la version gui de SpatiaLite (c’est-à-dire en dehors de QGis) puis j’ai ré ouvert QSpatiaLite dans QGis et… miracle mon shapefile a été importé !

En revanche dans QGis 1.7.4 pas de problème pour importer le shapefile mais pas de possibilité d’importer un tableau Excel => je l’ai donc enregistrer sous … dbf !!!

A vous de voir et de tester… mais j’imagine que ces problèmes vont être réglés petit à petit !!

 Pour vérifier l’intégrité de nos données il suffit de faire un clic droit => Show All pour voir apparaître la table attributaire du shapefile de Fait et la table d’inventaire des US.

Résultat de l’import dans QSpatiaLite

Maintenant que tout est prêt il ne nous reste plus qu’à faire notre requête…

3. Faire une Requête SQL (avec sous-requête)

Nous allons donc maintenant faire une requête SQL dans les deux tables : la table attributaire des Faits et la table des US.

Il suffit de taper notre requête dans l’onglet SQL (et non pas via le bouton SQL) :

select * from « Faits »  where  « Faits ».’Num_Fait’ in

(select   « Table_US ».’Num_Fait’    from  « Table_US » where   « Table_US ».’Type_US’ like ‘négatif%’);

Explication de texte:

=> Afficher tous les champs (select *) de la table Faits (from « Faits ») pour lesquels le numéro de Faits (where  « Faits ».’Num_Fait’)

=> correspond à la sous-requête (in)

=> je sélectionne les numéros de Faits dans la Table_US (select   « Table_US ».’Num_Fait’    from  « Table_US ») pour lesquels le type d’US commence par le terme « négatif » (where   « Table_US ».’Type_US’ like ‘négatif%’)

Note : le signe % remplace un nombre indéfini de caractères

Dans le menu déroulant Option, il faut choisir « Load in QGis as Spatial Layer », c’est le seul qui semble fonctionner pour afficher ma nouvelle couche dans QGis…

Puis clic sur le bouton « Run SQL »

Et voilà le résultat…

Tous les trous de poteaux ayant une US identifiée comme étant un « négatif de poteaux » sont affichées dans QGis, sous la forme d’une couche spatiale appellée par défaut « SqlResult »… nous pouvons la sauvegarder sous forme d’un shapefile (clic droit => Sauvegarder sous…format ESRI Shapefile)

 C’est pas beau ça ?!

 

4.Ki n’en veux encore ?

 Allez, je sens bien qu’au fond de la classe certains se sont déjà endormis alors que d’autres en veulent encore….

Imaginons maintenant que nous avons un inventaire par US de la présence de matériel Lithique sous la forme d’un tableau Excel dont voici un extrait :

Et bien nous pouvons simplement, par l’utilisation de deux sous-requêtes emboitées, demander à afficher les Faits qui ont une US dans laquelle le champs « Lithique » est renseigné par le mot « OUI »

Comme ceci :

Et ça marche !!!

5. Conclusion (OLD)

 Nous venons de voir ensemble qu’il était possible de « gérer » les relations de 1 à n (et même plusieurs) dans QGis, grâce à une base de données spatialisées, en l’occurrence SpatiaLite ou plus exactement le plug-in QSpatiaLite développé par Romain Rivière.

Il y a encore des bugs (ou des subtilités que je n’ai pas tout à fait saisies…) mais avec un peu d’effort la récompense est là !!!

En effet, il faut parfois jongler entre des versions de QGis : la 1.7.4 semble stable mais n’accepte que les tableaux dbf donc pas d’accents alors que QGis 1.8 les accepte mais bugs sur plusieurs manip : import des shapefiles, export des requêtes autrement que par l’option « Load in QGis as a Spatial layer,…)

Le passage par la version gui de spatialite (version windows 1.4.0) pour charger des shapefiles ou tester des requêtes SQL permet de résoudre la plupart des problèmes rencontrés.

Je vous invite bien sûr à regarder le tutoriel complet en français sur Spatialite ICI (le logiciel originel adapté pour QGis en changeant de nom pour QSpatialite) de Romain Rivière et quelques exemples de géotraitements possibles ICI

Je tiens à remercier vivement mon collègue Mathias pour son aide à la formulation de la première requête SQL, deux cerveaux valent toujours mieux qu’un seul !

6. Conclusion (NEW)

Comme je vous le disait au tout début de ce billet il est désormais possible (avec QGIS 2.14) de faire la requête SQL directement dans QGIS sans passer par spatialite grâce à la création d’un virtual layer… 

Pour cela rien de plus simple:

• Ajouter un virtual layer en cliquant sur l’icône dédié
• Dans la boîte de dialogue qui s’ouvre tapez la requête SQL

• Et c’est tout ce n’est pas du bonheur ca ?
• Un petit bémol cependant: quand on modifie des attributs dans la table fille (Table US dans l’exemple) ces changements ne semblent pas pris en compte dans le virtual layer qui ne semble donc pas dynamique à ce point 😉

7. Conclusion (NEW NEW)

Il est possible de faire la même chose de façon plus facile encore avec la clause JOIN

SELECT  DISTINCT *, Faits. »geometry »

FROM Faits

JOIN Table_US

ON « Num_Fait » = « NUM_FAIT »

WHERE « TYPE_US » LIKE ‘negatif%’

Dites-moi si ce tutoriel vous a été d’une quelconque utilité… en tout cas je sais, moi, ou le trouver pour la prochaine attaque anti-QGis sur la gestion des relations 1 à n !! 😉