postgresql - ST_DWithin 针对多个 SRID 的优化

Question

在我的 PostgreSQL 9.3 数据库中，我有一个名为 location 的表，其中包含一个用于存储 POINT 几何的 coordinate 列。这些点是使用 SRID 4326 创建的。在某些情况下，我们将这些坐标 ST_Transform 为 SRID 900913，以使用以米为单位的距离过滤它们。

例如，要使用 ST_WDithin 查找坐标在给定坐标 10000 米范围内的所有位置，查询如下所示:

SELECT *
FROM LOCATION 
WHERE ST_DWithin(ST_Transform(location.coordinate, 900913), ST_Transform(ST_GeomFromText('POINT(-74.005941 40.712784)', 4326), 900913), 10000)
ORDER BY ST_Distance_Sphere(location.coordinate, ST_GeomFromText('POINT(-74.005941 40.712784)', 4326))
LIMIT 100

这条语句的查询计划是这样的:

Limit  (cost=19207.98..19208.23 rows=100 width=36)
  ->  Sort  (cost=19207.98..19209.81 rows=729 width=36)
        Sort Key: (_st_distance(geography(coordinate), '0101000020E6100000282D5C56618052C0588E90813C5B4440'::geography, 0::double precision, false))
        ->  Seq Scan on location  (cost=0.00..19180.12 rows=729 width=36)
              Filter: ((st_transform(coordinate, 900913) && '010300002031BF0D000100000005000000D42FBDEAFB765FC187C3AD4ED1EB5241D42FBDEAFB765FC187C3AD4E59FF5241D42FBDEA73635FC187C3AD4E59FF5241D42FBDEA73635FC187C3AD4ED1EB5241D42FBDEAFB765FC187C3AD4ED1EB5241'::geometry) AND ('010100002031BF0D00D42FBDEA376D5FC187C3AD4E95F55241'::geometry && st_expand(st_transform(coordinate, 900913), 10000::double precision)) AND _st_dwithin(st_transform(coordinate, 900913), '010100002031BF0D00D42FBDEA376D5FC187C3AD4E95F55241'::geometry, 10000::double precision))

这有效，但速度很慢。所有涉及的坐标都转换为 SRID 900913，以便能够以米为单位工作。通过测试，我发现删除 ST_Transform 会显着加快此查询的速度。

我还尝试使用 ST_Buffer 创建一个圆形 POLYGON，然后测试 location.coordinate 是否与该多边形相交。为此，我将输入坐标 ST_Transform 为 SRID 900913，使用 ST_Buffer 绘制一个半径为米的圆，然后将该多边形 ST_Transform 为 SRID 4326，以便与我的位置表中的坐标进行比较。查询如下所示:

SELECT *
FROM LOCATION 
WHERE location.coordinate && ST_Transform(ST_Buffer(ST_Transform(ST_GeomFromText('POINT(-74.005941 40.712784)', 4326), 900913), 10000), 4326))
ORDER BY ST_Distance_Sphere(location.coordinate, ST_GeomFromText('POINT(-74.005941 40.712784)', 4326))
LIMIT 100

在我的测试中，第二个查询比第一个查询运行得快得多。它甚至比我使用 ST_DWithin 减去 ST_Transform 的查询版本运行得更快。从我读过的所有内容来看，似乎 ST_DWithin 应该是执行此类搜索的最快方法。这个问题的答案表明我应该能够创建一个转换为不同 SRID 的坐标索引:PosgtreSQL Optimize Query with st_transform, st_makepoint, and st_contains

我通过运行尝试了这个:

CREATE INDEX idx_location_coordinate_900913
 ON LOCATION
 USING gist
 (ST_Transform(coordinate, 900913))
 WHERE coordinate IS NOT NULL;

创建此索引后，我在运行原始查询时没有看到速度提升。我觉得很奇怪，这个命令很快就成功完成了，重建这个索引也很快。 location 表中有几万行，所以我想创建这个索引将是一个耗时的过程。是我创建的不正确吗？

在转换我的点时，我可以做些什么来加快 ST_DWithin 的速度？我的方法是否存在重大缺陷？

编辑:我正在为上面的初始查询添加执行计划。

Answer 1

我建议在 PostgreSQL 中创建一个函数来处理从米到十进制的转换。通过这样做，您可以避免转换每一行，而是 ST_DWithin 函数在 native 投影中工作。

您需要仔细检查数学，我建议将结果与使用 ST_Transform 进行比较，但我很确定这很接近。我使用的函数通常使用英里而不是米，但我快速添加了米的转换。

在函数中，值 3960 是地球直径的估计值，1609.34 处理从英里到米的变化。我不保证它与 ST_Transform 一样精确或准确，但它在性能方面应该好得多，因为它不必转换每一行。

CREATE FUNCTION meters_to_decimal_degrees(meters double precision)
RETURNS double precision AS
$BODY$
    SELECT (($1 * 180 * 1609.34) / ( 3960 * pi() ) ) AS decimal_degrees
$BODY$
LANGUAGE sql IMMUTABLE SECURITY DEFINER
;


ALTER FUNCTION public.meters_to_decimal_degrees(double precision) SET search_path=public, pg_temp;

这样，您的查询可以更改为:

SELECT *
    FROM LOCATION 
    WHERE ST_DWithin(location.coordinate, ST_GeomFromText('POINT(-74.005941 40.712784)', 4326), meters_to_decimal_degrees(10000))
    ORDER BY ST_Distance_Sphere(location.coordinate, ST_GeomFromText('POINT(-74.005941 40.712784)', 4326))
    LIMIT 100

希望对您有所帮助。