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.