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é ?

Pages: 1

 

#1 Wed 01 March 2023 17:11

SylvaDEES
Participant actif
Date d'inscription: 12 Jul 2012
Messages: 83

sélection géométrie à distance de x mètre sur plusieurs tables

Bonjour,

je cherche à réaliser une requête spatiale SQL   permettant :

> Dans un même schémas,  de sélectionner  toutes les géométries (Points) de la table "Point_topo" 

     se situant à plus de 50 centimètres  -  des géométries (Points ou Polylignes) de 10 autres tables ( les 10 conditions doivent être remplies ).


L'objectif est d'effectuer un nettoyage-suppression des géomètries isolées  de la table "Point_topo",
via une vue dédiée, ou export au format shape.


Merci pour vos contributions.

Cordialement.

Hors ligne

 

#2 Thu 02 March 2023 08:51

Sylvain M.
Participant assidu
Lieu: Saint-Pierre-des-Nids (53)
Date d'inscription: 8 Sep 2005
Messages: 994

Re: sélection géométrie à distance de x mètre sur plusieurs tables

Voila comment je procéderais :

1) faire un UNION des géométries des 10 tables en remplaçant le champ de geométrie (ligne ou point) par un buffer de 50cm autour de cette géometrie :

Code:

CREATE TABLE tables_union AS
(
SELECT    ST_Buffer(geom, 0.5) as geom
FROM    Table1

UNION

SELECT    ST_Buffer(geom, 0.5) as geom
FROM    Table2

UNION
...

SELECT    ST_Buffer(geom, 0.5) as geom
FROM    Table10
)

2) Selectionner dans la table "Point_topo" les géométries qui n'intersectent pas celles de "tables_union". Par exemple via la fonction ST_Disjoint (inverse de ST_Intersects)

Dernière modification par Sylvain M. (Thu 02 March 2023 08:52)

Sylvain M.

Hors ligne

 

#3 Thu 02 March 2023 09:23

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1542

Re: sélection géométrie à distance de x mètre sur plusieurs tables

Bonjour

Un peu pareil que Sylvain, mais:
  • faire un index spatial apres l'union
  • ne pas faire le buffer: ca prend du temps et pas besoin ici: utiliser st_dwithin(geom, geom, dist)
  • ne pas utiliser st_disjoint, qui ne peut pas utiliser d'index spatial, mais plutot la construction

select ...
from table t
where not exists (
   select null
   from t2
   where st_dwithin(t.geom, t2.geom, 0.5)
)

Nicolas

Hors ligne

 

#4 Thu 02 March 2023 17:38

Sylvain M.
Participant assidu
Lieu: Saint-Pierre-des-Nids (53)
Date d'inscription: 8 Sep 2005
Messages: 994

Re: sélection géométrie à distance de x mètre sur plusieurs tables

En effet, c'est mieux ! big_smile
(l’élève a encore du chemin wink )

Sylvain M.

Hors ligne

 

#5 Thu 02 March 2023 19:19

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1542

Re: sélection géométrie à distance de x mètre sur plusieurs tables

smile

Dernière modification par Nicolas Ribot (Thu 02 March 2023 19:20)

Hors ligne

 

#6 Fri 03 March 2023 16:20

SylvaDEES
Participant actif
Date d'inscription: 12 Jul 2012
Messages: 83

Re: sélection géométrie à distance de x mètre sur plusieurs tables

Bonjour Sylvain, bonjour Nicolas,

merci pour vos précieuses contributions. N'étant pas un maître du sql, j'ai tenté d'interpréter au mieux vos directives,
et j'ai rédigé une ébauche de requête spatiale  ( forcément en erreur ):

> 1ère étape : création de la table d'union de la géomètrie des 10 tables concernées :

CREATE TABLE union_tables AS
(
SELECT gid, insee, the_geom
FROM table 1

UNION
SELECT gid, insee, the_geom
FROM table 2

UNION
SELECT gid, insee, the_geom
FROM table 3

CREATE INDEX union_tables_idx
ON union_tables
USING gist
(the_geom) ;

)

>  erreur au niveau de la rédaction du create index


> 2ème étape : requête spatiale  st_dwithin  sur la table "Pont_topo"  depuis la table  "union_tables" :

SELECT *
from Point_topo a, union_tables b
WHERE ST_dwithin (a.the_geom, b.the_geom, 0.5)


Merci d'avance pour votre aide et vos correctifs.

Cordialement.

Hors ligne

 

#7 Fri 03 March 2023 17:15

Sylvain M.
Participant assidu
Lieu: Saint-Pierre-des-Nids (53)
Date d'inscription: 8 Sep 2005
Messages: 994

Re: sélection géométrie à distance de x mètre sur plusieurs tables

Pour la première étape, l'index spatial doit être calculé après la création de la table.
Là, vous l'avez mis entre les parenthèses du CREATE TABLE.

Voici ce qui devrait marcher :

Code:

CREATE TABLE union_tables AS
(
SELECT gid, insee, the_geom
FROM table 1
UNION
SELECT gid, insee, the_geom
FROM table 2
UNION
SELECT gid, insee, the_geom
FROM table 3
);

Code:

CREATE INDEX union_tables_idx
ON union_tables
USING gist
(the_geom) ;

Moi, sous Spatialite, je fais mes index spatiaux comme ça : "SELECT CreateSpatialIndex('ma_table', 'geom');", mais je pense que votre écriture est bonne aussi. J'aurais juste mis "the_geom" (union_tables_the_geom_idx) dans le nom de l'index, pour indiquer qu'il est lié à ce champ.

Pour la 2e étape, ce doit être quelque chose comme ça (mais j'avoue que je ne maitrise pas trop cette proposition, et du coup, si ça ne marche pas, on aura encore besoin de l'aide de Nicolas wink En l'occurrence, ce que je n'ai pas compris, c'est le "null" dans la requête de Nicolas) :

Code:

SELECT *
FROM Point_topo
WHERE NOT EXISTS (
   SELECT null
   FROM union_tables
   WHERE st_dwithin(Point_topo.the_geom, union_tables.the_geom, 0.5)
)

Dernière modification par Sylvain M. (Fri 03 March 2023 17:19)

Sylvain M.

Hors ligne

 

#8 Tue 07 March 2023 13:14

SylvaDEES
Participant actif
Date d'inscription: 12 Jul 2012
Messages: 83

Re: sélection géométrie à distance de x mètre sur plusieurs tables

Bonjour Sylvain,

merci pour ta réponse aux étapes 1 et 2, c'est beaucoup plus claire.  Je vais tester les deux requêtes.

Pour la 2éme étape, je confirme également que je ne comprends pas la fonction du "null" dans la requête proposée par Nicolas.

Je reviens vers vous dès les tests réalisés.

Cordialement.

Hors ligne

 

#9 Wed 08 March 2023 09:01

Nicolas Ribot
Membre
Lieu: Toulouse
Date d'inscription: 9 Sep 2005
Messages: 1542

Re: sélection géométrie à distance de x mètre sur plusieurs tables

Bonjour,

Dans cette forme SQL 'where exists' ou 'where not exists', on n'est pas interessé par les colonnes du select, mais juste de savoir s'il y a des lignes (rows) retournées par le sous-select.
Dans ce cas, on peut écrire select null, ou select 1, ou tout autre chose.

"Exists" va renvoyer vrai s'il y a des lignes dans la sous requete.
Dans l'exemple, on se sert de l'index spatial pour trouver les lignes qui matchent, puis le NOT exists va prendre le complémentaire de ces lignes, c'est à dire les lignes qui ne matchent pas la condition (ici, points a plus de 50cm).
(https://www.postgresql.org/docs/15/func … ERY-EXISTS)

Nicolas

Hors ligne

 

#10 Thu 09 March 2023 18:12

SylvaDEES
Participant actif
Date d'inscription: 12 Jul 2012
Messages: 83

Re: sélection géométrie à distance de x mètre sur plusieurs tables

Bonjour ,

merci pour votre précieuse contribution d'expert "sql". Après correction puis test des deux requêtes  étape 1  et  étape 2  sur une base postgres  test, puis en base de production,  tout fonctionne bien. Je comprends mieux, désormais, l'utilisation des opérateurs "where exists"  et  "where not exists" qui facilitent les requêtes spatiales.

La requête étape 2 a bien sélectionné tout les Point-topo étant à plus 50 cm des objets/géométrie de la  table UNION.

Bonne journée.

Géomatiquement.

Hors ligne

 

 

Pages: 1

Pied de page des forums

Powered by FluxBB