路徑資料是由Edge和Node構成的幾何網狀圖,主要用於構建道路和交通網路。
概述
GanosBase Networking是對象關係型資料庫PostgreSQL的一個擴充,提供了一系列的函數和預存程序,用於根據代價模型尋找最快、最短甚至是最優的路徑。如果代價是時間,則最佳路線為最快路線。如果代價是距離,則最佳路徑為最短路徑。路徑模型用於解決基於道路網的路徑規劃問題、電子地圖GPS導航路徑搜尋規劃問題、路由問題等。路徑模型完全相容PGRouting介面,支援已有應用的平滑遷移。
快速入門
建立擴充
Create Extension Ganos_Networking with schema public cascade;
建立表
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 );
插入記錄
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);
更新表屬性
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;
建立拓撲
SELECT pgr_createTopology('edge_table',0.001);
查詢最短路徑
-- 使用dijkstra演算法查詢最短路徑 SELECT * FROM pgr_dijkstra( 'SELECT id, source, target, cost, reverse_cost FROM edge_table', 2, 3 ); seq | path_seq | node | edge | cost | agg_cost -----+----------+------+------+------+---------- 1 | 1 | 2 | 4 | 1 | 0 2 | 2 | 5 | 8 | 1 | 1 3 | 3 | 6 | 9 | 1 | 2 4 | 4 | 9 | 16 | 1 | 3 5 | 5 | 4 | 3 | 1 | 4 6 | 6 | 3 | -1 | 0 | 5 (6 rows) -- 使用astar演算法查詢最短路徑 SELECT * FROM pgr_astar( 'SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edge_table', 2, 12, directed := false, heuristic := 2); seq | path_seq | node | edge | cost | agg_cost -----+----------+------+------+------+---------- 1 | 1 | 2 | 2 | 1 | 0 2 | 2 | 3 | 3 | 1 | 1 3 | 3 | 4 | 16 | 1 | 2 4 | 4 | 9 | 15 | 1 | 3 5 | 5 | 12 | -1 | 0 | 4 (5 rows) -- 使用trsp 路徑演算法 SELECT * FROM pgr_trsp( 'SELECT id::INTEGER, source::INTEGER, target::INTEGER, cost FROM edge_table', 2, 7, false, false, 'SELECT to_cost, target_id::int4, from_edge || coalesce('','' || via_path, '''') AS via_path FROM restrictions' ); seq | id1 | id2 | cost -----+-----+-----+------ 0 | 2 | 4 | 1 1 | 5 | 10 | 1 2 | 10 | 12 | 1 3 | 11 | 11 | 1 4 | 6 | 8 | 1 5 | 5 | 7 | 1 6 | 8 | 6 | 1 7 | 7 | -1 | 0 (8 rows)
刪除擴充
Drop Extension Ganos_Networking cascade;
SQL參考
詳細SQL手冊請參見pgrouting 官方文檔 。