PostGIS/pgRouting管网连通性分析及最优路径规划

一、拓展安装

pgrouting依赖postgis，要先安装postgis

https://github.com/pgRouting/pgrouting/wikites-on-Download%2C-Installation-and-building-pgRouting

云主机

需要已经安装过postgresql与postgis可使用命令：

sudo apt install postgresql-9.3-pgrouting

window系统

安装地址：http://download.osgeo.org/postgis/windows/

二、创建扩展

使用前需要先在pg数据库创建扩展

--pgrouting依赖postgis
CREATE EXTENSION PostGIS
CREATE EXTENSION pgRouting

三、样例：

1、新建数据表edge_table

CREATE TABLE edge_table (
    id BIGSERIAL,
    dir character varying,
    source BIGINT,
    target BIGINT,
    cost FLOAT,
    reverse_cost FLOAT,
    capacity BIGINT,
    reverse_capacity BIGINT,
    category_id INTEGER,
    reverse_category_id INTEGER,
    x1 FLOAT,
    y1 FLOAT,
    x2 FLOAT,
    y2 FLOAT,
    the_geom geometry
);

2、插入数据

INSERT INTO edge_table (
    category_id, reverse_category_id,
    cost, reverse_cost,
    capacity, reverse_capacity,
    x1, y1,
    x2, y2) VALUES
(3, 1,    1,  1,  80, 130,   2,   0,    2, 1),
(3, 2,   -1,  1,  -1, 100,   2,   1,    3, 1),
(2, 1,   -1,  1,  -1, 130,   3,   1,    4, 1),
(2, 4,    1,  1, 100,  50,   2,   1,    2, 2),
(1, 4,    1, -1, 130,  -1,   3,   1,    3, 2),
(4, 2,    1,  1,  50, 100,   0,   2,    1, 2),
(4, 1,    1,  1,  50, 130,   1,   2,    2, 2),
(2, 1,    1,  1, 100, 130,   2,   2,    3, 2),
(1, 3,    1,  1, 130,  80,   3,   2,    4, 2),
(1, 4,    1,  1, 130,  50,   2,   2,    2, 3),
(1, 2,    1, -1, 130,  -1,   3,   2,    3, 3),
(2, 3,    1, -1, 100,  -1,   2,   3,    3, 3),
(2, 4,    1, -1, 100,  -1,   3,   3,    4, 3),
(3, 1,    1,  1,  80, 130,   2,   3,    2, 4),
(3, 4,    1,  1,  80,  50,   4,   2,    4, 3),
(3, 3,    1,  1,  80,  80,   4,   1,    4, 2),
(1, 2,    1,  1, 130, 100,   0.5, 3.5,  1.999999999999,3.5),
(4, 1,    1,  1,  50, 130,   3.5, 2.3,  3.5,4);

3、生成路径信息和路径通达性

根据cost和reverse_cost两个字段，生成the_geom（两点间地理信息/路径信息）和dir两点间路径通达性：

UPDATE edge_table SET the_geom = st_makeline(st_point(x1,y1),st_point(x2,y2)),
dir = CASE WHEN (cost>0 AND reverse_cost>0) THEN 'B'   -- 双向通行
           WHEN (cost>0 AND reverse_cost<0) THEN 'FT'  -- 正向通行
           WHEN (cost<0 AND reverse_cost>0) THEN 'TF'  -- 反向通行
           ELSE '' END;                                -- 其他情况不操作

4、创建拓扑

--只有构建拓扑之后，才能进行路径规划。0.001是拓扑容差，两个点的距离在这个距离内，就算重合为一点。这个距离使用st_length计算
SELECT pgr_createTopology('edge_table',0.001);

5、尝试进行查询获取最短路径

pgr_dijkstra函数可以获取最短路径

SELECT * FROM pgr_dijkstra(   
 'SELECT id, source, target, cost, reverse_cost FROM edge_table',  
  4, 8)

6、官方样例中还有更多的使用方式

pointsOfInterest 、restrictions、new_restrictions、customer等

官网提供的完整样例 https://docs.pgrouting.org/3.0/en/sampledata.html

四、pgr_dijkstra使用

pgr_dijkstra(edges_sql, start_vid,  end_vid  [, directed])--一对一
pgr_dijkstra(edges_sql, start_vid,  end_vids [, directed])--一对多
pgr_dijkstra(edges_sql, start_vids, end_vid  [, directed])--多对一
pgr_dijkstra(edges_sql, start_vids, end_vids [, directed])--多对多

pgr_dijkstra入参

Parameter	类型	默认值	描述
edges_sql	TEXT		内部 SQL 查询。
start_vid	BIGINT		路径起始顶点的标识符。
start_vids	ARRAY[BIGINT]		起始顶点的标识符数组。
end_vid	BIGINT		路径结束顶点的标识符。
end_vids	ARRAY[BIGINT]		结束顶点的标识符数组。
directed	BOOLEAN	true	true 有向 false无向

pgr_dijkstra中的edges_sql

一对一sql

--id标识符、source起始点、target目标点、cost成本（权重）、reverse_cost逆向成本（权重）
 SELECT * FROM pgr_dijkstra(   
 'SELECT id, source, target, cost, reverse_cost FROM edge_table',  
  4, 8)

多对多sql

SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost FROM edge_table',
     ARRAY[4,2], ARRAY[3,5],
    FALSE
);

内部sql

Column	类型	默认值	描述
id	ANY-INTEGER		Identifier of the edge.
source	ANY-INTEGER		边的第一个端点顶点的标识符。
target	ANY-INTEGER		边的第二个端点顶点的标识符。
cost	ANY-NUMERICAL		node从使用edge到路径序列中的下一个节点的遍历成本。
reverse_cost	ANY-NUMERICAL	-1	node从使用edge到路径序列中的下一个节点的反向遍历成本。

返回参数

Column	类型	描述
seq	INT	序列值
path_id	INT	路径标识符。路径的第一个值为1。当有多个路径start_vid可以end_vid组合时使用。
path_seq	INT	路径中的相对位置。路径开头的值为1。
start_vid	BIGINT	起始顶点的标识符。当查询中有多个起始顶点时返回。
end_vid	BIGINT	结束顶点的标识符。当查询中有多个结束顶点时返回。
node	BIGINT	start_vid从到路径中节点的标识符end_vid。
edge	BIGINT	用于从node路径序列到下一个节点的边的标识符。-1对于路径的最后一个节点。
cost	FLOAT	node从使用edge到路径序列中的下一个节点的遍历成本。
agg_cost	FLOAT	总成本从start_v到node。

五、应用

官网完整的sql语句创建拓扑

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)

创建拓扑参数

Column	类型	描述
edge_table	text	表名
tolerance	float8	误差缓冲值，两个点的距离在这个距离内，就算重合为一点。这个距离使用st_length计算
the_geom	text	该表的空间坐标字段
id	text	该表的主键
source	int	空间起点编号
target	int	空间终点编号
rows_where	boolean	条件选择子集或行。默认值为true，表示源或目标具有空值的所有行，否则将使用条件。
clean	boolean	每次执行都重建拓扑图，默认false

实际使用时的sql

 SELECT
	pgr_createTopology ( 'erqihsl', 0.001,  'shape' , 'objectid', 'start', 'last' ) from erqihsl

创建拓扑成功时，会自动创建出一个(表名包含Vertices)扩展表，新表结构为：

Column	类型	描述（解释来自官网）
id	bigint	顶点的标识符。
cnt	integer	edge_table中引用此顶点的顶点数。See pgr_analyzeGraph.
chk	integer	指示顶点可能有问题。See pgr_analyzeGraph.
ein	integer	edge_table中引用这个顶点的顶点数。See pgr_analyzeOneway.
eout	integer	edge_table中引用该顶点作为输出的顶点数。See pgr_analyzeOneway.
the_geom	geometry	Point geometry of the vertex：顶点的点几何。

实际使用时的sql查询

SELECT * FROM pgr_dijkstra(   
 'SELECT objectid as id, start as source, last as target ,length as cost FROM erqihsl',  
  2010002150, 2010002551)

参考来源

https://docs.pgrouting.org/3.0/en/sampledata.html

https://blog.csdn.net/sinat_41310868/article/details/106912152

https://zhuanlan.zhihu.com/p/85905703

https://www.modb.pro/db/190857

https://docs.pgrouting.org/latest/en/pgr_createTopology.html