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

我有两个表，比如A和B，包含城市信息，有两列：纬度和经度。A包含100000条记录，B包含1000000条记录。我的目标是找到距离A 1公里内的B行（A中的每一行）。我该如何高效地完成这项工作？我的目标是不到30分钟。
下面的查询需要永远（我相信这是100000*1000000=1000亿行比较的叉积的结果！）:

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

is_nearby()只是一个简单的函数，可以发现纬度和经度之间的差异。
我做了一排a的测试，每排大约需要5秒。根据我的计算，查询需要几个星期才能完成执行，这是不可接受的。

最佳答案

是的，PostGIS会使速度更快，因为它（a）知道如何将纬度和经度转换为公里数（我将使用下面的geography type），（b）支持aGiST 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);