PolarDB:GanosBase Networking

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for PostgreSQL (Compatible with Oracle) instance

• Database superuser or a role with the CREATE EXTENSION privilege

Install the extension

CREATE EXTENSION Ganos_Networking WITH SCHEMA public CASCADE;

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

Insert edge 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 geometry and direction

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'   -- both directions
        WHEN (cost > 0 AND reverse_cost < 0) THEN 'FT'  -- source to target only
        WHEN (cost < 0 AND reverse_cost > 0) THEN 'TF'  -- target to source only
        ELSE ''
    END;

Build the topology

SELECT pgr_createTopology('edge_table', 0.001);

This call populates the source and target columns with node identifiers based on the geometry.

Query a path

All three examples below use the same edge_table. The inner query selects only the columns each algorithm requires.

Dijkstra's algorithm (general edge SQL — requires id, source, target, cost, reverse_cost):

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)

A* algorithm (edge SQL with x and y values — requires id, source, target, cost, reverse_cost, x1, y1, x2, y2):

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)

Turn restriction shortest path (TRSP) algorithm (requires a restrictions query in addition to the edge query):

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)

Remove the extension (optional)

DROP EXTENSION Ganos_Networking CASCADE;

Graph model

A graph is an ordered pair G = (V, E), where:

• V is a set of vertices (nodes)

• E ⊆ {(u, v) | u, v ∈ V} is a set of edges

Graphs are classified into various types such as undirected graphs, undirected simple graphs, directed graphs, and directed simple graphs. GanosBase Networking supports graphs specified as directed or undirected for calculation. Graphs can be represented in two ways:

• Graph with costs: each edge has a cost value (weight from source to target)

• Graph with costs and reverse costs: each edge also has a reverse_cost value (weight from target to source)

Algorithms

GanosBase Networking includes the following algorithms and analysis functions:

Some functions also accept a cost or cost matrix for calculation.

Use cases

• Route planning: Calculate the shortest or fastest path between points for logistics, delivery, and ride-hailing applications. GanosBase Networking also supports non-geographic graphs such as Internet node connections.

• Geospatial analysis: Perform road network analysis including nearest neighbor search and service zone analysis to understand the distribution and relationships of geospatial data.

• Traffic flow management: Analyze traffic volume data, predict congestion, and generate optimization recommendations for urban traffic management and intelligent transportation systems.

Function overloads

Most functions support multiple overloads based on the number of start and end vertices.

General edge SQL

Used by: Dijkstra's algorithm, bidirectional Dijkstra's algorithm, pgr_withPoints, flow analysis, component operations, and other general routing functions.

General edge SQL without ID

Used by: Johnson's algorithm, Floyd-Warshall algorithm, and other all-pairs algorithms.

General edge SQL with x and y values

Used by: A* algorithm, bidirectional A* algorithm.

Combinations SQL

Specifies custom start–end vertex pairs for combinations overloads.

Restrictions SQL

Used by: Turn restriction shortest path (TRSP) algorithm.

Points SQL

Used by functions that work with intermediate points on edges (for example, pgr_withPoints).

Path result columns

Returned by single-path functions: pgr_dijkstra, pgr_bdDijkstra, pgr_aStar, pgr_bdAstar, pgr_dijkstraNear, and similar functions.

The pgr_dijkstraNear function always returns start_vid and end_vid columns, regardless of the number of vertices in the query.

The pgr_withPoints function returns the same columns, with the following behavior for point identifiers:

• In start_vid: positive values are starting vertices; negative values are starting points.

• In end_vid: positive values are ending vertices; negative values are ending points.

• In node: positive values are vertices; negative values are points.

Multiple-path result columns (selective)

Returned by functions that find multiple alternative paths (selective): pgr_KSP, pgr_withPointsKSP, pgr_edgeDisjointPaths, and similar functions.

Multiple-path result columns (non-selective)

Returned by functions that are not selective for multiple paths.

Cost function result columns

Returned by cost and cost matrix functions.