PostGIS将st_dwithin转换为

Question

当我在地理专栏中使用st_dwithin和GIST索引时，解释计划显示索引扫描上的缩小条件变为

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

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

但解释计划表示，当列有GIST索引时，将其更改为以下内容

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

地理列有一个自定义的空间参照系，在转换为

设置代码如下所示

-- 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距离从3增加到230000将返回所有三个预期行，因为在srid 4326中这是它们的距离。

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

Answer 1

问题在于\u st\u 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;