阿里云RDS数据库路径模型-云淘科技

概述

Ganos Networking是对象关系型数据库PostgreSQL的一个扩展，提供了一系列的函数和存储过程，用于根据代价模型查找最快、最短甚至是最优的路径。如果代价是时间，则最佳路线为最快路线。如果代价是距离，则最佳路径为最短路径。路径模型用于解决基于道路网的路径规划问题、电子地图GPS导航路径搜索规划问题、路由问题等。路径模型完全兼容PGRouting接口，支持已有应用的平滑迁移。

快速入门

• 创建扩展

  Create Extension Ganos_Networking 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 (cost0) 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 官方文档 。