Suite

Comment trouver efficacement le point le plus proche sur la ligne de date ?

Comment trouver efficacement le point le plus proche sur la ligne de date ?


J'ai une table PostgreSQL 9.1 avec des centaines de milliers de points PostGIS. Pour chacun d'entre eux, j'aimerais trouver le point le plus proche dans un autre tableau de POINTS. Les points du deuxième tableau représentent une grille sur le monde entier, donc je sais qu'il y aura toujours une correspondance à moins d'un degré. C'est la requête que j'utilise jusqu'à présent, qui utilise les index GIST, elle est donc raisonnablement rapide (environ 30 secondes au total).

SELECTIONNER DISTINCT ON (p.id) p.id, ST_AsText(p.pos) , ST_AsText(first_value(g.location) OVER (PARTITION BY p.id ORDER BY ST_Distance(p.pos, g.location::geography)) ) DU point p JOINDRE la grille g SUR ST_DWithin(p.pos::geometry, g.location, 1)

Le seul problème est la date limite. Les points de la grille n'ont que la latitude 180, pas -180. Lorsque vous utilisez la version géométrique de ST_Distance, cela ne renvoie pas de points de l'autre côté de la ligne de date. Par exemple. si p.pos estPOINT(-179.88056 -16.68833)le point de grille le plus proche peut êtrePOINT(180 -16,25), mais la requête ci-dessus ne le renvoie pas. Quelle est la meilleure façon de résoudre ce problème ?

Je ne veux pas vraiment avoir deux coordonnées pour un seul point de grille (-180 et +180). J'ai essayé d'ajouter ma propre fonction qui vérifie ce cas spécifique, mais la requête ne revient pas dans 5 minutes, probablement parce qu'elle ne peut plus utiliser l'index. J'ai également essayé d'utiliser la version géographique de ST_DWithin et cette requête n'est pas revenue non plus après 5 minutes.


OK, j'ai enfin trouvé un moyen de le pirater qui non seulement contourne le problème de la date, mais qui est également plus rapide.

CRÉER OU REMPLACER LA FONCTION le plus proche_grille_point(point géographie(Point)) RETOURNE l'entier AS $BODY$ SELECT pointid FROM ( -- Le cas normal SELECT pointid, emplacement FROM grille WHERE ST_DWithin($1::geometry, location, 1) UNION ALL -- Le dateline hack SELECT pointid, location FROM grid WHERE (ST_X($1::geometry) < -178.75 AND longitude = 180) ) sub ORDER BY ST_Distance($1, location::geography) LIMIT 1; $BODY$ LANGUE SQL STABLE ; SELECT p.id, ST_AsText(p.pos), g.pointid, ST_AsText(g.location) FROM point p JOIN grid g ON near_grid_point(p.pos) = g.pointid

J'ai été très surpris de voir que cette fonction, qui est appelée pour chaque ligne, est plus rapide que la fonction de fenêtre d'origine, mais elle est - plus de 10 fois plus rapide. Les performances de PostgreSQL sont vraiment un art noir !