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

Question

我在AmazonRDS上使用PostResql9.3w/PostGIS 2.1.8。我有一个名为project_location的表，它定义了“地理Geofence”（每个Geofence本质上都是一个坐标和半径）。地理Geofence使用名为“位置”的几何图形列和名为“半径”的双列存储。我在location列上有一个空间索引。

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条记录。如果我查询表格来查找地理Geofence包含点的所有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如何使用索引后，我理解了为什么会出现这种情况。基本上，基于半径的边界框用于“预过滤”候选点，以在对这些点进行相对昂贵的距离计算之前确定可能的匹配。

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

Answer 1

PostGIS允许通过使用功能索引来加快查询速度。我不知道如何在地理数据类型中进行查询，因为那里没有ST_展开，但是如果您将数据存储在某个墨卡托投影中（例如，SRID=3857），查询将非常简单。

想法：

• 生成一个方框，在你的点周围以半径为单位展开；
• 在这些盒子上建立索引；
• 针对这些框查询用户点；
• 通过精确的半径重新检查。

在您的project_location表上：

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

现在，您可以使用ST_Expand（位置，半径），就像它是索引的几何列一样。

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

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

对于地理，您可以尝试使用ST_Envelope（ST_Buffer（geom，半径））存根ST_Expand。