在 PgSql 中，在庞大的数据集中找到最近邻的最佳查询是什么？

Question

我有一个巨大的表（大约4000万行），称为nearest_spot，表示行（以linestring格式）和它们所到的最近点（大约有1500个不同的点，存储在另一个表中）。最近的_点表如下所示：

 data_id || spot_id || spot_name || link_geom 

其中data_id为主键，spot_id是spot表主键的外键，spot_name是spot名称（我知道冗余不好但我不允许修改数据库）和link_geom是行坐标。

数据库位于PostgreSQL 10.6、PostGIS 2.5中，链接geom列有一个基本索引，最近的点表已经进行了真空分析。

我的目标是尽可能快地找到数据记录中某个点的最近邻居（在此表中）。

我已经知道如何找到最近的邻居，我的问题是找到它需要的时间。我对PostgreSQL和PostGIS非常陌生，我一直在阅读他们的文档，阅读了很多关于KNN优化的主题，我一直在寻找最有效的答案，但我无法在5分钟内得到结果（有时会达到30分钟），即使只搜索一行。我尝试过的不同查询如下：

SELECT *
FROM( SELECT A.position, B.spot_id
      FROM data A, nearest_spot B
      WHERE A.id = 1
      AND ST_DWithin(A.position,B.link_geom,20)
      ORDER BY A.position <-> B.link_geom
      LIMIT 10;)
ORDER BY ST_Distance(A.position,B.link_geom)
LIMIT 1;

SELECT *
FROM( SELECT A.position, B.spot_id
      FROM data A, nearest_spot B
      WHERE A.id = 1
      AND ST_Buffer(A.position,20) && B.link_geom
      ORDER BY A.position <-> B.link_geom
      LIMIT 10;)
ORDER BY ST_Distance(A.position,B.link_geom)
LIMIT 1;

SELECT *
FROM( SELECT A.position, B.spot_id
      FROM data A, nearest_spot B
      WHERE A.id = 1
      AND ST_Intersects(ST_Buffer(A.position,20), B.link_geom)
      ORDER BY A.position <-> B.link_geom
      LIMIT 10;)
ORDER BY ST_Distance(A.position,B.link_geom)
LIMIT 1;

我用

我还使用了有关空间索引的文档，以及有关 的文档

编辑：我意识到我所有的坐标都存储为几何（SRID 4326），所以ST_DWithin调用，虽然有很好的语法，返回的不是20米内的所有行，而是20度内的所有行（地球），所以事实上我的ST_DWithin没有让结果集变得更小，这可能是它花了这么长时间的最大原因之一，ST_Buffer也是如此。我将尝试将所有坐标转换为地理（与::地理），然后将它们与米一起使用，希望我能看到改进




Answer 1

是否需要由数据库执行？我认为最快的方法可能是将1500个点加载到空间索引中，例如KD树、四叉树或R树。然后在40M点上迭代，并在索引中搜索最近的邻居。

不费吹灰之力，您应该能够每秒执行 100,000 到 500,000 次 NN 搜索，因此 40M NN 搜索大约需要 2 到 5 分钟。