Message posté par : T. Rossini ---------------------------------------- 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 ---------------------------------------- Le message est situé https://georezo.net/forum/viewtopic.php?pid=346211#p346211 Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement -- Association GeoRezo - le portail géomatique https://georezo.net

Message posté par : neskuik01 ---------------------------------------- ----------------- tumasgiu écrit : C'est vous dans votre premier mesage qui en parlez : ----------------- neskuik01 é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 ) ---------------------------------------- Le message est situé https://georezo.net/forum/viewtopic.php?pid=346231#p346231 Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement -- Association GeoRezo - le portail géomatique https://georezo.net

Message posté par : neskuik01 ---------------------------------------- ----------------- 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 ? ---------------------------------------- Le message est situé https://georezo.net/forum/viewtopic.php?pid=346390#p346390 Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement -- Association GeoRezo - le portail géomatique https://georezo.net

Message posté par : Nicolas Ribot ---------------------------------------- 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 ---------------------------------------- Le message est situé https://georezo.net/forum/viewtopic.php?pid=346675#p346675 Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement -- Association GeoRezo - le portail géomatique https://georezo.net

Message posté par : Nicolas Ribot ---------------------------------------- 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 ---------------------------------------- Le message est situé https://georezo.net/forum/viewtopic.php?pid=346684#p346684 Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement -- Association GeoRezo - le portail géomatique https://georezo.net

Message posté par : Nicolas Ribot ---------------------------------------- Oui, c'est possible ;) https://www.postgresql.org/docs/13/indexes-expressional.html Nicolas ---------------------------------------- Le message est situé https://georezo.net/forum/viewtopic.php?pid=346708#p346708 Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement -- Association GeoRezo - le portail géomatique https://georezo.net

Message posté par : neskuik01 ---------------------------------------- Héhé c'est beaucoup plus logique effectivement ^^. J'ai relancé et j'ai eu une erreur de mémoire épuisé "échec d'une requête de taille 620" Je retenterais demain matin en local j'ai l'impression que le serveur postgres a quelque souci. J'ai mis le dump en PJ. ---------------------------------------- Ce message est accompagné de fichiers, pour les télécharger, suivre le lien ci-dessous. ---------------------------------------- Le message est situé https://georezo.net/forum/viewtopic.php?pid=346750#p346750 Pour y répondre : geobd(a)ml.georezo.net ou reply de votre messagerie Pour vous désabonner connectez-vous sur le forum puis Profil / Abonnement -- Association GeoRezo - le portail géomatique https://georezo.net