在 ServBay 中使用 pgRouting 进行地理空间路由分析

pgRouting 是 PostgreSQL 和 PostGIS 数据库的强大扩展模块，专门为地理空间数据提供了丰富的路由和网络分析功能。利用 pgRouting，开发者可以在路网数据上执行复杂的查询，例如查找两点之间的最短路径、解决旅行商问题 (TSP) 或进行服务区分析。这些功能在构建地图应用、物流规划、交通分析等场景中非常有用。

本文将详细介绍如何在 ServBay 这个本地 Web 开发环境中，轻松地为您的 PostgreSQL 数据库启用 pgRouting 扩展，并进行基本的配置和路由分析。

概述

ServBay 为 macOS 上的开发者提供了一个便捷的一体化本地开发环境，预集成了多种 Web 服务器、数据库和编程语言。ServBay 提供的 PostgreSQL 软件包已经包含了 pgRouting 和 PostGIS 扩展。这意味着您无需手动下载和编译这些扩展，只需在您的数据库中简单启用即可使用。

前提条件

在开始使用 pgRouting 之前，请确保您满足以下条件：

已安装并运行 ServBay： 如果您还没有安装 ServBay，请访问 ServBay 官方网站下载并安装最新版本。
ServBay 中已安装并启动 PostgreSQL 软件包： 在 ServBay 应用界面中，确认 PostgreSQL 软件包已安装并处于“运行中”状态。
了解基本的 SQL 和 PostgreSQL 操作： 本文假设您对 PostgreSQL 数据库和 SQL 查询有基本了解。
了解 PostGIS 基础知识： pgRouting 依赖于 PostGIS 来处理地理空间数据类型和函数。确保您的数据库中已启用 PostGIS 扩展。ServBay 预装的 PostgreSQL 软件包通常会包含 PostGIS。

安装和启用 pgRouting 扩展

ServBay 已经为您准备好了 pgRouting 扩展文件，您只需在需要使用它的特定数据库中启用该扩展即可。

连接到 PostgreSQL 数据库：
您可以通过多种方式连接到 PostgreSQL 数据库，例如使用命令行工具 psql、图形化工具 (如 pgAdmin, DBeaver) 或编程语言的客户端库。
使用 ServBay 提供的 psql 命令行工具连接是常见且直接的方式。您可以通过 ServBay 应用界面的“终端”按钮快速打开一个已配置好环境变量的终端窗口，或者手动将 ServBay 的 bin 目录添加到您的系统 PATH 中。
在终端中，使用以下命令连接到您的目标数据库（例如，一个名为 servbay_geo_db 的数据库，使用默认的 servbay 用户）：
bash
```
psql -U servbay -d servbay_geo_db
```
1
如果您的数据库名称、用户名或密码不同，请相应修改命令。
检查并启用 PostGIS 扩展（如果尚未启用）：
pgRouting 依赖于 PostGIS。在启用 pgRouting 之前，请确保 PostGIS 已经启用。在 psql 提示符下输入：
sql
```
CREATE EXTENSION IF NOT EXISTS postgis;
```
1
这条命令会创建或跳过创建 PostGIS 扩展。
创建 pgRouting 扩展：
在同一个数据库连接中，执行以下 SQL 命令来创建 pgRouting 扩展：
sql
```
CREATE EXTENSION pgrouting;
```
1
如果成功，您将看到类似 CREATE EXTENSION 的输出。
验证安装：
您可以通过列出已安装的扩展来验证 pgRouting 是否已成功启用：
sql
```
\dx
```
1
在输出列表中，您应该能看到 postgis 和 pgrouting 及其相关信息。

配置 pgRouting：准备路网数据和创建拓扑

pgRouting 算法需要在表示路网结构的表上运行。通常，这涉及一个包含线段（路段）的表，每个线段有起点、终点和权重（如距离、时间）。为了高效地进行路由计算，还需要从这些线段数据中构建一个“拓扑”结构，它表示路段之间的连接关系（节点）。

创建路网数据表

以下是一个基本的示例，展示如何创建一个存储路网数据的表 ways。该表包含路段的 ID、源节点 ID、目标节点 ID、正向和反向的旅行成本（cost, reverse_cost），以及使用 PostGIS GEOMETRY 类型存储的路段几何形状。

sql

-- 创建一个用于存储路网数据的表
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 类型存储路段的地理信息。LineString 表示这是一条线，4326 是常见的空间参考标识符 (SRID)，代表 WGS 84 坐标系（全球定位系统使用的坐标系）。

插入示例数据

您可以向 ways 表中插入一些示例路段数据：

sql

-- 插入示例路段数据
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 点对象。
ST_MakeLine(point1, point2): 使用两个点创建 PostGIS 线对象。
ST_SetSRID(geometry, srid): 为几何对象设置空间参考标识符。
示例数据代表了三个连接起来的路段，成本均为 1.0。这里的经纬度是北京市区的一个示例区域。

创建拓扑

在路网表数据准备好后，使用 pgr_createTopology 函数创建拓扑。这个函数会分析路段的几何形状，识别连接点（节点），并在您的表中填充 source 和 target 列，同时创建一个新的节点表（通常命名为 [your_table_name]_vertices_pgr）。

sql

-- 为路网数据创建拓扑
-- 参数：
-- 'ways': 要创建拓扑的表名
-- 0.00001: 容差值 (tolerance)，用于判断两个点是否足够接近而可以被视为同一个节点
-- 'geom': 包含几何数据的列名
-- 'id': 包含路段 ID 的列名
SELECT pgr_createTopology('ways', 0.00001, 'geom', 'id');

解释：

pgr_createTopology: 这是 pgRouting 提供的核心函数，用于构建网络的节点-边模型。
容差值（tolerance）非常重要，它决定了多少距离内的点会被认为是同一个交叉点或节点。选择一个合适的容差值取决于您的数据的精度和比例尺。

执行此函数后，ways 表的 source 和 target 列会被填充，并且会生成一个名为 ways_vertices_pgr 的新表，其中包含所有识别出的节点及其坐标。

使用 pgRouting 进行路由分析

一旦拓扑创建完成，您就可以使用 pgRouting 提供的各种算法函数进行路由分析了。以下是一些常用算法的示例。

最短路径分析 (Dijkstra)

查找两个节点之间的最短路径是路由中最常见的需求。pgRouting 提供了 Dijkstra 算法的实现。

sql

-- 查询从节点 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 -- 如果您需要考虑反向成本，可以设置为 false 或省略
)
JOIN ways ON edge = ways.id; -- 将结果与原始ways表连接以获取几何信息

解释：

pgr_dijkstra: 执行 Dijkstra 最短路径算法。
第一个参数是一个 SQL 查询，它必须返回网络的边（路段）信息，至少包含 id, source, target, cost。如果需要考虑反向路径，查询还需要包含 reverse_cost，并将 directed 参数设置为 false。
第二个和第三个参数是起始节点和目标节点的 ID。
JOIN ways ON edge = ways.id: 将算法返回的结果（只包含节点和边的 ID）与原始 ways 表连接，以便获取每个路段的几何形状 (geom)，这对于可视化非常有用。

旅行商问题 (Traveling Salesperson Problem - TSP)

TSP 算法寻找访问一系列指定地点（节点）的最短或最低成本回路。

sql

-- 解决访问节点 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 -- 起始节点ID
);

解释：

pgr_tsp: 执行旅行商问题算法。
第一个参数是一个 SQL 查询，它必须返回需要访问的节点信息，至少包含 id, x, y（节点的坐标）。通常从 ways_vertices_pgr 表中查询。
start_id: 指定旅行的起始节点。

服务区分析 (Driving Distance / Driving Time)

服务区分析确定从一个或多个起始点出发，在给定的成本（距离或时间）限制内可以到达的所有区域或路段。

sql

-- 从节点 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。
第三个参数是允许的最大成本（距离或时间）。
同样通过 JOIN 获取几何信息用于可视化。

可视化路由结果

将 pgRouting 的分析结果可视化是理解和展示路由信息的重要一步。您可以使用桌面 GIS 软件或在 Web 应用中集成地图库来实现。

使用桌面 GIS 工具 (如 QGIS)

QGIS 是一个免费开源的桌面 GIS 应用，可以直接连接到 PostgreSQL/PostGIS 数据库，并加载和显示空间数据，包括 pgRouting 的结果。

打开 QGIS。
选择 图层 (Layer) > 数据源管理器 (Data Source Manager)。
在左侧菜单选择 PostGIS。
点击 新建 (New) 按钮创建一个新的数据库连接。
填写您的 ServBay PostgreSQL 连接信息（例如：主机 (Host): localhost, 端口 (Port): 5432, 数据库 (Database): servbay_geo_db, 用户 (User): servbay, 密码 (Password): 您的 PostgreSQL 密码）。您可以点击“测试连接”按钮验证连接是否成功。
连接成功后，展开您的数据库，您将看到其中的表，包括 ways 和 ways_vertices_pgr。
选择您想加载的表或视图（例如，一个包含最短路径结果的查询视图），点击 添加 (Add) 按钮将其加载到 QGIS 中进行可视化。

在 Web 应用中可视化 (Leaflet 或 OpenLayers)

对于 Web 应用，您需要一个后端服务（例如使用 PHP, Node.js, Python 在 ServBay 中运行）来执行 pgRouting 查询，并将结果（通常是 GeoJSON 格式）返回给前端。前端使用地图库（如 Leaflet 或 OpenLayers）来显示这些地理数据。

以下是一个基本的 HTML 结构，演示如何在 Leaflet 中添加一个静态的折线。要显示动态的路由结果，您需要：

在后端执行 pgRouting 查询。
将查询返回的路段几何信息转换为 GeoJSON 或其他前端地图库支持的格式。
通过 API 端点将 GeoJSON 数据提供给前端。
在前端 JavaScript 中，使用 Leaflet 的 L.geoJSON 或类似方法加载并显示 GeoJSON 数据。

html

<!DOCTYPE html>
<html>
<head>
    <title>ServBay pgRouting Web 可视化示例</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），您就可以看到一个包含示例路线的地图。请注意，这是一个静态示例，展示如何将 GeoJSON 加载到 Leaflet 中。

注意事项

数据质量： pgRouting 的结果质量高度依赖于输入的路网数据质量。确保您的数据准确、完整且拓扑结构正确。
性能： 对于大型路网，路由计算可能会非常耗时。考虑使用索引、简化路网或使用更高效的算法。
内存： 大型拓扑结构可能需要大量内存。确保您的数据库服务器有足够的资源。

常见问题解答 (FAQ)

Q: 我在 CREATE EXTENSION pgrouting; 时收到错误，提示扩展不存在怎么办？ A: 首先，确认您的 PostgreSQL 软件包在 ServBay 中已安装并启动。然后，确认您连接的数据库版本是否支持 pgRouting，并且 ServBay 提供的 PostgreSQL 版本确实包含了该扩展（ServBay 通常会包含）。如果问题依旧，可能需要检查 ServBay 或 PostgreSQL 的日志文件获取更详细的错误信息。确保您是以具有足够权限的用户（如 servbay 用户）连接到数据库。

Q: pgr_createTopology 函数中的容差值 (tolerance) 如何选择？ A: 容差值取决于您的地理空间数据的精度。它定义了两个顶点之间被认为是同一个节点的最小距离。如果您的数据精度很高（例如使用 GPS 坐标），容差值可以设置得小一些（例如 0.000001 或更小）。如果数据精度较低或来源于不同源，可能需要一个稍大的容差值来确保连接性。过大的容差可能导致不相关的路段被错误地连接。

Q: 如何处理单行道或禁止掉头等交通规则？ A: 在 ways 表中，cost 和 reverse_cost 列就是用来处理这些情况的。对于单行道，您可以将反方向的 reverse_cost 设置为 NULL 或一个非常大的值（表示不可通行）。对于禁止掉头，这通常需要在更复杂的路网模型或通过后处理路由结果来实现，pgRouting 提供了一些高级功能来处理这些情况。

总结

通过 ServBay，在本地开发环境中搭建一个支持 pgRouting 的 PostgreSQL 数据库变得非常便捷。您只需简单的 SQL 命令即可启用扩展，然后准备您的路网数据并创建拓扑，即可利用 pgRouting 强大的地理空间路由算法进行分析。结合桌面 GIS 工具或 Web 地图库，您可以轻松地可视化路由结果，为您的 Web 应用或地理空间项目提供强大的数据支持。ServBay 简化了环境配置的复杂性，让开发者能够更专注于应用逻辑和功能实现。

在 ServBay 中使用 pgRouting 进行地理空间路由分析 ​

概述 ​

前提条件 ​

安装和启用 pgRouting 扩展 ​

配置 pgRouting：准备路网数据和创建拓扑 ​

创建路网数据表 ​

插入示例数据 ​

创建拓扑 ​

使用 pgRouting 进行路由分析 ​

最短路径分析 (Dijkstra) ​

旅行商问题 (Traveling Salesperson Problem - TSP) ​

服务区分析 (Driving Distance / Driving Time) ​

可视化路由结果 ​

使用桌面 GIS 工具 (如 QGIS) ​

在 Web 应用中可视化 (Leaflet 或 OpenLayers) ​

注意事项 ​

常见问题解答 (FAQ) ​

总结 ​

在 ServBay 中使用 pgRouting 进行地理空间路由分析

概述

前提条件

安装和启用 pgRouting 扩展

配置 pgRouting：准备路网数据和创建拓扑

创建路网数据表

插入示例数据

创建拓扑

使用 pgRouting 进行路由分析

最短路径分析 (Dijkstra)

旅行商问题 (Traveling Salesperson Problem - TSP)

服务区分析 (Driving Distance / Driving Time)

可视化路由结果

使用桌面 GIS 工具 (如 QGIS)

在 Web 应用中可视化 (Leaflet 或 OpenLayers)

注意事项

常见问题解答 (FAQ)

总结