Поиск КНН по отдельной категории в PostGIS/Постгреса


У меня Постгреса стол, etablissements с миллионами строк, и geom (st_point) колонки , пространственного индексирования (через корень). Я использую расширение с PostGIS. Эти строки могут иметь разную категорию, activite_principale.

Я хочу найти от заданной фиксированной точки (координаты) ближайший предприятий за 5 или 6 разных категорий, называемых "activite_principale" (1 ближайшие компании в рубрике).

Вот что я сделал сейчас и что работает :

    (WITH closest_candidates AS (
      SELECT
        ent.id,
        ent.name,
        ent.geom
      FROM
        geo_data.etablissements ent
      WHERE ent.activite_principale = '1071C'
      ORDER BY
        ent.geom <->
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
      LIMIT 10
    )
    SELECT id
    FROM closest_candidates
    ORDER BY
      ST_Distance(
        geom,
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
        )
    LIMIT 1)
    UNION ALL
    (WITH closest_candidates AS (
      SELECT
        ent.id,
        ent.name,
        ent.geom
      FROM
        geo_data.etablissements ent
      WHERE ent.activite_principale = '4711D'
      ORDER BY
        ent.geom <->
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
      LIMIT 10
    )
    SELECT id
    FROM closest_candidates
    ORDER BY
      ST_Distance(
        geom,
        'SRID=4326;POINT (5.4153978921979125 43.271437384501965)'::geometry
        )
    LIMIT 1)
  --  UNION ALL
-- [...]  And so on...

Я разделила предприятий таблица по Геом пространственный индекс и напоролся VACUUM ANALYZE geo_data.etablissements;

Вот результат EXPLAIN ANALYZE после кластеризации.

Планирование запроса shorte и исполнение, но все равно медленно (350-450ms).

Я использовать Postgres 10 & PostGIS с 2.4.

Я не уверен, что мой каскадные Союза все лучшее решение, поэтому я прошу. Моя проблема : как найти х ближайших точек из таблицы Y с различными категориями Z, и вы гарантированно каждая строка является ближайшим X для категории З в PostGIS.

Я не знаю, как улучшить на основе этих объяснений от планировщика запросов. Я могу сделать лучше производительность ?

Примечание : это кросс-пост из ГИС-клиент StackExchange



139
1
задан 13 апреля 2018 в 12:04 Источник Поделиться
Комментарии