将北京路网OSM文件导入到PostgreSQL + PostGIS 中,并利用osm2pgrouting工具+osmosis工具构建路网Graph拓扑结构
0. 提前新建好数据库 beijing 并安装以下3个扩展
create extension hstore;
create extension postgis;
create extension pgrouting;
1. 准备好 osmosis 工具和 osm2pgrouting 工具
2. cmd 一定是管理员
cd "C:\Program Files (x86)\osmosis\bin"
C:\Program Files (x86)\osmosis\bin>osmosis --read-pbf "D:\data\beijing.osm.pbf" --write-xml beijing.osm
3. osm2pgrouting 只接受.osm 不接受.osm.pbf二进制文件
cd D:\data
osm2pgrouting --file D:/data/beijing.osm --conf S:/PostgreSQL/10/bin/mapconfig_for_cars.xml --schema public --addnodes --prefix bj_ --suffix _car --clean -d beijing -U postgres -h localhost -p 5432 -W jiangshan
or 以下带全部参数
osm2pgrouting --file D:/data/beijing.osm --conf S:/PostgreSQL/10/bin/mapconfig_for_cars.xml --schema public --prefix bj_ --suffix _car --addnodes --attributes --tags --clean -d beijing -U postgres -h localhost -p 5432 -W jiangshan
正常应该输出以下:
Creating tables...
TABLE: public.bj_ways_car created ... OK.
TABLE: public.bj_pointsofinterest_car created ... OK.
TABLE: public.configuration created ... OK.
TABLE: public.osm_nodes created ... OK.
TABLE: public.osm_ways created ... OK.
TABLE: public.osm_relations created ... OK.
Opening configuration file: S:/PostgreSQL/10/bin/mapconfig_for_cars.xml
Parsing configuration
Exporting configuration ...
- Done
Parsing data
Current osm_nodes: 1600000
Final osm_nodes: 1613675
Current osm_ways: 200000
Final osm_ways: 210604
End Of file
Finish Parsing data
Adding auxiliary tables to database...
Export Ways ...
Processing 210604 ways:
[****| ] (9%) Total processed: 20000 Vertices inserted: 32536 Split ways inserted 32166
[*********| ] (18%) Total processed: 40000 Vertices inserted: 20369 Split ways inserted 27530
[**************| ] (28%) Total processed: 60000 Vertices inserted: 15299 Split ways inserted 22009
[******************| ] (37%) Total processed: 80000 Vertices inserted: 10957 Split ways inserted 15036
[***********************| ] (47%) Total processed: 100000 Vertices inserted: 10081 Split ways inserted 14253
[****************************| ] (56%) Total processed: 120000 Vertices inserted: 6480 Split ways inserted 9379
[*********************************| ] (66%) Total processed: 140000 Vertices inserted: 6910 Split ways inserted 10421
[*************************************| ] (75%) Total processed: 160000 Vertices inserted: 6187 Split ways inserted 8965
[******************************************| ] (85%) Total processed: 180000 Vertices inserted: 6729 Split ways inserted 11042
[***********************************************| ] (94%) Total processed: 200000 Vertices inserted: 13488 Split ways inserted 20426
[**************************************************|] (100%) Total processed: 210604 Vertices inserted: 3274 Split ways inserted 5000
Creating indexes ...
Processing Points of Interest ...
Adding functions for processing Points of Interest ...
To process pointsOfInterest table:
osm2pgr_pois_update(radius default 200, within default 50)
- Using areas of (radius)mts on POIS
- Using edges that are at least (within) mts of each POI
POIS that do not have a closest edge is considered as too far
#########################
size of streets: 210604
Execution started at: Fri Mar 19 12:31:46 2021
Execution ended at: Fri Mar 19 12:36:31 2021
Elapsed time: 285.297 Seconds.
User CPU time: -> 285.299 seconds
#########################
===================================================以下其他记录=========================================================
# 删除命令
# --删除表及相关依赖关系 -- 需要时再进行操作
DROP TABLE public.bjwayscar_vertices_pgr CASCADE;
DROP TABLE public.bjwayscar CASCADE;
DROP TABLE public.bjpointsofinterestcar CASCADE;
DROP TABLE public.configuration CASCADE;
DROP TABLE public.osm_nodes CASCADE;
DROP TABLE public.osm_ways CASCADE;
DROP TABLE public.osm_relations CASCADE;
# osm2pgrouting 源码解读
osm2pgrouting新建WAYS_TABLE,更新length_m, cost_s, reverse_cost_s的策略
# length_m, cost_s, reverse_cost_s计算规则 bjwayscar=WAYS_TABLE
UPDATE WAYS_TABLE SET length_m = ST_length(geography(ST_Transform(the_geom, 4326))),
cost_s = CASE
WHEN one_way = -1 THEN -ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_forward::float * 5.0 / 18.0)
ELSE ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_backward::float * 5.0 / 18.0)
END,
reverse_cost_s = CASE
WHEN one_way = 1 THEN -ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_backward::float * 5.0 / 18.0)
ELSE ST_length(geography(ST_Transform(the_geom, 4326))) / (maxspeed_backward::float * 5.0 / 18.0)
END
WHERE length_m IS NULL AND maxspeed_backward !=0 AND maxspeed_forward != 0;