Pasq.fr

Parce-qu'il y a forcément du sens à tout ce bordel !

Transfert d'attributs avec POSTGIS (entre autres)

Rédigé par Alain Aucun commentaire
autolargue transfert goldorak

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)

illustration problème

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.

table joindre par attributs

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)))||'}'

formule expliquée
'{'||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[].

requête sql expliquée
WITH RECURSIVE idselection AS (
SELECT
    te.id AS idi,
    ARRAY_AGG( c.id_ligne::TEXT) AS aa
FROM
    public.polygone AS te
CROSS JOIN public."lignes" AS c
WHERE
    ST_INTERSECTS(c.the_geom,te.geom)
GROUP BY
    te.id )
UPDATE public.polygone AS tp SET list_des_id = idselection.aa FROM idselection WHERE idselection.idi = tp.id;

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

FUNCTION expliquée
CREATE OR REPLACE FUNCTION asst.transf_id_lignes()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    NEW.list_des_id = (SELECT ARRAY_AGG(c.id_ligne)
    FROM
        public."lignes" AS c
    WHERE  st_intersects(c.the_geom,NEW.geom));
    RETURN NEW;
END;
$function$
;

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 :

TRIGGER expliqué
CREATE TRIGGER transidcana BEFORE
INSERT OR UPDATE ON
    public.polygone FOR EACH ROW EXECUTE PROCEDURE asst.transf_id_lignes();
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 :

lignes dans base de données

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.


Écrire un commentaire

Quelle est le premier caractère du mot 6cmgbqva ?