1.环境

1.1.软件

Postgresql v9.5

Postgis v2.5

pgRouting v2.2

1.2.数据

point：点数据，模拟poi

road：线数据，模拟路网

数据下载见文末

2.数据处理

以下数据均为模拟数据。

要对路网数据构建拓扑，首先需要将路网数据中的路段按照连接点打断。在图 1中可以看到，在两条线路交叉的位置是不存在有节点的，这对于两条路相交的路来说是不正确的，是高架桥、立交桥这种模型。简单起见，假设路网中的所有线路都是相交的。

为此，首先使用ArcGIS中高级编辑工具–>打断相交线工具将所有的路网数据线在相交处打断。只需要在编辑状态下选中全部线数据，点击“打断相交线工具”即可。（如图 2所示）

打断相交线后，其效果如图 3所示，通过查看属性表（如图 4），可以看到原本的10条线段已经变为了26条。

图 1

图 2

图 3

图 4

3.QGIS导入数据

数据处理完毕后，使用QGIS将数据导入到Postgresql中。此之前，已经在Postgresql中创建了名为PgRoutingTest的数据库，并使用如下SQL添加了PostGIS和pgRouting扩展。

1	`CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology; CREATE EXTENSION pgrouting;`

打开QGIS，在右侧PostGIS右键选择新建连接（图 5），然后在新建连接面板上填写连接的相关信息（图 6），其中1处为数据库的名称，2处为数据库的用户名、密码，点击3可以测试数据库能否成功连接。

图 5

图 6

成功添加连接后，将数据加载进QGIS，打开上方数据库|数据库管理器菜单，进入到PostGIS|PgRouting中（图 7），然后点击上方的“导入图层或文件”按钮，打开导入矢量图层面板（图 8）。在输入中选择要导入的图层，在输出表格中，架构可以为空，默认为public，表格输入当前图层导入数据库后的表格名，在主键位置处选择id（事先经过处理，保存其唯一性），其他的按照需要自行配置即可。最后点击OK即可将图层导入到数据库中。

同上，将road数据也导入到数据库中。

图 7

图 8

4.构建拓扑路网

4.1.修改路网数据表

为路网数据表添加三个字段：source、target、length，source、target代表路线的起始点，length代表路线长度。

1	`ALTER TABLE road ADD COLUMN source INTEGER; ALTER TABLE road ADD COLUMN target INTEGER; ALTER TABLE road ADD COLUMN length FLOAT8;`

4.2.创建路网拓扑信息

语法如下：

`SELECT pgr_createTopology(text edge_table, double precision tolerance, text the_geom:='the_geom', text id:='id', text source:='source',text target:='target', text rows_where:='true', boolean clean:=false);`

从the_geom开始，后面的参数都是可选的，各个参数的意义如下：

edge_table：路网数据表名；

tolerance：创建拓扑的距离容差；

the_geom：几何信息的字段名；

id：路网数据表的主键名；

source和target就是在3.1中创建的source和target字段；

rows_where：路网中建立拓扑的行进行筛选，相当于where字句的内容；

clean：是否清除之前建立的拓扑。

例如：

1	`SELECT pgr_createTopology('road',0.000001,'geom','id' , rows_where:='id in (xxx)');`

执行之后，数据库中会出现一个xxx_vertices_pgr的表格，xxx为edge_table的值，这里直接使用如下语句就行，如果返回OK，则表明拓扑创建成功，并且数据库中会多出名为road_vertices_pgr的表格。

1	`SELECT pgr_createTopology('road',0.000001,'geom','id');`

4.3.更新路网数据表中length

length代表路网中各条边的成本例如距离、时间等，此处使用距离代表各边的成本。语法如下：

1	`UPDATE road SET length = ST_Length(geom::geography);`

在实际使用时，由于可能会更新路网数据，因此可以在UPDATE语句中加入where条件。

4.4.路网节点与点图层关联

下一步计算point表中离路网最近节点，并将节点编号保存在数据库中。

首先创建字段nearest_node_id，用于保存路网中最近距离节点主键。

1	`Alter Table point Add Column nearest_node_id INTEGER;`

由于point一般是不变的，因此我们不必每次查询是动态查找point最近的路网节点，可以将最近路网节点的id写入到数据库中，这段SQL比较复杂，详细了解请到stackoverflow。

`UPDATE point p1 SET nearest_node_id=sub.vid FROM ( SELECT p.pid, b.id as vid FROM ( SELECT point.id as pid, point.* FROM point ) p, LATERAL ( SELECT road_vertices_pgr.id FROM road_vertices_pgr ORDER BY p.geom <-> road_vertices_pgr.the_geom LIMIT 1 ) b ) sub WHERE p1.id = sub.pid;`

5.最短路径查询

pgRouting的最短路径算法有多种，A*算法、Dijkstra算法、Floyd-Warshall算法等等，详见官网。这里使用最常见的Dijkstra算法。

我们假设要从A点（105, 31）到B点的最短路径，其最短路径目测如图 9所示。

图 9 （大致示意，省略中间部分节点路段）

5.1.计算距离起点最近的路网节点

使用如下语句，查询距离改点最近的路网节点，执行的效果如图 10所示，找到的最近的节点Id为1。

`SELECT id, ST_AsText(the_geom) as geom, ST_Distance_Sphere(ST_GeomFromText('POINT(105 31)',4326), v.the_geom) as distance FROM road_vertices_pgr v WHERE ST_DWithin(ST_GeomFromText('POINT(105 31)',4326), v.the_geom, 5) ORDER BY ST_GeomFromText('POINT(105 31)',4326)<-> v.the_geom LIMIT 1;`

图 10

5.2.查找终点最近路网节点Id

由于point图层中的最近路网节点Id应存在数据库中，所以直接查询数据库point表即可得到（图 11），距离p2最近的路网节点id为13，这与我们预测不符合。

1	`SELECT nearest_node_id FROM point WHERE name='p2';`

图 11

在QGIS中使用测量工具，发现13号路网节点距离p2的距离确实更近（图 12）。

图 12 (a)

图 12 (b)

图 12

5.3.最短路径查询

获取起始点在路网节点中的id后，使用pgr_dijkstra函数计算两节点间的最短路径，SQL如下，其中查询结果（图 13）中的seq表路径次序，node表示经过的节点，edge表示经过的边，cost表示路径成本（这里就是路长），edge_geom表示该路段的几何信息。将查询结果导入为新图层导入到QGIS中，并设置符号化之后结果如图 14所示。

`SELECT d.seq, d.node, d.edge, d.cost, ST_AsText(e.geom) AS edge_geom FROM pgr_dijkstra( 'SELECT id, source, target, length AS cost FROM road', -- 用于计算最短路径的路网 1, -- 起点节点id 13, -- 终点节点id FALSE -- 无向路径 ) as d LEFT JOIN road AS e ON d.edge = e.id ORDER BY d.seq;`

图 13

图 14

5.4.完善

在4.3中已经得到了路网中的最短距离路线，接下来还要将起点、终点到路网中的线路加上，这里有两种方案：

计算起点到路网中第一路段的最近点与终点到最后一路段的最近点，然后截取查询后路网，并将起点、终点连接进去。其结果示意图如图 15所示。

图 15 (大致示意，省略中间部分节点路段）

直接将起点终点与路网中的最近节点相连接。其结果示意图如图 16所示。

图 16 (大致示意，省略中间部分节点路段）

明显，第二种方案更为简单。在4.1中，已经查询出来了路网中1号节点（与起点最近节点）的几何坐标以及起点与1号节点的距离，在4.2中查询到了距离终点最近的节点id（13），通过这个节点id (13)可以在road_vertices_pgr表中查询到这个节点的几何信息，通过计算节点13与终点的距离，即可得到整段线路的长度。此处比较简单，不在赘述。

附：本文所使用的shapefile数据下载