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 !.
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

#1 jeu. 29 novembre 2018 20:07

Rlucas
Membre
Date d'inscription: 20 avril 2018
Messages: 25

Séléction stricte de géométries non intersectées entre deux tables

Bonjour,

Je bute sur une problème qui me parait simple mais que je n'arrive pas à comprendre.
A partir de deux couches de points, je cherche à séléctionner les points d'une couche dont les géométries n'intersectent strictement pas celles de l'autre couche. J'ai donc écrit la requête suivante :

Code:

SELECT DISTINCT table_a.* FROM table_a JOIN 
table_b ON ST_INTERSECTS(table_a.geom, table_b.geom)

Puis pour tester les temps d'execution j'ai essayé la même requête inversée avec ST_DISJOINT :

Code:

SELECT DISTINCT table_a.* FROM table_a JOIN 
table_b ON ST_DISJOINT(table_a.geom, table_b.geom)

La première requête renvoit 125 enregistrements tandis que la seconde 133. Or j'ai 155 enregistrements dans ma table_a.
Je suppose que la différence est due au fait que les fonctions géométriques comparent un à un les enregistrements. Je le conçois pour ST_INTERSECTS qui va me renvoyer un enregistrement si au moins une autre géométrie de la talbe_b l'intersecte.
Mais dans ce cas ST_DISTINCT aurait du me renvoyer le bon nombre de géométries .
Donc comment faire en sorte de séléctionner les géométries qui n'intersectent aucune autre géométrie?

Merci d'avance pour vos lumières.

Dernière modification par Rlucas (jeu. 29 novembre 2018 20:09)

Hors ligne

 

#2 jeu. 29 novembre 2018 20:48

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 732

Re: Séléction stricte de géométries non intersectées entre deux tables

Mettez vous à la place du moteur SQL, et essayez de deviner
le résultat de votre seconde requête.

Celle ci renvoie les éléments de A
qui sont disjoint avec au moins un des éléments de B.
Pas vraiment ce que vous recherchez.

Pour accomplir ce que vous désirez, il faut raisonner en négatif.
Vous joignez aux éléments de A tout les éléments de B
qui les intersectent, ensuite vous ne retenez que ceux n'ayant intersecté aucun élément.

Cela est possible avec une jointure externe (OUTER JOIN).
Un (INNER) JOIN ne renvoie que les couples A et B ayant satisfait le prédicat de jointure,
un OUTER JOIN quand a lui permet d'inclure dans le résultat les éléments de A et/ou de B,
n'ayant pas satisfait le prédicat.

Cette jointure "négative" s'appelle un ANTI JOIN.

Dernière modification par tumasgiu (jeu. 29 novembre 2018 21:03)

Hors ligne

 

#3 ven. 30 novembre 2018 14:08

Rlucas
Membre
Date d'inscription: 20 avril 2018
Messages: 25

Re: Séléction stricte de géométries non intersectées entre deux tables

Bonjour,

Merci pour votre réponse, je crois que j'ai encore un peu du mal avec la logique des jointures, qui n'est pas très intuitive pour appliquer des conditions strictes. J'ai donc repris ma requête avec l'écriture suivante :

Code:

SELECT DISTINCT table_a.* FROM table_a LEFT OUTER JOIN 
table_b ON ST_INTERSECTS(table_a.geom, table_b.geom)

Malheureusement, preuve que je n'ai pas bien compris celle ci me renvoie tous les enregistrements de la table, alors qu'a mon sens elle aurait dû

[joindre] aux éléments de A tout les éléments de B
qui les intersectent, ensuite vous ne retenez que ceux n'ayant intersecté aucun élément.


En cherchant j'ai trouvé la syntaxe suivante qui marche :

Code:

SELECT DISTINCT table_a.* FROM table_a LEFT OUTER JOIN 
table_b ON ST_INTERSECTS(table_a.geom, table_b.geom)
WHERE table_b.id ISNULL

Mais celle-ci n'aurait pas dû renvoyer les enregistrements de la table_a n'intersectant pas ceux de la table_b ou l'identifiant est nul, c'est à dire tous les enregistrement de la table_a (table_b.id étant non null)?

Hors ligne

 

#4 ven. 30 novembre 2018 14:34

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1131

Re: Séléction stricte de géométries non intersectées entre deux tables

Rlucas a écrit:

Bonjour,

Merci pour votre réponse, je crois que j'ai encore un peu du mal avec la logique des jointures, qui n'est pas très intuitive pour appliquer des conditions strictes. J'ai donc repris ma requête avec l'écriture suivante :

Code:

SELECT DISTINCT table_a.* FROM table_a LEFT OUTER JOIN 
table_b ON ST_INTERSECTS(table_a.geom, table_b.geom)

Malheureusement, preuve que je n'ai pas bien compris celle ci me renvoie tous les enregistrements de la table, alors qu'a mon sens elle aurait dû

[joindre] aux éléments de A tout les éléments de B
qui les intersectent, ensuite vous ne retenez que ceux n'ayant intersecté aucun élément.


En cherchant j'ai trouvé la syntaxe suivante qui marche :

Code:

SELECT DISTINCT table_a.* FROM table_a LEFT OUTER JOIN 
table_b ON ST_INTERSECTS(table_a.geom, table_b.geom)
WHERE table_b.id ISNULL

Mais celle-ci n'aurait pas dû renvoyer les enregistrements de la table_a n'intersectant pas ceux de la table_b ou l'identifiant est nul, c'est à dire tous les enregistrement de la table_a (table_b.id étant non null)?


Bonjour

(oubliez le outer, mot-clé inutile (optionnel en fait) pour les jointures)

C'est le principe du left join: renvoyer tous les éléments de la table left, a gauche. C'est en ce sens que c'est pratique, puisqu'on conserve l'intégralité des données de la table de gauche, et pour celle de droite: on a les valeurs quand la condition de jointure match, et on a null pour toutes les lignes de la table gauche qui ne match pas la jointure.

Du coup, si on veut trouver les éléments de la table de gauche qui ne respectent pas la condition de jointure, il suffit d'appliquer un WHERE dans la requete, en filtrant les colonnes de la table de droite: celles qui ont une valeur nulle.

Nicolas

Hors ligne

 

#5 ven. 30 novembre 2018 18:39

Rlucas
Membre
Date d'inscription: 20 avril 2018
Messages: 25

Re: Séléction stricte de géométries non intersectées entre deux tables

Merci pour les explications, c'est plus clair maintenant. Je pensais que la clause WHERE dans une jointure s'appliquait aux données en entrée de la fonction, pas en sortie.

Dernière question, j'ai essayé de faire une requête pour supprimer les doublons géométriques d'une table. J'ai une table de polylignes à partir de laquelle j'ai généré des startpoints et endpoints. Comme certains lignes sont jointives, j'ai des doublons géométriques.
Du coup j'ai pensé utiliser les jointures nouvellement maitrisées :

Code:

WITH start_point AS (
SELECT id, ST_Startpoint(ST_Linemerge(geom)) AS geom
FROM lignes
),
end_point AS (
SELECT id, ST_Endpoint(ST_Linemerge(geom)) AS geom
FROM lignes
),
end_point_left_outer AS (
SELECT DISTINCT end_point.* FROM end_point
LEFT OUTER JOIN start_point ON ST_EQUALS(end_point.geom, start_point.geom)
WHERE start_point.id ISNULL
),
start_point_right AS (
SELECT DISTINCT start_point.* FROM start_point
RIGHT JOIN end_point ON ST_EQUALS(start_point.geom, end_point.geom)
),
start_endpoint AS (
SELECT * FROM end_point_leftouter
UNION
SELECT * FROM start_point_right

Mais certains des startpoints ou endpoints ont disparu... Mais en faisant une recherche je ne trouve aucune documentation pour la suppression des doublons géométriques dans une table à partir des géométries (et non des id). Je vais donc ouvrir un nouveau sujet sur le forum.
Merci encore pour les explications, les tutos trouvés sur le net étaient un peu succints sur la logique du moteur SQL.
Bon weekend!

Hors ligne

 

#6 ven. 30 novembre 2018 19:15

Nicolas Ribot
Moderateur
Lieu: Toulouse
Date d'inscription: 9 sept. 2005
Messages: 1131

Re: Séléction stricte de géométries non intersectées entre deux tables

Bonsoir,

Sur le net, vous trouverez des infos sur l'interprétation des requetes SQL.
SQL n'est pas un langage de programmation; mais un langage déclaratif, qui donne des instructions générales au système pour récupérer des données depuis des tables.

Quand on écrit:

Code:

select fonction(t.colonne)
from matable t join autretable t2 on ...
where t.colonne = ...

Le systeme regarde d'abord le from, pour lister les tables à utiliser, puis regarde les jointures pour voir comment joindre les tables, puis applique le where pour virer toutes les lignes qui ne matchent pas, et enfin realise le select, c'est a dire va chercher les colonnes demandées.
Quand la colonne est le résultat d'une fonction (fonction(t.colonne) ici), la fonction est appelée sur chaque ligne, APRES le where donc.

Pour filtrer les doublons geo, plusieurs solutions (il y a des threads entiers sur la meilleure facon de faire sur le net):
Il faut d'abord comprendre ce qui est fait quand on écrit t.geom = t1.geom (opérateur égal).
Dans Postgis, l'opérateur = entre geom est défini sur la bounding box de l'objet (bbox), pas sur l'objet lui meme !

Pour tester l'egalité au sens topologique, postgis dispose de st_equals(geom1, geom2) et st_orderingEquals(geom1, geom2) (https://postgis.net/docs/manual-2.3/ST_ … quals.html).

Quand on veut virer des doublons geo, il faut alors se demander si les objets de la couche en question peuvent occuper la meme bounding box:
  • si oui: il faut virer les doublons en comparant les objets eux-meme, soit avec st_equals, soit en comparant la représentation textuelle des objets geo (en les encodant en hexa par exemple)
  • si non, alors deux objets ne pouvant occuper la meme bounding box, on peut identifier les doublons: ce sont les geom qui ont la meme bounding box.

Dans votre cas, comme ce sont des points, bbox et geom sont equivalentes. On peut identifier les points identiques: geom = geom.

Pas besoin de faire tant de jointures pour identifier les doublons dans votre cas:

1, unir dans une meme table start et end point
2, selectionner les points de la table, group by geom: (dans le group by, l'opérateur geom = geom sera utilisé)
3, garder l'id que vous voulez sur les n id qui seront attribués aux points identiques:

Code:

with tmp as (
  select *
  from (
       values
              (11, 'LINESTRING(0 0, 1 0)'::geometry),
              (22, 'LINESTRING(1 0, 1 1)'::geometry),
              (33, 'LINESTRING(1 1, 0 1)'::geometry),
              (44, 'LINESTRING(0 1, 0 0)'::geometry)
       ) as v(id, geom)
), tmp1 as (
  select id, st_startpoint(geom) as geom
  from tmp
  UNION ALL
  select id, st_endpoint(geom) as geom
  from tmp
) select array_agg(id) as ids,
        (array_agg(id))[1] as id, -- on peut prendre un id de ligne existant (le premier dans le tableau des id pour un meme point)
         row_number() over () as newid, -- ou générer un nouvel identifiant, non lié aux lignes, en utilisant les windows functions (row_number ici):
         st_astext(geom)
  from tmp1
  group by geom;

ids    id    newid    st_astext
{11,44}    11    1    POINT(0 0)
{44,33}    44    2    POINT(0 1)
{11,22}    11    3    POINT(1 0)
{33,22}    33    4    POINT(1 1)

Nicolas

Hors ligne

 

#7 ven. 30 novembre 2018 20:08

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 732

Re: Séléction stricte de géométries non intersectées entre deux tables

Nicolas Ribot a écrit:

Bonsoir,


SQL n'est pas un langage de programmation; mais un langage déclaratif, qui donne des instructions générales au système pour récupérer des données depuis des tables.


Si en fait, muni des CTE recursives et des WINDOW FUNCTIONS, SQL est Turing complet ![1]
Bon c'est pas très pratique comme langage  pour écrire par exemple un système d'exploitation,
mais c'est virtuellement faisable.
Enfin bon c’était surtout pour montrer que je connais le mot Turing complet [2].

Nicolas Ribot a écrit:

Quand on écrit:

Code:

select fonction(t.colonne)
from matable t join autretable t2 on ...
where t.colonne = ...

Le systeme regarde d'abord le from, pour lister les tables à utiliser, puis regarde les jointures pour voir comment joindre les tables, puis applique le where pour virer toutes les lignes qui ne matchent pas, et enfin realise le select, c'est a dire va chercher les colonnes demandées.
Quand la colonne est le résultat d'une fonction (fonction(t.colonne) ici), la fonction est appelée sur chaque ligne, APRES le where donc.


Je crois que l'incompréhension de beaucoup de gens qui débutent
vient du fait qu'on aborde les jointures avec la "notation MySQL"
(je sais pas si c'est le terme exact, mais je crois que c'est ce moteur
qui l'a introduit) :

Code:

SELECT * FROM a,b WHERE a.un_attribut = b.autre_attibut

Quand on se retrouve ensuite avec une jointure standard,
on observe que le test de jointure se fait avec la clause ON et
on se dit naturellement que le clause WHERE est équivalente,
et du coup on ne comprends plus très bien le déroulement des requêtes.


Nicolas Ribot a écrit:

Pour filtrer les doublons geo, plusieurs solutions (il y a des threads entiers sur la meilleure facon de faire sur le net):
Il faut d'abord comprendre ce qui est fait quand on écrit t.geom = t1.geom (opérateur égal).
Dans Postgis, l'opérateur = entre geom est défini sur la bounding box de l'objet (bbox), pas sur l'objet lui meme !

Pour tester l'egalité au sens topologique, postgis dispose de st_equals(geom1, geom2) et st_orderingEquals(geom1, geom2) (https://postgis.net/docs/manual-2.3/ST_ … quals.html).


Il me semble qu'à partir de Postgis 2.4, l'opérateur = teste bien l'égalité stricte des géométries et non pas celles des bounding boxs ([3]).

[1]https://stackoverflow.com/questions/900 … g-complete
[2]https://fr.wikipedia.org/wiki/Turing-complet
[3]http://blog.cleverelephant.ca/2017/09/p … ators.html

Dernière modification par tumasgiu (lun. 03 décembre 2018 17:47)

Hors ligne

 

#8 ven. 30 novembre 2018 20:10

Rlucas
Membre
Date d'inscription: 20 avril 2018
Messages: 25

Re: Séléction stricte de géométries non intersectées entre deux tables

Quand la colonne est le résultat d'une fonction (fonction(t.colonne) ici), la fonction est appelée sur chaque ligne, APRES le where donc.


La fonction ST_INTERSECTS est appellée après le WHERE? Mais dans ce cas si la condition est id ISNULL, celle ci ne devrait éliminer aucune ligne vu que l'id est une clé primaire NOT NULL?
Et comment peut-il 

regarde[r] les jointures pour voir comment joindre les tables


Sans appliquer la fonction ?


Sinon je n'aime pas trop les fonctions d'aggrégat (GROUP BY), celles-ci interdisant l'utilisation du "table.*" fort pratique lorsque l'on a des tables comportant des dizaines de colonnes à inclure sous la forme table.col1, table.col2.... et donc très fastidieuses à écrire.

En revanche les requêtes de récupération des id avec les regex et les fonctions de fenétrage sont très classes!

J'imagine donc à la lecture que pour faire une requête sans utiliser de GROUP BY, il va falloir passer par un rank over pour ne séléctionner que le premier résultat (par exemple) parmi les entités s'intersectant...

Merci beaucoup pour toutes ces précisions!

PS : une idée de pourquoi l'union d'un left outer join et d'un right join supprime des éléments? (cf. https://sql.sh/cours/jointures)

Dernière modification par Rlucas (ven. 30 novembre 2018 20:18)

Hors ligne

 

#9 ven. 30 novembre 2018 21:18

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 juil. 2010
Messages: 732

Re: Séléction stricte de géométries non intersectées entre deux tables

Nicolas parlait des fonctions appelées dans la clause SELECT.
Schematiquement l'odre d'évaluation d'une requête est :
FROM (JOIN) -> WHERE -> GROUP  BY -> SELECT

Hors ligne

 

Pied de page des forums

Powered by FluxBB

Partagez  |