Le portail francophone de la géomatique

Salut,

de manière très rapide.

En supposant une table définie comme suit:

CREATE TABLE tronc
(id integer, geo geometry('linestring'), est_ferme boolean);

L'idée est de transmettre la valeur de est_ferme aux enfants
de chaque tronçons:

WITH start AS
(SELECT t1.* 
 FROM tronc t2 RIGHT JOIN tronc t2 
 ON st_intersects(st_endpoint(t1.geo), st_startpoint(t2.geo))
 WHERE t1 is NULL 
  ),
RECURSIVE p(id, geo, f) AS
(SELECT * FROM start
 UNION
 SELECT t.id, t.geo, 
 p.f OR t.est_ferme   
 FROM p JOIN tronc t
 ON st_intersects(st_endnode(p.geo), st_startnode(t.geo)
 )

SELECT * FROM p
WHERE f;

Dernière modification par tumasgiu (Wed 13 June 2018 10:34)

Désolé j'ai été un peu vite hier.

Premièrement, concernant la syntaxe de la requête,
les CTE (requêtes dans le bloc with) récursives
doivent précéder les CTE classiques, ce qui engendrait
le problème.
La solution est de transformer la CTE start en sous-requête
dans la CTE p.
Ensuite, à moins d'utiliser Postgis 2.5, les géométries
ne peuvent pas être déclarée dans une CTE récursive.
Il faut donc effectuer une seconde jointure dans P avec tronc pour
les récupérer.

Voici la requête corrigée :

WITH 
RECURSIVE p(id, f) AS
(SELECT id, est_ferme
 FROM (SELECT t1.* 
            FROM tronc t1 RIGHT JOIN tronc t2 
            ON st_intersects(st_endpoint(t1.geo), 
                                   st_startpoint(t2.geo))
             WHERE t1 is NULL
          ) as start
 UNION
 SELECT t2.id,
 p.f OR t2.est_ferme
 FROM p JOIN tronc t1 ON t1.id = p.id
 JOIN tronc t2
 ON st_intersects(st_endpoint(t1.geo), st_startpoint(t2.geo))
 )

SELECT * FROM p
WHERE f;

Deuxièmement, concernant votre question sur la liste de vos conduites fermées,
mon exemple suppose qu'une colonne est_ferme symbolise l'état de vos tronçons.
Vous pouvez ajouter cette colonne à votre  table et la mettre à jour,
ou passer par une vue qui croisera vos tronçons avec la liste de vos conduites fermées.
Une autre solution serait de modifier la requête pour essayer de croiser directement votre
liste dans la partie récursive de la CTE p (en dessous du mot clef UNION).

Dernière modification par tumasgiu (Thu 14 June 2018 11:26)

Si vous avez la possibilité de fournir des données de test,
je veux bien y jeter un coup d'oeil.

Petite précision, la partie géométrique est approximative,
j'ai utilisé st_intersects, mais peut être que st_equals conviendrait mieux,
en supposant que votre réseau est topologiquement valide,
sinon il faudra surement utiliser des buffer, st_dwithin, ou aligner les points
sur une grille au préalable avec st_snaptogrid.

Bonjour,
Merci Tumasgiu pour tous ces éléments de réponse.
J'ai effectivement utilisé le ST_Dwithin car même si le réseau semble parfait, il ne l'est pas (tolérance, tolérance...).
J'ai également modifié la condition comme suit dans la première partie :  WHERE t1.est_ferme is NOT NULL

WITH 
RECURSIVE p(id, f) AS
(SELECT id, est_ferme
 FROM (SELECT t1.* 
            FROM tronc t1 RIGHT JOIN tronc t2 
            ON st_dwithin(st_endpoint(t1.geo), st_startpoint(t2.geo), 0.01)
             WHERE t1.est_ferme is NOT NULL
          ) as start
 UNION
 SELECT t2.id,
 p.f OR t2.est_ferme
 FROM p JOIN tronc t1 ON t1.id = p.id
 JOIN tronc t2
 ON st_dwithin(st_endpoint(t1.geo), st_startpoint(t2.geo), 0.01)
 )

SELECT * FROM p
WHERE f;

J'arrive à sortir une liste de conduites mais il me reste encore quelques erreurs. Je joints un backup avec un extrait des données...
L'objectif est d'isoler la conduite 127. Les vannes dont la valeur de est_ferme est 't' sont celles qui sont fermées suite à l'utilisation du SQL de Nicolas.
Il y a des conduites qui ne devraient pas être listées et d'autres qui le devraient et qui ne le sont pas, et je ne comprends pas pourquoi... sur l'image jointe le bon résultat est la sélection de conduites rouges.
Merci encore!!
Cdt

Dernière modification par zang (Fri 15 June 2018 02:56)

Salut,

désolé je vous avais oublié.

[EDIT]
j'ai dit une bêtise sur les opérations dont l'un des 2 opérandes est NULL.
Si effectivement les opérations arithmétiques renvoient NULL,
les opérations logiques fonctionnent en logique tri-valente, et NULL joue le rôle
de "ni vrai ni faux".
NULL OR TRUE et NULL OR FALSE renvoient donc bien respectivement TRUE et FALSE.
La requête me semble donc juste.

NULL est par contre bien coercisé en FALSE lorsque celui ci est le résultat d'une condition
WHERE ou ON.


Ci joint une copie d'écran du résultat, avec en bleu les tronçons ouverts,
en rose les tronçons dont la colonne est_ferme vaut True, et en rouge les tronçons fermés par la requête.

Le résultat me parait correct.

Dernière modification par tumasgiu (Wed 27 June 2018 18:16)

Oui je m'en doutais.

Par contre si votre réseau contient des boucles (cycles),
la requête ne fonctionnera pas correctement.

Aussi, il est toujours bon de mettre un LIMIT dans des requêtes
incluant des CTE récursives, pour éviter qu'elle ne boucle indéfiniment.
Une boucle infinie aura vite fait de saturer PostgreSQL voire même
le disque dur ou se trouve votre cluster, çà peut être très embêtant.

Dernière modification par tumasgiu (Wed 27 June 2018 18:14)