Adjust pgRouting 4 procedures to improve performance of created noded edges#413
Adjust pgRouting 4 procedures to improve performance of created noded edges#413rustprooflabs merged 5 commits intomainfrom
Conversation
rustprooflabs
added
documentation
Testing ColoradoData loaded normally, vanilla config. docker stop pgosm && docker build -t rustprooflabs/pgosm-flex .
docker run --name pgosm -d --rm -v ~/pgosm-data:/app/output -v /etc/localtime:/etc/localtime:ro -e POSTGRES_USER=$POSTGRES_USER -e POSTGRES_PASSWORD=$POSTGRES_PASSWORD -p 5433:5432 -d rustprooflabs/pgosm-flex
docker exec -it pgosm python3 docker/pgosm_flex.py --ram=20 --region=north-america/us --subregion=coloradoAfter data loaded, connected via psql. CREATE EXTENSION IF NOT EXISTS pgrouting;
CREATE EXTENSION IF NOT EXISTS pgdd;
CALL osm.routing_prepare_roads_for_routing();11 minutes 18 seconds on my laptop. Tons of I/O through this, the untuned checkpoint process almost certainly slows this down compared to a production type config for this workload. (localhost 🐘) postgres@pgosm=# CALL osm.routing_prepare_roads_for_routing();
NOTICE: table "edges_table" does not exist, skipping
NOTICE: Edge table table created
NOTICE: table "initial_intersection" does not exist, skipping
NOTICE: Intersections table created
NOTICE: table "geom_with_blade" does not exist, skipping
NOTICE: table "split_edges" does not exist, skipping
NOTICE: table "routing_road_edge" does not exist, skipping
NOTICE: routing_osm_road_edge table created
WARNING: Not adding a unique constraint that should exist... data cleanup needed.
NOTICE: table "routing_road_vertex" does not exist, skipping
NOTICE: routing_osm_road_vertex table created
CALL
Time: 678175.175 ms (11:18.175) |
|
Built and pushed the docker image with the new procedure. The routing-4 page will have the latest procedures after I merge. Preparing the network now involves only this: I brought the different costs together in a section together instead of parsed out. The final examples in the page likely need some work, but that can be a next step. I validated a variety of routes I am familiar with after working through all the splitting/combining steps. I believe the network generated is in the same range of quality as what the functions in the older procedures produced. I have validated I get the same routes in a few instances. |
1 participant
Details
Continuing from #408.
Release Notes
osm.routing_prepare_roads_for_routing()to handle majority of routing prep forosm.road_linelayerpgr_separateTouchingfor performance)Timing Notes
pgr_separateTouching(). Takes 2 seconds - Documentation instructions for routing no longer work (pgrouting >=4) #408 (comment)