Transfert d'attributs avec POSTGIS (entre autres)
Article un peu technique sur le traitement des transferts d'attributs entre couches, dans une base postgresql/postgis, ainsi que sur ces déclencheurs qui nous facilitent la vie.
De temps en temps, il faut aussi que je pousse la technique un peu plus loin, sinon vous allez croire que l'on ne peut faire que du télécran avec un SIG. (Vu les références, on voit que je suis né dans les 70's....)
L'article qui suit demande donc de connaître à minima postgresql/postgis, savoir faire une requête SQL, etc... Vous voilà prévenu.
Je suis sous POSTGRESQL 11 / POSTGIS 2.5 , QGIS 3.10.4 et MX LINUX 19.
La problèmatique
Imaginons, presque purement fictif, que je veuille lorsque je dessine un polygone, récupérer automatiquement des informations contenues dans des points, lignes ou polygones qui font intersection. En gros, je veux lister, par exemple les identifiants des lignes noires dans les polygones vert (cf. image)
Plusieurs solutions existent, allons de la plus simple, à la plus complexe mais élégante (amha)
La pas-solution QGIS.
Vous me direz, il suffit de faire : "joindre les attributs par localisation" (dans vecteur-> outils de gestion de données) et hop, oui, mais non. Pourquoi ?
- C'est un traitement manuel à lancer,
- cela crée une nouvelle couche, qui faudra substituer à votre couche originale et originelle,
- et surtout, cela crée, avec l'option "correspondance multiple" dans type de jointure, sinon, c'est encore pire, cela ne garde que la première valeur rencontré. cela crée une ligne pour chaque attribut... pas glop ! Je vous mets la table pour mieux comprendre : 5 lignes croisées donc 5 lignes dans la table de la nouvelle couche.
Aussi adapté que du tang dans une réception de l'ambassadeur, réputée pour leur bon goût. Ce n'est pas ce que l'on veut
Le problème est souvent de stocker plusieurs informations dans une seule "case" ou "ligne".
La solution QGIS
La solution est de concaténer, d'aggréger les attributs dans un seul champs, on peut en faire ainsi une liste. Pour le faire, nous avons besoin de la calculatrice de champ est d'une formule du genre de celle-ci :
'{'||aggregate(layer:= 'lignes',aggregate:='concatenate',expression:=id_ligne,concatenator:=',',filter:=intersects($geometry, geometry(@parent)))||'}'
| '{'||aggregate( | <-- la { sert à démarrer le tableau, le || sont une concaténation, puis ensuite la formule |
| layer:= 'lignes', | <-- couche où aller chercher l'info |
| aggregate:='concatenate', | <--type aggregation, ici une concaténation |
| expression:=id_ligne, | <-- champs à aggréger |
| concatenator:=',', | <-- séparateur de liste |
| filter:=intersects($geometry, geometry(@parent)) | <-- et le filtre qui permet de ne choisir que les entités de couches dont les géométries s'intersectent. |
| )||'}' | <-- et on ferme le tout. |
Voilà, mettre à jour le champs désiré avec cette formule et voilà aussi puissant que 2.21 Gigowatts, 2.21 Gigowatts, nom de Zeus !
Je rappelle que ma couche de polygone est stockée en postgis, c'est pour cela que je stocke dans un array (avec les {}), mais pour un autre format (.shp...), on peut très bien stocker simplement une liste sous forme de texte. On enlève les '{'|| et ||'}' et on les sépare avec un ' ; ' par exemple.
donc là, ce n'est pas si mal :
- je travaille dans une même couche
- mais c'est un opération manuelle. A moins de mettre cette formule dans la "valeur par défaut" des "formulaires d'attributs", mais attention lourd à chaque création ou modification.
Solution requête SQL
Comme je vous l'ai dit, ma couche est sous postgis, et donc si je pousse le bouchon un peu trop loin, Maurice! On peut très bien faire une requête SQL. Ce qui revient presque au même que la solution QGIS.
Attention, il faut que le champs où vous listez les attributs soit de type array (cf. docs.postgresql.fr/11/arrays.html ), sinon cela fait n'importe nawak ! Moi il est text[].
WITH RECURSIVE idselection AS ( |
With définit une fonction récursive, c'est une table temporaire ou chaque ligne va contenir les info demandées selection des identifiants de la couche de polygone et aggregation des identifiants issues des lignes dans un tableau Bien sûr, il faut joindre les 2 tables (CROSS JOIN joint toutes les identités) WHERE pour définir la condition d'intersection géométriques on groupe par identifiant pour avoir une seule ligne par résultat. Ensuite UPDATE met à jour le champs de stockage de la liste stockée dans la table temporaire, quand les identifiants sont identiques |
Bon, c'est vrai, c'est moins simple que de rembobiner une K7 avec son BIC. Mais cela fonctionne.
Comme les autres, le déclenchement est encore manuel, mais pour des interventions ponctuelles, pourquoi pas. L'interêt, c'est que l'on peut l'enregistrer sur une disquette3"5 sous format sql et la rapidité par rapport à QGIS car traité coté serveur. Et puis j'adore l'odeur du SQL au petit matin !
La solution par FUNCTION et TRIGGER PG
Là, accrochez-vous à vos minitels, parce-que voici la méthode avec déclencheur (TRIGGER). Si vous ne savez pas ce qu'est un déclencheur, bin tant pis, non je plaisante, pour faire simple, c'est une opération qui s'éxécute chaque fois que vous créez, modifiez, supprimez une ligne, c'est vous qui choississez quoi et quand.
En postgresql, c'est séparer en deux, le TRIGGER en lui-même qui déclenche une fonction (FUNCTION) prédéfinie. Voici donc la fonction en question
CREATE OR REPLACE FUNCTION asst.transf_id_lignes() |
Création de la fonction avec son petit nom. cette fonction retourne vers un déclencheur et elle est en langage plpgsql (language de sql augmenté de postgresql) Dans la fonction, la nouvelle list est un tableau aggrégé des identifiants qui viennent de la couche lignes Quand les geomètries s'intersectent, bien sûr. et retourne la liste |
Le TRIGGER en lui même n'est pas extraordinaire :
CREATE TRIGGER transidcana BEFORE |
Mon trigger s'appele transidcana, il se déclenche à l'insertion et à la modification dans la table polygone, pour toutes les lignes, et il execute la fonction définit avant. |
Ce qui donne en image dans la base :
Et dans QGIS, qui reconnaît que c'est un champs contenant un tableau, on peut abonder le tableau avec l'ajout de valeur (le +), remarquez les lignes qui liste les élèments :
même dans les formulaires
L'avantage majeur est que cela fonctionne quelque soit l'utilisateur, la plate-forme d'affichage, cela évites les erreurs et c'est aussi rapide que Flash,ahaaaa,Savior of the universe !
Fin
Voilà, vous êtes devenu aussi balèze que Conan le barbare, un grand merci aux personnes sur STACK OVERFLOW, qui m'aide toujours quand je suis en galère, et si vous avez d'autres astuces, optimisation, question, pensez aux commentaires, je garde AOL allumé au cas où.
Allez, zou, il est temps de vous dire salut les p'tits clous.