pgRouting User Guide

pgRouting is an extension module for PostgreSQL and PostGIS, offering various routing algorithms and functions such as shortest path, traveling salesman problem, and service area analysis. It is suitable for fields such as transportation, logistics, and network analysis. This article will detail how to install and use pgRouting in ServBay.

Installing pgRouting

ServBay already comes with the pgRouting extension module. You just need to enable it in the database. Here are the steps to enable pgRouting:

Connect to PostgreSQL database:
bash
```
psql -U your_username -d your_database
```
1
Create extension:
sql
```
CREATE EXTENSION pgrouting;
```
1
Verify installation:
sql
```
\dx
```
1

Configuring pgRouting

After enabling pgRouting, you can create and manage road network data and use various routing algorithms for analysis.

Creating Road Network Data Table

Here is an example demonstrating how to create a table containing road network data.

Create table:

sql

CREATE TABLE ways (
    id SERIAL PRIMARY KEY,
    source INTEGER,
    target INTEGER,
    cost DOUBLE PRECISION,
    reverse_cost DOUBLE PRECISION,
    geom GEOMETRY(LineString, 4326)
);

Insert sample data:

sql

INSERT INTO ways (source, target, cost, reverse_cost, geom) VALUES
(1, 2, 1.0, 1.0, ST_GeomFromText('LINESTRING(116.4074 39.9042, 116.4084 39.9052)', 4326)),
(2, 3, 1.0, 1.0, ST_GeomFromText('LINESTRING(116.4084 39.9052, 116.4094 39.9062)', 4326)),
(3, 4, 1.0, 1.0, ST_GeomFromText('LINESTRING(116.4094 39.9062, 116.4104 39.9072)', 4326));

Creating Topology

Before conducting routing analysis, you need to create a topology for the road network data.

Create topology:

sql

SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

Performing Routing Analysis with pgRouting

Below are some common examples of routing analysis.

Shortest Path

Query shortest path:

sql

SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM ways',
    1, 4, directed := true
);

Traveling Salesman Problem

Solving Traveling Salesman Problem:

sql

SELECT * FROM pgr_tsp(
    'SELECT id, x::float8 AS x, y::float8 AS y FROM ways_vertices_pgr',
    start_id := 1
);

Service Area Analysis

Service area analysis:

sql

SELECT * FROM pgr_drivingDistance(
    'SELECT id, source, target, cost FROM ways',
    1, 2, directed := true
);

Visualizing Routing Results

You can use various GIS tools (such as QGIS) or web mapping services (such as Leaflet, OpenLayers) to visualize the results of pgRouting.

Using QGIS

Connect to PostgreSQL database:
- Open QGIS
- Select "Data Source Manager" -> "PostGIS"
- Enter database connection information and connect
Load road network data:
- Select the table or view you need to load
- Click "Add" button

Using Leaflet

Create Web map:

html

<!DOCTYPE html>
<html>
<head>
    <title>Leaflet pgRouting Example</title>
    <link rel="stylesheet" href="https://unpkg.com/leaflet/dist/leaflet.css" />
    <script src="https://unpkg.com/leaflet/dist/leaflet.js"></script>
</head>
<body>
    <div id="map" style="width: 600px; height: 400px;"></div>
    <script>
        var map = L.map('map').setView([39.9042, 116.4074], 13);
        L.tileLayer('https://{s}.tile.openstreetmap.org/{z}/{x}/{y}.png', {
            attribution: '© OpenStreetMap contributors'
        }).addTo(map);

        // Add a polyline from pgRouting data
        var polyline = L.polyline([
            [39.9042, 116.4074],
            [39.9052, 116.4084],
            [39.9062, 116.4094],
            [39.9072, 116.4104]
        ]).addTo(map);
    </script>
</body>
</html>

Summary

pgRouting is a powerful routing extension module. With simple configuration and usage, you can achieve efficient routing analysis in PostgreSQL. ServBay already includes the pgRouting extension module. Follow the steps in this article to install and configure it, and you can start using it. With various routing algorithms and analysis functions, you can better utilize road network data to provide reliable database support for your applications.

pgRouting User Guide ​

Installing pgRouting ​

Configuring pgRouting ​

Creating Road Network Data Table ​

Creating Topology ​

Performing Routing Analysis with pgRouting ​

Shortest Path ​

Traveling Salesman Problem ​

Service Area Analysis ​

Visualizing Routing Results ​

Using QGIS ​

Using Leaflet ​

Summary ​