Forums PostgreSQL.fr

robinson

Membre

Jointure spatiale multiple

Bonjour,

Je cherche à insérer dans une table les identifiants de certaines lignes sous conditions d'intersection géométrique. Ma table représente des canalisations (lignes) dont certaines, secondaires, ne sont reliées que par un seul côté aux canalisations principales. J'ai besoin de récupérer les identifiants des canalisations principales pour les insérer dans une colonne des canalisations secondaires.
J'ai écrit le code suivant (j'écrirai le UPDATE plus tard) :

WITH
canalisation_secondaire AS (
SELECT * FROM canalisations WHERE branchemnt = 'O'
),
canalisation_principale AS (
SELECT * FROM canalisations WHERE branchemnt = 'N'
)
SELECT canalisation_secondaire.idcana, canalisation_secondaire.id, canalisation_principale.idcana FROM canalisation_secondaire, canalisation_principale
WHERE ST_INTERSECTS(canalisation_secondaire.geom, canalisation_principale.geom)

Néanmoins, certaines lignes se croisent et je récupère donc aussi les identifiants des canalisations principales coupant les canalisations secondaires en leur milieu. J'ai donc essayé de rajouter une contrainte pour ne garder que les identifiants des canalisations en bout de ligne. J'ai donc créé une table temporaire avec des points (st_startpoint et endpoint).
Néanmoins, je n'arrive pas à réaliser une double clause where sur une table extérieure :

WITH
canalisation_secondaire AS (
SELECT * FROM canalisations WHERE branchemnt = 'O'
),
canalisation_principale AS (
SELECT * FROM canalisations WHERE branchemnt = 'N'
)
SELECT canalisation_secondaire.idcana, canalisation_secondaire.id, canalisation_principale.idcana FROM canalisation_secondaire, canalisation_principale 
WHERE ST_INTERSECTS(canalisation_secondaire.geom, canalisation_principale.geom)
AND ST_INTERSECTS(canalisation_secondaire.geom, start_end_point.geom)

La table start_end_point est manquante de la clause FROM, et si je la rajoute j'obtiens le double des résultats attendus

La formulation avec une double jointure ne fonctionne pas non plus (elle renvoie plus d'enregistrements qu'attendu) :

WITH
canalisation_secondaire AS (
SELECT * FROM canalisations WHERE branchemnt = 'O'
),
canalisation_principale AS (
SELECT * FROM canalisations WHERE branchemnt = 'N'
)
SELECT canalisation_secondaire.idcana, canalisation_secondaire.id, canalisation_principale.idcana FROM canalisation_secondaire
LEFT OUTER JOIN canalisation_principale ON ST_INTERSECTS(canalisation_secondaire.geom, canalisation_principale.geom)
LEFT OUTER JOIN endpoint_startpoint ON ST_INTERSECTS(canalisation_secondaire.geom, canalisation_principale.geom)

Idéalement, j'aurais souhaité faire porter la condition sur l'intersection stricte de trois tables. Est-ce possible?
Merci d'avance.

Dernière modification par robinson (13/11/2018 21:19:00)

robinson

Membre

Re : Jointure spatiale multiple

Bonjour,

Etant toujours bloqué, je vais reformuler ma question. J'aimerai pouvoir executer une commande du type :

WITH
t1 AS (
SELECT * FROM t01 WHERE x = 'O'
),
t2 AS (
SELECT * FROM t02 WHERE x = 'N'
)
SELECT t1.id, t2.id FROM t1, t2, t3, t4
WHERE ST_INTERSECTS (t1.geom, t2.geom)
AND ST_INTERSECTS (t1.geom, t3. geom)
OR ST_INTERSECTS (t1.geom, t4.geom)

J'ai essayé avec des jointures mais cela ne marche pas non plus... Une bonne âme aurait elle une suggestion?
Merci d'avance.

rjuju

Administrateur

Re : Jointure spatiale multiple

Difficile de vous répondre sur "ça ne marche pas".  Il faudrait au minimum la structure des tables, et quelques exemples de lignes qui posent problème (un affichage des géométries avec st_astext() par exemple si les géométroes ne sont pas trop grosses) ainsi que le problème exact, et idéalement un moyen de reproduire.

---

Julien.
https://rjuju.github.io/

robinson

Membre

Re : Jointure spatiale multiple

Merci pour votre réponse.
Pour préciser, je souhaite sélectionner les couples d'identifiants des tables t1 et t2 où les géometries s'intersectent et qui vérifient aussi deux conditions d'intersection sur les géometries des tables t3 et t4.
Je me demandais surtout quelle était la syntaxe correcte pour exprimer la requête présentée ci dessus. Cette formulation est incorrecte, et celle avec des jointures ne renvoie aucun résultat (alors même que des géometries s'intersectant existent. En effet, je ne vois pas comment exprimer un OR avec des jointures, ni comment réaliser une condition spatiale sur quatre tables différentes. Les tables sont de type :
id(integer)  col1(varchar) col2(varchar) col3(varchar) geom(geometry)

La question est vraiment syntaxique, et malgré plusieurs recherches dans des forums j'ai n'ai trouvé personne traitant un problème similaire.

rjuju

Administrateur

Re : Jointure spatiale multiple

Vous ne pouvez joindre que des ensembles deux par deux.  Vous pouvez joindre t1 à t2, puis le résultat de cette jointure à t3 par exemple, ou joindre t1 à t3, t2 à t3 et joindre ensuite ces deux ensembles (du moins avec la syntaxe JOIN).  C'est de toutes façons ce que fera postgres.

Est-ce que votre problème est une erreur de syntaxe ou un résultat différent de ce que vous voulez ? Naivement je me dis que votre clause WHERE contenant des AND et des OR mériterait bien des parenthèses pour être sur que ça fasse ce que vous pensez que ça devrait faire.

---

Julien.
https://rjuju.github.io/

robinson

Membre

Re : Jointure spatiale multiple

Bonjour,

Merci pour votre réponse. Effectivement je me demandais si il fallait décomposer la requête pour comparer les tables deux à deux ou si c'était possible autrement. En revanche je ne comprends pas trop quand vous parlez de mettre des parenthèses? Il est possible de faire des condition "imbriquées" avec des parenthèses dans un where?

Pour ce qui est de ma requête, j'en suis venu à bout en apprenant que les CTE pouvaient appeller une CTE précédente. J'ai donc construit mes jointures progressivement deux à deux dans les CTE avec WITH.

WITH
canalisation_piquage AS (
SELECT * FROM papaichton_raepa_canalaep_l_ddd WHERE branchemnt = 'O'
),
canalisation_principale AS (
SELECT * FROM papaichton_raepa_canalaep_l_ddd WHERE branchemnt = 'N'
),
startpoint AS (
SELECT ST_Startpoint(st_linemerge(geom)) as geom FROM papaichton_raepa_canalaep_l_ddd
),
endpoint AS(
SELECT ST_Endpoint(st_linemerge(geom)) as geom FROM papaichton_raepa_canalaep_l_ddd
),
startpoint_endpoint AS (
SELECT * FROM startpoint
UNION
SELECT * FROM endpoint
),
condition0 AS (
SELECT canalisation_piquage.idcana, canalisation_piquage.id, canalisation_principale.idcana, ST_INTERSECTION(canalisation_piquage.geom, canalisation_principale.geom) as geom 
FROM canalisation_piquage, canalisation_principale
WHERE ST_INTERSECTS(canalisation_piquage.geom, canalisation_principale.geom)
)
SELECT * FROM condition0, startpoint_endpoint
WHERE ST_INTERSECTS(condition0.geom, startpoint_endpoint.geom)

Merci pour le coup de main.