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

pgRouting — це потужний розширювальний модуль для баз даних PostgreSQL і PostGIS, що пропонує розширені функції маршрутизації й мережевого аналізу геопросторових даних. Завдяки pgRouting розробники можуть виконувати складні запити по дорожніх мережах: знаходити найкоротший шлях між двома точками, вирішувати задачу комівояжера (TSP) або проводити аналіз сервісних зон. Такі можливості незамінні для створення картографічних застосунків, планування логістики, транспортного аналізу тощо.

У цій статті докладно описано, як легко активувати розширення pgRouting для PostgreSQL у локальному середовищі розробки ServBay, а також налаштувати й виконати базовий маршрутизаційний аналіз.

Огляд

ServBay — це зручне комплексне локальне середовище розробки для macOS, у якому вже попередньо інтегровані різні веб-сервери, системи баз даних і мови програмування. Пакет PostgreSQL у складі ServBay вже містить розширення pgRouting й PostGIS. Це означає, що вам не потрібно вручну завантажувати чи компілювати ці розширення — достатньо просто їх активувати у своїй базі даних.

Попередні умови

Перед початком роботи з pgRouting переконайтеся, що виконані такі умови:

1. ServBay встановлено й запущено: Якщо ще не встановили ServBay, перейдіть на офіційний сайт ServBay, завантажте та встановіть останню версію.
2. Пакет PostgreSQL у ServBay встановлений й активований: У панелі ServBay переконайтеся, що PostgreSQL встановлений і перебуває в активному стані.
3. Є базові знання SQL і роботи з PostgreSQL: Припускається, що ви знайомі з основами SQL та PostgreSQL.
4. Ознайомлені з основами PostGIS: pgRouting використовує PostGIS для роботи з геопросторовими типами даних і функціями. Переконайтеся, що розширення PostGIS активовано у вашій базі даних. У передвстановленому пакеті PostgreSQL від ServBay PostGIS зазвичай є.

Встановлення й активація розширення pgRouting

Файли розширення pgRouting уже доступні у складі ServBay, тож достатньо активувати це розширення у потрібній базі даних.

1. Підключення до бази даних 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. Перед активацією pgRouting переконайтеся у наявності PostGIS, ввівши в psql:

   CREATE EXTENSION IF NOT EXISTS postgis;

   Ця команда створить або пропустить створення розширення PostGIS, якщо воно вже є.

3. Створення розширення pgRouting:

   В тій же сесії бази виконайте:

   CREATE EXTENSION pgrouting;

   Якщо все успішно, ви побачите повідомлення на кшталт CREATE EXTENSION.

4. Перевірка встановлення:

   Для перевірки активних розширень виконайте:

   \dx

   У списку має бути видно postgis і pgrouting з відповідними відомостями.

Налаштування pgRouting: підготовка мережі й створення топології

Алгоритми pgRouting працюють із таблицею, що зберігає мережу доріг — переважно це таблиця ліній (відрізків) із прив’язкою до початкової та кінцевої точок (вершин) і ваг (наприклад, відстань чи час). Щоби забезпечити коректний обрахунок маршрутів, із даних формують “топологічну” структуру, яка визначає зв’язки між відрізками та вузлами мережі.

Створення таблиці з мережею доріг

Приклад створення базової таблиці ways для зберігання дорожньої мережі. Таблиця містить ID відрізка, вихідний і кінцевий вузол, вартість проходження (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: автоматична генерація унікального ID для відрізків.
• source, target: вказують на ID вузлів (вершин) початку й кінця відрізка. Можуть бути прописані вручну або згенеровані автоматично під час топологізації.
• cost, reverse_cost: ваги лінії. Для одностороннього руху вартість у зворотному напрямку (reverse_cost) можна встановити як NULL або максимальне значення.
• geom: геометричний об’єкт типу LineString у географічних координатах (SRID 4326 — система WGS 84).

Додавання зразкових даних

Для прикладу додамо кілька відрізків у таблицю 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, а також створює додаткову таблицю вузлів (наприклад, ways_vertices_pgr).

-- Створити топологію для дорожньої мережі
-- Параметри:
-- 'ways': назва таблиці з мережею
-- 0.00001: допуск (tolerance) для визначення, чи точки вважаються однією вершиною
-- 'geom': назва колонки із геометрією
-- 'id': назва колонки з ID відрізка
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

Пояснення:

• pgr_createTopology: основна функція pgRouting для побудови мережевої топології.
• Допуск визначає, на якій відстані точки рахуються однією вершиною — важливо підібрати коректне значення згідно з точністю даних.

Після виконання цієї функції поля source і target в таблиці ways будуть заповнені, а таблиця ways_vertices_pgr міститиме всі знайдені вузли з їх координатами.

Маршрутизаційний аналіз із pgRouting

Після створення топології можна використовувати алгоритми pgRouting для різних типів маршрутизаційного аналізу. Нижче кілька поширених прикладів.

Аналіз найкоротшого шляху (Dijkstra)

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

-- Знайти найкоротший шлях від вузла 1 до 4
-- Параметри:
-- 'SELECT id, source, target, cost FROM ways': джерело графа має містити id, source, target і cost
-- 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 -- для обліку reverse_cost, можна поставити false
)
JOIN ways ON edge = ways.id; -- під’єднання до оригінальної таблиці ways для отримання геометрії

Пояснення:

• pgr_dijkstra: реалізація алгоритму Дейкстри в pgRouting.
• Перший параметр — SQL-запит, що передає інформацію про ребра мережі.
• Другий і третій — ID початкового і кінцевого вузлів.
• За допомогою JOIN можна отримати геометрію та зручно візуалізувати результат.

Задача комівояжера (Traveling Salesperson Problem, TSP)

TSP дозволяє знайти маршрут для обходу кількох конкретних точок з мінімальними витратами, починаючи з обраного вузла.

-- Розв’язання TSP для обходу вузлів 1, 2, 3, 4, старт з вершини 1
-- Параметри:
-- 'SELECT id, x::float8 AS x, y::float8 AS y FROM ways_vertices_pgr': вибірка вузлів і їх координат
-- 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
);

Пояснення:

• pgr_tsp: розв’язання задачі комівояжера.
• Перший параметр — SQL-запит із вузлами та їх координатами (x, y).
• start_id — ID стартової вершини.

Аналіз сервісних зон (Driving Distance / Driving Time)

Аналіз сервісних зон показує, які ділянки мережі доступні з однієї або кількох точок за заданої максимальної вартості (відстані чи часу).

-- Знайти всі доступні відрізки на відстані до 2 одиниць від вузла 1
-- Параметри:
-- 'SELECT id, source, target, cost FROM ways': джерело графа
-- 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: визначає зону досяжності в мережі.
• Параметри аналогічні до Dijkstra.
• Результат зручно використовувати для візуалізації доступних зон.

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

Візуалізація результатів — ключовий етап аналізу. Для цього можна скористатися GIS-додатками на ПК або інтегрувати бібліотеки для карт у свої веб-застосунки.

Використання настільних GIS-інструментів (наприклад, QGIS)

QGIS — вільний та відкритий GIS застосунок, який може напряму підключатися до PostgreSQL/PostGIS і відображати просторові дані, включаючи результати pgRouting.

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

Веб-візуалізація (Leaflet або OpenLayers)

Для інтеграції аналізу в веб-застосунку потрібен бекенд (наприклад, PHP, Node.js, Python у складі ServBay) для виконання запитів pgRouting і видачі результату (зазвичай у GeoJSON) у фронтенд. У браузері можна використовувати бібліотеки, як Leaflet чи OpenLayers для відображення даних.

Нижче спрощений HTML-приклад, як додати статичну лінію на карту з використанням Leaflet. Для інтеграції реального результату:

1. На сервері виконуйте маршрутизаційний запит pgRouting.
2. Конвертуйте геометрію у формат GeoJSON.
3. Видавайте GeoJSON через API у фронтенд.
4. У фронтенді завантажуйте й відображайте GeoJSON методом Leaflet L.geoJSON.

<!DOCTYPE html>
<html>
<head>
    <title>Приклад веб-візуалізації ServBay pgRouting</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 із результатами pgRouting
        // У реальному застосуванні geojsonData слід отримувати через AJAX із бекенду
        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 на карту з виділенням маршруту
        L.geoJSON(geojsonData, {
            style: function (feature) {
                return {color: "#ff0000", weight: 4}; // Червона товста лінія
            }
        }).addTo(map);

        // Автоматичне масштабування на видимі об’єкти
        if (L.geoJSON(geojsonData).getBounds().isValid()) {
             map.fitBounds(L.geoJSON(geojsonData).getBounds());
        }


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

Збережіть цей файл у кореневій директорії сервера в ServBay (наприклад, /Applications/ServBay/www/pgrouting-demo/index.html). Після цього відкрийте його через ServBay (наприклад, http://pgrouting-demo.servbay.demo) — побачите карту з прикладом маршруту. Зауважте, що це статичний приклад — у реальному проєкті GeoJSON-form дані підвантажуватимуться динамічно після маршрутизаційного запиту.

Рекомендації

• Якість даних: Точність аналізу pgRouting напряму залежить від якості й коректності просторових даних. Перевіряйте топологію, повноту й актуальність своєї мережі.
• Продуктивність: У разі великих мереж обчислення маршрутів може бути повільним. Додавайте індекси, спрощуйте мережу, обирайте оптимальні алгоритми.
• Витрати пам’яті: Великі топології можуть споживати багато ОЗП. Переконайтеся, що сервер має відповідні ресурси.

Поширені питання (FAQ)

Питання: Що робити, якщо при CREATE EXTENSION pgrouting; виникає помилка, що розширення не знайдено?
Відповідь: Перевірте, чи встановлений і запущений пакет PostgreSQL у ServBay. Переконайтеся, що версія сервера та пакета підтримує pgRouting, і що розширення включене у вашу збірку (у стандартному пакеті ServBay воно має бути). Якщо проблема лишається — перевірте журнали ServBay/PostgreSQL на докладнішу інформацію. Не забудьте підключитися під користувачем із достатніми правами (servbay).

Питання: Як правильно обрати значення допуску (tolerance) у pgr_createTopology?
Відповідь: Допуск залежить від точності ваших геоданих. Він визначає мінімальну відстань, на якій дві вершини вважаються однією. Для високоточного GPS-джерела використовуйте невеликі значення (наприклад, 0.000001). Для даних грубішої точності — трохи більші, щоб забезпечити з’єднаність мережі. Завелике значення може призвести до помилкових зв’язків не пов’язаних відрізків.

Питання: Як врахувати односторонній рух або заборону розворотів?
Відповідь: Для одностороннього руху використовуйте колонки cost і reverse_cost: для односторонньої дороги значення reverse_cost ставимо NULL або дуже велике (непрохідність). Для заборони розворотів та складніших обмежень потрібні вдосконалені моделі дорожньої мережі чи пост-обробка результатів маршрутизації — в pgRouting є додаткові функції для цього.

Підсумки

ServBay значно спрощує розгортання локального середовища PostgreSQL із розширенням pgRouting. Достатньо декількох SQL-команд для активації й початкового налаштування. Після підготовки мережі й топології використовуйте потужні алгоритми pgRouting для складного геопросторового аналізу. Завдяки інтеграції з настільними GIS-програмами й веб-картографічними бібліотеками ви можете легко візуалізувати й застосовувати результати для своїх геопросторових чи веб-проєктів. Простота налаштування в ServBay дозволяє розробникам зосередитися на логіці застосунку та створенні цінних функцій.