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

Question

我有两个表，比如 A 和 B，它们包含两列城市信息:纬度和经度。 A 包含 100,000 条记录，B 包含 1,000,000 条记录。我的目标是找到距离 A 1 公里以内的 B 行(对于 A 中的每一行)。我该如何有效地做到这一点？我的目标是少于 30 分钟。

以下查询需要永远(我相信这是 100,000 * 1,000,000 = 1000 亿行比较的叉积的结果!):

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

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

我做了一行A的测试，每行大约需要5秒。根据我的计算，查询将需要数周时间才能完成执行，这是 Not Acceptable 。

Answer 1

是的，PostGIS 会让事情变得更快，因为它 (a) 知道如何将纬度和经度转换为千米(我将使用下面的 geography type)，并且 (b) 支持 GiST index ，这是 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);