主页 / dba / 问题 / 182839
Accepted
Randomize
Asked: 2017-08-08 01:42:29 +0800 CST

在两个表中查找最近的地理点？

  • 772

我有两张桌子，在同一个国家（英国）都有纬度/经度列。表大小大约为 80M 和 50M。

除了纬度/经度列，我以这种方式为两个表创建了地理索引：

SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);
UPDATE my_table SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326);
CREATE INDEX my_table_geom_idx ON my_table USING gist(geom);

为了在 0.1 英里内找到 80M 相对于 50M 的表中最近的点，我运行如下：

   SELECT A.latitude, A.longitude, B.latitude, B.longitude,
   FROM my_table_50 AS A, my_table_80 AS B
   where ST_Distance(A.geom, B.geom) < 0.1609 -- 1 mile / 10
   ORDER BY ST_Distance(A.geom, B.geom) ASC LIMIT 1;

查询运行速度很慢（几乎是笛卡尔 50M X 80M）。

有没有办法加快速度？

另外，对这样的问题使用“postgis 地理索引”真的有用吗？使用“毕达哥拉斯定理”就足够了（就像在这里选择的答案https://stackoverflow.com/questions/1664799/calculating-distance-between-two-points-using-pythagorean-theorem一样），因为我期待距离的方式比地球半径短得多，否则可能会导致一些错误？

postgresql spatial

1 个回答

  1. Best Answer

    不要使用ST_Distance. 它永远不会使用索引。而是将 KNN 距离与<->一起使用，并ST_DWithin尽可能使用。

    为了在 0.1 英里内找到 80M 相对于 50M 的表中最近的点，我运行如下：

    要找到最近的点，你可以做..

    SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
ORDER BY A.geom <-> B.geom
LIMIT 1

    要找到一英里内的最近点，

    SELECT A.latitude, A.longitude, B.latitude, B.longitude,
FROM my_table_50 AS A, my_table_80 AS B
WHERE ST_DWithin(A.geom, B.geom, 1609.34)
ORDER BY A.geom <-> B.geom
LIMIT 1

    此外，除非你的 postgis 是旧的，否则永远不要这样做

    SELECT AddGeometryColumn('my_table_50/80', 'geom', 4326, 'POINT', 2);

    从文档中，

    已更改：2.0.0 此函数不再更新 geometry_columns，因为 geometry_columns 是从系统目录中读取的视图。默认情况下，它也不创建约束，而是使用 PostgreSQL 的内置类型修饰符行为。因此，例如使用此函数构建 wgs84 POINT 列现在等效于：ALTER TABLE some_table ADD COLUMN geom geometry(Point,4326);

    确保在上面你有一个关于 A.geom 和 B.geom 的空间/gist 索引。并且，考虑将它们都聚集在这些索引上。

    • 3

相关问题