pgRouting安装及使用示例

文章目录环境文档用途详细信息环境系统平台Linux x86-64 Red Hat Enterprise Linux 7版本4.5.10文档用途本文介绍pgRouting的安装及使用示例。详细信息简介pgRouting是PostgreSQL下基于PostGIS的扩展插件提供了地理空间路由和路径网络分析功能。pgRouting解决路网规划问题比如从起点出发经过很多个点最终回到起点从起点出发经过很多个点最终回到终点两点之间提供多条备用路径选择。安装pgRouting依赖PostGIS安装前需确认已安装PostGIS。pgRouting安装包如下P001-hgdb-see-4.5.–gis*-pgrouting*-f8960d5-2025****.x86_64.rpmpgRouting安装包需与数据库版本匹配。安装#rpm包执行 rpm -ivh P001-hgdb-see-4.5.*-*-gis*-pgrouting*-f8960d5-2025****.x86_64.rpm #deb包执行 dpkg -i P001-hgdb-see-4.5.*-*-gis*-pgrouting*-3b95507-2024****.arm64.deb安装完成后安全版数据库使用sysdba用户企业版使用highgo用户登录数据库创建pgrouting扩展如下psql osm_db sysdba osm_db# create extension pgrouting osm_db# \dx List of installed extensions Name | Version | Schema ----------------------------------------- pgrouting | 3.3.1 | public plpgsql | 1.0 | pg_catalog postgis | 3.2.2dev | public postgis_topology | 3.2.2dev | topology使用示例下面使用pgrouting实现点到点多点到一点的路径规划。例子实现步骤如下1、数据准备#采用osm导入的数据#选取一部分数据createtableplanet_osm_roads_testasSELECTosm_id,admin_level,name,oneway,operator,z_order,wayFROMplanet_osm_roadsLIMIT10000offset55000;#添加source、tartget、cost、reverse_cost字段source、tartget字段是创建道路数据拓扑关系需要的cost、reverse_cost是成本估算值权重osm_db# alter table planet_osm_roads_test add column source int;osm_db# alter table planet_osm_roads_test add column target int;osm_db# alter table planet_osm_roads_test add column cost float;osm_db# alter table planet_osm_roads_test add column reverse_cost float;2、创建拓扑关系osm_db# SELECT pgr_createTopology(planet_osm_roads_test, 0.001, way, osm_id);#生成了新表planet_osm_roads_test_vertices_pgrDDL如下CREATETABLEpublic.planet_osm_roads_test_vertices_pgr(id bigserialNOTNULL,cnt int4NULL,chk int4NULL,ein int4NULL,eout int4NULL,the_geompublic.geometry(point,4326)NULL,CONSTRAINTplanet_osm_roads_test_vertices_pgr_pkeyPRIMARYKEY(id));CREATEINDEXplanet_osm_roads_test_vertices_pgr_the_geom_idxONpublic.planet_osm_roads_test_vertices_pgrUSINGgist(the_geom);3、设置道路通行成本权重这里简单的以道路数字化长度计算osm_db# select distinct oneway from planet_osm_roads;oneway--------no-1yes osm数据中oneway表示道路通行方向其他数据源也有此字段这里oneway有四个选项null道路双向可通行 yes道路单行道实际通行方向和数字化方向相同no道路禁止通行或禁止某类型车通行-1道路单行道实际通行方向和数字化方向相反#道路双向通行成本道路双向可通行时正向、反向成本都为道路数字化长度updateplanet_osm_roads_testsetcostst_length(way),reverse_costst_length(way)whereonewayisnull;#道路实际方向与数据方向一致反向成本设置无限大值updateplanet_osm_roads_testsetcostst_length(way),reverse_cost999999999999whereonewayyes;#道路实际方向与数据方向相反正向成本设置无限大值updateplanet_osm_roads_testsetcost999999999999,reverse_costst_length(way)whereoneway-1;#道路禁止通行正向、反向成本都设置无线大值updateplanet_osm_roads_testsetcost999999999999,reverse_cost999999999999whereonewayno;4、实现效果测试 从planet_osm_roads_test_vertices_pgr表中大概选取几个点251-山大中心校区762-美联广场1043-万虹广场。1 测试从251到762的路径点到点#通过成本估算计算出需要经过的点select*frompgr_dijkstra(select osm_id as id , source, target, cost from planet_osm_roads_test,251,762,true);seq|path_seq|node|edge|cost|agg_cost|------------------------------------------------------------------1|1|251|1046076383|0.006844842308275152|0.0|2|2|244|548989046|0.011145742962676768|0.006844842308275152|3|3|274|553764906|0.0017951356130137173|0.01799058527095192|4|4|277|959638562|0.0011370426069386695|0.019785720883965636|5|5|362|959638563|0.001616456363375162|0.020922763490904307|6|6|363|548989050|0.0007897126122848|0.02253921985427947|7|7|278|556421924|0.0008899553078673|0.02332893246656422|8|8|365|557853475|0.004719408081710194|0.024218887774431517|9|9|366|553892277|0.0059124676278269495|0.02893829585614171|10|10|368|554179650|0.007977539964683337|0.03485076348396866|11|11|1218|685031739|0.00450564696523659|0.042828303448651996|12|12|1217|554179639|0.0012612492102538943|0.04733395041388858|查出以上经过的点对应的坐标select*fromplanet_osm_roads_test_vertices_pgrwhereidin(selectnodefrompgr_dijkstra(select osm_id as id , source, target, cost from planet_osm_roads_test,251,762,true)orderbypath_seq);在地图上展示经过的点如下从以上地图展示看到pgRouting规划的从251到762的路径导航是合理的。2 测试从1043、762到251的路径多点到一点查出从1043、762到251经过的点的坐标select*fromplanet_osm_roads_test_vertices_pgrwhereidin(selectnodefrompgr_dijkstra(select osm_id as id , source, target, cost from planet_osm_roads_test,ARRAY[1043,762],251,true)orderbypath_seq);在地图上展示以上经过的点如下从以上地图展示看到pgRouting规划的从1043、762到251的路径导航是合理的。总结本文档介绍了pgRouting的安装使用pgRouting实现了点到点和多点到一点的路径导航规划。