Пространственные индексы — быстрый поиск объектов на карте с рабочими примерами SQL

Пространственный индекс сокращает число геометрий, которые база данных проверяет во время поиска по карте, радиусу или зоне. В гайде показано, как устроен такой отбор, как создать индекс в PostGIS, MySQL и SQLite, проверить его через план запроса и избежать ошибок с координатами.

Пространственный индекс сокращает поиск с миллионов объектов до небольшой группы кандидатов

Представим сервис доставки с двумя миллионами адресов. Пользователь открывает карту Москвы и видит участок размером несколько километров. Без пространственного индекса база данных проверяет координаты всех двух миллионов строк. С индексом она быстро находит записи, чьи области расположения пересекают видимую часть карты, а точную геометрию проверяет только у отобранных кандидатов.

Обычный индекс B-tree хорошо работает с одним упорядоченным значением: идентификатором, датой, ценой или фамилией. Пространственный объект занимает две и более координатных оси. Точка имеет X и Y, линия состоит из набора точек, полигон описывает область с внешней границей и возможными отверстиями. Для таких данных нужен индекс, который умеет искать объекты по положению, пересечению, вложенности и расстоянию.

Пространственные индексы применяют в задачах, где запрос содержит географическое или геометрическое условие:

показать здания в текущем окне карты;
найти кафе в радиусе 1500 метров;
определить район, в который попала координата;
выбрать дороги, пересекающие зону ремонта;
найти ближайший пункт выдачи;
связать миллионы GPS-точек с административными полигонами;
отобрать объекты внутри помещения, чертежа или трёхмерной сцены.

PostGIS хранит пространственные данные в типах geometry и geography и обычно использует R-tree, реализованное через индексный метод GiST. MySQL поддерживает R-tree для пространственных столбцов InnoDB и MyISAM. SQLite предоставляет модуль RTree в виде виртуальной таблицы. Эти реализации различаются синтаксисом и набором функций, а общий принцип отбора остаётся близким. Официальные описания доступны в документации PostGIS, MySQL 8.4 и SQLite RTree.

Ограничивающий прямоугольник превращает сложную геометрию в четыре числа

Полигон района может содержать десятки тысяч вершин. Сравнивать каждую вершину со всеми объектами дорого. Индекс хранит упрощённое представление геометрии — минимальный ограничивающий прямоугольник, который часто называют MBR или bounding box.

Для двумерного объекта такой прямоугольник описывают четыре значения:

minX, maxX, minY, maxY

У точки все пары совпадают:

minX = maxX = X
minY = maxY = Y

У линии или полигона прямоугольник охватывает все вершины. Длинная извилистая река и прямоугольная промышленная зона могут иметь близкие bounding box, хотя их реальные формы сильно различаются.

Два прямоугольника пересекаются, когда выполняются четыре условия:

A.maxX >= B.minX
A.minX <= B.maxX
A.maxY >= B.minY
A.minY <= B.maxY

Эта проверка требует нескольких сравнений чисел. Точная проверка пересечения полигонов может включать сотни или тысячи сегментов. Поэтому пространственный запрос обычно проходит два этапа.

Фильтр по индексу. База быстро отбирает объекты с подходящими ограничивающими прямоугольниками.
Точная проверка. Пространственная функция проверяет реальные точки, линии и границы у кандидатов.

Такой процесс называют filter and refine — фильтрация и уточнение. Ложное совпадение на первом этапе считается нормальной частью работы индекса. Например, bounding box дугообразного полигона может накрывать пустое пространство внутри изгиба. Точка попадёт в прямоугольник, хотя останется за пределами самого полигона. Точная функция удалит эту строку из результата.

Индекс отвечает на вопрос «какие объекты могут подходить». Пространственный предикат отвечает на вопрос «какие объекты подходят точно».

R-tree группирует соседние прямоугольники в сбалансированное дерево

R-tree организует bounding box по уровням. Листья дерева ссылаются на строки таблицы. Узлы выше хранят прямоугольники, которые охватывают группы дочерних объектов. Корневой узел охватывает весь набор данных.

Упрощённая схема выглядит так:

Корень
├── северная часть города
│   ├── квартал A
│   │   ├── дом 1
│   │   └── дом 2
│   └── квартал B
│       ├── парк
│       └── школа
└── южная часть города
    ├── квартал C
    └── квартал D

Во время поиска база сравнивает область запроса с прямоугольниками верхнего уровня. Ветки без пересечения пропускаются целиком. Если окно карты находится в северной части города, дерево не читает множество страниц, относящихся к южной части.

R-tree описал Антонин Гуттман в работе 1984 года о динамическом поиске многомерных объектов. Современные СУБД используют разные варианты и инфраструктуру вокруг этой идеи. PostGIS реализует R-tree поверх GiST, SQLite применяет R*Tree, а MySQL создаёт R-tree для поддерживаемых пространственных столбцов.

Скорость зависит от перекрытия прямоугольников. Когда bounding box соседних ветвей сильно пересекаются, запрос проходит по нескольким веткам. Особенно много лишних кандидатов создают длинные диагональные линии, огромные полигоны, геометрии со сложной формой и наборы объектов, которые покрывают почти одну область.

GiST служит каркасом для пространственного R-tree в PostGIS

GiST расшифровывается как Generalized Search Tree. PostgreSQL предоставляет этот метод как универсальный каркас для индексных структур. PostGIS задаёт правила упаковки геометрий, сравнения bounding box, выбора ветвей и обработки пространственных операторов.

Типичная команда выглядит так:

CREATE INDEX places_geom_gix
ON places
USING GIST (geom);

Название gix служит удобным соглашением и не влияет на работу. После построения индекса полезно обновить статистику:

ANALYZE places;

Планировщик PostgreSQL оценивает размер таблицы, избирательность условия и стоимость чтения. Для маленькой таблицы он может выбрать последовательное сканирование. Такой план часто быстрее чтения индекса и отдельных страниц таблицы. Наличие Seq Scan на десяти строках не говорит о поломке индекса.

SP-GiST делит пространство на неперекрывающиеся области

SP-GiST расшифровывается как Space-Partitioned Generalized Search Tree. Метод поддерживает структуры, которые последовательно делят пространство, включая quad-tree и k-d tree. Он подходит для некоторых распределений точек и специальных классов запросов.

Пример для двумерных точек PostGIS:

CREATE INDEX sensors_geom_spgix
ON sensors
USING SPGIST (geom);

Выбор между GiST и SP-GiST проверяют на собственных данных. Распределение координат, доля вставок, тип геометрий и набор запросов влияют сильнее общего названия алгоритма. Для универсального проекта с точками, линиями и полигонами GiST обычно становится первым вариантом.

BRIN хранит сводку по диапазонам страниц огромной таблицы

BRIN группирует последовательные страницы таблицы и хранит общую пространственную область для каждой группы. Индекс занимает мало места и быстро строится. Он приносит пользу, когда строки физически расположены близко к пространственному порядку: например, GPS-точки загружены по маршруту или данные заранее отсортированы по географическому ключу.

CREATE INDEX gps_geom_brin
ON gps_points
USING BRIN (geom);

Таблица со случайным порядком координат создаёт широкие пересекающиеся сводки. В таком наборе BRIN пропускает меньше страниц. Официальное руководство PostGIS рекомендует BRIN для очень больших, пространственно упорядоченных и редко изменяемых таблиц.

Тип geometry и тип geography задают единицы измерения

Большая часть ошибок с расстоянием появляется до построения индекса. Причина связана с системой координат и типом столбца.

geometry рассматривает координаты в плоской системе. Единицы определяет SRID — идентификатор системы пространственных координат. В EPSG:4326 координаты записаны как долгота и широта в градусах. Значение 0.01 означает сотую долю градуса, а не 10 метров.

geography работает с координатами на поверхности Земли. Функции расстояния для этого типа используют метры. Расчёты требуют больше процессорного времени. Запрос вида «объекты в радиусе 2 км» получается понятным и безопасным для широких территорий.

Практический выбор:

Задача	Подходящий тип
Точки по всему миру, расстояние в метрах	`geography(Point, 4326)`
Карта одного города в локальной метрической проекции	`geometry(..., подходящий SRID)`
Топология, пересечения и сложная обработка полигонов	`geometry`
Небольшой учебный проект с координатами GPS	`geography` для радиуса, `geometry` для простых окон карты
Чертёж здания или игровая сцена	`geometry` с собственной системой единиц

Все геометрии в операции должны иметь совместимую систему координат. PostGIS требует одинаковый SRID для многих функций. Преобразование выполняет ST_Transform, а присвоение номера без пересчёта координат выполняет ST_SetSRID. Подмена одной функции другой создаёт неверные координаты.

-- Назначает метку системе координат, числа остаются прежними
SELECT ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326);

-- Пересчитывает координаты из EPSG:4326 в Web Mercator
SELECT ST_Transform(
  ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326),
  3857
);

Порядок координат в WKT и ST_MakePoint для привычных примеров PostGIS записывают как X, Y, то есть долгота, широта:

POINT(37.6173 55.7558)

Перестановка чисел отправит московскую точку в другую часть мира.

Рабочий стенд PostGIS запускается одной командой Docker Compose

Следующий пример создаёт базу PostgreSQL с PostGIS. Он рассчитан на официальный образ postgis/postgis. Тег можно заменить совместимым тегом из списка образов.

Создайте файл compose.yaml:

services:
  db:
    image: postgis/postgis:18-3.6
    environment:
      POSTGRES_DB: geo
      POSTGRES_USER: geo
      POSTGRES_PASSWORD: geo
    ports:
      - "5432:5432"
    volumes:
      - postgis_data:/var/lib/postgresql/data

volumes:
  postgis_data:

Запустите контейнер:

docker compose up -d

Откройте psql внутри контейнера:

docker compose exec db psql -U geo -d geo

Проверьте расширение:

CREATE EXTENSION IF NOT EXISTS postgis;

SELECT PostGIS_Full_Version();

Таблица кафе показывает индексированный поиск по радиусу

Создадим пять точек в Москве. Столбец location имеет тип geography(Point, 4326), поэтому расстояния задаются в метрах.

DROP TABLE IF EXISTS cafes;

CREATE TABLE cafes (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    location geography(Point, 4326) NOT NULL
);

INSERT INTO cafes (name, location) VALUES
(
    'Кафе у Кремля',
    ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography
),
(
    'Кафе у Большого театра',
    ST_SetSRID(ST_MakePoint(37.6187, 55.7601), 4326)::geography
),
(
    'Кафе на Арбате',
    ST_SetSRID(ST_MakePoint(37.5931, 55.7522), 4326)::geography
),
(
    'Кафе в Парке Горького',
    ST_SetSRID(ST_MakePoint(37.6013, 55.7299), 4326)::geography
),
(
    'Кафе у ВДНХ',
    ST_SetSRID(ST_MakePoint(37.6326, 55.8263), 4326)::geography
);

CREATE INDEX cafes_location_gix
ON cafes
USING GIST (location);

ANALYZE cafes;

Запрос ищет кафе в радиусе 2000 метров от точки возле Кремля:

SELECT
    id,
    name,
    round(
        ST_Distance(
            location,
            ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography
        )
    ) AS distance_m
FROM cafes
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography,
    2000
)
ORDER BY distance_m;

ST_DWithin включает индексируемую проверку области и точный расчёт расстояния. Документация PostGIS прямо рекомендует эту функцию для запросов «в пределах заданного расстояния». Условие вида ST_Distance(location, point) <= 2000 заставляет планировщик вычислять расстояние для большого числа строк и часто лишает запрос эффективного первичного отбора.

Плохой шаблон для большой таблицы:

SELECT id, name
FROM cafes
WHERE ST_Distance(
    location,
    ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography
) <= 2000;

Рабочий шаблон:

SELECT id, name
FROM cafes
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography,
    2000
);

Оператор ближайшего соседа находит первые N объектов

Для поиска трёх ближайших кафе PostGIS предоставляет оператор <->. В ORDER BY он поддерживает индексированный KNN-поиск.

SELECT
    id,
    name,
    round(
        ST_Distance(
            location,
            ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography
        )
    ) AS distance_m
FROM cafes
ORDER BY location <-> ST_SetSRID(
    ST_MakePoint(37.6176, 55.7558),
    4326
)::geography
LIMIT 3;

Без LIMIT сервер всё равно должен сформировать полный упорядоченный результат. KNN особенно полезен для небольшой верхней части списка: ближайших пяти аптек, десяти терминалов или двадцати машин.

Для сценария «три ближайших объекта в пределах 5 км» объедините ограничение радиуса и сортировку:

SELECT
    id,
    name,
    round(
        ST_Distance(
            location,
            ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography
        )
    ) AS distance_m
FROM cafes
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography,
    5000
)
ORDER BY location <-> ST_SetSRID(
    ST_MakePoint(37.6176, 55.7558),
    4326
)::geography
LIMIT 3;

EXPLAIN показывает участие индекса в запросе

Добавьте EXPLAIN (ANALYZE, BUFFERS) перед запросом:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name
FROM cafes
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)::geography,
    2000
);

На учебной таблице из пяти строк PostgreSQL может выбрать Seq Scan. Создание индекса не обязывает планировщик читать его. Последовательное чтение одной страницы дешевле обхода дерева.

Для проверки на заметном объёме добавим 200 000 случайных точек в границах Москвы:

INSERT INTO cafes (name, location)
SELECT
    'Точка ' || n,
    ST_SetSRID(
        ST_MakePoint(
            37.35 + random() * 0.65,
            55.55 + random() * 0.45
        ),
        4326
    )::geography
FROM generate_series(1, 200000) AS g(n);

ANALYZE cafes;

Повторный план обычно содержит узел Index Scan, Bitmap Index Scan или индексное условие, связанное с GiST. Конкретный вид зависит от версии PostgreSQL, статистики, размера радиуса и распределения точек.

Проверить размер таблицы и индекса можно так:

SELECT
    pg_size_pretty(pg_relation_size('cafes')) AS table_size,
    pg_size_pretty(pg_relation_size('cafes_location_gix')) AS index_size;

Удаление индекса для сравнительного теста:

DROP INDEX cafes_location_gix;

Возврат индекса:

CREATE INDEX cafes_location_gix
ON cafes
USING GIST (location);

ANALYZE cafes;

В рабочей системе сравнивайте планы на копии данных или в тестовой среде. Удаление индекса на производственной базе способно резко замедлить запросы.

Полигональные зоны позволяют определить район и пересечение маршрута

Точки покрывают поиск ближайших объектов. Полигон отвечает за зоны доставки, районы, кадастровые участки и контуры помещений.

Создадим две упрощённые зоны:

DROP TABLE IF EXISTS delivery_zones;

CREATE TABLE delivery_zones (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    geom geometry(Polygon, 4326) NOT NULL
);

INSERT INTO delivery_zones (name, geom) VALUES
(
    'Центральная зона',
    ST_GeomFromText(
        'POLYGON((
            37.56 55.72,
            37.68 55.72,
            37.68 55.79,
            37.56 55.79,
            37.56 55.72
        ))',
        4326
    )
),
(
    'Северная зона',
    ST_GeomFromText(
        'POLYGON((
            37.55 55.79,
            37.72 55.79,
            37.72 55.88,
            37.55 55.88,
            37.55 55.79
        ))',
        4326
    )
);

CREATE INDEX delivery_zones_geom_gix
ON delivery_zones
USING GIST (geom);

ANALYZE delivery_zones;

ST_Covers включает точки на границе зоны

Запрос определяет зоны для координаты:

SELECT id, name
FROM delivery_zones
WHERE ST_Covers(
    geom,
    ST_SetSRID(ST_MakePoint(37.6176, 55.7558), 4326)
);

ST_Covers возвращает истину и для точки на границе полигона. ST_Contains использует более строгую топологическую семантику: граничная точка может не считаться содержащейся. Для доставки и административного поиска поведение на границе нужно зафиксировать в бизнес-правиле.

ST_Intersects отбирает объекты в окне карты

Веб-карта отправляет координаты видимой области. Сервер строит прямоугольник через ST_MakeEnvelope:

SELECT id, name
FROM delivery_zones
WHERE ST_Intersects(
    geom,
    ST_MakeEnvelope(
        37.58, 55.73,
        37.65, 55.78,
        4326
    )
);

ST_Intersects использует bounding box как первичный индексный фильтр и затем проверяет точное пересечение.

Когда приложению достаточно грубого отбора для последующей обработки, можно применить оператор &&:

SELECT id, name
FROM delivery_zones
WHERE geom && ST_MakeEnvelope(
    37.58, 55.73,
    37.65, 55.78,
    4326
);

&& сравнивает ограничивающие прямоугольники. Результат может содержать геометрию, которая не пересекает окно точной формой. Для отображения полигонов на карте такой кандидат часто допустим, поскольку клиент всё равно обрезает геометрию по экрану. Для расчёта налоговой зоны, доставки или доступа нужна точная функция.

Пространственный JOIN связывает точки с полигонами

Запрос считает кафе в каждой зоне:

SELECT
    z.id,
    z.name,
    count(c.id) AS cafes_count
FROM delivery_zones AS z
LEFT JOIN cafes AS c
    ON ST_Covers(z.geom, c.location::geometry)
GROUP BY z.id, z.name
ORDER BY z.id;

На больших таблицах пространственный JOIN способен стать самым дорогим запросом системы. Проверьте индексы на обеих сторонах, статистику и избирательность. Разбиение работы по территории или времени снижает размер промежуточных наборов.

Для регулярной аналитики часто сохраняют идентификатор зоны рядом с точкой и обновляют его при загрузке данных. Такой подход уменьшает число повторных пространственных JOIN. Он требует процедуры пересчёта после изменения границ.

MySQL создаёт SPATIAL INDEX для столбца POINT

MySQL 8.4 поддерживает пространственные столбцы InnoDB и R-tree индекс. Индексируемый столбец объявляют NOT NULL. SRID фиксируют в определении типа.

CREATE TABLE mysql_cafes (
    id bigint unsigned NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    location point SRID 4326 NOT NULL,
    PRIMARY KEY (id),
    SPATIAL INDEX mysql_cafes_location_sidx (location)
) ENGINE = InnoDB;

Для явного порядка долгота–широта используем параметр axis-order=long-lat:

INSERT INTO mysql_cafes (name, location) VALUES
(
    'Кафе у Кремля',
    ST_GeomFromText(
        'POINT(37.6173 55.7558)',
        4326,
        'axis-order=long-lat'
    )
),
(
    'Кафе у Большого театра',
    ST_GeomFromText(
        'POINT(37.6187 55.7601)',
        4326,
        'axis-order=long-lat'
    )
),
(
    'Кафе в Парке Горького',
    ST_GeomFromText(
        'POINT(37.6013 55.7299)',
        4326,
        'axis-order=long-lat'
    )
);

Создадим прямоугольник видимой области:

SET @viewport = ST_GeomFromText(
    'POLYGON((
        37.59 55.72,
        37.63 55.72,
        37.63 55.77,
        37.59 55.77,
        37.59 55.72
    ))',
    4326,
    'axis-order=long-lat'
);

Индексируемый запрос:

SELECT
    id,
    name,
    ST_AsText(location, 'axis-order=long-lat') AS coordinates
FROM mysql_cafes
WHERE MBRWithin(location, @viewport);

План проверяется командой:

EXPLAIN
SELECT id, name
FROM mysql_cafes
WHERE MBRWithin(location, @viewport);

В столбце key ожидается имя пространственного индекса на достаточно большой таблице. Официальный пример MySQL показывает переход от полного чтения ALL к индексному диапазонному доступу для условия MBRContains.

Функции с префиксом MBR работают с ограничивающими прямоугольниками. Для точной проверки сложных полигонов используйте топологическую функцию после первичного отбора или функцию, для которой оптимизатор поддерживает пространственный индекс в вашей версии. План запроса остаётся главным способом проверки.

SQLite R*Tree хранит координатные границы в виртуальной таблице

SQLite подходит для мобильных приложений, локальных карт, настольных программ и небольших встроенных систем. Модуль RTree создаёт отдельную виртуальную таблицу. Реальные атрибуты удобно хранить в обычной таблице, а границы — в RTree.

Пример ниже выполняется в SQLite с включённым модулем R*Tree:

CREATE TABLE places (
    id integer PRIMARY KEY,
    name text NOT NULL,
    lon real NOT NULL,
    lat real NOT NULL
);

CREATE VIRTUAL TABLE places_index USING rtree(
    id,
    min_lon, max_lon,
    min_lat, max_lat
);

Добавим точки:

INSERT INTO places (id, name, lon, lat) VALUES
(1, 'Кремль', 37.6173, 55.7558),
(2, 'Большой театр', 37.6187, 55.7601),
(3, 'Парк Горького', 37.6013, 55.7299),
(4, 'ВДНХ', 37.6326, 55.8263);

INSERT INTO places_index (
    id,
    min_lon, max_lon,
    min_lat, max_lat
)
SELECT
    id,
    lon, lon,
    lat, lat
FROM places;

Запрос окна карты:

SELECT
    p.id,
    p.name,
    p.lon,
    p.lat
FROM places_index AS r
JOIN places AS p ON p.id = r.id
WHERE r.max_lon >= 37.59
  AND r.min_lon <= 37.63
  AND r.max_lat >= 55.72
  AND r.min_lat <= 55.77
ORDER BY p.id;

Результат содержит Кремль, Большой театр и Парк Горького. ВДНХ находится севернее окна.

Для прямоугольных объектов сохраните реальные границы:

INSERT INTO places_index (
    id,
    min_lon, max_lon,
    min_lat, max_lat
) VALUES (
    10,
    37.60, 37.62,
    55.74, 55.76
);

SQLite R*Tree отвечает за быстрый отбор прямоугольников. Точную геометрию полигона и проверку точки внутри него можно хранить и выполнять через SpatiaLite, собственный код или библиотеку геометрии. Синхронизацию обычной и виртуальной таблиц обеспечивают транзакции или триггеры.

Пример триггера для точек:

CREATE TRIGGER places_after_insert
AFTER INSERT ON places
BEGIN
    INSERT INTO places_index (
        id,
        min_lon, max_lon,
        min_lat, max_lat
    ) VALUES (
        NEW.id,
        NEW.lon, NEW.lon,
        NEW.lat, NEW.lat
    );
END;

Для обновления и удаления потребуются отдельные триггеры:

CREATE TRIGGER places_after_update
AFTER UPDATE OF lon, lat ON places
BEGIN
    UPDATE places_index
    SET min_lon = NEW.lon,
        max_lon = NEW.lon,
        min_lat = NEW.lat,
        max_lat = NEW.lat
    WHERE id = NEW.id;
END;

CREATE TRIGGER places_after_delete
AFTER DELETE ON places
BEGIN
    DELETE FROM places_index
    WHERE id = OLD.id;
END;

Сеточные индексы превращают координату в идентификатор ячейки

R-tree хранит области и хорошо подходит для точной геометрии в СУБД. Распределённая аналитика часто использует сеточные индексы. Поверхность разбивают на ячейки, а точке присваивают идентификатор ячейки.

Выбор разрешения определяет точность и объём данных

Крупные ячейки дают короткий список идентификаторов и грубую географию. Мелкие ячейки повышают точность, увеличивают число записей покрытия и стоимость JOIN.

Для аналитики по районам города подходит среднее разрешение. Для поиска входа в здание нужна более мелкая сетка. Для страны или континента достаточно крупных ячеек. Подходящее разрешение определяют экспериментом на реальных запросах и допустимой погрешности.

Тип запроса определяет подходящую индексную структуру

Один проект может использовать несколько пространственных индексов.

Сценарий	Практичный первый выбор
Точки, линии и полигоны в PostgreSQL	GiST
Большая таблица пространственно упорядоченных точек	BRIN после проверки плана
Специальные точечные нагрузки в PostgreSQL	SP-GiST
Пространственный столбец InnoDB	MySQL `SPATIAL INDEX`
Локальный двумерный поиск в SQLite	R*Tree
Распределённая агрегация по ячейкам	H3, S2 или Geohash
Ближайшие объекты в PostGIS	GiST и KNN-оператор `<->`
Поиск в окне карты	GiST/R-tree и пересечение bounding box

Сравнение проводят по нескольким метрикам:

время построения индекса;
размер индекса на диске;
задержка типовых запросов;
число прочитанных страниц;
скорость вставки и обновления;
число кандидатов после индексного фильтра;
доля ложных совпадений bounding box;
нагрузка на процессор во время точной проверки.

Тест из одной точки и одного радиуса даёт слабую картину. Нагрузочный набор должен включать центр города, окраины, районы с высокой плотностью, крупные полигоны, маленькие полигоны и разные размеры окна карты.

Функция в WHERE должна поддерживать индексный отбор

Созданный индекс может оставаться без работы из-за формы запроса. В PostGIS индекс используют пространственные операторы и предикаты, которые умеют сформировать bounding box условие. К распространённым индексируемым операциям относятся ST_Intersects, ST_Covers, ST_Within, ST_DWithin и оператор &&. Точный список зависит от типа данных, операторного класса и версии PostGIS.

Проверка через EXPLAIN надёжнее предположений.

Обёртка индексируемого столбца преобразованием меняет выражение

Есть индекс на geom:

CREATE INDEX objects_geom_gix
ON objects
USING GIST (geom);

Запрос применяет функцию к столбцу:

SELECT *
FROM objects
WHERE ST_Intersects(
    ST_Transform(geom, 3857),
    ST_MakeEnvelope(0, 0, 1000, 1000, 3857)
);

Обычный индекс на geom не соответствует выражению ST_Transform(geom, 3857). Есть три решения.

Первое — преобразовать геометрию запроса в SRID столбца:

SELECT *
FROM objects
WHERE ST_Intersects(
    geom,
    ST_Transform(
        ST_MakeEnvelope(0, 0, 1000, 1000, 3857),
        4326
    )
);

Второе — хранить дополнительный столбец в часто используемой проекции:

ALTER TABLE objects
ADD COLUMN geom_3857 geometry(Geometry, 3857);

UPDATE objects
SET geom_3857 = ST_Transform(geom, 3857);

CREATE INDEX objects_geom_3857_gix
ON objects
USING GIST (geom_3857);

Синхронизацию обеспечивает приложение, generated column при поддерживаемом выражении или триггер.

Третье — создать функциональный индекс:

CREATE INDEX objects_geom_3857_expr_gix
ON objects
USING GIST (ST_Transform(geom, 3857));

Запрос должен повторять выражение индекса в совместимой форме. Документация ST_Transform отдельно упоминает функциональные индексы для часто используемых преобразований.

Большой радиус снижает избирательность

Запрос радиуса 200 метров в таблице по всей стране возвращает малую долю строк. Индекс экономит много чтений. Радиус 3000 километров может охватить большую часть таблицы. Планировщик способен выбрать последовательное сканирование, поскольку индекс уже мало сокращает работу.

Индекс ускоряет отбор, когда условие исключает значительную часть данных. Универсальной границы в процентах нет: стоимость зависит от размера строк, кэша, диска, распределения координат и стоимости точной функции.

NULL и пустые геометрии требуют отдельной политики

Пространственный столбец для обязательного местоположения лучше объявить NOT NULL. Пустая геометрия GEOMETRYCOLLECTION EMPTY имеет иной смысл: объект существует, но его форма пуста. Проверки и агрегаты обрабатывают её отдельно.

Перед загрузкой данных полезно проверить:

SELECT
    count(*) FILTER (WHERE geom IS NULL) AS null_count,
    count(*) FILTER (WHERE ST_IsEmpty(geom)) AS empty_count,
    count(*) FILTER (WHERE NOT ST_IsValid(geom)) AS invalid_count
FROM objects;

ST_IsValid предназначена для двумерных геометрий и особенно полезна для полигонов.

Ошибки SRID и неверные единицы дают правдоподобный неверный результат

Самая опасная пространственная ошибка часто выглядит как корректный запрос с пустым или странным результатом.

Градусы попадают в условие, рассчитанное на метры

Столбец geometry(Point, 4326) хранит градусы:

WHERE ST_DWithin(geom, point, 1000)

Число 1000 означает тысячу градусов в координатах этой геометрии. Условие покрывает огромную область.

Для метров используйте geography:

WHERE ST_DWithin(
    geom::geography,
    point::geography,
    1000
)

Постоянное приведение всего столбца может потребовать отдельного функционального индекса:

CREATE INDEX objects_geom_geography_gix
ON objects
USING GIST ((geom::geography));

Второй вариант — преобразовать данные в подходящую метрическую проекцию и индексировать преобразованный столбец.

ST_SetSRID не исправляет координаты

Такой запрос только меняет метаданные:

SELECT ST_SetSRID(geom, 3857);

Если числа были долготой и широтой, они останутся теми же числами. Для пересчёта применяют:

SELECT ST_Transform(geom, 3857);

Сначала исходная геометрия должна иметь правильный SRID.

Перепутанные широта и долгота проходят проверку диапазона

Широта Москвы около 55.75, долгота около 37.62. Обе величины укладываются в допустимые диапазоны широты и долготы. Перестановка может не вызвать ошибку.

Полезная проверка для данных в EPSG:4326:

SELECT id, name, ST_AsText(geom)
FROM objects
WHERE ST_X(geom) NOT BETWEEN -180 AND 180
   OR ST_Y(geom) NOT BETWEEN -90 AND 90;

Она находит явные нарушения диапазона и не обнаруживает все перестановки. Дополнительная проверка должна учитывать ожидаемую территорию:

SELECT id, name
FROM objects
WHERE NOT ST_Intersects(
    geom,
    ST_MakeEnvelope(19, 41, 180, 82, 4326)
);

Границы примера грубо охватывают территорию России и требуют адаптации к проекту.

Сложные полигоны увеличивают число ложных кандидатов

Bounding box работает особенно хорошо для компактных объектов. Узкий полумесяц, длинная река или граница побережья занимает маленькую долю собственного прямоугольника. Индекс возвращает много кандидатов из пустых частей bounding box.

Варианты оптимизации зависят от запроса:

разбить огромный полигон на части через ST_Subdivide;
хранить упрощённую геометрию для масштаба карты;
предварительно ограничить запрос атрибутом страны, региона или типа;
разделить таблицу по территории;
использовать сеточное покрытие для первичного JOIN;
хранить несколько представлений геометрии для разных масштабов.

Пример разбиения:

CREATE TABLE regions_subdivided AS
SELECT
    r.id AS region_id,
    part.geom
FROM regions AS r
CROSS JOIN LATERAL ST_Subdivide(r.geom, 256) AS part(geom);

CREATE INDEX regions_subdivided_geom_gix
ON regions_subdivided
USING GIST (geom);

ANALYZE regions_subdivided;

Число 256 задаёт максимальное количество вершин в части для этой операции. Подходящий предел определяют измерением: слишком мелкие части увеличивают число строк, крупные сохраняют широкие bounding box.

Исходный идентификатор позволяет собрать результаты:

SELECT DISTINCT region_id
FROM regions_subdivided
WHERE ST_Intersects(
    geom,
    ST_MakeEnvelope(37.5, 55.6, 37.8, 55.9, 4326)
);

Индекс ускоряет чтение ценой места и более дорогих изменений

Каждая вставка точки или изменение полигона обновляет пространственный индекс. Массовая загрузка часто проходит быстрее по схеме:

создать таблицу;
загрузить данные;
проверить геометрии;
построить индекс;
выполнить ANALYZE.

Для работающей таблицы индекс удалять нельзя без оценки влияния на запросы. PostgreSQL поддерживает построение GiST с CONCURRENTLY, что снижает блокировку записи:

CREATE INDEX CONCURRENTLY objects_geom_gix
ON objects
USING GIST (geom);

Эта команда не выполняется внутри обычного блока транзакции и обычно работает дольше стандартного построения.

Массовые обновления раздувают таблицу и индекс. Обслуживание PostgreSQL включает autovacuum, VACUUM, статистику и контроль размера. После крупной загрузки полезно выполнить:

VACUUM (ANALYZE) objects;

Решение о REINDEX принимают по метрикам, размеру, истории обновлений и результатам диагностики. Регулярный REINDEX по расписанию без признаков проблемы создаёт лишнюю нагрузку.

Пространственный индекс проверяют планом и измерениями

Рабочая проверка состоит из пяти шагов.

Шаг 1 — зафиксировать запрос и параметры

Сохраните реальный SQL, радиус, координату, размер окна карты и ожидаемое число строк. Запрос на 100 метров и запрос на 100 километров имеют разную избирательность.

Шаг 2 — получить план с фактическим выполнением

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

ANALYZE выполняет запрос. Для UPDATE, DELETE и тяжёлых запросов используйте тестовую среду или безопасную транзакцию.

Шаг 3 — найти индексное условие

В плане PostGIS встречаются:

Index Scan
Bitmap Index Scan
Index Cond
Recheck Cond
Rows Removed by Index Recheck

Recheck Cond отражает уточнение кандидатов у lossy-индекса. Наличие повторной проверки не считается ошибкой.

Шаг 4 — сравнить объём работы

Смотрите на:

фактическое время;
число строк после каждого узла;
shared hit и shared read;
строки, удалённые точной проверкой;
повторные циклы узлов;
расхождение плановых и фактических оценок.

Большое расхождение оценок указывает на слабую статистику или сложное распределение данных.

Шаг 5 — повторить тест на нескольких областях

Проверьте плотный центр, редкую окраину, крупный полигон, маленький полигон, узкий маршрут и разные радиусы. Один удачный план не описывает всю нагрузку.

Короткий чек-лист перед запуском в продакшен

пространственный столбец имеет явный тип и SRID;
порядок координат зафиксирован как долгота–широта или X–Y;
расстояния считаются в понятных единицах;
индекс создан на том выражении, которое участвует в запросе;
ST_DWithin используется для радиуса в PostGIS;
KNN-оператор <-> используется для ближайших объектов с LIMIT;
точные бизнес-условия проверяются топологической функцией;
EXPLAIN (ANALYZE, BUFFERS) проверен на реалистичном объёме;
статистика обновлена после массовой загрузки;
крупные и сложные полигоны протестированы отдельно;
стоимость вставок и размер индекса включены в мониторинг;
граничное поведение Contains, Covers, Within зафиксировано тестами.

Хороший пространственный индекс начинается с правильных координат и запроса

Для большинства проектов на PostgreSQL разумная отправная точка выглядит так: корректный SRID, тип geometry или geography по единицам задачи, GiST-индекс, ST_DWithin для радиуса, ST_Intersects или ST_Covers для геометрических условий и обязательная проверка через EXPLAIN.

Индекс приносит максимальную пользу, когда область запроса мала относительно всего набора данных. Точный тест остаётся частью вычисления, поскольку bounding box хранит приближённую форму. Первый практический ориентир — взять самый медленный пространственный запрос проекта, проверить его план и сравнить число строк после индексного фильтра с общим размером таблицы.