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.