PostGIS/pgRouting管網連通性分析及最優路徑規劃
- 一、拓展安裝
  - 云主機
  - window系統
- 二、創建擴展
- 三、樣例：
  - 1、新建資料表edge_table
  - 2、插入資料
  - 3、生成路徑資訊和路徑通達性
  - 4、創建拓撲
  - 5、嘗試進行查詢獲取最短路徑
- 四、pgr_dijkstra使用
  - pgr_dijkstra入參
  - 內部sql
  - 回傳引數
- 五、應用
- 參考來源

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/508138.html

標籤：PostgreSQL

上一篇：索引

下一篇：動手測起來！搭載全自研資料庫內核，我們將性能提升了20%