Le portail francophone de la géomatique

Salut,

Il serait plus facile je pense d'écrire une fonction pl/psql pour arriver à ce que vous voulez faire.
Cela dit, voilà une idée de requête qui pourrait peut être fonctionner :

WITH RECURSIVE r(id, geom, attr) AS
(
    SELECT --partie de base de la requête recursive : on récupére tous les polygones sans attributs ayant un voisin avec attribut
        a.id,
        a.geom
        b.attr
    FROM 
        poly a 
    JOIN 
        poly b
    on  a.attr is null and b.attr is not null
        and st_intersects(a.geom, b.geom)
    UNION ALL
    SELECT --partie recursive : on récupére les enregistrements sans attributs voisins des enregistrements produits par le cas de base
        a.id,
        a.geom,
        r.attr
    FROM
        poly a
    RIGHT JOIN r
        ON a.id <> r.id --on veut éviter de reprendre les polygones déja traités
        and a.attr is null and r.attr is not null 
        and st_intersects(a.geom, b.geom)
),
x as (
    select * from r LIMIT 1000 -- on mets généralement une limit au resultat produit par une requête recursive
                                                 -- ca permet de tester qu'elle fonctionne et d'éviter qu'elle ne tourne indéfiniment
                                                 -- si on a mal ecrit la requête
)


UPDATE poly a
SET attr = x.attr
FROM  (select id,max(attr) from x GROUP BY id) x
WHERE 
    a.id = x.id
;

Les CTE récursives se composent généralement d'une partie de base (dans le code la requête au dessus du UNION ALL) et d'une partie recursive (sous le UNION ALL). La CTE s'arrête une fois qu'elle ne produit plus de nouveaux resultat (une nouvelle itération de la partie recursive ne renvoie pas d'enregistrement).

Une difficulté de votre demande vient du fait qu'il peut potentiellement y avoir plusieurs candidats pour chaque polygone sans attribut. Les GROUP BY et LATERAL JOIN n'étant pas autorisés dans les CTE recursives, la solution que je propose utilise la CTE pour générer toutes les combinaisons possibles et la sous requête de la clause FROM de la commande UPDATE selectionne la valeur à attribuer (ici j'ai choisi la fonction max mais on peut en choisir une autre ou faire cela de façon non déterministe).

Je ne suis pas sûr des bonnes performances de la requête, si elle fonctione.

Dernière modification par tumasgiu (Hier 12:05)

Bonjour,

Si les polygones à corriger intersectent des voisins dont ils doivent prendre une valeur, pourquoi ne pas utiliser st_clusterIntersecting ?
Vous aurez des groupes de polygones qui se touchent et dans chaque groupe, les polygones avec attribut null et des voisins avec l'attribut que voulez prendre.

(je sens bien que je n'ai pas tout compris à votre pb )

Nicolas

Vous auriez un exemple visuel ?

Nicolas

Tout à fait. Je pense que l'approche avec st_clusterintersectings est valide. Après cela depend peut être de la façon dont on veut que les attributs se "diffusent" de voisin en voisin ? Il faudrait plus de précision sur le problème

En apparté je viens de voir que les fonctions de cluster ont maintenant une version utilisable dans les window function, .

Dernière modification par tumasgiu (Hier 13:34)

Bonjour,
Je n'ai pas encore testé la solution avec WITH RECURSIVE. Je vais m'y mettre ce matin !

Pour mieux expliquer mon problème de base : A l'intérieur d'un même ensemble de polygones jointifs, je peux avoir des grands polygones qui ont des valeurs d'attributs différents et une multitude de petits polygones avec des valeurs nulles. Certains de ces petits polygones sont entourés de petits polygones  avec des valeurs nulles aussi. Du coup, je doute qu'on puisse utiliser st_clusterintersectings (que je n'ai jamais utilisé par ailleurs ! ).
Ce que je souhaitais c'est de boucler pour que les valeurs nulles soient, petit à petit, remplacées par une des valeurs des voisins.

Si je veux aller au bout de ma démarche, je dois exécuter cette "diffusion récursive" à plusieurs endroits dans mon script. Peut-être qu'une fonction plpsql serait plus appropriées ?  Mais là aussi , il faut que je me repenche sur ce type de prog, car ca fait quelques années que je n'ai pas pratiqué ! Si vous avez quelque chose de simple pour démarrer ?

Merci d'avance,

Un sample, un sample !

Nico

Helas c'était à craindre.

Vous pouvez tout à fait augmenter le nombe d'enregistrement à retourner. Le limit n'est la que pour vous prémunir d'une possible requête qui ne terminerait jamais et saturerait votre serveur.


Votre code est bien, mais je pense que vous pouvez le simplifier comme suit (non testé) :

CREATE OR REPLACE function  diffusion() returns text
LANGUAGE plpgsql 
AS $$ 
DECLARE 
    updated := 1
    NBPOLYUPDATED INT := 0;
BEGIN 

  WHILE updated > 0 LOOP 

    RAISE NOTICE 'i: %', i;

    EXECUTE 'update poly a
    set attr = b.attr
    from poly b 
    where a.attr is null and b.attr is not null and st_intersects(a.geom,b.geom)'
    ;

    GET DIAGNOSTICS updated := ROW_COUNT ;
    --https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

    NBPOLYUPDATED  := updated + NBPOLYUPDATED ;

  END LOOP;

   return 'NB TOTAL UPDATED: '|| NBPOLYUPDATED   ;

END; 
$$ 
;

select diffusion();

Il n'y a plus qu'a tester la version de Nicolas

Dernière modification par tumasgiu (Aujourd'hui 14:20)