Le portail francophone de la géomatique

SylvaDEES · Wed 01 March 2023 17:11

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.

Sylvain M. · Thu 02 March 2023 08:51

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)

Nicolas Ribot · Thu 02 March 2023 09:23

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

Sylvain M. · Thu 02 March 2023 17:38

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

Nicolas Ribot · Thu 02 March 2023 19:19

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

SylvaDEES · Fri 03 March 2023 16:20

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.

Sylvain M. · Fri 03 March 2023 17:15

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 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)

SylvaDEES · Tue 07 March 2023 13:14

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.

Nicolas Ribot · Wed 08 March 2023 09:01

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

SylvaDEES · Thu 09 March 2023 18:12

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.

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Wed 01 March 2023 17:11

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

#2 Thu 02 March 2023 08:51

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

Code:

#3 Thu 02 March 2023 09:23

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

#4 Thu 02 March 2023 17:38

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

#5 Thu 02 March 2023 19:19

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

#6 Fri 03 March 2023 16:20

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

#7 Fri 03 March 2023 17:15

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

Code:

Code:

Code:

#8 Tue 07 March 2023 13:14

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

#9 Wed 08 March 2023 09:01

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

#10 Thu 09 March 2023 18:12

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

Pied de page des forums