PostGIS: геопространственные данные без отдельной гео-базы

Прямой ответ

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

Два основных типа:

• geometry — координаты в плоской проекции. Быстрее, подходит для небольших областей и задач, где проекция выбрана заранее.
• geography — координаты на эллипсоиде Земли. Расстояния считаются в метрах по поверхности, что корректнее для больших расстояний, но вычисления дороже.

Каждое значение имеет SRID — идентификатор системы координат. Самый распространённый — 4326 (WGS 84, широта/долгота в градусах). Смешивание значений с разными SRID в одной операции приводит к ошибке или неверному результату.

CREATE EXTENSION postgis;

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

CREATE INDEX pickup_points_location_idx ON pickup_points USING gist (location);

-- ближайшие 5 точек к заданным координатам
SELECT id, name,
       round(ST_Distance(location, ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography)) AS meters
FROM pickup_points
ORDER BY location <-> ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography
LIMIT 5;

Оператор <-> — расстояние по ограничивающим рамкам; вместе с индексом GiST он даёт эффективный поиск ближайших соседей (KNN). Без индекса запрос выполняет полный перебор.

Пространственный индекс GiST хранит ограничивающие прямоугольники (bounding box) объектов, поэтому сначала отбираются кандидаты по рамке, а затем выполняется точная проверка. Это означает, что для сильно вытянутых объектов отбор по рамке может быть менее избирательным.

Практика: типовые задачи и приёмы

1. Поиск в радиусе.

SELECT id, name
FROM pickup_points
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography, 1500);

ST_DWithin для типа geography принимает расстояние в метрах и использует индекс; это предпочтительнее, чем фильтрация по вычисленному ST_Distance.

2. Принадлежность территории. Хранение зон доставки как полигонов и проверка попадания точки:

SELECT z.zone_name
FROM delivery_zones z
WHERE ST_Contains(z.boundary::geography, p.location)
FROM pickup_points p WHERE p.id = 42;

3. Форматы обмена. PostGIS читает и пишет WKT, WKB и GeoJSON:

SELECT ST_AsGeoJSON(location) FROM pickup_points WHERE id = 42;
INSERT INTO zones (boundary) VALUES (ST_GeomFromGeoJSON('{"type":"Polygon", ...}'));

4. Упрощение геометрии для карты. Детализированный полигон региона может содержать десятки тысяч точек, что медленно передаётся в браузер. ST_SimplifyPreserveTopology уменьшает число точек с сохранением топологии.

5. Агрегация по сетке. Группировка точек по гексагональной или квадратной сетке для тепловых карт:

SELECT ST_SnapToGrid(location::geometry, 0.01) AS cell, count(*)
FROM pickup_points GROUP BY cell ORDER BY count(*) DESC LIMIT 20;

Рекомендации:

1. Выбирайте тип под задачу. Для расчёта расстояний между городами — geography; для чертежей, планов помещений и проекций — geometry с подходящим SRID.
2. Всегда указывайте SRID. Значение без SRID — источник трудноуловимых ошибок: координаты интерпретируются неверно, а запрос формально работает.
3. Создавайте пространственный индекс. Без GiST любой пространственный запрос превращается в перебор таблицы. О выборе индексов — в статье про индексы PostgreSQL.
4. Проверяйте план запроса. Функция, применённая к колонке в условии (WHERE ST_X(location) = ...), обычно отключает использование индекса.
5. Разделяйте OLTP и аналитику. Массовые пространственные агрегации по всей таблице нагружают основную базу; для отчётов данные лучше выгружать — о различии нагрузок в статье про OLTP и OLAP.
6. Проверяйте корректность геометрии. Самопересечения полигонов и незамкнутые кольца приводят к ошибкам в функциях площади и принадлежности. Функция ST_IsValid выявляет такие случаи, а ST_MakeValid исправляет их; проверку стоит включать в загрузку данных, а не выполнять по требованию.
7. Храните исходные данные отдельно от производных. Упрощённая геометрия для карты и точная геометрия для расчётов — разные наборы; замена одной другой приводит к потере точности без возможности восстановления.

Практический сценарий: поиск ближайших пунктов выдавал неверный порядок

Контекст: сервис показывал «ближайшие» пункты выдачи. Пользователи в ряде городов видели в начале списка пункты, которые находились дальше.

1. Диагностика. Колонка хранилась как geometry(Point, 4326), а расстояние вычислялось через ST_Distance без приведения к geography. Результат был в градусах, а не в метрах.
2. Причина. Градус широты и градус долготы соответствуют разным расстояниям на местности: на широте Москвы градус долготы примерно вдвое короче градуса широты. Сортировка по «градусному» расстоянию давала неверный порядок.
3. Дополнительно. Индекс по колонке отсутствовал, поэтому каждый запрос выполнял полный перебор 1,2 млн строк и занимал около 900 мс.
4. Действия. (а) Тип колонки изменили на geography(Point, 4326) с пересчётом существующих значений; (б) создали индекс GiST; (в) сортировку перевели на оператор <->, а фильтрацию по радиусу — на ST_DWithin в метрах; (г) добавили тест, проверяющий порядок для набора точек с известными координатами.
5. Результат. Порядок стал соответствовать фактическому расстоянию, время запроса снизилось до 6 мс.

Вывод: в геопространственных задачах большинство ошибок связано не с алгоритмами, а с системами координат и единицами измерения. Проверка единицы результата — обязательная часть отладки.

FAQ

Нужен ли PostGIS, если координаты можно хранить в двух числовых колонках? Для хранения — нет, для запросов — да. Расстояние, принадлежность полигону, пересечение линий и индексация по пространству требуют геометрических типов и функций. Самостоятельная реализация этих расчётов почти всегда содержит ошибки в краевых случаях.

Что такое SRID и почему он важен? Это идентификатор системы координат, определяющий, как числа переводятся в положение на Земле. Одинаковые пары чисел в разных SRID означают разные точки. Операции между значениями с разными SRID невозможны без преобразования.

Насколько PostGIS медленнее специализированных гео-баз? Для большинства прикладных задач — сопоставимо, при наличии индекса. Выигрыш в том, что данные остаются в той же транзакционной базе, что и бизнес-сущности: соединения, ограничения и резервное копирование работают как обычно.

Можно ли использовать PostGIS для анализа больших наборов точек? Да, но с оговорками: массовые агрегации нагружают основную базу. Практика — выполнять тяжёлые расчёты на реплике или выгружать данные в аналитическое хранилище.

Источники: PostGIS documentation, PostgreSQL docs (index types), GeoJSON — ссылки в шапке.