PostgreSQL - 优化基于纬度和经度的连接，比较距离

Question

我有两个表，称为A和B，它们包含有关城市信息的两列：纬度和经度。A包含100,000条记录，B包含1,000,000条记录。我的目标是找到距离A中每一行1公里以内的B行。如何高效地完成这个任务？我希望在30分钟内完成。

以下查询需要很长时间（我认为这是因为需要进行100,000 * 1,000,000 = 100亿次行比较的结果！）：

select *
from A
inner join B
on is_nearby(A.latitude, A.longitude, B.latitude, B.longitude)

is_nearby() 是一个简单的函数，用于查找纬度和经度之间的差异。

我对 A 行的一个数据进行了测试，每行大约需要 5 秒钟。按照我的计算，查询执行需要数周时间，这是不能接受的。

- arimbun

---

我考虑过使用PostGIS，但我不确定它是否会显著提高性能。有人有更好的想法吗？ - arimbun

你有没有看一下右边显示的相关问题的答案（比如这个）？ - j.p.

我尝试了PostGIS的解决方案，它非常好用。谢谢！ - arimbun

Answer 1

是的，PostGIS会使事情更快，因为它（a）知道如何将经纬度转换为公里（我将在下面使用地理类型），并且（b）支持GiST索引，这对于GIS是最优的。

假设您的系统上有可用的PostGIS版本2，请升级您的数据库和表：

CREATE EXTENSION postgis;

-- Add a geog column to each of your tables, starting with table A
ALTER TABLE A ADD COLUMN geog geography(Point,4326);
UPDATE A SET geog = ST_MakePoint(longitude, latitude);
CREATE INDEX ON A USING GIST (geog);
--- ... repeat for B, C, etc.

现在需要找到距离A不到1公里的B行（对于A中的每一行）:

SELECT A.*, B.*, ST_Distance(A.geog, B.geog)/1000 AS dist_km
FROM A
JOIN B ON ST_DWithin(A.geog, B.geog, 1000);

- Mike T

---

我注意到使用上述解决方案后性能显著提高（至少100倍），正是我所需要的。感谢您的反馈！ - arimbun