Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 Mon 23 August 2021 22:59

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

répercuter des valeurs d'un segment à un autre

Bonjour,

J'ai des valeurs en amont de mon réseau hydrographique que je souhaite répercuter sur l'aval , par ex un site sur lequels un produit X est répendu et que l'on souhaite cartographier la pollution du cours d'eau. Le souci c'est qu'il peut y'avoir plusieurs site qui présentent le même cas de figure sur le même bassin versant.

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 ?

Hors ligne

 

#2 Tue 24 August 2021 10:40

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1538

Re: répercuter des valeurs d'un segment à un autre

Bonjour,

Vous pouvez utiliser un CTE récursive (https://www.postgresql.org/docs/13/queries-with.html) pour parcourir le réseau hydro à partir d'un point et calculer ce que vous voulez (somme par exemple) pour les points suivants (il y a des exemples de ces requetes sur ce forum)

Nicolas

Hors ligne

 

#3 Tue 24 August 2021 13:20

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Nicolas Ribot a écrit:

Bonjour,

Vous pouvez utiliser un CTE récursive (https://www.postgresql.org/docs/13/queries-with.html) pour parcourir le réseau hydro à partir d'un point et calculer ce que vous voulez (somme par exemple) pour les points suivants (il y a des exemples de ces requetes sur ce forum)

Nicolas


Merci !! je vais regarder ça !

Hors ligne

 

#4 Tue 31 August 2021 17:25

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Je suis tombé sur la fonction rédigé par tumasgiu : http://sqlfiddle.com/#!17/53864/4 et notamment le thread suivant :
https://georezo.net/forum/viewtopic.php?pid=300325

Je m'en suis inspiré sans reconstruire cependant la topologie avec pgrouting.

Ma table contient 4 champs :
pk : clé primaire
pollu_19 : valeur de 0 ou 1 si pollution
id_nd_ini : noeud initial du vecteur
id_nd_ini : noeud terminal du vecteur



j'ai modifié la requête de la sorte :

Code:

WITH RECURSIVE additionflow(
pk,
id_nd_ini,
id_nd_fin
) 
AS(
SELECT 
0,
t.pk,
t.id_nd_ini,
t.id_nd_fin
t.pollu_19
from turbi."input" t
LEFT JOIN test t2 
ON t.id_nd_ini = t2.id_nd_fin
UNION
SELECT
t.pollu_19 -1, 
t.pk,
t.id_nd_ini,
t.id_nd_fin
FROM additionflow
JOIN turbi."input" t
ON additionflow.id_nd_fin =t.id_nd_ini
)

La requête ne fonctionne pas mais j'aimerais comprendre le concept. Faut-il préalablement créer une table additionflow(pk,id_nd_ini,id_nd_fin) qui servira à "héberger" la donnée ? Faire une Union sur une donnée en cours de création me parait étrange.

Dernière modification par neskuik01 (Tue 31 August 2021 23:24)

Hors ligne

 

#5 Wed 01 September 2021 10:48

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1538

Re: répercuter des valeurs d'un segment à un autre

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

Hors ligne

 

#6 Wed 01 September 2021 15:34

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Nicolas Ribot a écrit:

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


Merci pour cet éclairage smile. Effectivement j'ai procédé aux modifications (j'exécute toujours sur des subsets).
Je pense que je me trompe dans la requête d'aggregation  :

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;


Le résultat ne me retourne que de 0 ou 1 alors que je devrais avoir des 0, 1, 2,3 .. Je soupçonne le "group by f.pk " d'être responsable .

Hors ligne

 

#7 Mon 06 September 2021 23:03

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Je tente toujours sans succès d'additionner les valeurs sur mon réseau hydrographique.  Ma requête ressemble desormais à ceci :

Code:

Create table turbi.input_bckup as 
select *from turbi.input;

DROP FUNCTION IF EXISTS turbi.ft_flowAdd (tu text) CASCADE;

CREATE FUNCTION turbi.ft_flowAdd (tu text)  RETURNS VOID
--AS  $$ BEGIN EXECUTE 'UPDATE turbi."input" tu SET v19 = GREATEST(v19, (SELECT max(v19) FROM tu t2 WHERE t2.id_nd_fin = tu.id_nd_ini)) WHERE tu.id_bdcarth IN (' || tu || ' )'; END; $$
--LANGUAGE plpgsql;
AS  $$ BEGIN EXECUTE 'UPDATE turbi."input" tu SET turbi_19 = GREATEST(turbi_19, (SELECT max(turbi_19) FROM tu t2 WHERE t2.id_nd_fin = tu.id_nd_ini)) WHERE tu.id_bdcarth IN (' || tu || ' )'; END; $$
LANGUAGE plpgsql;

WITH RECURSIVE additionflow (pk,turbi_19,id_bdcarth,id_nd_ini,id_nd_fin,date_obs,detruit_le)
    AS(
        SELECT 
        tu.pk,
        0,
        tu.id_bdcarth,
        tu.id_nd_ini,
        tu.id_nd_fin,
        tu.date_obs,
        tu.detruit_le,
        tu.statut
        from turbi."input" tu
        LEFT JOIN turbi."input" t2 
        ON tu.id_nd_ini = t2.id_nd_fin
        WHERE t2.id_nd_fin IS NULL
        UNION
        SELECT
        tu.pk,
        tu.turbi_19,
        tu.id_bdcarth,
        tu.id_nd_ini,
        tu.id_nd_fin,
        tu.date_obs,
        tu.detruit_le,
        tu.statut
        FROM additionflow
        JOIN turbi."input" tu
        ON additionflow.id_nd_fin =tu.id_nd_ini
    )

    SELECT turbi.ft_flowAdd(string_agg(f.id_bdcarth::text, ',')) 
    FROM (SELECT max(turbi_19) turbi_19, id_bdcarth FROM additionflow GROUP BY id_bdcarth) f
    WHERE turbi_19 > 0 
    GROUP BY turbi_19
    ORDER BY turbi_19;

Je me retrouve avec une erreur "la relation tu n'existe pas" , La question que je me pose donc c'est dois-je créer une table spécifique au préalable ? le sql fiddle me perturbe un peu .

Hors ligne

 

#8 Thu 09 September 2021 14:54

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Après avoir compris qu'il s'agissait de sql dynamique j'en suis arrivé à cette requête fonctionne mais partiellement.
j'ai bien re-vérifié que les end_point et start_point soient bien joint  donc le problème ne vient pas d'un souci de connexité.

Capture d'écran :
zone bleu = tronçon source
linéaire rouge = résultat de la requête
https://ibb.co/ZVsKG4p

Code:

DROP FUNCTION IF EXISTS pg_temp.upd(tu text) CASCADE;
CREATE FUNCTION pg_temp.upd(tu text) RETURNS VOID
AS $$ BEGIN 

EXECUTE 'UPDATE turbi.input tu SET v19 = (SELECT SUM(v19) FROM turbi.input t2 WHERE t2.id_nd_fin = tu.id_nd_ini) 
WHERE tu.id_bdcarth IN (' || tu || ' )'; END; $$

LANGUAGE plpgsql;
WITH 
RECURSIVE rec(turbi_19, id_bdcarth, id_nd_ini, id_nd_fin, date_obs, detruit_le )
AS(
SELECT 0, tu.id_bdcarth, tu.id_nd_ini, tu.id_nd_fin, tu.date_obs, tu.detruit_le FROM  turbi.input tu
LEFT JOIN turbi.input t2 
ON tu.id_nd_ini = t2.id_nd_fin
WHERE t2.id_nd_fin IS NULL
UNION
SELECT tu.turbi_19, tu.id_bdcarth, tu.id_nd_ini, tu.id_nd_fin, tu.date_obs, tu.detruit_le  
FROM rec
JOIN turbi.input tu
ON rec.id_nd_fin = tu.id_nd_ini
)
SELECT turbi_19, string_agg(f.id_bdcarth::text, ', '  ORDER BY id_bdcarth), pg_temp.upd(string_agg(f.id_bdcarth::text, ',')) 
FROM (SELECT sum(turbi_19) turbi_19, id_bdcarth FROM rec GROUP BY id_bdcarth) f
WHERE turbi_19 =0 
GROUP BY turbi_19
ORDER BY turbi_19;

DROP TABLE IF EXISTS turbi.inputVAL2;
CREATE table turbi.inputVAL2 as
SELECT distinct id_bdcarth,v19 valeur,id_nd_ini,id_nd_fin, turbi_19,geom FROM turbi.input ORDER BY id_bdcarth;

Dernière modification par neskuik01 (Thu 09 September 2021 15:13)

Hors ligne

 

#9 Thu 09 September 2021 17:43

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1134

Re: répercuter des valeurs d'un segment à un autre

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

Dernière modification par tumasgiu (Thu 09 September 2021 17:47)

Hors ligne

 

#10 Thu 09 September 2021 19:18

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

tumasgiu a écrit:

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


Bonjour

Non il s'agit d'un réseau hydro en revanche y'a des segments en doubles (plusieurs entrée de polluant sur un même segment ) donc un réseau tel quel :

                               E
                                \
A-----BBB----C--DD----F

Hors ligne

 

#11 Thu 09 September 2021 20:19

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1134

Re: répercuter des valeurs d'un segment à un autre

Dnas ce cas, peut être qu'une requête comme çà devrait suffire :

Code:

WITH RECURSIVE summed as(
  SELECT
    id, sum(valeur)as valeur
  FROM
    segment
  GROUP BY
    id
),

 bfs(i,v) as (
  SELECT
    f.id, valeur::bigint
  FROM
    segment f
  WHERE
    id = 1 --ici, remplacer la condition par st_shortestline
  UNION
  SELECT
    f.id,
    valeur + v
  FROM
    bfs
  JOIN 
    relation s ON parent = i
  JOIN
    summed f ON enfant = f.id    
 )
 
SELECT
  i,
  max(v)
from 
  bfs
GROUP BY
  i
LIMIT 100

http://sqlfiddle.com/#!17/cdff73/1

Dernière modification par tumasgiu (Fri 10 September 2021 14:21)

Hors ligne

 

#12 Thu 09 September 2021 20:24

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Je vais tester ça

Hors ligne

 

#13 Thu 09 September 2021 22:12

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Ca me parait bizarre néanmoins d'employer une fonction postgis alors que j'ai un réseau logique avec un champs id_nd_ini et id_nd_fin.

Hors ligne

 

#14 Fri 10 September 2021 08:16

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1134

Re: répercuter des valeurs d'un segment à un autre

C'est vous dans votre premier mesage qui en parlez :


neskuik01 a écrit:

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 partie superieure d'une requête récursive est sa base, donc la partie ou vous associez aux tronçons vos points de pollution avec shortest_line.

Dernière modification par tumasgiu (Fri 10 September 2021 09:01)

Hors ligne

 

#15 Fri 10 September 2021 13:56

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

tumasgiu a écrit:

C'est vous dans votre premier mesage qui en parlez :


neskuik01 a écrit:

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 partie superieure d'une requête récursive est sa base, donc la partie ou vous associez aux tronçons vos points de pollution avec shortest_line.


Ahhh oui ^^,  non mais je ne fais pas tous en une seule requête,

J'ai créer un script pgsql qui décompose traitement par traitement (un peu comme un script qgis model builder).
Etape 1 :
Jointure par proximité des tables  ou figure les ID de la bd carthage + les noeuds initiaux et finaux ET les id de mes points de pollution et les date de référence.

Etape 2 :
Ajoute pour chaque année une colonne avec valeur 1 si id point pollution est pas null et année de la date de relevé correspondante sinon 0

Etape 3 :
Addition des valeurs de l'amont vers l'aval en utilisant les identifiants des noeuds . (Et c'est là que je bloque )

Hors ligne

 

#16 Wed 15 September 2021 18:19

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

J'ai modifié  ma requête j'arrive desormais à récupérer la valeur max de mon réseau hydro et à la répercuter en aval  :

Code:

WITH RECURSIVE walk_network(v19, id_nd_ini, id_nd_fin,id_bdcarth) 
AS ( SELECT 0,  
 id_nd_ini, 
 id_nd_fin, 
 id_bdcarth   
FROM somme_join n  
 UNION ALL   
 SELECT 
     n.v19+w.v19, 
     n.id_nd_ini, n.id_nd_fin, n.id_bdcarth    
      FROM somme_join n, walk_network w    
       WHERE w.id_nd_fin = n.id_nd_ini )
       
        SELECT max(v19),id_bdcarth FROM walk_network group by id_bdcarth

Alors y'a du mieux mais c'est pas la valeur max que je cherche à répercuter mais la précédente somme

Hors ligne

 

#17 Thu 16 September 2021 09:28

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1134

Re: répercuter des valeurs d'un segment à un autre

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.

Hors ligne

 

#18 Thu 16 September 2021 16:02

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

tumasgiu a écrit:

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.


Effectivement je pensais identifier les segments de départ en procédant à une selection des segments dont le noeud initial ne dispose pas d'équivalent dans les noeuds terminaux. le hic c'est que les segments de départ se retrouvent par la même, exclus du calcul.

Hors ligne

 

#19 Thu 16 September 2021 16:31

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Code:

--CREATE TABLE IF NOT EXISTS 
WITH RECURSIVE walk_network (v19, id_bdcarth, id_nd_ini,id_nd_fin) 
AS (
    select 
    0,
    n.id_bdcarth,
    n.id_nd_ini,
    n.id_nd_fin
    --b.id_bdcarth,
    --b.id_nd_ini,
    --b.id_nd_fin
    from hydro_subset n
    LEFT JOIN 
    hydro_subset b
    ON n.id_nd_ini = b.id_nd_fin 
    WHERE b.id_nd_ini is null
        UNION ALL   
         SELECT 
            n.v19+ w.v19, 
            n.id_bdcarth,
            n.id_nd_ini, 
            n.id_nd_fin 
            FROM somme_join n, walk_network w    
               WHERE w.id_nd_fin = n.id_nd_ini 
               )
               
                SELECT sum(distinct v19), id_bdcarth FROM walk_network group by id_bdcarth

Avec ça j'arrive exactement au résultat souhaité , Est-ce envisageable de procédé à une jointure sur l'Union pour rajouter les segments initiaux ?

Hors ligne

 

#20 Thu 16 September 2021 17:32

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Enfin je tempère un peu ce que je dis, ca fonctionne mais sur un petit jeu de donnée ...

Hors ligne

 

#21 Tue 21 September 2021 16:55

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1538

Re: répercuter des valeurs d'un segment à un autre

Bonjour,

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

Nicolas

Hors ligne

 

#22 Mon 27 September 2021 22:42

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Nicolas Ribot a écrit:

Bonjour,

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

Nicolas


Oups désolé je n'avais pas vu votre message.
Je test mes requêtes sur le jeu de donnée en PJ (sql dump).

J'avoue que je me suis complètement perdu dans mes différents tests.

L'adaptation de la requête de paul ramsey https://blog.cleverelephant.ca/2010/07/ … stgis.html
ne me permet pas d'additionner les valeurs qui arrivent de différents affluents..

J'ai brièvement tenter une extraction des données sous python mais je voyais encore moins comment structurer l'algorithme.


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#23 Tue 28 September 2021 13:50

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1538

Re: répercuter des valeurs d'un segment à un autre

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

Hors ligne

 

#24 Tue 28 September 2021 14:10

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1538

Re: répercuter des valeurs d'un segment à un autre

Un exemple du résultat en image:
a gauche, le reseau initial, avec comme valeur v19 un nombre entre 0 et 10 aleatoire.
a droite, le reseau avec cumul des valeurs des enfants + valeur du parent, pour chaque segment.

Nico


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#25 Tue 28 September 2021 14:25

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Déjà un grand merci pour tous le temps passé et les explications.

Effectivement j'ai des géométries doubles car v19 correspond à la valeur que je souhaite additionner pour l'année 2019 (je dois faire ca sur 10 ans).
C'est pourquoi je travaillais sur des tables par année sans géométrie.

Je vais tester cette méthode de suite smile

Hors ligne

 

#26 Tue 28 September 2021 14:36

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Nicolas Ribot a écrit:

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


Je rebondis juste sur cette partie :

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.


si je comprend bien je dois remplacer mes valeurs ? (en gros 1 /0 c'était un peu équivalent à vrai / faux pour la présence d'un phénomène)

Hors ligne

 

#27 Tue 28 September 2021 15:50

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1538

Re: répercuter des valeurs d'un segment à un autre

Non pas besoin de remplacer vos valeurs si 0/1 sont bien les valeurs des segments.
C'est juste que dans ce dataset, il y avait essentiellement des 0, ce qui n'etait pas tres illustratif pour vérifier que le cumul se faisait bien.

Nico

Hors ligne

 

#28 Wed 29 September 2021 13:14

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Pour en venir aux nouvelles , j'ai effectué le test sur le réseau fournit avec différent type de valeurs et les additions sont toujours exactes.  J'ai en revanche essayer sur un réseau un peu plus important (2000 segments), en effectuant bien vacuum analyse + création d'un index spatial + nettoyage  et conversion en Linestring et ça a mouliner toute l'après midi .

Je n'ai pas eu de résultat (il semblerait que j'ai tué le processus par inadvertance).

Hors ligne

 

#29 Wed 29 September 2021 13:29

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1538

Re: répercuter des valeurs d'un segment à un autre

Bonjour,
Sur un réseau plus important, il se peut qu'il y ait des boucles dans le réseau: le code montré hier ne marche pas dans ce cas et la requête ne finira jamais. Il faut ajouter une condition dans la 2eme partie de l'"union all" par exemple en accumulant dans un tableau les id déjà rencontrés et tester si on retrouve cet id.

2000 segments avec les index qui vont bien (attention, on utilise le st_startpoint(geom) st_endpoint(geom) dans la partie spatiale st_dwithin, il faut donc créer des index fonctionnels sur ces valeurs, et pas sur la geom elle-même) ca devrait tourner plutot vite.

Nicolas

Hors ligne

 

#30 Wed 29 September 2021 16:42

neskuik01
Participant assidu
Date d'inscription: 16 Feb 2015
Messages: 197

Re: répercuter des valeurs d'un segment à un autre

Bonjour,

La partie sur les index me parait obscure, il est possible de créer des index sur le résultat de fonction (startpoint() endpoint() ce sont des données temporaires non ?)?

Hors ligne

 

Pied de page des forums

Powered by FluxBB