时间戳

首页 > 解决方案 > PostGIS 将地理列上的 st_dwithin 转换为 && 而不保留自定义空间参考系统

postgresql - PostGIS 将地理列上的 st_dwithin 转换为 && 而不保留自定义空间参考系统

问题描述

当我在具有 GIST 索引的地理列上使用 st_dwithin 时，解释计划显示索引扫描的缩小条件变为 && 条件。在这种不同的情况下，如果在没有索引的情况下使用全表扫描执行 st_dwithin，则结果集会丢失包含的行。

没有索引的解释计划中的条件是

st_dwithin(
  geog,
  '010100002010A4000000000000006066400000000000000000'::geography,
  '3'::double precision,
  false
)

但是解释计划表明，当列有 GIST 索引时，它变为以下

geog && _st_expand(
  '010100002010A4000000000000006066400000000000000000'::geography,
  '3'::double precision
)

geography 列具有自定义空间参考系统，在转换为 && 条件时未保留该参考系统。自定义空间参考系统是一个球体，其半径为 1 度等于 1 米的大圆距离。所以 (0 0) 的一点距离 (0 1) 有一个距离。

设置代码如下

-- Insert the custom spatial reference system into the db.
insert into spatial_ref_sys values (42000, 'customsrs', 1,
     'GEOGCS[
       "Normal Sphere (r=57.2957795)",
       DATUM["unknown",
         SPHEROID["Sphere",57.29577951308232087679,0]
       ],
       PRIMEM["Greenwich",0],
       CS[ellipsoidal,2],
       AXIS["latitude",north],
       AXIS["longitude",east],
       UNIT["degree",0.0174532925199433]
     ]', '+proj=longlat +ellps=sphere +R=57.29577951308232087679 +no_defs');

-- Create a new table with a geography column in the new spatial reference system.
CREATE TABLE geographytest(gid serial PRIMARY KEY, geog geography(POINT, 42000));

-- Insert some data around the dateline
insert into geographytest (gid, geog) values
(1, 'srid=42000;POINT(179 0)'),
(2, 'srid=42000;POINT(178 0)'),
(3, 'srid=42000;POINT(-179 0)'),
(4, 'srid=42000;POINT(-179 90)'),
(5, 'srid=42000;POINT(0 0)');


-- Select all points within a distance of 3 from POINT(179 0).
--   The expected 3 points are returned, with st_distances of 0, 1 and 2.
select
 gid,
 st_distance(
   geog,
   'srid=42000;POINT(179 0)',
   false
 ),
 st_dwithin(
   geog,
   'srid=42000;POINT(179 0)',
   3,
   false
 )
from
 geographytest
where
 st_dwithin(
   geog,
   st_geogfromtext('srid=42000;POINT(179 0)'),
   3,
   false
 );

-- Create a GIST index on our geography column
CREATE INDEX geographytestindex ON geographytest USING gist (geog);
VACUUM analyze geographytest (geog);

-- Now select again using the same query.
--   Now only one result is returned, the row that has POINT(179 0).
--   The explain plan indicates that the index scan is using 
--   Index Cond: (geog && _st_expand('010100002010A4000000000000006066400000000000000000'::geography, '3'::double precision))
--   when performing the select.
select
 gid,
 st_distance(
   geog,
   'srid=42000;POINT(179 0)',
   false
 ),
 st_dwithin(
   geog,
   'srid=42000;POINT(179 0)',
   3,
   false
 )
from
 geographytest
where
 st_dwithin(
   geog,
   st_geogfromtext('srid=42000;POINT(179 0)'),
   3,
   false
 );

将 st_dwithin 距离增加到 230000 而不是 3 会返回所有三个预期的行，因为这是它们在 srid 4326 中的距离。

当列上存在索引时，如何让 PostGIS 在查询中使用我的自定义空间参考系统？

标签： postgresqlpostgis

解决方案

问题在于_st_expand. 此函数扩展给定地理的边界框。查看代码，我们可以看到使用WGS84半径修改了所需的距离，因此确实忽略了自定义CRS。如果您愿意，可以报告错误。

/* Read our distance value and normalize to unit-sphere. */
distance = PG_GETARG_FLOAT8(1);
/* Magic 1% expansion is to bridge difference between potential */
/* spheroidal input distance and fact that expanded box filter is */
/* calculated on sphere */
unit_distance = 1.01 * distance / WGS84_RADIUS;

推荐阅读