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:Table_AVANT
Et dont on voudrait au final qu’elle ressemble plutôt à ça:
Table-finale
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 !)
Icone_SpatiaLite
Avant toute chose il faut vous créer une Base de Données SpatiaLite: File > Creating a New (empty) SQLite DB
Creer une BDD SQLite
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é : Import_Shapefile
Note: Pour importer des tables en CSV, TXT: icône icone import csv txt et pour importer du DBF, icône icone import dbf
– 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
Boite de Dialogue : Import_Shapefile
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
Affichage-de-la-Table
… 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)
SQL
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 +
jointure
OK
 
Vérification en ouvrant la (nouvelle) table attributaire du shapefile MatosARCH.shp
table jointe
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 » icone_table contrairement à notre shapefile icone_shapeMatosARCH ?
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
Recover geometry
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)
Recover-Geometry-Column
et ça marche:                                recover OK
Pour l’afficher dans QGis utiliser le bouton Icone_QGis_Spatialite
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:

Table-finale

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…

Publicités
Tagué , , , , , ,

15 réflexions sur “QGis-SpatiaLite-SQL: Concaténation de plusieurs lignes de texte ayant le même identifiant

  1. tiphaine dit :

    Super ce tutoriel, merci beaucoup!

    Malheureusement, mon problème de récapitulation/concaténation est sur un fichier xls (transformé en dbf), et c’est seulement une fois cette opération faite que je voudrais faire une jointure pour avoir un shapefile. (j’ai un shapefile avec uniquement des formes et les numéro d’US associés, si je fais la jointure avant, je perds les infos au delà du premier enregistrement pour une même US).
    Je te tiens au courant sur mes avancées.

    • archeomatic dit :

      Salut Tiphaine,
      Avec SpatiaLite tu peux aussi bien importer une table au format DBF (mais aussi CSV ou TXT), le seul truc est de bien faire gaffe aux accents et caractères bizarre! Sinon il y a surement moyen de traiter ses données avec Excel, à voir….
      Au plaisr de te revoir par ici!

  2. tiphaine37 dit :

    Bonjour à tous,

    attention un caractère tel que ° peut être très embêtant pour Qspatialite!

  3. Cyrille dit :

    Bonjour, est ce possible de créer une table spatiale directement en SQL ? je n’y arrive pas. As tu une idée de comment faire ? Passer par une vue ?

    Exemple, je pars de la table des communes pour arriver au regions avec un group by

    merci

    • archeomatic dit :

      Salut Cyrille,
      En fait, tu voudrais faire a peu près la même chose que dans le tutoriel mais à partir d’une table spatiale… c’est ça ?
      passer avec un group by des communes au département et que spatialite fasse « automatiquement » une agrégation des polygones de communes pour en faire des polygones de département ??? la répose ne serait elle pas par ici ? => http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook-fr/html/union.html
      A noter que l’auteur de la traduction française du cookbook de spatialite est l’auteur de QSpatialite !! (l’extension spatialite pour Qgis)
      j’espère avoir compris ta question et t’avoir bien orienté… après à voir si cela fonctionne avec QSpatialite et/ou spatialite… donnes-moi des nouvelles…
      Sylvain
      PS: désolé pour cette réponse aussi tardive 😉

  4. Cyrille dit :

    Effectivement, cela semble bien être ça :

    Je créé la table départements à partir de la table commune avec ce select :

    CREATE TABLE DPTS AS
    SELECT code_dept, nom_dept, ST_Union(geometry) AS geometry
    FROM COMMUNE
    GROUP BY code_dept;

    puis :

    SELECT RecoverGeometryColumn(‘DPTS’, ‘geometry’, 2154, ‘MULTIPOLYGON’, ‘XY’);

    Ou 2154 correspond à Lambert 93.

    Ce dernier select correspondant à toute ta manip « Transformer » la Table en Shapefile: »

    Derniere question faut il mieux créer une vue ou une table ?

    Merci!

  5. Cyrille dit :

    Je precide que je fais tout ca sous Spatialite Gui en dehors de Qgis.

  6. Samy dit :

    Merci pour cette présentation efficace !
    Je réagis juste par rapport à ce que tu dis ici : « pas d’accès multi-utilisateur à la même base de données sur d’autres postes du réseau. »

    Je viens tout juste de découvrir Qspatialite. J’ai créé une base .sqlite que j’ai placé sur le serveur de mon entreprise.

    J’ai testé la lecture et l’écriture simultanée sur deux ordinateurs connectés au réseau et ça marche dans les deux sens.
    D’ailleurs c’est bizarre, parce que je croyais que la lecture était partagée mais que l’écriture était exclusive. Sais-tu comment gérer ça ?

    A+

  7. ash` dit :

    Bonjour;

    Merci pour le billet qui ouvre bien des perspectives…
    Je cherche à créer un champ pour un fichier shape qui contiendrait la concaténation des valeurs d’un champ d’un autre shape file en fonction selon que ces entités soient contenu dans les entités de la couche cible ou non (une jointure spatiale sur champ texte pour faire simple).
    Savez vous SpatialeLite sait faire ca ?

    • archeomatic dit :

      Bonjour ash,
      Je ne suis pas sur de bien comprendre ce que tu cherches à faire..
      En tout cas avec spatialite on peut évidemment faire des concaténation (avec || ) avec des conditions et on peut faire des jointures spatiales aussi (je n’ai personnellement pas encore trop testé les opérateurs spatiaux)..
      Quoi qu’il arrive je ne peux que trop te conseiller de te référer au cookbook de spatialite : http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook-fr/index.html
      Au plaisir de te relire !

      • ash` dit :

        Navré de ne pas avoir été plus clair.
        Je cherche effectivement à faire une jointure spatiale portant sur un champ texte.
        Donc ajouter aux entités d’une couche A la concaténation des valeurs d’un champ pour toutes les entités d’une couche B qui intersecterait les entités de A.
        Est ce plus clair ? 🙂

        Je creuse le cookbook, mais le SQL et moi…

      • archeomatic dit :

        Salut Ash !
        et pourquoi ne pas tout simplement faire une jointure par localisation dans Qgis (Menu Vecteur > Outils de gestion de données > Joindre les attributs par localisation) en choisissant l’option « Prendre les attributs de la première entitée localisée » PUIS faire ta concaténation avec la calculatrice de champs ?
        Pas de SQL… ou presque !!
        A+
        PS: surement possible en tout SQL avec spatialite aussi… 😉

  8. ash` dit :

    C’est à mon tour de ne pas comprendre ce que tu veux dire.
    Si je vais la jointure par localisation en prenant les attributs de la première entité localisée, je vais justement perdre les autres valeurs que je veux concaténé, non ? 🙂
    Je planche sur spatialite, mais j’ai du retard en SQL a rattraper ! :))

  9. ash` dit :

    Hello, je laisse le lien du post ForumSIG où j’ai résolu mon problème 🙂
    Cela dit, je suis toujours curieux de comprendre ou tu voulais en venir avec ta solution « NOSQL » 🙂
    http://www.forumsig.org/showthread.php/38358-Jointure-spatiale-avec-champ-texte

  10. faudric dit :

    bon, moi, je découvre spatialelite et j’ai suivi vos conseil: pas de problème… sauf que çà ne m’a pas tout concaténer. en effet, j’obtiens des champs vides dans ma 2ème table alors que mes cellules ne sont pas vide dans la première. y a t’il une limite de lignes?

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :