Le portail francophone de la géomatique

Bonjour à tous,


J'ai essayé de retravailler sur mon problème...


Je ne pense pas avoir été très limpide dans mon post précédent donc je vous donne en plus une image de ce que j'ai aujourd'hui et de ce que j'aimerais obtenir
[img]http://imageshack.com/a/img922/8581/w7qCZM.jpg[/img]

Je souhaite donc :
- que "si la valeur de "classe_corr" du tronçon amont (B/enfant) est inférieure à celle de son ou de ses tronçons en aval (A/parent), alors le tronçon B prend (la plus grande) valeur de A/parent".
Et ainsi de suite jusqu'à la fin de mon réseau hydrographique.
- la cerise sur le gâteau, ce serait d'avoir la classe 0 en aval (tronçon(s) "grands-parents") du premier tronçon parent (A le plus aval) si et seulement s'il n'y a aucune note aujourd'hui sur ces tronçons grands-parents ET qu'il existe un tronçon parent noté en amont ; sinon ça doit rester en classe "NULL"



J'ai tenté une requête récursive mais pour l'instant :
- soit elle ne me donne qu'une partie du résultat attendue (avec UNION): seuls quelques tronçons B (enfant) sont modifiés et je n'arrive pas à récupérer les valeurs initiales des tronçons A ou B (ceux qui doivent conserver la valeur initiale de class_corr car les conditions de case ne sont pas vérifiées) :

DROP TABLE IF EXISTS testok CASCADE;
CREATE TABLE testok AS (
WITH RECURSIVE montaison (idsplit, source, target, classe_corr) 
AS
   (SELECT DISTINCT idsplit, source, target, classe_corr 
    FROM   testsum
    WHERE  testsum.source NOT IN (SELECT target FROM testsum)
    UNION  
    SELECT enfant.idsplit, enfant.source, parent.target,
    (CASE 
        WHEN (parent.classe_corr > enfant.classe_corr) THEN parent.classe_corr    
        WHEN (parent.classe_corr < enfant.classe_corr) THEN enfant.classe_corr
        WHEN (parent.classe_corr IS NULL) THEN 0
        ELSE enfant.classe_corr
    END) 
    FROM   testsum AS enfant
           INNER JOIN montaison AS parent
                 ON parent.target = enfant.source
                )
SELECT s.idsplit, s.source, s.target, s.max, s.classe, s.classe_corr, m.idsplit as enfantidsplit, m.source as enfantsource, m.target as parenttarget, m.classe_corr as classang_franchi, s.region_zone_hydro, s.geom
FROM   testsum s
LEFT OUTER JOIN montaison m
ON s.idsplit = m.idsplit

)

- soit elle tourne longtemps et/ou j'ai un message d'erreur (avec UNION ALL) :

DROP TABLE IF EXISTS testok CASCADE;
CREATE TABLE testok AS (
WITH RECURSIVE montaison (idsplit, source, target, classe_corr) 
AS
   (SELECT DISTINCT idsplit, source, target, classe_corr 
    FROM   testsum
    WHERE  testsum.source NOT IN (SELECT target FROM testsum)
    UNION  ALL
    SELECT enfant.idsplit, enfant.source, parent.target,
    (CASE 
        WHEN (parent.classe_corr > enfant.classe_corr) THEN parent.classe_corr    
        WHEN (parent.classe_corr < enfant.classe_corr) THEN enfant.classe_corr
        WHEN (parent.classe_corr IS NULL) THEN 0
        ELSE enfant.classe_corr
    END) 
    FROM   testsum AS enfant
           INNER JOIN montaison AS parent
                 ON parent.target = enfant.source
                )
SELECT s.idsplit, s.source, s.target, s.max, s.classe, s.classe_corr, m.idsplit as enfantidsplit, m.source as enfantsource, m.target as parenttarget, m.classe_corr as classang_franchi, s.region_zone_hydro, s.geom
FROM   testsum s
LEFT OUTER JOIN montaison m
ON s.idsplit = m.idsplit

)

le message d'erreur :

Je précise que tout est en local, sur un ordinateur portable...
Pstgresql 9.5 et postgis 2.3.2

Auriez-vous une idée pour m'aiguiller dans cette requête récursive ?

Merci d'avance !

Bonjour tumasgiu

Et merci beaucoup pour votre réponse.
Comme je vous le disais, je débute, surtout avec les récursives et... les fonctions. J'avais effectivement pensé à utiliser les maximums de profondeur mais n'arrivait pas à tourner tout ça correctement, pour que ce soit cyclique.

J'ai utilisé votre code, en l'adaptant (je pense) à mes données. En fin de requête j'ai donc ce code pour mettre à jour/corriger le champ classe_corr de la table testsum (ma couche de tronçons) :

DROP FUNCTION IF EXISTS pg_temp.upd(t text) CASCADE;
CREATE FUNCTION pg_temp.upd(t text) RETURNS VOID
AS $$ BEGIN EXECUTE 'UPDATE testsum t SET classe_corr = GREATEST(classe_corr, (SELECT max(classe_corr) FROM testsum  t2 WHERE t2.target = t.source)) WHERE t.idsplit IN (' || t || ' )'; END; $$
LANGUAGE plpgsql;


WITH 
RECURSIVE rec(depth, idsplit, s, e)
AS(
SELECT 0, t.idsplit, t.source , t.target FROM testsum t
LEFT JOIN testsum t2 
ON t.source = t2.target
WHERE t2.target IS NULL
UNION
SELECT depth + 1, t.idsplit, t.source, t.target
FROM rec
JOIN testsum t
ON rec.e = t.source
)
SELECT pg_temp.upd(string_agg(f.idsplit::text, ',')) 
FROM (SELECT max(depth) depth, idsplit FROM rec GROUP BY idsplit) f
WHERE depth > 0
GROUP BY depth
ORDER BY depth;

Je n'ai pas de bug annoncés mais je dois lancer la partie récursive plusieurs fois pour aboutir à mes fins ; c'est à dire pour que la modification des valeurs sur les tronçons "fils" se propage jusqu'au dernier.
En image, ça donne ça :

Premier lancement de la récursive après création de la fonction :
[img]http://imageshack.com/a/img923/8484/tjwsyU.jpg[/img]

Deuxième lancement de la récursive :
[img]http://imageshack.com/a/img923/6427/sIwZIY.jpg[/img]

Troisième lancement de la récursive :
[img]http://imageshack.com/a/img923/5183/tuucwm.jpg[/img]


etc.

J'ai testé, je peux arriver à mes fins mais en lançant une dizaine de fois la requête (je suppose que ça dépend du nombre d'entités tronçons et/ou de leurs connexions entre eux si j'ai bien analysé la requête).

Ai-je mal utilisé vos éléments ?
Est-ce lié à ce que vous appelez "mise à l'échelle" (je ne comprends pas trop votre expression dans ce contexte) ?


En vous remerciant de nouveau de votre aide !

Bonsoir:

le message d'erreur sur la récursive avec union all: attention: ca veut dire que la requete ne finit jamais et remplit tout le disque (no space left). Et ca, PG n'aime pas du tout.
Vérifiez que vous avez bien récupéré l'espace disque apres la fin de cette requete. (select pg_database_size('mabase')) et taille sur le disque.

Sinon, malgré les nouvelles explications, je ne suis pas sur de comprendre ce que vous voulez.
Les numéros sur les images correspondent aux id de troncon ou aux classes ?

Vous auriez un exemple écrit des troncons et attributs que vous voudriez, par ex sur 2 ou 3 troncons ?

Nicolas

Bonsoir tumasgiu et Nicolas,

Merci de votre retour.
Je n'ai pas la possibilité de vous transmettre des données ce soir, ce sera pour demain matin à la première (ou presque) heure !

@tumasgiu
- ..et ça donne l'occasion d'en apprendre encore plus à vos lecteurs
- j’exécute sous pgadminIII
- je vais chercher "isolation transactionnelle" !

@Nicolas
- oui, je me suis faite avoir plusieurs fois avec le union all mais toujours réussi à refaire tourner correctement après
- pour l'espace disque, je ne comprends pas trop ce que je fais :$ ni ce que j'ai fait : j'avais lancé (select pg_database_size('mabase')) en changeant le nom de la base et ça m'avait donné un nombre à quelques chiffres mais maintenant :

ERREUR:  n'a pas pu tester le fichier « base/72185/42736700 » : Permission denied

- les numéros sur les premières images sont les id de tronçons, désolée d'avoir laissé trainer ça. Les classes sont seulement illustrées par les couleurs
- pour l'exemple écrit, la transmission demain d'une partie des données, ça ira ?

Merci encore,
Bonne soirée

Salut,

j'ai pu testé de mon coté avec votre échantillon,
et je n'arrive pas au problème que vous évoquez.

Donc j'en déduis que comme Nicolas, je n'ai pas réellement compris
ce que vous vouliez.

pré-conditions de la requête:
Les tronçons sont tous connectés.
la valeur source d'un tronçon est égale à la valeur target
des tronçons connectés en aval.
Pas de boucle dans le réseau.

Ce que la requête fait :
Mise à jour de classe_cor de tout les tronçons avec la valeur maximum des
tronçons connectés en aval, si celle ci est  supérieure à la valeur de classe_cor,
en respectant l'ordre topologique.

Post-conditions:
Chaque tronçon a une valeur classe_cor supérieure ou égale à celles de ses connections en aval,
soit :

SELECT NOT EXISTS(
    SELECT t.idsplit, t2.idsplit, t.classe_cor, t2.classe_cor 
    FROM test.meonais t 
    JOIN test.meonais t2
    ON t2.target = t.source 
    WHERE t.classe_cor < t2.classe_cor);
-- doit renvoyer vrai

Dernière modification par tumasgiu (Thu 05 October 2017 16:55)

Que voulez vous dire exactement ?
La requête est lancée sur l'ensemble des bassins versants, et les résultats ne sont corrects que sur une partie ?
La requête est lancée par bassins, et bloque  sur certains ?
Que veut dire bloquer ? La requête se termine ? avec un message d'erreur ?

Je n'ai pas employé la terminologie exacte, je voulais parler de cycle
(on parle aussi de circuit dans le cas des graphes orientés) ,
Les cycles d'un graphe sont tout les chemins dont le sommet de départ est égale au sommet d'arrivée.
en tenant compte du sens des arcs si on se trouve dans un graphe orienté ce qui est votre cas.

Dernière modification par tumasgiu (Tue 10 October 2017 11:14)

Si vous n'avez pas réussi à résoudre votre problème,

la seule chose qui me vient spontanément, est que vos
deux sous-ensembles (Bassin Rhône vs. Cotes Méditerranéennes)
doivent posséder des valeurs de champ identiques, ce qui empêcherait
la requête de s'exécuter correctement.

Est ce que les champs idsplit, source et target sont cohérents ?
çàd pas partagés entre vous deux sous-ensembles ?

Dernière modification par tumasgiu (Wed 25 October 2017 09:56)