PostGIS: пространственная база данных

Урок про PostGIS — расширение PostgreSQL, которое добавляет в обычную базу данных геометрию и пространственные запросы.

PostGIS — расширение PostgreSQL, добавляющее тип данных «геометрия», пространственные функции (расстояние, пересечение) и пространственные индексы.

Пока данных мало, хватает файлов GeoJSON и скриптов на Python. Но когда объектов миллионы и к ним обращаются десятки пользователей, нужна настоящая база. PostGIS делает из PostgreSQL — одной из лучших реляционных СУБД — мощную геоплатформу. Если вы знаете SQL, переход почти бесшовный: те же CREATE TABLE, SELECT, JOIN, плюс колонка-геометрия и пространственные функции.

Геометрия как тип колонки

В PostGIS у таблицы появляется колонка типа geometry (или geography для расчётов на сфере). В неё кладут точки, линии, полигоны, а в запросах применяют функции, начинающиеся с ST_ (Spatial Type): ST_Distance, ST_Contains, ST_Area, ST_Intersects. Это ровно те операции, что мы реализовывали вручную, — теперь они в базе и работают на индексах.

Покажем привычный SQL, который вы можете запустить в песочнице ниже (без PostGIS-специфики — на чистом стандартном SQL), чтобы вспомнить базовую модель «таблица с координатами»:

CREATE TABLE cities (
  id INTEGER PRIMARY KEY,
  name TEXT,
  lat REAL,
  lon REAL,
  population INTEGER
);

INSERT INTO cities VALUES
  (1, 'Москва', 55.7558, 37.6173, 13000000),
  (2, 'Казань', 55.7963, 49.1088, 1300000),
  (3, 'Тула',   54.2044, 37.6184, 460000),
  (4, 'Самара', 53.1959, 50.1002, 1140000);

-- города-миллионники, по убыванию населения
SELECT name, population
FROM cities
WHERE population >= 1000000
ORDER BY population DESC;

Вывод (песочница SQLite):

Москва | 13000000
Казань | 1300000
Самара | 1140000

Это атрибутивный запрос. В настоящем PostGIS к нему добавилась бы пространственная часть — например, «в радиусе 500 км от Москвы», — через ST_DWithin по геометрической колонке.

Как выглядит пространственный запрос PostGIS

Следующий фрагмент уже PostGIS-специфичен (тип geography, функции ST_), поэтому он только для чтения — в SQLite-песочнице он не выполнится:

-- города в радиусе 500 км от Москвы
SELECT name
FROM cities
WHERE ST_DWithin(
  geom::geography,
  ST_MakePoint(37.6173, 55.7558)::geography,
  500000          -- метров
);

-- площадь региона в кв. км
SELECT name, ST_Area(geom::geography) / 1e6 AS area_km2
FROM regions;

Как работает под капотом

Сердце производительности PostGIS — пространственный индекс GiST (обобщённое дерево поиска, по сути R-дерево). Без него запрос «что в радиусе» перебирал бы все строки; с ним база по bounding box мгновенно отсекает далёкие объекты и точно проверяет лишь немногих кандидатов — тот самый двухступенчатый приём из раздела про spatial join, но встроенный в СУБД. PostGIS хранит геометрию в формате WKB, поддерживает тысячи систем координат (таблица spatial_ref_sys с EPSG-кодами) и умеет перепроецировать на лету через ST_Transform. Тип geography считает расстояния и площади честно на эллипсоиде, а geometry — на плоскости (быстрее, но требует подходящей проекции).

Частые ошибки

• Забыть пространственный индекс. Без GiST-индекса пространственные запросы на больших таблицах «висят».
• Смешать SRID. Операции над геометриями в разных системах координат PostGIS отвергает — приводите к одному SRID.
• Мерить площадь в geometry без проекции. На geometry в градусах площадь бессмысленна; берите geography или перепроецируйте.

Когда файлов уже мало

Граница, за которой GeoJSON и скрипты перестают справляться, наступает быстрее, чем кажется. Как только данных миллионы объектов, к ним обращаются несколько приложений одновременно, нужны транзакции (одновременная правка без порчи данных) и быстрые запросы «что в этой области» — наступает время базы. PostGIS даёт всё это, не заставляя бросать знакомый SQL: те же таблицы, те же SELECT и JOIN, плюс геометрический столбец и функции ST_. Для команды, которая уже живёт в PostgreSQL, это естественное расширение, а не новая технология с нуля, — что и сделало PostGIS отраслевым стандартом пространственных баз.

Сила PostGIS не в наборе функций (они есть и в shapely), а в том, что вычисления идут рядом с данными и на индексах. Запрос «найди все объекты в радиусе 500 метров от точки» по миллионам строк база выполняет за миллисекунды, потому что GiST-индекс по bounding box мгновенно отсекает далёкое, а не тянет всё в приложение для перебора. Сюда же — перепроекция на лету через ST_Transform, поддержка тысяч систем координат, типы geometry (быстрый плоскостной) и geography (честный эллипсоидный). Связка PostGIS как хранилища, geopandas как аналитического слоя и folium как витрины — типичная архитектура промышленного геопроекта, и каждый компонент в ней занимается тем, в чём силён.

Итог

• PostGIS добавляет в PostgreSQL тип геометрии, функции ST_ и индексы.
• Обычный SQL остаётся прежним; геометрия — это ещё одна колонка.
• Индекс GiST (R-дерево) делает пространственные запросы быстрыми.
• Тип geography считает на эллипсоиде, geometry — на плоскости.