PostGIS в PostgreSQL: работа с геоданными в SQL

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

Установка и основные типы

CREATE EXTENSION postgis;

-- Проверить версию
SELECT PostGIS_Version();

Основные типы данных

-- POINT: точка (долгота, широта)
-- LINESTRING: линия
-- POLYGON: многоугольник
-- GEOMETRY: любая геометрия (декартовы координаты)
-- GEOGRAPHY: геодезические координаты (сфера Земли, метры)

-- Создать таблицу с геоколонкой
CREATE TABLE places (
    id      SERIAL PRIMARY KEY,
    name    TEXT,
    location GEOGRAPHY(POINT, 4326)  -- 4326 = WGS84 (GPS-координаты)
);

GEOGRAPHY vs GEOMETRY:

• GEOGRAPHY: учитывает кривизну Земли, расстояния в метрах — используйте для реальных координат
• GEOMETRY: плоская геометрия в произвольных единицах — для локальных проекций, CAD

Вставка геоданных

-- Создать точку через WKT (Well-Known Text)
INSERT INTO places (name, location) VALUES
    ('Кремль',     ST_GeogFromText('POINT(37.6173 55.7520)')),  -- долгота, широта
    ('Эрмитаж',    ST_GeogFromText('POINT(30.3141 59.9398)')),
    ('Байкал',     ST_GeogFromText('POINT(108.1650 53.5587)'));

-- Через функции
ST_MakePoint(longitude, latitude)::geography
ST_SetSRID(ST_MakePoint(37.6173, 55.7520), 4326)::geography

-- Через GeoJSON
ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [37.6173, 55.7520]}')

Расстояние между точками

-- Расстояние от точки до всех мест (в метрах для GEOGRAPHY)
SELECT
    name,
    ROUND(ST_Distance(
        location,
        ST_GeogFromText('POINT(37.6173 55.7520)')  -- Москва
    )) AS distance_m
FROM places
ORDER BY distance_m;

-- Расстояние в километрах
SELECT name,
       ROUND(ST_Distance(location, ST_GeogFromText('POINT(37.6 55.7)')) / 1000, 1) AS km
FROM places
ORDER BY km;

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

-- Найти все кафе в 500 метрах от заданной точки
SELECT name, address
FROM cafes
WHERE ST_DWithin(
    location,
    ST_GeogFromText('POINT(37.6173 55.7520)'),
    500  -- радиус в метрах
)
ORDER BY ST_Distance(location, ST_GeogFromText('POINT(37.6173 55.7520)'));

N ближайших объектов (KNN)

-- 5 ближайших к пользователю кафе
SELECT name, address,
       ROUND(ST_Distance(location, :user_location) / 1000, 2) AS km
FROM cafes
ORDER BY location <-> :user_location::geography  -- оператор KNN (быстрый с GiST!)
LIMIT 5;

Оператор <-> использует KNN GiST индекс — значительно быстрее ORDER BY ST_Distance().

Индекс для геоданных

-- GiST-индекс для быстрого пространственного поиска
CREATE INDEX idx_cafes_location ON cafes USING gist(location);

-- После индекса ST_DWithin и <-> используют индекс:
EXPLAIN ANALYZE
SELECT * FROM cafes WHERE ST_DWithin(location, ..., 500);
-- Index Scan using idx_cafes_location

Работа с полигонами

-- Создать полигон (границы района)
CREATE TABLE districts (
    id      SERIAL PRIMARY KEY,
    name    TEXT,
    boundary GEOGRAPHY(POLYGON, 4326)
);

INSERT INTO districts (name, boundary) VALUES
('Центральный', ST_GeogFromText(
    'POLYGON((37.60 55.75, 37.65 55.75, 37.65 55.78, 37.60 55.78, 37.60 55.75))'
));

-- Найти район, в котором находится точка
SELECT d.name
FROM districts d
WHERE ST_Within(
    ST_GeogFromText('POINT(37.62 55.76)')::geometry,
    d.boundary::geometry
);

-- Точки внутри полигона
SELECT p.name
FROM places p, districts d
WHERE d.name = 'Центральный'
  AND ST_Within(p.location::geometry, d.boundary::geometry);

Вычислительные функции

-- Длина маршрута (линии)
SELECT ST_Length(route::geography) AS length_m FROM routes;

-- Площадь полигона
SELECT ST_Area(boundary::geography) / 1000000 AS area_km2 FROM districts;

-- Центр полигона
SELECT ST_Centroid(boundary::geometry) AS center FROM districts;

-- Пересечение двух областей
SELECT ST_Intersection(a.boundary::geometry, b.boundary::geometry)
FROM districts a, districts b
WHERE a.name = 'Район А' AND b.name = 'Район Б';

-- Пересекаются ли полигоны?
SELECT ST_Intersects(a.boundary::geometry, b.boundary::geometry)
FROM districts a, districts b;

Вывод в GeoJSON

-- Вернуть геометрию как GeoJSON (для фронтенда)
SELECT
    name,
    ST_AsGeoJSON(location)::jsonb AS geojson,
    ST_X(location::geometry) AS longitude,
    ST_Y(location::geometry) AS latitude
FROM places;

-- Feature Collection для Leaflet/MapBox
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        json_build_object(
            'type', 'Feature',
            'geometry', ST_AsGeoJSON(location)::json,
            'properties', json_build_object('name', name, 'category', category)
        )
    )
) AS geojson
FROM places;

Практический кейс: доставка в зоне

-- Есть ли у нас доставка по адресу пользователя?
SELECT
    d.zone_name,
    d.delivery_fee
FROM delivery_zones d
WHERE ST_Within(
    ST_GeogFromText('POINT(' || :user_lng || ' ' || :user_lat || ')')::geometry,
    d.coverage_area::geometry
)
ORDER BY d.delivery_fee
LIMIT 1;

-- Нет результата → вне зоны доставки

Итог: ключевые функции PostGIS

PostGIS превращает PostgreSQL в полноценную ГИС-систему. Для большинства задач с геоданными (радиус поиска, зоны доставки, кластеризация) — это лучший выбор в экосистеме PostgreSQL.