Discussion :

[Bettylolz]

Bonjour,

Je me lance dans les bases de données spatiales et donc je dois faire des requetes SQL spatiales.
Je manipule le SQL régulièrement pour faire du data-management mais je ne pensais pas que la dimension "spatiale" me perdrait aussi vite.

Alors... je vous explique mon pb: J'ai une couche de coordonnées géographiques (environ 9000 points) -qu'on appelera "couche1"- et une couche de polygones (environ 2500 polygones), qu'on appelera "couche 2".
Je veux récupérer la distance entre chaque point de la couche1 et le polygone(couche 2) le plus proche.
Idéalement je voudrais afficher une colonne dans une nouvelle table avec l'identifiant de ma couche 1, l'identifiant de ma couche 2 le plus proche des coordonnées de ma couche 1 et la distance entre les 2.

Voici ma requete:

NB: J'ai fait au préalable un st_transform sur les geom de mes 2 couches pour avoir la mm projection (WGS 84: 4326)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
create table public.MERGE as
SELECT a.id_couche1,min(ST_Distance(a.geom2_couche1, b.geom2_couche2,TRUE)) as distance
    FROM public."COUCHE1" as a,public."COUCHE2" as b 
	group BY a.id_couche1;

Le souci c'est que (i) ca tourne pendant des heures et ca c'est pas possible parce qu'il faut que je le fasse au moins 50 fois et (ii) je ne peux pas afficher mon identifiant couche2 (Enfin quand je le fais il refuse de tourner si je le mets pas dans "group by" et du coup j'ai les distances pour les 2500 polygones pour chaque point de ma couche1).

Avez-vous des astuces/solutions/corrections qui pourraient m'aider?

Merci beaucoup.

[ced]

Bonjour,

Pour une première requête spatiale sous PostGIS, vous n'avez pas choisi la plus facile à réaliser.
Tout d'abord, si vous voulez que les performances de la requête soient acceptables, il faut impérativement indexer spatialement les géométries qui sont dans la requête (geom2_couche1 et geom2_couche2). Sans ça, ça va être une catastrophe en temps d'exécution.

Une fois que c'est indexé, alors vous pouvez faire une recherche du plus proche voisin (KNN) entre les deux couches. Il y a plusieurs façon d'écrire ce genre de requêtes, en voici une :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT a.id_couche1, pr.distance
FROM public."COUCHE1" AS a
JOIN LATERAL (
    SELECT ST_Distance(a.geom2_couche1, b.geom2_couche2) AS distance
    FROM public."COUCHE2" AS b
    ORDER BY a.geom2_couche1 <-> b.geom2_couche2
    LIMIT 1
) AS pr ON TRUE;

Attention ! Un point qui tombe à l'intérieur d'un polygone renvoie une distance égale à zéro (ce qui est parfaitement compréhensible, vu qu'il est à l'intérieur du polygone).
À tester et à adapter à votre cas.

Bon courage,
ced

[Bettylolz]

Alors c'est génial ca marche !!!! Merci beaucoup pour votre retour :-) :-) :-) par contre maintenant je veux comprendre ;-)

Alors, en effet j'ai vu qu'il me faudrait crééer des index pour améliorer les performances mais ne sachant pas je n'avais pas crééé l'index sur la bonne variable.
Merci donc de cette précision :-)

Quand je créé l'index sur geom pour ma couche 2 j'ai un message d'erreur:

NB: la couche 2 sort de BDTOPO

ERROR: ERREUR: la taille de la ligne d'index, 4144, dépasse le maximum pour un btree de version 4, soit 2704, pour l'index « idx_geom_sport »
DETAIL: La ligne d'index référence le tuple (143,2) dans la relation « couche2 ».
HINT: Les valeurs plus larges qu'un tiers d'une page de tampon ne peuvent pas être
indexées.
Utilisez un index sur le hachage MD5 de la valeur ou passez à l'indexation
de la recherche plein texte.
SQL state: 54000

Mais bon ca marche quand mm sans ca :-)

Par contre pourriez-vous m'expliciter les lignes de commande de votre programme que je n'ai pas saisies? ou corriier si j'ai compris de travers?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*Ca me semble faire l'équivalent d'un left join? afin de garder que les observations de la couche1? */
SELECT a.id_couche1, pr.distance
FROM public."couche1" AS a
 
/*Là on va crééer une sous couche à partir de couche2 pr calculer la distance*/
JOIN LATERAL (
    SELECT b.id_couche2, ST_Distance(a.geom2_couche1, b.geom2_couche2) AS distance
    FROM public."Couche2" AS b
 
/*A partir de là je comprends bcp moins bien*/
    ORDER BY a.geom2_couche1 <-> b.geom2_couche2 /*C'est pour faire la correspondance entre les géométries?*/
    LIMIT 1 /*pkoi une limite à 1?*/
 
) AS pr ON TRUE; /*??*/

Question subsidiaire: le fait que ma distance soit exprimée en degrés c'est dû à la projection choisie?

Merci infiniment pour votre aide.

Une très belle fin de journée à vous.

[ced]

Attention ! Un index spatial n'est pas un index de type BTREE (indexation par défaut quand on crée un index sans préciser le type). Il faut créer un index de type GIST, comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX couche2_geom2_idx ON public."COUCHE2" USING gist(geom2);

Normalement, il n'y aura plus de message d'erreur et les requêtes spatiales vont pouvoir utiliser ces index GIST (elles ne feront rien avec un index BTREE). Donc refaites tous vos index sur vos géométries avec le type GIST.

Pour plus d'explication sur la requête, c'est une jointure latérale, qui permet d'utiliser des éléments de la requête principale. Voici ce qu'en dit la documentation de PostgreSQL :

Le mot clé LATERAL peut précéder un élément sous-SELECT de la clause FROM. Ceci permet au sous-SELECT de faire référence aux colonnes des éléments du FROM qui apparaissent avant lui dans la liste FROM. (Sans LATERAL, chaque sous-SELECT est évalué indépendamment et donc ne peut pas faire référence à tout autre élément de la clause FROM.)

Comme c'est une jointure, il lui faut une condition (ON...), mais comme elle n'a ici aucun intérêt, il suffit de mettre ON TRUE;.

Le ORDER BY ... permet de faire la recherche KNN (K plus proches voisins) des deux géométries en utilisant leur indexation spatiale. Et le LIMIT 1, c'est pour ne garder que le voisin le plus proche (le premier voisin en distance).
Mais vous pourriez ramener les K plus proches voisins (avec un LIMIT K) de chaque point (d'où l'acronyme KNN pour "K Nearest Neighboors").

J'espère que c'est plus clair.

ced

[Bettylolz]

C'est limpide
Merci beaucoup pour votre réactivité, vos explications et le temps que vous m'avez accordé :-)

Bonne fin de journée

[escartefigue]

Attention : l'utilisation de la clause LIMIT ne permet pas de gérer les ex-aequo. Il est préférable de calculer le rang avec RANK ou DENSE_RANK

[ced]

Exact.
Dans le cas présent, comme on veut juste la distance minimale d'un point au polygone le plus proche, peut importe les ex-aequo (que le point soit à équidistance de deux polygones ne pose pas de problème)...

À noter que depuis PostgreSQL 13, les ex-aequo peuvent être récupérés avec la clause FETCH FIRST... WITH TIES à la place de la clause LIMIT.

[tengueu-f]

Bonjour,

merci pour les informations que vous avez partagé dans cette discussion.

Actuellement, je suis une requête a peu prêt similaire. Je pense que votre exemple m'aidera à résoudre le problème que je rencontre. Cependant, je travail sur PostgreSQL du cout, on me dit que l'opérateur "<->" n'existe pas.

Connaissez vous l'équivalent pour ce système ?

[ced]

Bonjour,

Si, cet opérateur est bien présent dans PostgreSQL depuis très longtemps pour les données de type géométrie.
Quelle est votre version de PostgreSQL et quel est le type des colonnes sur lesquelles vous essayez d'appliquer cet opérateur ?

ced

[tengueu-f]

j'essaye de l'appliquer sur des champs de type st_geometry. Pour la version de postgreSQL je ne suis pas très sur de laquelle c'est, je me renseigne chez mon admin.

voilà a quoi ressemble ma requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
SELECT a.num_acc, pr.distance
FROM transports.baac_caracteristiques AS a
JOIN LATERAL (
    SELECT ST_Distance(a.shape, b.shape) AS distance
    FROM geovoie.calibr_filaire AS b
    ORDER BY a.shape <-> b.shape
    LIMIT 1
) AS pr ON TRUE;

l'objectif est de lier chaque point à la voie la plus proche

[ced]

Et le message d'erreur renvoyé ?

[tengueu-f]

ERROR: ERREUR: l'opérateur n'existe pas : st_point <-> st_geometry
LINE 6: ORDER BY a.shape <-> b.shape
^
HINT: Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.


SQL state: 42883
Character: 200

[tengueu-f]

Ma table transport.baac_caracteristique, en plus d'avoir un champ shape(st-geometry) a aussi des champs correspondants au coordonnées X et Y (latitude et longitude). Cependant, la table geovoie.calibr_filaire à juste comme champs spatial shape (st_geometry)

[ced]

Il y a un souci avec le type des colonnes SHAPE...
Normalement, les colonnes contenant des géométries (que ce soit des points, des lignes, des polygones) sont de type GEOMETRY.
Et éventuellement avec un sous-type, par exemple GEOMETRY(POINT) pour des points...

Un problème dans la définition des tables ?

L'extension PostGIS est bien installée ?

[tengueu-f]

L'extension de POSTGIS est bien installé je vien de vérifié. c'est la version 3.0.2 qui est installé.

Pour ce qui est du type des données, nous n'avons jamais eu de problème, c'est la toute première fois. On à beaucoup de requetés qui ont fonctionnés et qui fonctionnent sans problème.
Quand j'essaye de convertir pour quitter du type st_geometry ou st_point au type geometry j'ai le même message d'erreur que précédemment.