Discussion :

[Solevita]

Bonjour,

Après beaucoup de temps passer à m'arracher les cheveux et souhaitant conserver ceux restants, je recherche une âme charitable qui me tirera de cette situation .

Je dispose de valeur sur un réseau (hydrographique) que je cherche à additionner dans le sens du courant.
Chaque segment possède un champs identifiant id_nd_ini (identifiant l'amont du segment) et id_nd_fin (la fin)

id_bdcarth	id_nd_ini	id_nd_fin	v19	valeur attendue
1	10	11	1	1
2	11	12	0	1
3	12	13	1	2

J'ai actuellement cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
WITH RECURSIVE walk_network (v, i, ini,fin) AS
 (    
	 select  	
	 0, 	   
	 n.id_bdcarth,
	 n.id_nd_ini,
	 n.id_nd_fin,
	 array[n.id_bdcarth] as ids 	  
	 from last_input_somme n  		
	 LEFT JOIN last_input_somme b  		
	 ON n.id_nd_ini = b.id_nd_fin 		
	 WHERE b.id_nd_ini is null 		
		 UNION ALL 			 
			SELECT 				
			n.v19+v, 				
			n.id_bdcarth,
			n.id_nd_ini, 
			n.id_nd_fin ,		
			ids || n.id_bdcarth
			FROM last_input_somme n, walk_network w 				
			WHERE w.fin = n.id_nd_ini and not (n.id_bdcarth = any(ids)) ) 	
 
 
			select  i,  v from walk_network

la table last_input_somme contient 37 entités et je me retrouve avec un select qui me retourne 98 lignes et de surcroit même si il y'a bien une addition , certaines valeurs sont omises

[SQLpro]

Sans votre jeu d'essais sous forme CREATE TABLE... INSERT ... ça va être difficile de vous répondre. Mais à vu de nez il manque un filtre dans la requête finale pour ne prendre en compte que les totaux finaux et non pas les résultats intermédiaires...

A +

[philouZ]

Bonjour,

Tu devrais regarder les fonctions de fenêtrage, je pense que ça peut répondre à ton besoin (Fonctions de fenêtrage), notamment la fonction lag().

[Solevita]

Sans votre jeu d'essais sous forme CREATE TABLE... INSERT ... ça va être difficile de vous répondre. Mais à vu de nez il manque un filtre dans la requête finale pour ne prendre en compte que les totaux finaux et non pas les résultats intermédiaires...

A +

J'ai ajouté le fichier sql en PJ .

Dans ma requête finale j'avais tenté de faire un select i, sum(distinct v) ... mais les valeurs n'étaient pas du tous cohérentes (2+3 = 8).

[Solevita]

Bonjour,

Tu devrais regarder les fonctions de fenêtrage, je pense que ça peut répondre à ton besoin (Fonctions de fenêtrage), notamment la fonction lag().

Je connaissais pas l'existence de ces fonctions ca semblerait être une option

[Solevita]

Bonjour,

Tu devrais regarder les fonctions de fenêtrage, je pense que ça peut répondre à ton besoin (Fonctions de fenêtrage), notamment la fonction lag().

En fait je ne suis pas certain que ca me soit utile car lag permet d'effectuer des opérations sur des tuples qui sont à une distance définit ce qui n'est pas le cas chez moi. Si j'ordonne mes tables selon le champ "id_nd_fin" je vais me retrouvé avec les segment les plus en amonts (qui ne sont pas forcément connectés les uns aux autres)

[philouZ]

Bonjour,

Alors basé sur ton jeu de donnée et pour un tronçon voici ce que ça donne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
with recursive x(id_bdcarth,id_nd_ini,id_nd_fin,v19) as (
	select id_bdcarth,id_nd_ini,id_nd_fin,v19 from last_input_somme
	where id_bdcarth = 973195760
	union all
	select b.id_bdcarth,b.id_nd_ini,b.id_nd_fin,b.v19 
	from x,last_input_somme b 
	where x.id_nd_fin = b.id_nd_ini
)
select * from x

Ensuite si tu veux la somme il te suffit de faire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
with recursive x(id_bdcarth,id_nd_ini,id_nd_fin,v19) as (
	select id_bdcarth,id_nd_ini,id_nd_fin,v19 from last_input_somme
	where id_bdcarth = 973195760
	union all
	select b.id_bdcarth,b.id_nd_ini,b.id_nd_fin,b.v19 
	from x,last_input_somme b 
	where x.id_nd_fin = b.id_nd_ini
)
select sum(v19) from x

Exemple sur fiddle : https://www.db-fiddle.com/f/nsKWKVsdEnGFiukELGT8Zc/0

Ce code fonctionne pour un tronçon identifié au départ. Est-ce que c'est ce que tu recherches ?

[Solevita]

Merci d'avoir pris le temps de plancher sur cette question ,

Effectivement c'est à peu près cette démarche avec une nuance c'est que je ne pars pas d'un point en amont vers un point en aval mais de tous les points en amont vers tous les exutoires et que le jeu de données sur lequel je travail comporte 150 000 segments .
Dans la requête j'ai également un champs qui aggrège les identifiants des segments et qui test que l'id n'a pas déjà été calculé (éviter que des boucles topologiques potentielles entrainent des boucles infinies)
J'ai eu l'occasion de parler avec un quelqu'un habitué à travailler sur les réseau hydro. Il me conseille de décomposer le traitement et de faire tourner ça sur python.

Donc j'hésite désormais entre deux méthodes:
- Utiliser votre méthode en la faisant boucler sur les segments les plus en aval d'un bassin versant jusqu'à à arriver à un exutoire principal défini arbitrairement.
- découper le réseau hydro par zone hydrographique (la plus petite entité de bassin versant) , réaliser des tables de chainages amont aval entre ces zones (je crains malheureusement que le process soit déjà trop lourd à cette échelle)

[SQLpro]

Pour information, la dernière (ou prochaine...) version de PG intégrera (ou intègre déjà) le parcours de graphe en largeur ou profondeur de préférence, à votre choix, ce qui permet généralement d'améliorer certaines requêtes récursives dont la topologie est connue.

A +