Геоданные в PostgreSQL: первый проект на PostGIS

За 20 минут вы подключите PostGIS к базе PostgreSQL, сохраните координаты точек и найдёте все объекты в радиусе одного километра от заданного места. Пример рассчитан на PostgreSQL 16 и PostGIS 3.x: он подойдёт, если расширение уже доступно на сервере или его может включить администратор.

Что понадобится

• Работающий сервер PostgreSQL 16 и созданная база данных.
• Учётная запись, которой разрешено создавать расширения и таблицы. В управляемой базе право на установку PostGIS может быть только у администратора.
• Клиент для SQL-запросов: например, psql или графический инструмент, подключённый к нужной базе.
• PostGIS, установленный на сервере. Команда в SQL включает расширение в базе, но не устанавливает его серверные файлы.

Термин. SRID — идентификатор системы координат. Он сообщает PostGIS, как интерпретировать координаты. В примере используется SRID 4326 — географические координаты долготы и широты. Важно: сначала в ST_MakePoint передают долготу, затем широту.

Подключаем PostGIS и проверяем базу

1. Откройте соединение именно с той базой, где будут храниться геоданные. Выполните запрос:

   CREATE EXTENSION IF NOT EXISTS postgis;

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

2. Убедитесь, что функции PostGIS доступны, и заодно посмотрите версии компонентов:

   SELECT PostGIS_Full_Version();

   Проверка: PostgreSQL вернул строку с информацией о PostGIS и его компонентах. Если функции нет, не переходите к созданию таблицы: сначала проверьте, установлено ли расширение на сервере и поддерживает ли его ваш провайдер.

Создаём таблицу с координатами

1. Добавьте таблицу точек. Тип geography удобен для запросов расстояния в метрах на поверхности Земли:

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

   Проверка: выполните SELECT column_name, udt_name FROM information_schema.columns WHERE table_name = 'places';. В списке должна быть колонка location. Ограничение Point не даст случайно записать в неё линию или полигон.

2. Добавьте несколько тестовых точек. ST_MakePoint принимает долготу и широту, ST_SetSRID задаёт системе координат идентификатор 4326, а приведение к geography включает географические расчёты:

   INSERT INTO places (name, location) VALUES
       ('Точка A', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::geography),
       ('Точка B', ST_SetSRID(ST_MakePoint(-73.9900, 40.7500), 4326)::geography),
       ('Точка C', ST_SetSRID(ST_MakePoint(-73.9000, 40.8000), 4326)::geography);

   Проверка: выполните SELECT id, name, ST_AsText(location::geometry) FROM places;. Запрос должен вернуть три строки с точками формата POINT(долгота широта).

Координаты без правильной системы отсчёта — это просто два числа. Ошибочный порядок широты и долготы может поместить объект совсем не туда, а неверно назначенный SRID заставит PostGIS считать, что данные описывают другую часть мира.

Ищем точки рядом

1. Создайте пространственный индекс, чтобы таблица могла масштабироваться за пределы тестовых трёх строк:

   CREATE INDEX places_location_gix
       ON places USING GIST (location);

   Проверка: выполните SELECT indexname FROM pg_indexes WHERE tablename = 'places'; и убедитесь, что в результате есть places_location_gix. На маленькой таблице PostgreSQL может выбрать последовательное чтение вместо индекса — это нормально.

2. Найдите объекты не дальше километра от заданной точки и выведите расстояние:

   WITH center AS (
       SELECT ST_SetSRID(
           ST_MakePoint(-73.9857, 40.7484),
           4326
       )::geography AS point
   )
   SELECT places.name,
          round(ST_Distance(places.location, center.point)::numeric) AS distance_m
   FROM places
   CROSS JOIN center
   WHERE ST_DWithin(places.location, center.point, 1000)
   ORDER BY distance_m;

   Проверка: точки в радиусе появятся в результате, а distance_m покажет расстояние в метрах. Радиус здесь задан аргументом 1000 функции ST_DWithin. Запрос использует те же координаты и SRID, что и тестовые данные.

Если не получилось

• ERROR: extension "postgis" is not available. На сервере, вероятно, нет файлов расширения или провайдер не предоставляет PostGIS. Одной SQL-командой это не исправить: проверьте список доступных расширений в панели управления или обратитесь к администратору.
• ERROR: permission denied to create extension "postgis". У пользователя нет нужных прав. Попросите администратора включить расширение в конкретной базе; не выдавайте приложению права администратора только ради этого шага.
• ERROR: type "geography" does not exist. Проверьте, что CREATE EXTENSION выполнена в той же базе и перед созданием таблицы. Соединение с другой базой — частая причина такой ошибки.
• Запрос вернул ноль строк. Проверьте порядок координат, SRID и единицы радиуса. В этом примере долгота идёт первой, широта — второй, а радиус для geography задаётся в метрах.
• Запрос медленный, хотя индекс создан. На малой таблице планировщик может предпочесть последовательное чтение. На реальном объёме изучите план через EXPLAIN ANALYZE и проверьте, что условие запроса использует индексируемую колонку без несовпадающего преобразования.

Что делать дальше

Подставьте реальные координаты и проверьте несколько известных точек на карте. Затем запустите EXPLAIN ANALYZE на запросе с объёмом данных, похожим на рабочий: сам факт наличия GiST-индекса не гарантирует, что PostgreSQL выберет его для каждого запроса.

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