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.
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:
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…
WordPress:
J'aime chargement…
Sur le même thème
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.
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!
Bonjour à tous,
attention un caractère tel que ° peut être très embêtant pour Qspatialite!
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
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 😉
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!
Je precide que je fais tout ca sous Spatialite Gui en dehors de Qgis.
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+
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 ?
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 !
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…
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… 😉
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 ! :))
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
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?
Bonjour,
j’aimerai bine mettre une seul valeurs pour toutes les valeur qui sont dans la meme colonne.
merci
Bonjour Hassani
Je ne comprends pas bien ta question ? Peux-tu la reformuler (en prenant par exemple l’exemple donner dans le billet de blog) ?
PS: évidemment en 2018 avec QGIS j’utiliserais les virtual layers pour faire la même chose plus simplement…