-
-
Save ImreSamu/efda6093b67391a0edafff39d8056cb5 to your computer and use it in GitHub Desktop.
pgrouting_example.sql // proposal for a question: https://lists.osgeo.org/pipermail/postgis-users/2022-April/045458.html
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment