#1 Mon 23 August 2021 22:59
- neskuik01
- Participant assidu
- Date d'inscription: 16 Feb 2015
- Messages: 198
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: 198
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
Merci !! je vais regarder ça !
Hors ligne
#4 Tue 31 August 2021 17:25
- neskuik01
- Participant assidu
- Date d'inscription: 16 Feb 2015
- Messages: 198
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: 198
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
Merci pour cet éclairage . 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: 198
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: 198
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: 1144
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: 198
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
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: 1144
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: 198
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: 198
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: 1144
Re: répercuter des valeurs d'un segment à un autre
C'est vous dans votre premier mesage qui en parlez :
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: 198
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.
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: 198
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: 1144
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: 198
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.
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: 198
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: 198
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: 198
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
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.
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
Hors ligne
#25 Tue 28 September 2021 14:25
- neskuik01
- Participant assidu
- Date d'inscription: 16 Feb 2015
- Messages: 198
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
Hors ligne
#26 Tue 28 September 2021 14:36
- neskuik01
- Participant assidu
- Date d'inscription: 16 Feb 2015
- Messages: 198
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 simplesCode:
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: 198
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: 198
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