Le portail francophone de la géomatique

Bonjour,

L'union ou union all est obligatoire dans une CTE recursive, c'est comme ca qu'a chaque itération, le résultat précédent peut etre ajouté au résultat courant.

Plusieurs erreurs dans votre code:
• la cte déclare 3 champs alors que votre select en liste 5: les champs déclarés dans la cte doivent correspondre aux champs des select de la CTE (vous pouvez omettre la déclaration des champs dans la cte aussi)
• il manque une virgule avant le dernier champ du premier select (t.pollu_19).
• il manque une condition de stop je pense dans la partie UNION de la CTE: sans cela, la recursion ne s'arrete jamais.

La logique de ce type de CTE n'est pas facile a comprendre je trouve: essayez sur un exemple simple (peu de lignes dans la table de départ) pour vous familiariser avec ce concept de récursion. (la doc donne qq exemples, avec notamment la gestion des conditions de stop)

Nicolas

DROP TABLE turbi.flowAdd_v1;
CREATE TABLE turbi.flowAdd_v1 AS
WITH RECURSIVE additionflow
    AS(
        SELECT
        tu.pk,
        tu.pollu_19,
        tu.id_bdcarth,
        tu.id_nd_ini,
        tu.id_nd_fin
        from turbi."input" tu
        LEFT JOIN turbi."input" t2
    ON tu.id_nd_ini = t2.id_nd_fin
    WHERE t2.id_nd_ini IS NULL
    UNION
        SELECT
        tu.pk,
        tu.pollu_19 ,
        tu.id_bdcarth,
        tu.id_nd_ini,
        tu.id_nd_fin
        FROM additionflow
        JOIN turbi."input" tu
        ON additionflow.id_nd_fin =tu.id_nd_ini
    )
    SELECT f.pk, f.id_bdcarth, f.pollu_19 FROM
    (SELECT  pk, id_bdcarth, SUM(pollu_19) pollu_19 FROM additionflow  GROUP BY id_bdcarth,pk) f
    GROUP BY pollu_19,f.pk,f.id_bdcarth ORDER BY pollu_19 ;

select * from turbi.flowAdd_v1;

Bonjour,

Peut être que votre cas d'utilisation est plus simple que celui du fil dont est tiré le code que vous avez essayé d'adapter.

Est ce que le genre de cas de figure suivant existe dans votre réseau ?

Code:

 
   --C--
  /     \
 A---B---D

Je dispose de points comme indication de la pollution. J'effectue un ST_shortestLine pour relier mes points à mon réseau hydro (BDTopo) suivie d'une jointure pour affecter les champs du points vers mon réseau hydro. La question que je cherche à résoudre c'est comment transférer et faire la somme de ses polluants vers l'aval ?

La requête proposée ne fonctionne qui si vous choississez vos noeuds de depart, par exemple tous ceux qui n'ont pas de parents.

Bonjour,

Vous avez un exemple de vos données et du résultat attendu ?

Nicolas

Bonjour,

Dans votre cas, de ce que je comprends, il vous faut identifier les enfants de chaque tronçon, puis faire la somme des valeurs portées par ces enfants pour avoir un cumul des valeurs amont pour chaque tronçon du graphe.

Je vous conseille de travailler avec des linestring pour votre réseau, et non pas des multilinestrings avec 1 élément.

Je vois également dans le dataset qu'il y a des doublons géométriques dans votre réseau: c'est pas pratique en terme de traitement: vous pouvez soit virer les doublons et ne garder qu'un segment (a vous de choisir lequel), soit garder un seul segment géométrique et faire la somme des valeurs des segments identiques.

Enfin, dans le dataset fourni, quasiment toutes les valeurs v19 sont à 0, ce qui ne permet pas de calculer des valeurs en aval: dans mon exemple, je change v19 pour contenir la longueur du segment concerné comme valeur.

Le WITH RECURSIVE est pratique pour trouver les enfants de chaque tronçon. Mais à chaque itération, s'il y a plusieurs segments connectés au segment courant, on cumule trop d'info et la somme des valeurs est fausse.

Vous pouvez alors utiliser la récursion pour trouver les enfants, puis ensuite "nettoyer" le résultat pour virer les doublons et bien avoir, pour chaque segment, la liste des enfants sans doublons. Vous pouvez ensuite faire la somme des valeurs des enfants + segment courant.

En SQL:

"'nettoyage" et conversion en LINESTRING simples

Code:

alter table data_subset rename column wkb_geometry to geom;
alter table data_subset alter column geom type geometry(LINESTRING, 2972) using st_geometryn(geom, 1);

On vire les doublons géometriques en ne gardant qu'une entrée: ici, vous pouvez aussi cumuler les valeurs pour les segments superposés, au lieu de ne prendre qu'une valeur de segment, au pif.
Avec array_agg, on peut cumuler les id des segments superposés et virer le premier element du tableau: restent alors les id des segments qu'on veut virer;

Code:

with tmp as (
    select unnest((array_agg(ogc_fid))[2:]) as ogc_fid
    from data_subset
    group by id_bdcarth
) delete from data_subset d
using tmp t
where d.ogc_fid = t.ogc_fid;

pas de valeurs pertinentes dans v19: on maj avec une valeur fictive: la longueur de chaque segment

Code:

alter table data_subset alter column v19 type float;
update data_subset set v19 = round(st_length(geom)::numeric, 1)::float;

Pour trouver les segments de départ, on cherche les segments dont le startpoint n'est pas connecté à un autre segment => un seul point dans le group start/end point:

Code:

select (array_agg(ogc_fid))[1] as ogc_fid,
       1::int as iter,
       (array_agg(v19))[1] as v19,
       (array_agg(st_endpoint(geom)))[1] as geom
from data_subset
group by unnest(ARRAY[st_startpoint(geom), st_endpoint(geom)])
having count(*) = 1;

pour ne pas cumuler les valeurs du segments courant, s'il y a plusieurs enfants, on peut extraire les enfants de chaque segments:
et calculer les ids uniques des enfants pour chaque segment:
ca permettra de faire la somme des valeurs qu'on veut:
Comme attributs, on veut surtout la valeur du segment courant, la liste des ogc_fid de ses enfants, et le endpoint du segment courant qui permet de trouver le segment connecté.

Ici je fais la recherche du voisin connecté avec postgis: si le réseau est orienté, le endpoint d'un enfant est connecté au startpoint de son parent.
Une fois la recursion faite, il suffit de fabriquer le tableau des identifiants des enfants (le tableau complet calculé dans la récursion, moins le premier élément qui est l'id du segment courant (donc pas un enfant)

On peut alors construire le tableau des id uniques des enfants pour chaque segment.
Une jointure finale permet de calculer la somme des valeurs des enfants d'un segment et d'ajouter la valeur du segment courant (ou pas, suivant votre contexte)

Code:

with recursive toto as (
    select (array_agg(ogc_fid))[1] as ogc_fid,
           (array_agg(v19))[1] as v19,
           1::int as iter,
           array_agg(ogc_fid) as fids,
           (array_agg(st_endpoint(geom)))[1] as geom
    from data_subset
    group by unnest(ARRAY[st_startpoint(geom), st_endpoint(geom)])
    having count(*) = 1

    UNION ALL

    select d.ogc_fid,
           d.v19,
           iter+1,
           t.fids || d.ogc_fid,
           st_endpoint(d.geom)
    from data_subset d join toto t on st_dwithin(t.geom, st_startpoint(d.geom), 0.001)
), tmp as (
    select ogc_fid, v19, unnest(fids[1:cardinality(fids) - 1]) as childs
    from toto
), tmp1 as (
    select t.ogc_fid, t.v19, array_agg(distinct childs) as childs
    from tmp t
    group by t.ogc_fid, t.v19
) select t.ogc_fid, t.v19 + sum(d.v19) as vals, dd.geom
from tmp1 t join data_subset d on d.ogc_fid = any(t.childs)
            join data_subset dd on t.ogc_fid = dd.ogc_fid
group by t.ogc_fid, t.v19, dd.geom;

Nicolas

Enfin, dans le dataset fourni, quasiment toutes les valeurs v19 sont à 0, ce qui ne permet pas de calculer des valeurs en aval: dans mon exemple, je change v19 pour contenir la longueur du segment concerné comme valeur.