Promo Center

50% off for new user

Direct Mail-46% off

Learn More

Path model

Updated at: 2024-10-16 12:35

Path data is a geometric network diagram that consists of edges and nodes. It is used to build road and traffic networks.

Overview

GanosBase Networking is an extension of PostgreSQL. It provides a series of functions and stored procedures to find the fastest, shortest, and optimal paths based on cost models. If the cost is time, the fastest path is the optimal path. If the cost is distance, the shortest path is the optimal path. The path model can be used for path planning on a road network, path search and planning in GPS navigation on an electronic map, and routing. The path model is fully compatible with pgRouting functions and allows you to smoothly migrate existing applications.

Quick start

  • Create an extension.

    Create Extension Ganos_Networking with schema public cascade;

  • Create a 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
    );
  • Insert data.

    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 data.

    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;
  • Create a topology.

    SELECT pgr_createTopology('edge_table',0.001);
  • Query the shortest path.

    -- Use Dijkstra's algorithm to query the shortest path.
    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)
    
    -- Use the A* algorithm to query the shortest path.
    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)
    
    -- Use the turn restricted shortest path (TRSP) algorithm to query the shortest path.
    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)
  • Delete the extension.

    Drop Extension Ganos_Networking cascade;

SQL reference

For more information, see the official pgRouting manual.

  • On this page (1)
  • Overview
  • Quick start
  • SQL reference
Feedback
phone Contact Us

Chat now with Alibaba Cloud Customer Service to assist you in finding the right products and services to meet your needs.

alicare alicarealicarealicare