PostGIS — расширение PostgreSQL, которое превращает реляционную базу данных в полноценную геопространственную систему. Если ваше приложение работает с координатами, геозонами или пространственным поиском — PostGIS нужно знать. В этой статье разберём типы данных, ключевые функции, индексирование и интеграцию с Node.js и Python.

Установка и включение

-- В PostgreSQL после установки расширения (apt/brew/docker)
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- Проверка версии
SELECT PostGIS_Version();
-- 3.4.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Для Docker рекомендуем образ postgis/postgis:16-3.4.

geometry vs geography

Это самый важный выбор при проектировании схемы.

| Параметр | geometry | geography | |---|---|---| | Система координат | Плоская (проекция) | Сфера (WGS84) | | Единицы по умолчанию | Единицы проекции (градусы для SRID 4326) | Метры | | Точность на больших расстояниях | Ошибка растёт с расстоянием | Точная | | Производительность | Быстрее | Медленнее (~10-20%) | | Поддержка функций | Полная | Подмножество | | Когда использовать | Локальные данные (город, регион), карты в проекции | Глобальные данные, расчёты расстояний в метрах |

Практическое правило:

• Храните координаты как geometry(Point, 4326) (SRID 4326 = WGS84 — это широта/долгота GPS).
• Для расчётов расстояний кастуйте в geography прямо в запросе через ::geography.

-- Создание таблицы с пространственной колонкой
CREATE TABLE places (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    category    TEXT,
    location    geometry(Point, 4326) NOT NULL,
    created_at  TIMESTAMPTZ DEFAULT now()
);

-- Вставка точки (долгота, широта — обратите внимание на порядок!)
INSERT INTO places (name, category, location) VALUES
    ('Красная площадь', 'landmark',
     ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)),
    ('Эрмитаж', 'museum',
     ST_SetSRID(ST_MakePoint(30.3141, 59.9400), 4326));

Важно: PostGIS использует порядок (longitude, latitude) = (X, Y), а не привычный (lat, lng). Частая причина ошибок.

Ключевые функции

ST_DWithin — поиск в радиусе

Самый частый запрос в геоприложениях:

-- Найти все кафе в радиусе 1 км от точки
-- geography::geography обеспечивает расчёт в метрах
SELECT
    name,
    category,
    ST_Distance(
        location::geography,
        ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography
    ) AS distance_m
FROM places
WHERE
    category = 'cafe'
    AND ST_DWithin(
        location::geography,
        ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography,
        1000  -- метры (работает только с geography)
    )
ORDER BY distance_m
LIMIT 20;

ST_Contains и ST_Within — точка в полигоне

-- Хранение геозон (полигонов)
CREATE TABLE zones (
    id       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name     TEXT NOT NULL,
    type     TEXT,  -- 'delivery', 'event', 'restricted'
    boundary geometry(Polygon, 4326) NOT NULL
);

-- Создание полигона из координат
INSERT INTO zones (name, type, boundary) VALUES (
    'Зона доставки центр', 'delivery',
    ST_SetSRID(
        ST_MakePolygon(ST_GeomFromText(
            'LINESTRING(37.60 55.76, 37.65 55.76, 37.65 55.73, 37.60 55.73, 37.60 55.76)'
        )),
        4326
    )
);

-- Проверка: в какой зоне находится пользователь?
SELECT z.name, z.type
FROM zones z
WHERE ST_Contains(z.boundary,
    ST_SetSRID(ST_MakePoint($1, $2), 4326)
);
-- ST_Within(point, polygon) эквивалентно ST_Contains(polygon, point)

ST_Intersection и ST_Overlaps — пересечение геометрий

-- Площадь пересечения двух зон доставки (в кв. метрах)
SELECT
    a.name AS zone_a,
    b.name AS zone_b,
    ST_Area(ST_Intersection(a.boundary, b.boundary)::geography) AS intersection_m2
FROM zones a
JOIN zones b ON a.id < b.id
WHERE ST_Overlaps(a.boundary, b.boundary);

Работа с треками и линиями

-- Хранение маршрутов
CREATE TABLE routes (
    id    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name  TEXT,
    path  geometry(LineString, 4326)
);

-- Длина маршрута в метрах
SELECT name, ST_Length(path::geography) AS length_m
FROM routes;

-- Ближайшая точка на маршруте к пользователю
SELECT
    name,
    ST_AsText(ST_ClosestPoint(path,
        ST_SetSRID(ST_MakePoint($1, $2), 4326))) AS closest_point,
    ST_Distance(path::geography,
        ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography) AS distance_m
FROM routes
ORDER BY distance_m
LIMIT 1;

GiST-индекс: как он работает

Обычный B-tree индекс не работает для пространственных данных — он умеет сравнивать только по одному измерению. GiST (Generalized Search Tree) использует R-tree структуру с ограничивающими прямоугольниками (MBR — Minimum Bounding Rectangle).

-- Создание пространственного индекса
CREATE INDEX places_location_gist ON places USING GIST (location);

-- Для geography можно индексировать напрямую
CREATE INDEX zones_boundary_gist ON zones USING GIST (boundary);

-- Проверка использования индекса
EXPLAIN ANALYZE
SELECT * FROM places
WHERE ST_DWithin(location::geography,
    ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography, 1000);

-- В плане должно быть: Index Scan using places_location_gist

Принцип работы R-tree

Индекс строит иерархию ограничивающих прямоугольников:

1. Каждая геометрия оборачивается в MBR.
2. MBR объединяются в группы, каждая группа — тоже MBR.
3. При запросе PostgreSQL проверяет пересечение с MBR на каждом уровне и отсекает ветки дерева, которые не могут содержать результаты.

Это объясняет, почему функции ST_DWithin, ST_Contains, ST_Overlaps используют индекс, а ST_Distance > N — нет (для последнего нужно переписать как NOT ST_DWithin).

Партиционирование по регионам

При большом объёме геоданных (десятки миллионов объектов) партиционирование по регионам улучшает производительность запросов:

-- Родительская таблица
CREATE TABLE points_of_interest (
    id       UUID NOT NULL DEFAULT gen_random_uuid(),
    name     TEXT NOT NULL,
    category TEXT,
    location geometry(Point, 4326) NOT NULL,
    region   TEXT NOT NULL  -- 'europe', 'asia', 'americas'
) PARTITION BY LIST (region);

-- Партиции для регионов
CREATE TABLE poi_europe   PARTITION OF points_of_interest FOR VALUES IN ('europe');
CREATE TABLE poi_asia     PARTITION OF points_of_interest FOR VALUES IN ('asia');
CREATE TABLE poi_americas PARTITION OF points_of_interest FOR VALUES IN ('americas');

-- Индекс создаётся на каждой партиции
CREATE INDEX ON poi_europe   USING GIST (location);
CREATE INDEX ON poi_asia     USING GIST (location);
CREATE INDEX ON poi_americas USING GIST (location);

-- Запросы с фильтром по region автоматически используют только нужную партицию
SELECT * FROM points_of_interest
WHERE region = 'europe'
  AND ST_DWithin(location::geography,
    ST_SetSRID(ST_MakePoint(37.6173, 55.7558), 4326)::geography, 5000);

Для более гранулярного партиционирования можно использовать геохеш (H3 или Geohash) как ключ партиции.

Интеграция с Node.js

const { Pool } = require('pg');

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

// Поиск ближайших объектов
async function findNearby(lng, lat, radiusMeters, category, limit = 20) {
  const query = `
    SELECT
      id,
      name,
      category,
      ST_AsGeoJSON(location)::json AS geojson,
      ST_Distance(
        location::geography,
        ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography
      )::int AS distance_m
    FROM places
    WHERE
      ($3::text IS NULL OR category = $3)
      AND ST_DWithin(
        location::geography,
        ST_SetSRID(ST_MakePoint($1, $2), 4326)::geography,
        $4
      )
    ORDER BY distance_m
    LIMIT $5
  `;

  const { rows } = await pool.query(query, [lng, lat, category, radiusMeters, limit]);
  return rows;
}

// Создание объекта (с конвертацией GeoJSON → PostGIS)
async function createPlace(name, category, lng, lat) {
  const { rows } = await pool.query(
    `INSERT INTO places (name, category, location)
     VALUES ($1, $2, ST_SetSRID(ST_MakePoint($3, $4), 4326))
     RETURNING id, name, category,
       ST_AsGeoJSON(location)::json AS geojson`,
    [name, category, lng, lat]
  );
  return rows[0];
}

// Экспорт в GeoJSON FeatureCollection
async function exportZonesAsGeoJSON() {
  const { rows } = await pool.query(`
    SELECT json_build_object(
      'type', 'FeatureCollection',
      'features', json_agg(
        json_build_object(
          'type', 'Feature',
          'geometry', ST_AsGeoJSON(boundary)::json,
          'properties', json_build_object('id', id, 'name', name, 'type', type)
        )
      )
    ) AS geojson
    FROM zones
  `);
  return rows[0].geojson;
}

Интеграция с Python (SQLAlchemy + GeoAlchemy2)

from geoalchemy2 import Geometry
from geoalchemy2.functions import ST_DWithin, ST_Distance, ST_MakePoint, ST_SetSRID
from sqlalchemy import Column, String, func
from sqlalchemy.dialects.postgresql import UUID
import uuid

class Place(Base):
    __tablename__ = 'places'
    id       = Column(UUID, primary_key=True, default=uuid.uuid4)
    name     = Column(String, nullable=False)
    category = Column(String)
    location = Column(Geometry(geometry_type='POINT', srid=4326), nullable=False)

# Поиск ближайших
def find_nearby(session, lng: float, lat: float, radius_m: float):
    user_point = func.ST_SetSRID(func.ST_MakePoint(lng, lat), 4326)
    
    return (
        session.query(
            Place,
            func.ST_Distance(
                Place.location.cast(Geometry(srid=4326)),
                func.cast(user_point, 'geography')
            ).label('distance_m')
        )
        .filter(
            func.ST_DWithin(
                Place.location.cast('geography'),
                func.cast(user_point, 'geography'),
                radius_m
            )
        )
        .order_by('distance_m')
        .limit(20)
        .all()
    )

# Используется в location-based приложениях
# Подробнее: /blog/location-based-igry-i-geoprilozhenia

Советы по производительности

| Проблема | Решение | |---|---| | Индекс не используется | Убедитесь, что функция использует операторы &&, ~=, @> (они триггерят индекс). ST_DWithin использует индекс, ST_Distance в WHERE — нет | | Медленный кастинг geography | Хранить дублирующую колонку geography или материализованное представление | | VACUUM не поспевает | Для часто обновляемых таблиц уменьшить autovacuum_vacuum_scale_factor | | Большой ST_Union медленный | Использовать ST_Collect (не объединяет геометрии, просто группирует) | | Cluster scan вместо index scan | CLUSTER таблицу по GiST-индексу: CLUSTER places USING places_location_gist |

Итог

PostGIS — мощный инструмент, который закрывает большинство задач геопространственной обработки данных на уровне базы данных. Выбор между geometry и geography, правильное создание GiST-индексов и умение писать запросы с ST_DWithin вместо вычисления расстояния в WHERE — это основа производительного геоприложения. Для практического применения в реальном проекте смотрите статью Location-based приложения и геоигры.