Le portail francophone de la géomatique

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 (Thu 29 November 2018 21:03)

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

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:

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:

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

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 

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 (Fri 30 November 2018 20:18)