ServBay에서 pgRouting을 활용한 공간 라우팅 분석

pgRouting은 PostgreSQL 및 PostGIS 데이터베이스의 강력한 확장 모듈로, 공간 데이터를 위한 다양한 라우팅 및 네트워크 분석 기능을 제공합니다. pgRouting을 활용하면 교통망 데이터에서 두 지점 간 최단 경로 찾기, 여행자 문제(TSP) 해결, 서비스 영역 분석과 같은 복잡한 쿼리를 실행할 수 있습니다. 이러한 기능은 지도 애플리케이션, 물류 경로 최적화, 교통 분석 등 다양한 분야에서 유용하게 사용됩니다.

이 문서에서는 로컬 웹 개발 환경인 ServBay에서 PostgreSQL 데이터베이스에 pgRouting 확장을 쉽게 활성화하고, 기본 설정 및 라우팅 분석을 수행하는 방법을 단계별로 안내합니다.

개요

ServBay는 macOS 개발자를 위한 웹 서버, 데이터베이스, 프로그래밍 언어를 미리 통합한 편리한 로컬 개발 환경을 제공합니다. ServBay의 PostgreSQL 패키지에는 이미 pgRouting과 PostGIS 확장이 포함되어 있어, 별도의 다운로드나 빌드 없이 데이터베이스에서 확장만 활성화하면 바로 사용할 수 있습니다.

사전 준비 사항

pgRouting을 사용하기 전에 아래 조건을 확인하세요.

1. ServBay 설치 및 실행: 아직 ServBay를 설치하지 않았다면 ServBay 공식 웹사이트에서 최신 버전을 다운로드 후 설치하세요.
2. ServBay에서 PostgreSQL 패키지 설치 및 실행: ServBay 앱에서 PostgreSQL 패키지가 설치되어 있고 "실행 중" 상태인지 확인하세요.
3. 기본 SQL 및 PostgreSQL 사용법 이해: 이 문서는 PostgreSQL 및 SQL 쿼리 기본 사용법을 알고 있다고 가정합니다.
4. PostGIS 기본 지식: pgRouting은 공간 데이터 타입 및 함수 처리를 위해 PostGIS가 필요합니다. 데이터베이스에 PostGIS 확장이 활성화되어 있어야 하며, ServBay의 PostgreSQL 패키지에는 일반적으로 PostGIS가 함께 제공됩니다.

pgRouting 확장 설치 및 활성화

ServBay에 이미 pgRouting 확장 파일이 포함되어 있으므로, 사용하는 특정 데이터베이스에서 확장만 활성화하면 됩니다.

1. PostgreSQL 데이터베이스에 연결:

   PostgreSQL 데이터베이스는 명령줄 도구(psql), GUI 도구(예: pgAdmin, DBeaver), 또는 프로그래밍 언어의 클라이언트 라이브러리 등 다양한 방법으로 연결할 수 있습니다.

   ServBay에서 제공하는 psql 명령줄 도구 사용이 가장 간단합니다. ServBay 앱의 "터미널" 버튼을 클릭하면 환경 변수가 설정된 터미널이 열립니다. 또는 ServBay의 bin 디렉터리를 시스템 PATH에 직접 추가할 수도 있습니다.

   예를 들어, servbay_geo_db 이름의 데이터베이스와 기본 사용자 servbay로 접속하려면 아래와 같이 입력합니다.

   psql -U servbay -d servbay_geo_db

   데이터베이스명, 사용자명, 비밀번호가 다르다면 적절히 수정하세요.

2. PostGIS 확장 확인 및 활성화 (미설정 시):

   pgRouting은 작동을 위해 PostGIS가 필요하니, 아래 명령으로 PostGIS를 먼저 활성화하세요.

   CREATE EXTENSION IF NOT EXISTS postgis;

   이미 활성화되어 있다면 이 명령은 무시됩니다.

3. pgRouting 확장 생성:

   같은 데이터베이스 연결에서 아래 SQL로 pgRouting 확장을 설치합니다.

   CREATE EXTENSION pgrouting;

   성공하면 CREATE EXTENSION 메시지가 출력됩니다.

4. 설치 확인:

   아래 명령어로 설치된 확장 목록을 확인합니다.

   \dx

   출력에 postgis와 pgrouting 확장이 포함되어 있는지 확인합니다.

pgRouting 설정: 도로망 데이터 준비와 토폴로지 생성

pgRouting 알고리즘은 네트워크 구조를 표현한 테이블에서 동작합니다. 일반적으로 각 도로 구간(라인)이 소스/타겟 노드와 가중치(예: 거리, 시간)를 포함한 한 행으로 표현됩니다. 네트워크의 연결 구조를 명확히 하기 위해 토폴로지(연결 정보)를 생성해야 라우팅 계산이 가능합니다.

도로망 데이터 테이블 생성

아래는 도로망 데이터를 저장할 ways라는 기본 테이블 예시입니다. 이 테이블은 각 도로 구간의 ID, 시작 및 끝 노드 ID, 정/역방향 통행 비용(cost, reverse_cost), 그리고 PostGIS의 GEOMETRY 타입을 사용한 구간의 도형 정보를 포함합니다.

-- 도로망 데이터를 저장할 테이블 생성
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: PostGIS의 GEOMETRY 타입으로 도로의 위치 정보를 갖습니다. 4326은 WGS 84 좌표계(SRID)입니다.

예시 데이터 삽입

아래는 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): 경도/위도로 PostGIS Point 생성.
• ST_MakeLine(point1, point2): 두 지점을 잇는 선(LineString) 생성.
• ST_SetSRID(geometry, srid): 형상에 좌표계(SRID) 부여.
• 예시 데이터는 세 개의 서로 연결된 도로로서, 모두 비용이 1.0입니다. 좌표는 베이징 시내 일대의 예시입니다.

토폴로지 생성

도로망 테이블 준비가 끝나면 pgr_createTopology 함수를 이용해 토폴로지를 생성할 수 있습니다. 도로들의 형상을 분석해 연결된 포인트(노드)를 식별하고, source와 target 컬럼을 채워주며, 별도의 노드 테이블(기본적으로 [테이블명]_vertices_pgr)을 생성합니다.

-- 도로망 토폴로지 생성
-- 매개변수:
-- 'ways': 도로 테이블명
-- 0.00001: 허용 오차(tolerance) - 두 점이 어느 정도 가까우면 같은 노드로 볼 것인지
-- 'geom': 도형 정보가 있는 컬럼명
-- 'id': 도로 구간의 고유 ID 컬럼명
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

설명:

• pgr_createTopology: 네트워크의 노드-에지 모델을 구축하는 핵심 함수.
• 허용 오차(tolerance)는 데이터의 정밀도와 지도의 크기에 따라 적절히 조정하세요.

실행 후 ways 테이블의 source와 target 컬럼이 채워지고, 자동 생성된 ways_vertices_pgr 테이블에 노드와 좌표가 등록됩니다.

pgRouting으로 라우팅 분석하기

토폴로지가 완성되면 pgRouting이 제공하는 다양한 알고리즘 함수를 활용해 실질적인 라우팅 분석을 할 수 있습니다. 대표적인 알고리즘 몇 가지를 살펴봅니다.

최단 경로 분석(Dijkstra)

두 노드 간 최단 경로 찾기는 가장 일반적인 라우팅 요구입니다. pgRouting에서는 Dijkstra 알고리즘을 지원합니다.

-- 노드 1번에서 4번까지 최단 경로 찾기
-- 매개변수:
-- 'SELECT id, source, target, cost FROM ways': 그래프를 구성할 쿼리 (id, source, target, cost 필수)
-- 1: 시작 노드 ID
-- 4: 목표 노드 ID
-- directed := true: 방향 그래프 여부(정방향, 역방향 비용 사용)
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: Dijkstra 최단 경로 알고리즘 실행 함수.
• 첫 번째 매개변수 쿼리에서 반드시 id, source, target, cost를 포함해야 하며, 역방향 허용 시 reverse_cost도 필요.
• 두 번째/세 번째 매개변수로 출발/도착 노드ID 입력.
• 결과를 ways 테이블과 조인하면 형상(geom) 정보를 함께 사용할 수 있어 지도 시각화에 유용합니다.

여행자 문제 (TSP: Traveling Salesperson Problem)

TSP 알고리즘은 지정된 여러 지점을 최적의 비용으로 한 번씩 방문하는 순환 경로를 찾습니다.

-- 1, 2, 3, 4번 노드를 모두 방문하는 TSP (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 -- 시작 노드ID
);

설명:

• pgr_tsp: 여행자 문제(외판원 순회) 알고리즘 실행.
• 쿼리에는 id, x, y(노드 좌표)가 반드시 포함되어야 하며, 주로 ways_vertices_pgr에서 조회합니다.
• start_id: 시작 지점을 지정합니다.

서비스 영역 분석(Driving Distance/Time)

서비스 영역 분석은 특정 시작점에서 주어진 비용(거리, 시간) 내에 도달 가능한 모든 영역이나 도로를 찾는 기능입니다.

-- 노드 1번에서 비용 2 이하로 도달 가능한 모든 도로 찾기
-- 매개변수:
-- '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; -- 결과와 ways 테이블 조인해서 형상도 불러오기

설명:

• pgr_drivingDistance: 서비스 영역 분석 실행 함수.
• 첫 번째 매개변수는 그래프 쿼리로 pgr_dijkstra와 동일하게 작성.
• 두 번째 매개변수는 시작 노드ID, 세 번째는 최대 허용 비용(거리 등).
• 결과와 ways 테이블을 조인해 도형 정보를 시각화에 사용할 수 있습니다.

라우팅 결과 시각화

pgRouting 분석 결과를 시각화하는 것은 경로 분석 결과를 이해하고 외부에 제공하는 데 매우 중요합니다. 다음은 대표적인 두 가지 방법입니다.

데스크탑 GIS(QGIS 등) 활용

QGIS는 무료 오픈소스 데스크탑 GIS 프로그램으로, PostgreSQL/PostGIS 데이터베이스에 직접 연결해 공간 데이터를 쉽게 불러오고 시각화할 수 있습니다.

1. QGIS 실행
2. [레이어] > [데이터 소스 관리자] 메뉴 선택
3. 좌측 메뉴에서 PostGIS 선택
4. 새로 만들기(New) 버튼 클릭, 데이터베이스 연결 정보 입력
5. 예: 호스트(Host): localhost, 포트(Port): 5432, 데이터베이스(Database): servbay_geo_db, 사용자(User): servbay, 비밀번호(Password): PostgreSQL 비밀번호
6. "연결 테스트"로 연결 확인
7. 데이터베이스가 연결되면, ways, ways_vertices_pgr 등 필요한 테이블 또는 뷰를 선택하고 추가(Add) 버튼으로 레이어를 지도에 올릴 수 있습니다.

웹 애플리케이션에서 시각화(Leaflet, OpenLayers)

웹 애플리케이션에서는 백엔드(예: PHP, Node.js, Python)를 통하여 pgRouting 쿼리를 실행하고, 결과를 GeoJSON 형식으로 프론트엔드에 제공해야 합니다. 프론트엔드에서는 Leaflet, OpenLayers 등 지도 라이브러리를 활용해 데이터를 시각화할 수 있습니다.

아래는 Leaflet에서 정적 GeoJSON 라인을 추가하는 기본 HTML 예제입니다. 동적으로 라우팅 결과를 표시하려면:

1. 백엔드에서 pgRouting 쿼리를 실행
2. 도로 구간의 형상 정보를 GeoJSON 등 프론트엔드가 이해할 형식으로 변환
3. API 엔드포인트로 GeoJSON 데이터를 제공
4. 프론트엔드 자바스크립트에서 Leaflet L.geoJSON 등을 통해 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>ServBay pgRouting 결과 시각화</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);

        // 예시: pgRouting 쿼리 결과를 변환한 GeoJSON 데이터 추가
        // 실제 사용 시 여기 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]
                        ]
                    }
                }
            ]
        };

        // L.geoJSON으로 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>

위 HTML 파일을 ServBay 웹사이트 루트(예: /Applications/ServBay/www/pgrouting-demo/index.html)에 저장하고, ServBay를 통해 접속(예: http://pgrouting-demo.servbay.demo)하면 예시 경로가 표시된 지도를 볼 수 있습니다. 실제 프로젝트에서는 백엔드와 연동해 동적으로 라우팅 결과를 표시하세요.

유의사항

• 데이터 품질: pgRouting 결과의 신뢰도는 입력 도로 데이터 품질에 달려 있습니다. 데이터의 정확성, 완전성, 토폴로지 구조의 일관성을 보장하세요.
• 성능: 대규모 도로망에서는 라우팅 계산이 느려질 수 있습니다. 인덱스 활용, 도로망 단순화, 고성능 알고리즘을 도입하세요.
• 메모리: 큰 토폴로지 구조는 많은 메모리를 소모하니, 데이터베이스 서버 사양에 주의하세요.

자주 묻는 질문 (FAQ)

Q: CREATE EXTENSION pgrouting; 명령에서 확장이 없다는 오류가 발생합니다. 어떻게 하죠?
A: ServBay에서 PostgreSQL 패키지가 정상적으로 설치 및 실행 중인지 확인하세요. 연결한 데이터베이스 버전과 ServBay에 포함된 PostgreSQL 버전이 pgRouting을 지원하는지도 점검해야 합니다. 문제가 계속된다면 ServBay나 PostgreSQL의 로그를 참고하세요. 또한 데이터베이스에 충분한 권한(예: servbay 사용자)으로 접속했는지도 꼭 확인하세요.

Q: pgr_createTopology의 허용 오차(tolerance)는 어떻게 정해야 하나요?
A: 오차 허용값은 공간 데이터의 정밀도에 따라 결정됩니다. 이 값은 두 점 사이가 어느 정도 가까우면 같은 노드로 간주하는 최소 거리입니다. GPS 등 아주 정밀한 데이터일 경우 0.000001 등 아주 작은 값이 적합하고, 여러 출처의 다양한 품질 데이터라면 약간 더 큰 값을 사용할 수 있습니다. 값이 너무 크면 연결되지 않아야 할 도로가 잘못 연결될 위험이 있으니 주의하세요.

Q: 일방통행 도로, 유턴 금지 등 교통 규칙은 어떻게 처리하나요?
A: ways 테이블의 cost 및 reverse_cost 컬럼으로 처리할 수 있습니다. 일방통행 도로의 역방향(reverse_cost)은 NULL 또는 매우 큰 값(경로 불가 의미)으로 설정하세요. 유턴 금지 등 복잡한 조건은 네트워크 모델을 더욱 정교하게 설계하거나, 라우팅 결과 후처리를 통해 구현할 수 있습니다. pgRouting은 이런 고급 상황 처리를 위한 다양한 기능을 제공합니다.

요약

ServBay에서는 로컬 개발 환경에 PostgreSQL 및 pgRouting 지원을 쉽게 구축할 수 있습니다. SQL 명령 몇 줄로 확장을 설치 및 활성화하고, 도로망 데이터를 준비해 토폴로지를 만들면, pgRouting의 강력한 공간 라우팅 알고리즘을 활용할 수 있습니다. 데스크탑 GIS나 웹 지도 라이브러리와 연계하면 라우팅 결과를 직관적으로 시각화하고, 웹 서비스나 공간 프로젝트 구축에 효과적인 데이터 지원이 가능합니다. ServBay는 환경 설정의 복잡함을 줄여, 개발자가 비즈니스 로직과 서비스 구현에 집중할 수 있도록 도와줍니다.