postgresql - ST_DWithin 不使用带有非文字参数的索引

Question

我在 Amazon RDS 上使用 PostreSQL 9.3 w/PostGIS 2.1.8。我有一个名为 project_location 的表，它定义了“geo-fences”(每个本质上都是一个坐标和半径)。地理围栏使用名为“location”的几何列和名为“radius”的双列存储。我在位置列上有一个空间索引。

CREATE TABLE project_location
(
  ...
  location geography(Point,4326),
  radius double precision NOT NULL,
  ...
)
CREATE INDEX gix_project_location_location 
ON project_location USING gist (location);

该表目前有大约 50,000 条记录。如果我查询表格以查找地理围栏包含点的所有 project_locations，例如

SELECT COUNT(*) 
FROM project_location 
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(-84.1000, 34.0000),4326)::geography, radius);

我发现没有使用空间索引。 EXPLAIN 的结果显示如下:

"Aggregate  (cost=11651.97..11651.98 rows=1 width=0)"
"  ->  Seq Scan on project_location  (cost=0.00..11651.97 rows=1 width=0)"
"        Filter: ((location && _st_expand('0101000020E610000066666666660655C00000000000004140'::geography, radius)) AND ('0101000020E610000066666666660655C00000000000004140'::geography && _st_expand(location, radius)) AND _st_dwithin(location, '0101000020E610000066666666660655C00000000000004140'::geography, radius, true))"

但是，如果半径是一个常数值，如下所示

SELECT COUNT(*) 
FROM project_location 
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(-84.1000, 34.0000),4326)::geography, 1000);

空间索引被 EXPLAIN 用作显示

"Aggregate  (cost=8.55..8.56 rows=1 width=0)"
"  ->  Index Scan using gix_project_location_location on project_location  (cost=0.28..8.55 rows=1 width=0)"
"        Index Cond: (location && '0101000020E610000066666666660655C00000000000004140'::geography)"
"        Filter: (('0101000020E610000066666666660655C00000000000004140'::geography && _st_expand(location, 1000::double precision)) AND _st_dwithin(location, '0101000020E610000066666666660655C00000000000004140'::geography, 1000::double precision, true))"

阅读了 ST_DWithin 如何使用索引后，我明白了为什么会这样。本质上，基于半径的边界框用于“预过滤”候选点以确定可能的匹配，然后再对这些点进行相对昂贵的距离计算。

我的问题是有什么方法可以进行这种类型的搜索以便可以使用空间索引吗？基本上是一种查询具有一堆可变半径地理围栏的表的方法？

Answer 1

PostGIS 允许通过使用功能索引来加速您的查询。我不确定如何在 geography 数据类型中执行此操作，因为那里没有 ST_Expand，但如果您将数据存储在某些墨卡托投影中(例如，SRID= 3857).

想法:

• 生成一个围绕您的点扩展 radius 单位的框；
• 在这些框上建立索引；
• 查询用户对这些盒子的点数；
• 通过精确的半径重新检查。

在您的 project_location 表上:

create index on project_location using gist (ST_Expand(location, radius));

现在您可以像使用索引几何列一样使用 ST_Expand(location, radius)。

select count(*) from project_location where ST_Intersects(ST_Expand(location, radius), <your_point>) and ST_Distance(location, <your_point>) < radius;

现在您将跳过 ST_DWithin，因为您希望重新检查永远不会尝试使用索引，并在几何函数上使用索引。

对于 geography，您可以尝试使用 ST_Envelope(ST_Buffer(geom, radius)) stub ST_Expand。