Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Wed 13 June 2018 03:38

zang
Participant actif
Date d'inscription: 4 Oct 2005
Messages: 55

[POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Bonjour,
Je travaille sur un réseau d'eau potable.
J'ai réussi à faire fonctionner la commande SQL pour identifier mes vannes à fermer afin d'isoler un tronçon (https://georezo.net/forum/viewtopic.php?id=91354, merci beaucoup Nicolas!).
Je souhaiterais désormais identifier tous les tronçons isolés suite à la fermeture de ces vannes. J'ai suivi plusieurs pistes mais en vain.
J'imagine que cela doit être possible d'identifier tous les tronçons parcourus lors de la recherche de connexion entre tronçons jusqu'au stop, c'est à dire jusqu'à mes vannes. Mais je ne trouve pas...
Comment pourrais-je récupérer l'identifiant des tronçons isolés, de mon bout de réseau situé entre mes vannes fermées svp? A partir du résultat des vannes à fermer, ou bien à partir d'une liste d'identifiants de vannes. Une solution pourrait être d'insérer une valeur, 1 par ex, dans un nouveau champs de ma couche conduite pour identifier celles qui auraient été parcourues? Sachant qu'un select vaudrait mieux qu'un update...
En espérant avoir été sufisamment clair...
Merci par avance pour votre aide,
Cdt
Franck

Dernière modification par zang (Wed 13 June 2018 03:42)

Hors ligne

 

#2 Wed 13 June 2018 10:33

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Salut,

de manière très rapide.

En supposant une table définie comme suit:

Code:

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:

Code:

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)

Hors ligne

 

#3 Thu 14 June 2018 00:19

zang
Participant actif
Date d'inscription: 4 Oct 2005
Messages: 55

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Merci pour ta réponse Tumasgiu,
J'aimerai juste comprendre car je n'arrive pas à obtenir de résultat. Ca bloque au niveau du p de RECURSIVE p(id, geo, f) AS
J'ai fait une modif, dis moi si je me trompe car je ne suis pas un pro. Dans la première partie j'ai compris, j'ai juste remplacé le FROM tronc t2 RIGHT JOIN tronc t2  par FROM tronc t1 RIGHT JOIN tronc t2.
Ensuite c'est plus flou pour moi. Déjà je ne comprends pas pourquoi ça bloque sur p, j'ai essayé plusieurs modifs sans succès...
Et où est-ce que je dois renseigner ma condition avec la liste de mes conduites fermées (celles qui sont en amont de mes vannes fermées)?
Merci pour ton aide!
Cdt

Code:

WITH start AS
(SELECT t1.* 
 FROM tronc t1 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;

Hors ligne

 

#4 Thu 14 June 2018 11:24

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

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 :

Code:

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)

Hors ligne

 

#5 Thu 14 June 2018 16:04

zang
Participant actif
Date d'inscription: 4 Oct 2005
Messages: 55

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Bonjour,
Merci pour le suivi.
Bon la requête tourne mais ne me sors pas de résultats. Pour faire le test, j'ai directement actualisé le champs est_ferme de ma table conduites.
Je prendrai le temps de faire de nouvelles tentatives un peu plus tard.
En attendant, je me demandais si il ne serait pas possible d'actualiser ce champs est_ferme lors de la première requête, à la recherche des vannes à fermer (https://georezo.net/forum/viewtopic.php?id=91354). Ne serait-il pas possible d'insérer une valeur dans un champs au cours de la recherche récursive? C'est à dire, grossièrement, je pars de ma conduite que je veux isoler, je recherche toutes les conduites qui m'amènent jusqu'aux vannes à fermer et sur ces conduites parcourues je fais un update sur le champs est_ferme en insérant la valeur true?
Merci,
Cdt
Franck

Hors ligne

 

#6 Thu 14 June 2018 17:59

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

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.

Hors ligne

 

#7 Thu 14 June 2018 23:52

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Pour votre question de l'update pendant la requête récursive,
théoriquement ca doit être faisable,
dans la pratique, ca n'est probablement pas supporté,
et de plus ca serait sans doute un peu complexe.

Hors ligne

 

#8 Fri 15 June 2018 02:55

zang
Participant actif
Date d'inscription: 4 Oct 2005
Messages: 55

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

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

Code:

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)


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#9 Wed 27 June 2018 16:22

zang
Participant actif
Date d'inscription: 4 Oct 2005
Messages: 55

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Bonjour,
Un petit up sur le sujet?
Je ne sais pas si quelqu'un a pu tester, et surtout si il y a un quelconque problème dans mes données?
Merci beaucoup à vous!

Hors ligne

 

#10 Wed 27 June 2018 17:51

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

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)


Fichier(s) joint(s) :
Pour accéder aux fichiers vous devez vous inscrire.

Hors ligne

 

#11 Wed 27 June 2018 18:06

zang
Participant actif
Date d'inscription: 4 Oct 2005
Messages: 55

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Bonjour,
Merci pour le suivi, c'est super sympa!!
En fait j'ai exactement le même résultat, mais il n'est pas correct. Par exemple les conduites 6 et 7 qui partent vers le Nord ne devraient pas être selectionnées. A l'inverse la 127 ou la 10517 devraient l'être...
Par contre sur votre image il apparait clairement un problème de sens de numérisation des lignes.
Je vais corriger cela et refaire tourner le SQL, je reviens très vite!
Encore merci!!

Hors ligne

 

#12 Wed 27 June 2018 18:11

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

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)

Hors ligne

 

#13 Wed 27 June 2018 18:26

zang
Participant actif
Date d'inscription: 4 Oct 2005
Messages: 55

Re: [POSTGIS] Identifier tronçons isolés suite à fermeture de vannes

Waouuu super, ça fonctionne parfaitement. Je ne sais pas où j'avais la tête, c'est vraiment la base quand on utilise st_endpoint et st_startpoint...
J'ai effectivement généré un réseau fermé, mais en réalité il y a une entrée dans un réseau donc normalement il ne devrait jamais y avoir de problèmes de redondance.
Mais je vais quand même me pencher sur le LIMIT désormais, vous avez raison de le signaler!!
Encore 1000 merci!!

Hors ligne

 

Pied de page des forums

Powered by FluxBB