selection par proximité postgis [Résolu]

[jonathan1]

Bonjour,

J'utilise postgis et je cherche à récupérer les valeurs d'une colonne d'une table dont les points correspondants sont les plus proches des points d'une autre table. Les tables sont assez grosses (et encore c'était bien pire avant) 20000 et 300000 points environ.

Code :

1
2
SELECT a.*, b."Z" FROM tmp_pts_aep a,mnt_lg b
WHERE Distance(a.the_geom,b.the_geom)=(SELECT min(distance(d.the_geom,c.the_geom)) FROM tmp_pts_aep c, mnt_lg d);

Cette requête est très longue, et je ne sais pas si elle va produire un résultat correct.

Qu'en pensez-vous ?

Merci.

(il n'y a peut être pas beaucoup de géomaticien par ici....)

[xavier-Pierre]

bonjour je ferais comme ça:
au prealable il faut creer des gist index sur les deux tables

Code :

1
2
 CREATE INDEX id1 ON tmp_pts_aep USING GIST ( the_geom); 
CREATE INDEX id2 ON mnt_lg  USING GIST ( the_geom);

puis une requete du style

Code :

1
2
SELECT a.*, b."Z" FROM tmp_pts_aep a,mnt_lg b,min(Distance(a.the_geom,b.the_geom)) FROM tmp_pts_aep a,mnt_lg b WHERE b.the_geom  && 'BOX3D(x( a.the_geom)-val y(a.the_geom)-val
, x( a.the_geom)+val y(a.the_geom)+val)'::box3d

&&: operateur "inclus dans"
BOX3D(x1 y1,) definit un rectangle dont coord sommet haut gauche sont x1 y1 et sommet inf droit x2 y2
dans la requete j'ai centré ce rectangle par rapport au poin t de la premiere table , val etant une valeur arbitraire que tu fixes poour etre sur d'avoir le point le plus proche de la deuxieme table tout en limitant la recherche aux points les plus proches(c'est clair...)
de plus l'operateur && utilise les gist index

[jonathan1]

Merci pour cette réponse.

Effectivement les index risques d'êtres utiles...

Sinon c'est clair pour la box3D, par contre elle est décrite en 2D est-ce normal ??.

voila ce que j'execute :

Code :

1
2
3
CREATE TABLE result AS SELECT a.*, b."X", b."Y", b."Z" FROM tmp_pts_aep a,mnt_lg b
WHERE Distance(a.the_geom,b.the_geom)=(SELECT min(distance(d.the_geom,c.the_geom)) FROM tmp_pts_aep c, mnt_lg d
WHERE b.the_geom && 'BOX3D(x(a.the_geom)-30 y(a.the_geom)-30, x(a.the_geom)+30 y(a.the_geom)+30)::box3D');

mais j'obtiens une erreur :

Code :

1
2
3
4
5
6
7
8
9
ERREUR:  erreur de syntaxe sur ou près de « y »
LIGNE 3 : WHERE b.the_geom && BOX3D(x(a.the_geom)-30 y(a.the_geom)-30,...
                                                     ^
 
********** Erreur **********
 
ERREUR: erreur de syntaxe sur ou près de « y »
État SQL :42601
Caractère : 239

edit : j'actualise le message (zones [code] au fur et à mesure mais je vais bientôt en poster un nouveau car pour suivre ca risque d'être dur...

[jonathan1]

Code :

1
2
3
CREATE TABLE result AS SELECT a.*, b."X", b."Y", b."Z" FROM tmp_pts_aep a,mnt_lg b
WHERE Distance(a.the_geom,b.the_geom)=(SELECT min(distance(d.the_geom,c.the_geom)) FROM tmp_pts_aep c, mnt_lg d
WHERE d.the_geom && 'BOX3D(x(c.the_geom)-30 y(c.the_geom)-30, x(c.the_geom)+30 y(c.the_geom)+30)'::box3d);

Code :

1
2
3
4
5
6
ERREUR:  BOX2DFLOAT4 parser - couldnt parse.  It should look LIKE: BOX(xmin ymin,xmax ymax)
 
********** Erreur **********
 
ERREUR: BOX2DFLOAT4 parser - couldnt parse.  It should look LIKE: BOX(xmin ymin,xmax ymax)
État SQL :XX000

[jonathan1]

Le problème, c'est ce qu'il y a dans les box3D : x(a.the_geom)+30...

Peut on utiliser une fonction à cette endroit ? Si oui comment ?

Merci

[jonathan1]

Une solution (simple) :

Code :

WHERE d.the_geom && ST_expand(c.the_geom, 30)

En attente du résultat..

[jonathan1]

La requête ne m'a retourné que deux lignes : celles pour lesquelles les points d'une couche avaient les coordonnées x et y égales (sur la partie entière) aux points de l'autre couche.
2 points sur environ 20000 !

[xavier-Pierre]

bonjour ,
peut-etre que 30 est une valeur trop faible essaye une valeur plus importante .
autrement tu peux essayer lafonction buffer(the_geom, ta_valeur)

[xavier-Pierre]

re bonjour , je pense qu'il ya un probleme avec la precision des distances ,on a jamais egalité sauf quand la distance est nulle (=0), je n'arrive pas a faire des arrondis qui permettrai de resoudre ce probleme
je te propose une autre solution (un peu bizarre...) mais qui devrait marcher
en gros il faut faire l'egalité sur un champs identifiant de la deuxiieme bas (que j'ai noté id) et dans la sous requete tu ordonne par la distance et tu limite le resultat à la premiere ligne

Code :

1
2
3
4
5
SELECT a.*, b."Z",b.id FROM tmp_pts_aep a,mnt_lg b
WHERE b.id=(SELECT  d.id FROM tmp_pts_aep c, mnt_lg d)
WHERE d.the_geom && buffer(c.the_geom,30) 
order by distance(a.the_geom,b.the_geom) limit 1)
;

[jonathan1]

Merci pour cette réponse,

Je test des choses dans ce genre la...

[jonathan1]

Bonjour,

Encore mieux, 1 ligne retournée.

Code :

1
2
3
4
SELECT a.*, b."X" ,b."Y", b."Z", distance(a.the_geom,b.the_geom)
FROM tmp_pts_aep a,mnt_lg1 b
WHERE b.the_geom && ST_buffer(a.the_geom,300) 
ORDER BY distance ASC LIMIT 1;

Je vais essayer la requête avec lien identifiant...

[jonathan1]

Bonjour,

J'ai résolu mon problème sans fonctionnalité spatiale (en partie seulement).

-création d'une table (ID_DIST) avec les identifiants des points des deux tables et la distance qu'il y a entre chacun deux (dans la limite d'un buffer).
-auto jointure qui retourne l'occurrence ayant la distance minimale pour chaque identifiant d'une des couches.
-mise à jour d'une des tables en fonction de ID_DIST.

(L'élévation que je souhaite obtenir sera possible grâce à une macro fournie par le logiciel que j'utilise.)