pgrouting_example.sql // proposal for a question: https://lists.osgeo.org/pipermail/postgis-users/2022-April/045458.html

pgrouting_example.sql

-- minimal https://pgrouting.org/ network example
CREATE EXTENSION IF NOT EXISTS POSTGIS;
CREATE EXTENSION IF NOT EXISTS PGROUTING;

DROP TABLE IF EXISTS edge_table CASCADE;
CREATE TABLE edge_table (
    id bigint primary key,
    source bigint,
    target bigint,
    cost float,
    reverse_cost float,
    the_geom geometry
);
-- network example from
--   http://blog.cleverelephant.ca/2010/07/network-walking-in-postgis.html
INSERT INTO edge_table VALUES( 1, 3011, 3000, 1, -1, 'LINESTRING(1 1, 0 0)');
INSERT INTO edge_table VALUES( 2, 3021, 3011, 1, -1, 'LINESTRING(2 1, 1 1)');
INSERT INTO edge_table VALUES( 3, 3012, 3011, 1, -1, 'LINESTRING(1 2, 1 1)');
INSERT INTO edge_table VALUES( 4, 3031, 3021, 1, -1, 'LINESTRING(3 1, 2 1)');
INSERT INTO edge_table VALUES( 5, 3032, 3021, 1, -1, 'LINESTRING(3 2, 2 1)');
INSERT INTO edge_table VALUES( 6, 3023, 3012, 1, -1, 'LINESTRING(2 3, 1 2)');
INSERT INTO edge_table VALUES( 7, 3013, 3012, 1, -1, 'LINESTRING(1 3, 1 2)');
INSERT INTO edge_table VALUES( 8, 3042, 3032, 1, -1, 'LINESTRING(4 2, 3 2)');
INSERT INTO edge_table VALUES( 9, 3034, 3023, 1, -1, 'LINESTRING(3 4, 2 3)');
INSERT INTO edge_table VALUES(10, 3024, 3023, 1, -1, 'LINESTRING(2 4, 2 3)');
INSERT INTO edge_table VALUES(11, 3014, 3013, 1, -1, 'LINESTRING(1 4, 1 3)');
INSERT INTO edge_table VALUES(12, 3043, 3042, 1, -1, 'LINESTRING(4 3, 4 2)');
INSERT INTO edge_table VALUES(13, 3044, 3034, 1, -1, 'LINESTRING(4 4, 3 4)');
-- update cost by real length
UPDATE edge_table SET cost = ST_Length(the_geom);
VACUUM ANALYZE edge_table;
-- create gis index
CREATE INDEX edge_table_gix ON edge_table USING GIST (the_geom);
-- create vertices table:  public.edge_table_vertices_pgr
SELECT pgr_createVerticesTable('edge_table');
-- check Graph
SELECT pgr_analyzeGraph('edge_table',0.0);


------------------------------------------
-- minimal pgRouting Network example
-- "downstream path" from all "deadends" to 3000
-------------------------
SET work_mem = '10GB'; -- increase memory for big graphs
DROP TABLE IF EXISTS dijkstra CASCADE;
CREATE TABLE dijkstra AS
 SELECT *
 FROM pgr_dijkstra( --  https://docs.pgrouting.org/3.3/en/pgr_dijkstra.html
  -- graph network
  'SELECT id, source, target, cost, reverse_cost FROM edge_table'
  -- Combinations SQL :  from all deadends to 3000
 ,'SELECT id as source, 3000 as target FROM edge_table_vertices_pgr WHERE cnt = 1 AND id<>3000'
 );
VACUUM ANALYZE dijkstra;
CREATE INDEX idx_dijkstra_edge ON dijkstra(edge);
CREATE INDEX idx_dijkstra_node ON dijkstra(node);

-- STEP 2:  get the paths - descending length order
SELECT
   SUM(d.cost)::NUMERIC(20,2) AS route_cost
  ,start_vid  -- starting vertex ( "deadends" in this example )
  ,end_vid    -- ending vertex .. constant 3000
  ,ST_AsText(ST_Linemerge(ST_Union(e.the_geom))) AS the_geom_text
  ,Array_agg(d.edge ORDER BY path_seq) AS edge_ids 
FROM dijkstra   d
   , edge_table e
WHERE d.edge=e.id
GROUP BY start_vid, end_vid
ORDER BY route_cost DESC
;
----------------------

---+------------+-----------+---------+-------------------------------------+--------------+
---| route_cost | start_vid | end_vid |            the_geom_text            |   edge_ids   |
---+------------+-----------+---------+-------------------------------------+--------------+
---|       6.24 |      3044 |    3000 | LINESTRING(4 4,3 4,2 3,1 2,1 1,0 0) | {13,9,6,3,1} |
---|       5.83 |      3043 |    3000 | LINESTRING(4 3,4 2,3 2,2 1,1 1,0 0) | {12,8,5,2,1} |
---|       4.83 |      3024 |    3000 | LINESTRING(2 4,2 3,1 2,1 1,0 0)     | {10,6,3,1}   |
---|       4.41 |      3014 |    3000 | LINESTRING(1 4,1 3,1 2,1 1,0 0)     | {11,7,3,1}   |
---|       3.41 |      3031 |    3000 | LINESTRING(3 1,2 1,1 1,0 0)         | {4,2,1}      |
---+------------+-----------+---------+-------------------------------------+--------------+
---(5 rows)


-- edge_ids, node_ids 
SELECT
   start_vid  -- starting vertex ( "deadends" in this example )
  ,end_vid    -- ending vertex .. constant 3000 
  ,Array_agg(d.edge ORDER BY path_seq) AS edge_ids 
  ,Array_agg(d.node ORDER BY path_seq) AS node_ids  
  ,SUM(d.cost)::NUMERIC(20,2) AS route_cost  
FROM dijkstra   d
GROUP BY start_vid, end_vid
ORDER BY route_cost DESC
;

---+-----------+---------+-----------------+---------------------------------+------------+
---| start_vid | end_vid |    edge_ids     |            node_ids             | route_cost |
---+-----------+---------+-----------------+---------------------------------+------------+
---|      3044 |    3000 | {13,9,6,3,1,-1} | {3044,3034,3023,3012,3011,3000} |       6.24 |
---|      3043 |    3000 | {12,8,5,2,1,-1} | {3043,3042,3032,3021,3011,3000} |       5.83 |
---|      3024 |    3000 | {10,6,3,1,-1}   | {3024,3023,3012,3011,3000}      |       4.83 |
---|      3014 |    3000 | {11,7,3,1,-1}   | {3014,3013,3012,3011,3000}      |       4.41 |
---|      3031 |    3000 | {4,2,1,-1}      | {3031,3021,3011,3000}           |       3.41 |
---+-----------+---------+-----------------+---------------------------------+------------+
---(5 rows)