ApsaraDB RDS:Path model

Cost model

Every edge in the graph carries a numeric cost value. The meaning of cost determines the type of result:

Negative cost convention

A negative cost value means the edge does not exist in that direction. Use this to model one-way streets or blocked routes:

• cost < 0: the forward direction (source → target) is impassable.

• reverse_cost < 0: the reverse direction (target → source) is impassable.

Result columns

All path-finding functions return the same set of columns:

Choose an algorithm

Set up the extension and sample data

Step 1: Create the extension.

Create Extension Ganos_Networking with schema public cascade;

Step 2: Create the 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
);

Step 3: Insert sample data.

In the sample data below, a cost or reverse_cost of -1 means the edge does not exist in that direction.

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);

Step 4: Build edge geometry and direction flags.

The dir column encodes traversability: B (both directions), FT (forward only, source to target), TF (backward only, target to source), or empty (impassable in both directions).

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;

Step 5: Build the network topology.

pgr_createTopology assigns source and target node IDs to each edge. The tolerance parameter (0.001) controls how close two endpoints must be to be considered the same node.

SELECT pgr_createTopology('edge_table', 0.001);

Step 6: Query shortest paths.

Use Dijkstra's algorithm for a directed graph (default).

-- Dijkstra: shortest path from node 2 to node 3
SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, cost, reverse_cost FROM edge_table',
    2, 3
);

Expected output:

 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)

The final row has edge = -1, indicating node 3 is the destination. agg_cost of 5 is the total path cost.

Use the A* algorithm for geospatial graphs. The inner SQL must also include x1, y1, x2, y2 coordinates. Use directed := false for undirected graphs.

-- A*: shortest path from node 2 to node 12 (undirected, heuristic 2)
SELECT * FROM pgr_astar(
    'SELECT id, source, target, cost, reverse_cost, x1, y1, x2, y2 FROM edge_table',
    2, 12,
    directed := false, heuristic := 2);

Expected output:

 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 TRSP for networks with turn restrictions. The restrictions SQL defines which turn sequences are penalized or forbidden.

-- TRSP: shortest path from node 2 to node 7, with turn restrictions
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'
);

Expected output:

 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)

Clean up

To remove the extension and all associated objects:

Drop Extension Ganos_Networking cascade;