全部產品
Search
文件中心

ApsaraDB RDS:路徑模型

更新時間:Oct 16, 2024

路徑資料是由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 官方文檔