ST_DWithin не использует индекс с нелитеральным аргументом

Я использую PostreSQL 9.3 с PostGIS 2.1.8 на Amazon RDS. У меня есть таблица с именем project_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 записей. Если я запрошу таблицу, чтобы найти все 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 использует индексы, я понимаю, почему это так. По сути, ограничивающая рамка на основе радиуса используется для «предварительной фильтрации» точек-кандидатов для определения возможных совпадений перед выполнением относительно дорогостоящего вычисления расстояния для этих точек.

Мой вопрос, есть ли способ выполнить этот тип поиска, чтобы можно было использовать пространственный индекс? В принципе, способ запроса таблицы с кучей геозон переменного радиуса?


postgresql postgis
person Mike    schedule 01.12.2016    source источник
comment
Вы должны отметить это и попросить переместить его на dba.se.   -  person Evan Carroll    schedule 01.12.2016
comment
@EvanCarroll Что такое dba.se? а почему нужно пометить?   -  person Juan Carlos Oropeza    schedule 01.12.2016
comment
dba.stackexchange.com (чтобы администраторы могли его переместить)   -  person Evan Carroll    schedule 01.12.2016
comment
@EvanCarroll, а почему вы думаете, что нужно переместить?   -  person Juan Carlos Oropeza    schedule 01.12.2016
comment
Более специализированная база пользователей postgresql и postgis   -  person Evan Carroll    schedule 01.12.2016
comment
@EvanCarroll Я не согласен, это хороший форум для postgresql и postgis. И в этом случае я думаю, что более подходящим будет gis.stackexchange.com.   -  person Juan Carlos Oropeza    schedule 01.12.2016
comment
@JuanCarlosOropeza, это не проблема с ST_DWithin, это вопрос о планировщике (или отправке функции pg)   -  person Evan Carroll    schedule 01.12.2016
comment
У меня нет идей по этому поводу. Опять же, я предлагаю вам пометить это и попросить модераторов отправить его на dba.se.   -  person Evan Carroll    schedule 04.12.2016

Ответы (3)


arrow_upward
1
arrow_downward

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_Expand с ST_Envelope(ST_Buffer(geom, radius)).

person Darafei Praliaskouski    schedule 20.04.2018

arrow_upward
0
arrow_downward

Чтобы избавиться от простых вещей, не могли бы вы попробовать установить радиус заброса на double precision,

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

Также вставка вывода

  • \dfS ST_DWithin
  • \dfS _ST_DWithin
person Evan Carroll    schedule 01.12.2016
comment
Радиус приведения к двойной точности не влияет на результаты (пространственный индекс не используется). - person Mike; 02.12.2016
comment
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ st_dwithin (география, география, двойная точность) ВОЗВРАЩАЕТ логическое значение AS 'SELECT $ 1 && _ST_Expand ($ 2, $ 3) И $ 2 && _ST_Expand ($ 1, $ 3) AND _ST_DWithin ($ 1, $ 2, $ 3, true sq)' СТОИМОСТЬ 100; - person Mike; 02.12.2016
comment
СОЗДАТЬ ИЛИ ЗАМЕНИТЬ ФУНКЦИЮ _st_dwithin (география, география, двойная точность, логическое значение) ВОЗВРАЩАЕТ логическое значение AS '$ libdir / postgis-2.1', 'geography_dwithin' ЯЗЫК c НЕЗАМЕНИМАЯ СТРОГАЯ СТОИМОСТЬ 100; - person Mike; 02.12.2016

arrow_upward
0
arrow_downward

Единственный способ, которым я могу справиться с этим, - это создать fence_contain таблицу

 geofence_id    point_id

Конечно, вам понадобятся триггеры при обновлении / создании для геозон и точек, чтобы таблица обновлялась.

person Juan Carlos Oropeza    schedule 01.12.2016