Геопространственный анализ маршрутов с pgRouting в ServBay

pgRouting — это мощный модуль-расширение для PostgreSQL и PostGIS, предоставляющий обширный набор функций для маршрутизации и сетевого анализа геопространственных данных. С помощью pgRouting разработчики могут выполнять сложные запросы по дорожной сети, например, находить кратчайший путь между точками, решать задачу коммивояжёра (TSP) или анализировать зоны обслуживания. Эти возможности востребованы при создании картографических приложений, планировании логистики, транспортном анализе и других задачах.

В этой статье подробно рассмотрено, как легко включить расширение pgRouting для вашей базы данных PostgreSQL в локальной среде разработки Web-приложений ServBay, а также выполнить базовую настройку и анализ маршрутов.

Обзор

ServBay предоставляет разработчикам на macOS интегрированную локальную среду с предустановленными веб-серверами, базами данных и языками программирования. В дистрибутив PostgreSQL от ServBay уже включены расширения pgRouting и PostGIS. Это означает, что не требуется вручную скачивать или собирать эти расширения — достаточно просто включить их в нужной базе данных.

Предварительные требования

Перед использованием pgRouting убедитесь, что выполнены следующие условия:

1. Установлен и запущен ServBay: Если вы еще не установили ServBay, скачайте и установите последнюю версию с официального сайта ServBay.
2. Пакет PostgreSQL в ServBay установлен и активирован: В интерфейсе ServBay убедитесь, что пакет PostgreSQL установлен и находится в статусе «Запущен».
3. Базовые знания SQL и PostgreSQL: В статье предполагается, что вы знакомы с основами работы с базой данных PostgreSQL и SQL-запросами.
4. Понимание основ PostGIS: Для работы с геоданными pgRouting использует возможности PostGIS. Убедитесь, что расширение PostGIS уже включено в вашей базе данных. Обычно пакет PostgreSQL от ServBay содержит PostGIS по умолчанию.

Установка и включение расширения pgRouting

ServBay уже содержит файлы расширения pgRouting — достаточно включить его в нужной базе данных.

1. Подключение к базе данных PostgreSQL:

   Подключиться к базе данных PostgreSQL можно разными способами: через консольный клиент psql, графические инструменты (например, pgAdmin, DBeaver) или из приложений с помощью клиентских библиотек.

   Один из самых простых и быстрых способов — использовать командную строку psql от ServBay. Вы можете открыть терминал через кнопку «Терминал» в интерфейсе ServBay (переменные среды уже настроены) либо добавить директорию bin ServBay в переменную среды PATH вручную.

   В терминале используйте следующую команду для подключения к нужной базе данных (например, к базе servbay_geo_db под пользователем servbay):

   psql -U servbay -d servbay_geo_db

   Если у вас другие имя базы, пользователь или пароль — внесите соответствующие изменения в команду.

2. Проверка и включение расширения PostGIS (если еще не включено):

   pgRouting зависит от функций PostGIS. Для начала убедитесь, что расширение PostGIS уже работает. В командном окне psql выполните:

   CREATE EXTENSION IF NOT EXISTS postgis;

   Эта команда установит расширение PostGIS или пропустит установку, если оно уже включено.

3. Создание расширения pgRouting:

   В том же подключении к базе выполните SQL-команду:

   CREATE EXTENSION pgrouting;

   После успешного выполнения вы увидите сообщение CREATE EXTENSION.

4. Проверка установки:

   Чтобы проверить, что pgRouting активировано, выведите список расширений:

   \dx

   В списке должны отображаться postgis и pgrouting вместе с их параметрами.

Настройка pgRouting: подготовка дорожной сети и создание топологии

Алгоритмы pgRouting работают на таблице, моделирующей структуру дорожной сети. Обычно в такой таблице каждая строка — дорожный сегмент (ребро графа), у которого есть начальная и конечная точки (узлы), и веса для разных направлений (например, стоимость, время). Для корректных расчетов маршрутных задач из этих сегментов строится топология — структура, описывающая соединения между узлами.

Создание таблицы дорожной сети

Пример: создадим таблицу ways для хранения дорожной сети. В ней есть идентификатор сегмента, идентификаторы начального и конечного узлов, веса движения в обе стороны (cost, reverse_cost) и геометрия линии (с помощью PostGIS).

-- Создать таблицу для хранения дорожной сети
CREATE TABLE ways (
    id SERIAL PRIMARY KEY, -- Уникальный идентификатор сегмента
    source INTEGER,        -- ID начального узла
    target INTEGER,        -- ID конечного узла
    cost DOUBLE PRECISION, -- Стоимость движения по ребру (например, расстояние или время)
    reverse_cost DOUBLE PRECISION, -- Стоимость движения в обратном направлении
    geom GEOMETRY(LineString, 4326) -- Геометрия сегмента, LineString, SRID 4326 (WGS 84)
);

Объяснение:

• SERIAL PRIMARY KEY: генерация уникальных идентификаторов сегментов.
• source, target: идентификаторы узлов начала и конца сегмента (наполняются автоматически при создании топологии).
• cost, reverse_cost: веса движения по сегменту — для односторонней дороги значение reverse_cost можно задать как NULL или очень большим числом.
• geom: хранит геометрию сегмента (координаты линии) с помощью PostGIS, LineString в системе координат WGS 84 (4326).

Вставка примеров данных

Добавим тестовые сегменты в таблицу ways:

-- Добавить примеры дорожных сегментов
INSERT INTO ways (source, target, cost, reverse_cost, geom) VALUES
(1, 2, 1.0, 1.0, ST_SetSRID(ST_MakeLine(ST_MakePoint(116.4074, 39.9042), ST_MakePoint(116.4084, 39.9052)), 4326)),
(2, 3, 1.0, 1.0, ST_SetSRID(ST_MakeLine(ST_MakePoint(116.4084, 39.9052), ST_MakePoint(116.4094, 39.9062)), 4326)),
(3, 4, 1.0, 1.0, ST_SetSRID(ST_MakeLine(ST_MakePoint(116.4094, 39.9062), ST_MakePoint(116.4104, 39.9072)), 4326));

Объяснение:

• ST_MakePoint(x, y): создание точки по указанным долготе и широте.
• ST_MakeLine(point1, point2): формирование линии между двумя точками.
• ST_SetSRID(geometry, srid): установка системы координат геометрии.
• В примере три соединённых сегмента с одинаковым весом (1.0). Координаты соответствуют небольшому участку Пекина.

Создание топологии

Когда таблица дорожной сети готова, используйте функцию pgr_createTopology для генерации топологической структуры. Эта функция анализирует геометрию сегментов, распознаёт узлы (точки пересечения), заполняет столбцы source и target и создаёт новую таблицу с узлами (обычно [имя_таблицы]_vertices_pgr).

-- Построение топологии по таблице дорожной сети
-- Параметры:
-- 'ways': имя таблицы
-- 0.00001: допуск (tolerance) — максимум расстояния между точками, чтобы считать их одним узлом
-- 'geom': имя столбца с геометрией
-- 'id': имя столбца с идентификатором сегмента
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

Объяснение:

• pgr_createTopology: ключевая функция pgRouting для формирования графовой модели из дорожных данных.
• Значение допуска (tolerance) определяет, насколько близко точки должны располагаться, чтобы считаться одним узлом; выбор зависит от точности данных.

После выполнения этой функции в столбцах source и target в таблице ways появятся значения, а также будет создана таблица ways_vertices_pgr со всеми найденными узлами и их координатами.

Анализ маршрутов с помощью pgRouting

Когда топология построена, можно использовать различные алгоритмы pgRouting для анализа маршрутов. Вот несколько популярных примеров.

Поиск кратчайшего пути (алгоритм Дейкстры)

Наиболее частая задача — найти кратчайший путь между двумя узлами. Для этого есть реализация алгоритма Дейкстры.

-- Поиск кратчайшего пути от узла 1 до узла 4
-- Параметры:
-- 'SELECT id, source, target, cost FROM ways': SQL-запрос, возвращающий необходимые для графа столбцы
-- 1: ID начального узла
-- 4: ID конечного узла
-- directed := true: учитывать направление (различие весов cost и reverse_cost)
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM ways',
    1, -- ID начального узла
    4, -- ID конечного узла
    directed := true -- Для учёта обратных весов можно установить false или опустить
)
JOIN ways ON edge = ways.id; -- Присоединяем геометрию из таблицы ways

Объяснение:

• pgr_dijkstra: вызывает алгоритм кратчайшего пути Дейкстры.
• Первый параметр — SQL-запрос, выдающий ребра графа с полями id, source, target, cost. Для учета движений в обратную сторону добавьте reverse_cost и выставьте directed := false.
• Второй и третий параметры — ID начального и конечного узлов.
• JOIN ways ON edge = ways.id: позволяет получить координаты маршрута для последующей визуализации.

Задача коммивояжёра (TSP)

TSP — поиск кратчайшего пути, чтобы посетить заданный набор узлов, начав в указанной точке.

-- Поиск оптимального маршрута обхода узлов 1, 2, 3, 4, начиная с узла 1
-- Параметры:
-- 'SELECT id, x::float8 AS x, y::float8 AS y FROM ways_vertices_pgr': SQL-запрос с узлами и координатами
-- start_id := 1: ID начального узла
SELECT seq, node, edge, cost
FROM pgr_tsp(
    'SELECT id, ST_X(the_geom)::float8 AS x, ST_Y(the_geom)::float8 AS y FROM ways_vertices_pgr WHERE id IN (1, 2, 3, 4)', -- выборка узлов с координатами
    start_id := 1 -- ID начального узла
);

Объяснение:

• pgr_tsp: решает задачу коммивояжёра.
• Первый параметр — запрос, возвращающий идентификаторы и координаты нужных узлов (id, x, y).
• start_id: начальная точка маршрута.

Анализ зоны обслуживания (Driving Distance / Driving Time)

Этот анализ определяет, какие сегменты можно достичь из заданной точки при ограниченной стоимости (например, не дальше определённого расстояния).

-- Из узла 1 определить все сегменты, достижимые при стоимости не превышающей 2
-- Параметры:
-- 'SELECT id, source, target, cost FROM ways': SQL-запрос для графа
-- 1: ID стартового узла
-- 2: максимальная стоимость
-- directed := true: направленность дорог
SELECT seq, id1 AS node, id2 AS edge, cost, geom
FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM ways',
    1, -- ID стартового узла
    2, -- максимальная стоимость
    directed := true
)
JOIN ways ON edge = ways.id; -- Подключение геометрии маршрута

Объяснение:

• pgr_drivingDistance: выделяет зону досягаемости по стоимости.
• В параметры передаются запрос на граф, стартовый узел, максимальный лимит стоимости.
• Через JOIN можно сопоставить результаты с геометрией для визyализации.

Визуализация результатов маршрутизации

Визуализация результатов анализа — важный шаг для анализа и представления маршрутов. Используйте программы ГИС или библиотеки для веб-карт.

Использование настольных ГИС (например, QGIS)

QGIS — бесплатное десктопное ГИС-приложение с возможностью прямого подключения к PostgreSQL/PostGIS и отображения пространственных данных, включая результаты pgRouting.

1. Запустите QGIS.
2. Откройте Слой (Layer) > Управление источниками данных (Data Source Manager).
3. Слева выберите PostGIS.
4. Нажмите Создать (New) для добавления нового подключения.
5. Введите параметры подключения ServBay PostgreSQL (например, Host: localhost, Port: 5432, Database: servbay_geo_db, User: servbay, Password: ваш пароль). Для проверки соединения используйте кнопку «Тестировать подключение».
6. После успешного соединения откройте вашу базу — вы увидите таблицы, включая ways и ways_vertices_pgr.
7. Выберите нужную таблицу или представление (например, представление результата поиска кратчайших путей) и добавьте в QGIS для визуализации.

Визуализация в Web-приложении (Leaflet или OpenLayers)

Для Web-приложений понадобится бэкенд (например, на PHP, Node.js или Python в серверах ServBay), который будет выполнять запросы pgRouting и возвращать результат на фронтенд (чаще в формате GeoJSON). На клиенте отображать данные удобно с помощью библиотек Leaflet или OpenLayers.

Ниже — базовый пример HTML-файла с отображением статического маршрута в Leaflet. Для отображения реальных результатов маршрутизации:

1. Выполните нужный pgRouting-запрос на бэкенде.
2. Преобразуйте итоговые геометрии в GeoJSON или другой поддерживаемый формат.
3. Предоставьте результат через API фронтенду.
4. На стороне клиента загрузите GeoJSON через Leaflet с помощью функции L.geoJSON.

<!DOCTYPE html>
<html>
<head>
    <title>Пример визуализации pgRouting в ServBay</title>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css" />
    <script src="https://unpkg.com/leaflet/dist/leaflet.js"></script>
    <style>
        #map { height: 600px; width: 100%; } /* Размеры контейнера карты */
    </style>
</head>
<body>
    <h1>Визуализация результата pgRouting (ServBay)</h1>
    <div id="map"></div>

    <script>
        // Инициализация карты, установка центра и масштаба
        var map = L.map('map').setView([39.906, 116.409], 14); // Центр — пример массива данных

        // Добавление слоя OpenStreetMap
        L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
            attribution: '&copy; <a href="https://www.openstreetmap.org/copyright">OpenStreetMap</a> contributors'
        }).addTo(map);

        // Пример: добавить статические GeoJSON данные маршрутов (замените на динамические в реальных задачах)
        var geojsonData = {
            "type": "FeatureCollection",
            "features": [
                {
                    "type": "Feature",
                    "properties": {
                        "id": 1,
                        "cost": 1.0
                    },
                    "geometry": {
                        "type": "LineString",
                        "coordinates": [
                            [116.4074, 39.9042],
                            [116.4084, 39.9052]
                        ]
                    }
                },
                {
                    "type": "Feature",
                    "properties": {
                        "id": 2,
                        "cost": 1.0
                    },
                    "geometry": {
                        "type": "LineString",
                        "coordinates": [
                            [116.4084, 39.9052],
                            [116.4094, 39.9062]
                        ]
                    }
                },
                {
                    "type": "Feature",
                    "properties": {
                        "id": 3,
                        "cost": 1.0
                    },
                    "geometry": {
                        "type": "LineString",
                        "coordinates": [
                            [116.4094, 39.9062],
                            [116.4104, 39.9072]
                        ]
                    }
                }
            ]
        };

        // Добавить GeoJSON на карту через Leaflet
        L.geoJSON(geojsonData, {
            style: function (feature) {
                return {color: "#ff0000", weight: 4}; // Красная толстая линия
            }
        }).addTo(map);

        // Подгонка обзора карты под все объекты GeoJSON
        if (L.geoJSON(geojsonData).getBounds().isValid()) {
             map.fitBounds(L.geoJSON(geojsonData).getBounds());
        }


    </script>
</body>
</html>

Сохраните такой HTML-файл в корневом каталоге вашего сайта ServBay (например, /Applications/ServBay/www/pgrouting-demo/index.html). Затем откройте сайт через ServBay (например, по адресу http://pgrouting-demo.servbay.demo) — вы увидите карту с примером маршрута. В данном примере данные статичны, но его легко адаптировать под динамическую загрузку GeoJSON через API.

Важно учитывать

• Качество данных: Надежность результатов pgRouting сильно зависит от качества исходной дорожной сети: данные должны быть точными, топологически корректными и полными.
• Производительность: На крупных сетях вычисления маршрутов могут быть медленными. Используйте индексы, оптимизируйте сеть либо применяйте более производительные алгоритмы, если необходимо.
• Память: Большие топологии требуют достаточно памяти — убедитесь, что сервер PostgreSQL оснащён достаточными ресурсами.

Часто задаваемые вопросы (FAQ)

В: При выполнении CREATE EXTENSION pgrouting; возникает ошибка — расширение не найдено. Что делать? О: Прежде всего, удостоверьтесь, что пакет PostgreSQL в ServBay установлен и запущен. Затем проверьте версию базы данных — поддерживает ли она pgRouting, а также есть ли данное расширение в вашей сборке PostgreSQL (в ServBay обычно оно есть). Если ошибка повторяется, загляните в логи PostgreSQL и ServBay для получения подробностей. Также убедитесь, что вы подключаетесь под пользователем с нужными правами (servbay).

В: Как выбрать оптимальное значение допуска (tolerance) в функции pgr_createTopology? О: Это зависит от точности ваших геоданных. Допуск определяет, на каком минимальном расстоянии две вершины считаются одним и тем же узлом. Если используете весьма точные (например, GPS) данные — допустимо ставить очень маленький допуск (например, 0.000001). Если данные неточные или собраны из разных источников — допуска стоит выбрать побольше, чтобы не было разрывов. Слишком большой допуск может вызвать ошибочные объединения несвязанных сегментов!

В: Как учитывать односторонние дороги или запрет разворота? О: Через поля cost и reverse_cost в таблице ways. Для односторонней дороги в обратную сторону reverse_cost задается как NULL или очень большое число (движение невозможно). Запрет разворота обычно реализуется в более сложных моделях или через последующую обработку результата маршрутизации — у pgRouting есть расширенные функции для работы с такими случаями.

Итоги

С помощью ServBay развернуть локальную базу PostgreSQL с поддержкой pgRouting очень просто. Нужно лишь выполнить несколько SQL-команд для включения расширений, затем подготовить таблицу дорожной сети и создать топологию — и можно использовать мощные алгоритмы маршрутизации pgRouting для геоаналитики. Совместите это с настольными ГИС или веб-картами, чтобы получать визуализацию маршрутов и предложить вашему проекту надежную геопространственную платформу. ServBay снимает головную боль с настройкой окружения, позволяя разработчику сосредоточиться на логике и функциях приложения.