QGis-SpatiaLite-SQL: Concaténation de plusieurs lignes de texte ayant le même identifiant

Un nouveau post pour répondre à la question d’une collègue:

« j’ai plusieurs lignes avec le même numéro d’us.

j’ai réussi à utiliser l’outil résumé statistique (dans ArcGis, ndlr).

pour additionner les valeur de NR (nombre de restes) et les poids.

mais je ne trouve pas la solution pour remplir le champ description avec toutes les valeurs des champs ayant le même numéro d’us et en séparant par une virgule. »

Objectif: Faire le récapitulatif d’un champ et faire une concaténation des lignes descriptives (texte) 

Bon je ne suis surement pas assez clair donc partons d’un shapefile d’exemple (puisqu’il s’agit bien d’un shapefile au départ…) dont la table attributaire ressemblerait a ceci:

Et dont on voudrait au final qu’elle ressemble plutôt à ça:

Après d’infructueuses recherches sur ArcGis, Qgis nous nous sommes orienté vers le SQL, mais là, pareil Access, LibreOffice Base, etc… n’acceptent pas la fonction SQL : GROUP_CONCAT qui est pourtant LA solution toute trouvée pour résoudre ce problème !

Nous nous servirons donc de…

1) SpatiaLite

1.1) Qu’est ce que SpatiaLite ?

C’est une extension de SQLite qui permet de gérer les géométries spatiales.

Et SQLite ?

C’est un moteur de base de données relationnelle, accessible par le langage SQL.

Mais certains l’expliquent beaucoup mieux que moi, ici par exemple…

Ouai, et alors…

 Eh bah il peut lire les shapefiles, les tableaux (csv et txt), accepte le SQL et permet d’exporter en dbf, csv et txt….cela ne vous suffit pas ?  il est compatible avec QGis !

Je vous passe les détails de l’installation et vous laisse pour l’instant (en attendant un potentiel tuto…peut être) vous instruire via ce tutoriel très bien fait: le SpatiaLite CookBook.

1.2) Installation et Démarrage:

Télécharger le fichier qui vous correspond ici à cette adresse : http://www.gaia-gis.it/spatialite-2.4.0-4/binaries.html pour moi c’est spatialite_gui-win-x86-1.4.0.zip

Extraire tout le dossier… chercher le fichier spatialite.gui (profitez-en pour créer un raccourci sur le bureau pour vos utilisations ultérieures) et lancez le (double-clic: c’est un exécutable, il ne nécessite pas d’installation !)

Avant toute chose il faut vous créer une Base de Données SpatiaLite: File > Creating a New (empty) SQLite DB

Nommer la base MaBase.sqlite et l’enregistrer dans un dossier de travail.

1.3) Importer le shapefile:

Rien de plus simple (je sais je me répète mais après une journée de recherche certaines choses paraissent simplissimes et bien faite 😉 ), il y a un icône dédié : 

Note: Pour importer des tables en CSV, TXT: icône  et pour importer du DBF, icône 

– Parcourir les dossiers pour trouver votre shapefile

– Indiquer le système de coordonnées du shapefile dans le champ SRID : ici 4326

(qui correspond au code EPSG : cf.QGis, clic droit sur le shapefile > Propriétés > Général > SCR)

– Choisir l’encodage UTF-8 unicode

– OK

Et là, oooh miracle la table attributaire de notre shapefile apparaît dans l’arborescence à gauche avec tout plein d’autres tables propres à SpatiaLite et sa gestion de l’espace !

Note: les noms de champs ainsi que ceux des enregistrements ne doivent pas contenir ni accents, ni espace, … comme d’hab

 

1.4) Ouvrir la table:

Clic droit sur la table MatosARCH > edit table rows

Et là, oooh miracle la table attributaire dans toute sa splendeur

… avec nos beaux champs (colonnes) et mêmes plus : un champ geometry qui a été créé pour caractériser les entités graphique du shapefile (des polygones en l’occurrence) !!

 

Passons alors aux choses sérieuses 😉 …

2) le SQL

C’est le langage qui permet de faire tout ou presque d’une base de données et même se que nous allons lui demander:

– Créer une nouvelle table Recap_ MAT

– Récapituler la colonne US

– et concaténer (« rassembler ») les enregistrements du champ MATERIEL qui correspondent à ses US

   dans un nouveau champ Description_MAT

2.1) La formule magique…

… est à taper dans la « fenêtre SQL » en haut à droite (1)

Rafraîchir la vue des table : dans la fenêtre de gauche > Clic droit > Refresh (2)

Et voilà le résultat...(3)

2.1) …l’explication de la formule magique:

Je crée une table « Recap_MAT » ainsi:

CREATE TABLE Recap_MAT AS

Je sélectionne la colonne US, je groupe par US et je concatène les enregistrements de la colonne MATERIEL séparés par une virgule et un espace…

SELECT US , GROUP_CONCAT(MATERIEL, « , «  )

…le tout dans une colonne que j’appelle DescriptionMAT

AS DescriptionMAT

depuis la table MatosARCH (notre table importée)

FROM MatosARCH

Je groupe par numéros d’US identiques

GROUP BY US

et je trie par numéro d’US

ORDER BY US

 3) La jointure:

3.1) avec QGis:

Il faut tout d’abord exporter notre table fraîchement créée: Clic droit sur la table Recap_MAT > Export as csv

nous l’appellons Recap_MAT.csv

J’ouvre QGis ainsi que mon shapefile de départ MatosARCH.shp et ma table Recap_MAT.csv

Clic droit sur le shapefile > Propriétés > Onglet jointure

Clic sur le bouton +

OK

 

Vérification en ouvrant la (nouvelle) table attributaire du shapefile MatosARCH.shp

Et pourquoi pas le faire directement en SQL ? …

3.2) avec SpatiaLite:

3.2.1) Requête SQL:

Retournons dans SpatiaLite et dans la « fenêtre SQL » :

CREATE TABLE JoinMAT AS

SELECT *

FROM MatosARCH, Recap_MAT

WHERE MatosARCH.US = Recap_MAT.US

En langage vernaculaire: je crée une table JoinMAT ainsi: je sélectionne (et affiche) toutes les colonnes des tables MatosARCH et Recap_MAT avec une jointure sur les colonnes US de la table MatosARCH et US de la table Recap_MAT

La nouvelle table JoinMAT s’affiche alors (après un clic droit > Refresh) dans l’arborescence des tables de la fenêtre de gauche.

 

3.2.2) « Transformer » la Table en Shapefile:

Vérifier qu’elle possède un champ Geometry

Peut-être avez vous remarqué que la table JoinMAT est précédé d’une icône « table »  contrairement à notre shapefile MatosARCH ?

Résolvons ce problème avec un clic droit sur le champ geometry (dans l’arborescence des champs) sous l’intitulé de la table JoinMAT > Recover Geometry column

Dans la boîte de dialogue qui s’ouvre, il faut:

– spécifier le Système de Coordonnées de la couche dans le champ

– définir la dimension : ici XY (2 dimensions)

– et enfin, le type de géométrie (ici celle d’origine = polygone)

et ça marche:                                

Pour l’afficher dans QGis utiliser le bouton 

Créer une nouvelle connexion > Bouton « Nouveau » > Parcourir pour récupére notre base MaBase.sqlite > OK

Puis Bouton « Connexion »

Choisir la couche JoinMAT > Bouton « Ajouter »

 

Oooh la belle couche qui s’affiche et aaah la belle table avec notre champ DescriptionMAT 😀

3.3) Bonus

On peut tout aussi bien calculer directement la somme des NR et la somme des poids (même si on mélange un peu ici les serviettes et les torchons) dans la même table, ainsi avec :

SELECT US, MATERIEL,SUM(NR), SUM(Poids), GROUP_CONCAT(MATERIEL, « , «  ) AS DescriptionMAT
FROM MatosARCH
GROUP BY US
ORDER BY US

Nous obtenons cette table:

4) Conclusion

Alors c’est pas la classe ça ? si vous avez osé vous lancer dans SpatiaLite je pense que vous vous rendrez vite compte de l’intérêt de la chose: grâce aux requêtes SQL nous pouvons faire des concaténations, des jointures, créer des tables (ou même des vues) et tout pleins de possibilités que le logiciel SIG ne peut pas faire dans la manipulation des données.

Mais on se rend compte aussi des limites: SpatiaLite ne nécessite pas une architecture client-serveur (« avantage » me direz-vous : pas besoin d’installer un serveur en local.. il s’exécute comme un logiciel quelconque), MAIS, revers de la médaille: il ne permet pas d’accès multi-utilisateur à la même base de données sur d’autres postes du réseau. En revanche, la base de données (avec toutes les tables et shapefiles) est facilement transmissible par mail par exemple !

Enfin, Spatialite peut être implémenter dans QGis via le Plug-In QSpatiaLite et permet la gestion de sa Base de Données directement dans le logiciel… et même d’afficher le résultat de ses requête en live !  to be continued…