如何提高 PostgreSQL 上地理数据"GROUP BY" "MAX"？

Question

我有3列公司的地址表：company_id，纬度，伦敦。

我需要与每家公司保持最近的距离。 我为PostgreSQL使用立方体和地球距离扩展。

查询工作正常，但速度很慢：

SELECT
company_id,
MIN(earth_distance(ll_to_earth(lat,lon), ll_to_earth(53.96,83.96))) AS distance
FROM companies
GROUP BY company_id;

GIST索引，如：

CREATE INDEX i_name on companies USING gist(ll_to_earth(lat, lon));

不使用。

如何解决此问题？ 谢谢。

Answer 1

通常，索引可以帮助您从大表中查找几行或提高使用ORDER BY查询的速度。查询需要扫描表中的所有行，并且对所有行执行复杂的计算。因此，索引无法帮助您，因为 Postgres 不使用索引作为预先计算的值。

相反，应将ll_to_earth(lat, lon)的值预先计算到单独的列中，并在查询中使用此列。

Answer 2

使用 PostGIS 索引

您需要一个 KNN 索引。

CREATE EXTENSION postgis;
ALTER TABLE companies ADD COLUMN geog geography(POINT 4326);
UPDATE companies
SET geog = ST_MakePoint(lon,lat)::geogprahy;
SELECT DISTINCT ON (company_id)
company_id,
ST_Distance(company_id,ST_Point(83.96,53.96)::geography)
FROM companies
ORDER BY company_id, geog <=> ST_Point(83.96,53.96)::geography DESC;