PostgreSQL实战指南：多领域数据库应用与实践

第四部分：PostgreSQL与时空数据库

借助业界领先的 PostGIS 扩展，PostgreSQL 摇身一变，成为一个功能强大的地理空间信息系统（GIS）。本部分将带领读者深入探索 PostgreSQL 在时空数据领域的应用，从基础的地理数据处理到复杂的空间分析与路径规划，无所不包。

第18章：路由规划与位置服务应用

将 PostGIS 的空间数据处理能力与 pgRouting 的网络分析能力相结合，可以构建出强大的路由规划和位置服务应用，例如地图导航、物流配送优化和应急服务范围分析。本章将深入实践 pgRouting 的核心功能，并将其应用于解决真实世界的路径问题。

18.1 pgRouting 核心概念回顾

在第9章中，我们初步接触了 pgRouting。这里我们回顾一下其核心工作流程：

数据准备: 拥有一个包含网络边（如道路）的 PostGIS 表。
拓扑构建: 使用 pgr_createTopology 为网络边创建节点，并填充 source 和 target 字段。
成本计算: 为每条边定义一个或多个“成本”（cost），如距离、时间等。
路径查询: 使用 pgr_dijkstra (Dijkstra算法) 或 pgr_aStar (A*算法) 等函数计算最短路径。

Dijkstra vs A*:
- Dijkstra: 从起点开始，向外扩展搜索，直到找到终点。它保证找到最短路径。
- A*: Dijkstra 的一种优化。它利用一个“启发式函数”（heuristic）来估计当前节点到终点的距离，优先搜索“看起来”离终点更近的节点。在大多数情况下，A* 比 Dijkstra 更快。

18.2 高级路由功能

pgRouting 不仅仅是计算点到点的最短路径。

考虑方向性: 真实世界的道路网络通常包含单行道。可以在 pgr_dijkstra 等函数的 directed 参数中体现，或者在成本计算中为反向通行的边设置一个极大的成本。
转弯成本: 在复杂的交叉口，左转、右转和直行的耗时是不同的。pgRouting 支持在路径计算中加入转弯成本，以获得更真实的路径规划结果。
多点路径 (TSP): 旅行商问题（Traveling Salesperson Problem），即找到访问多个点的最短路径。pgRouting 提供了 pgr_TSP 函数来解决这个问题。

18.3 服务区分析 (Driving Distance)

服务区分析是位置服务中的一个常见需求，例如：“从消防站出发，5分钟内可以到达哪些区域？” 或 “从我的位置出发，步行15分钟可以到达的所有商店”。

pgRouting 的 pgr_drivingDistance 函数专门用于解决这个问题。它会返回从一个起始节点出发，在给定的最大成本（如时间或距离）内可以到达的所有节点和边。

18.4 场景实战：物流配送路径优化

业务场景描述:

一个物流公司的配送员需要从仓库出发，依次访问3个客户，然后返回仓库。我们需要为他规划一条总距离最短的配送路线。这是一个典型的旅行商问题 (TSP)。

数据准备与查询:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
-- 假设我们已经有一个构建好拓扑的 roads 表 (同第9章)
-- 并且有一个包含仓库和客户位置的表
CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(POINT, 4326)
);

INSERT INTO locations (name, geom) VALUES
('Warehouse', ST_GeomFromText('POINT(-73.99 40.72)', 4326)),
('Customer A', ST_GeomFromText('POINT(-73.97 40.73)', 4326)),
('Customer B', ST_GeomFromText('POINT(-73.98 40.75)', 4326)),
('Customer C', ST_GeomFromText('POINT(-74.00 40.74)', 4326));

-- 1. 找到每个位置最近的道路网络节点
-- 使用 LATERAL JOIN 和 KNN (<->) 操作符
ALTER TABLE locations ADD COLUMN node_id INTEGER;

UPDATE locations
SET node_id = (
    SELECT v.id
    FROM roads_vertices_pgr v
    ORDER BY locations.geom <-> v.the_geom
    LIMIT 1
);

-- 2. 使用 pgr_TSP 计算最优路径
-- 我们需要提供一个包含所有途经点 node_id 的数组
WITH points AS (
    SELECT array_agg(node_id) AS ids FROM locations
)
SELECT
    tsp.seq,
    tsp.node AS node_id,
    loc.name,
    tsp.cost,
    tsp.agg_cost
FROM
    pgr_TSP(
        'SELECT id, source, target, cost FROM roads',
        (SELECT ids FROM points),
        start_id := (SELECT node_id FROM locations WHERE name = 'Warehouse'),
        end_id := (SELECT node_id FROM locations WHERE name = 'Warehouse')
    ) AS tsp
LEFT JOIN locations loc ON tsp.node = loc.node_id
ORDER BY tsp.seq;

代码解释与思考:

寻找最近节点: 真实世界的位置（如客户地址）通常不在道路网络的精确节点上。因此，第一步是为每个位置点找到它在 roads_vertices_pgr 表中最接近的网络节点。我们使用了 ORDER BY geom <-> other_geom LIMIT 1 的 KNN (K-Nearest Neighbors) 查询方法，这在有空间索引的情况下非常高效。
pgr_TSP: 这个函数接收路网的 SQL、一个包含所有需要访问的节点 ID 的数组。它还允许指定起点和终点。函数会返回一个访问序列，以及每段和累积的成本。
结果解读: 返回结果的 seq 列给出了最优的访问顺序。通过将结果与 locations 表连接，我们可以清晰地看到规划出的路线：Warehouse -> Customer A -> Customer B -> Customer C -> Warehouse (这只是一个可能的顺序，实际顺序由算法计算得出)。

18.5 场景实战：消防站服务范围分析

业务场景描述:

城市规划部门需要分析一个新建消防站的服务能力，即：在接到报警后的5分钟内，该消防站的消防车可以覆盖哪些街道？

数据准备与查询:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
-- 假设 roads 表的 cost 代表了通过该路段的平均时间 (分钟)
-- 找到消防站最近的网络节点
WITH fire_station AS (
    SELECT id FROM roads_vertices_pgr
    ORDER BY ST_GeomFromText('POINT(-73.975 40.76)', 4326) <-> the_geom
    LIMIT 1
)
-- 使用 pgr_drivingDistance 计算5分钟内可达的范围
SELECT
    dd.seq,
    r.name AS street_name,
    r.geom
FROM
    pgr_drivingDistance(
        'SELECT id, source, target, cost FROM roads',
        (SELECT id FROM fire_station),
        5, -- 最大成本 (5分钟)
        directed := false
    ) AS dd
JOIN roads r ON dd.edge = r.id
ORDER BY dd.agg_cost;

代码解释与思考:

pgr_drivingDistance: 这个函数的用法与 pgr_dijkstra 类似，但它返回的是从起点出发，在给定最大成本内所有可达的节点和边。
可视化: 这个查询的结果（一系列的道路 geom）可以直接在 GIS 软件（如 QGIS）中进行可视化，生成一张服务范围地图。这对于城市规划、商业选址等应用非常有价值。

18.6 总结

本章我们深入探索了 pgRouting 的高级应用。我们学习了如何解决多点路径规划的旅行商问题 (TSP)，以及如何进行服务区分析 (Driving Distance)。通过物流配送和消防站覆盖范围这两个实战案例，我们看到了 PostGIS + pgRouting 在构建复杂的、真实世界的位置服务应用中的巨大潜力。

PostgreSQL实战指南：多领域数据库应用与实践 - 第18章：路由规划与位置服务应用

文章目录