基于PgRouting的百万级别路网查询优化__前文《基于PgRouting的GIS网络分析--数据准备》描述了如何进行数据准备工作，入门的朋友可以参考如何搭建环境，导入数据和建立索引等准备。pgrouting是postgis的插件，主要做网络分析等业务使用，一般一个地区，一个城市几万级别的路网，查询是非常快速的，但是全国路网动辄几百万，几千万的路网规模，默认查询就非常的慢了。于是，本文主要以dijkstra算法，安装pg的单机默认配置，重点阐述如何“动脑经”加速路径查询速度，而不是单纯依靠机器配置（毕竟再牛逼的机器也架不住无脑的大量运算啊），当然本文的方法并不是非常规范和标准，但提供了一个解决问题的思路，即大量路网的复杂查询优化一定要避免全表查询，尽量减少计算！！！ _

PG中文社区 /

mdi-home

首页

社区新闻

中文文档

加入ACE

mdi-home

首页

mdi-chat-processing

社区新闻

mdi-book-open-variant

中文文档

mdi-account-multiple-check

加入ACE

mdi-file-multiple-outline

相关资料

mdi-blank

mdi-exit-to-app

退出账号

基于PgRouting的百万级别路网查询优化

原作者：遥想公瑾当年　　创作时间：2017-09-25 22:46:30+08　　

AA 发布于2017-09-25 22:46:30　　

评论: 1　　

顶: 895　

踩: 955　

## 一.全表路径分析查询
![](http://upload-images.jianshu.io/upload_images/68979-9003255a390b6762.png?imageMogr2/auto-orient/strip)
### 查询路网的线数据规模：
```
network=# select count(*) from ways;
  count
---------
 1250371
(1 row)
```
 以dijkstra算法示例查询：

![](http://upload-images.jianshu.io/upload_images/68979-c364a611b89e7cc0.png?imageMogr2/auto-orient/strip)

如上图：A点坐标[115.2,39.8]，B点坐标[115.4,40]，A点的附近对应道路的gid是487371，B点附近对应道路的gid是62553（gid事先查询好的，测试就不写如何获取坐标附近的道路gid），考虑到道路有单行道的关系，所以有通行权重cost和反向权重reverse_cost ，查询语句如下：
```
SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways
',487371,62553,true);
```
返回93行记录，平均耗时5.8s，但是如图可知，其实AB两点比较近，而大部分路网其实对他们的计算根本就没关系，于是我们考虑在一开始查询时就规避无效路网。

## 二.矩形范围过滤
![](http://upload-images.jianshu.io/upload_images/68979-3dada4e425e3612c.png?imageMogr2/auto-orient/strip)
我们发现，AB之间范围及其附近的路网就足够分析出路径，而大量其他数据是没有任何影响作用的，我们设AB两点构成一个矩形，然后缓冲2km作为备用参与分析道路（即红色斜线部分），语句如下：
```
SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways
where st_intersects(geom,st_buffer(ST_PolygonFromText(''POLYGON((115.2 39.8,115.4 39.8,115.4 40,115.2 40,115.2 39.8))'',4326),0.02))
',487371,62553,true);
```
返回93行记录，平均耗时150 ms。
实验结果证明：与全表查询的分析结果一致，但全表查询是矩形查询耗时的 5800/150约49倍，明显优化速度还是很明显的。
![](http://upload-images.jianshu.io/upload_images/68979-1c684182a4b26514.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)
但是矩形查询也存在一个问题，当AB两点的经度接近（极端情况就是一致），那么两点只能构成一个 水平或者垂直的 线段（无法构造矩形区域），绝大部分形成一个细长的面区域如上图，这种情况下，因不能构造矩形筛选区域，或者说构造的区域过于狭窄无法满足路径查询的要求，采用“线性过滤”会更恰当些。
## 三.线性范围过滤
 AB两点坐标接近垂直或水平时，可选用线性查询。举例：AB两点不变，根据AB两点坐标构成线，缓冲5公里（线比矩形那个要大，尽量将可能的道路加入分析），查询语句如下：
```
SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways
where st_intersects(geom,st_buffer(ST_LineFromText(''LineString(115.2 39.8,115.4 40)'',4326),0.05))
',487371,62553,true);
```
耗时：180ms，效果仍然比较明显。

![](http://upload-images.jianshu.io/upload_images/68979-8d891837d37e14fd.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240)

#### 全表查询AB路径如下：

```
--测试样本
network=# select count(*) from ways;
  count
---------
 1250371
(1 行记录)
network=# SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways'
,647331,856772,true);
--耗时8.1s
```
#### 线性查询AB路径如下：
```
network=# select count(*) from ways where geom&&st_buffer(ST_LineFromText('LineString(114.53247 37.34692,118.125 39.82983)',4326),0.08);
 count
--------
 678892
(1 行记录)
network=# SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways
where geom&&st_buffer(ST_LineFromText(''LineString(114.53247 37.34692,118.125 39.82983)'',4326),0.08)
',647331,856772,true);
--耗时4.5s
```
#### 网格查询AB路径如下：
```
network=# select count(*) from ways where mapid in ( select mapid from maps where st_intersects(geom,st_buffer(ST_LineFromText('LineStr
ing(114.53247 37.34692,118.125 39.82983)',4326),0.08)));
 count
--------
 127914
(1 行记录)
network=# SELECT * FROM pgr_dijkstra('SELECT gid as id,snodeid as source,enodeid as target,length::float as cost,rev_length::float as reverse_cost FROM ways
where mapid in (select mapid from maps where st_intersects(geom,st_buffer(ST_LineFromText(''LineString(114.53247 37.34692,118.125 39.82983)'',4326),0.08)))
',647331,856772,true);
--耗时1.8s
```
#### 表格对比如下：
查询方式| 查询数据量|查询时间
----|------|----
全表查询	 | 1250371  |8.1 s
线性查询	| 678892  |4.5 s
网格查询	 | 127914  |1.8 s
虽然不是太满意结果，但是结果的确体现了“智慧的结晶”了，还算欣慰。

## 五.道路属性过滤
以全国路网分析简介（未测试），虽然几百万路网，但是 高速，快速路这种道路并不是很多，在做远距离路径分析时，优先选择起点最近的高速A1，终点附近的高速B1，那么先规划AA1，再A1B1，再B1B，将全国路网全表查询细分成高速路路径分析，高速和城市道路分析，细节部分也可采用范围叠加规划。同样的，对完整路网拆分成高速国道的一级查询，省道县道的二级查询，乡道的三级查询等等，尽可能将路网从整体分析中抽离出去。
## 六.总结
在近距离路径分析时，可考虑范围进行过滤，然后查询过滤后的路网。在远距离路径分析时，可以实际的条件做过滤。在城市道路时，远距离规划车辆路径，应优先规划起点到绕城，绕城出口到终点。
当然，以上测试都是单机测试，全部思路是减少查询路网数量，是动脑经的结果，没有在数据库的配置，硬件的优化上下功夫。作者将在适当时机，移入pgxl进行规划分析测试，看集群是否有利于查询速度提升。

评论:1　　

顶: 895　

踩: 955　

请在登录后发表评论，否则无法保存。

1# __ xiaowu 回答于 2024-04-23 14:14:49+08

收到红包高情商回复:https://www.nanss.com/yulu/4471.html 告诫背后诋毁人的句子:https://www.nanss.com/yulu/4145.html 网名昵称:https://www.nanss.com/mingcheng/4275.html 鼓励老公的话:https://www.nanss.com/yulu/4090.html 古代女名字:https://www.nanss.com/shenghuo/4267.html 你的婚礼经典台词:https://www.nanss.com/yulu/4239.html 古代名字:https://www.nanss.com/mingcheng/4058.html 当选感言:https://www.nanss.com/gongzuo/4186.html 做人难:https://www.nanss.com/yuedu/4348.html 谷雨文案:https://www.nanss.com/wenan/4510.html 形容人美到极致的成语:https://www.nanss.com/xuexi/4176.html 经典暖心短句感恩:https://www.nanss.com/yulu/4041.html 绝对看不懂的表白:https://www.nanss.com/shenghuo/4141.html 莎士比亚最经典的句子:https://www.nanss.com/xuexi/4157.html 三字网名干净:https://www.nanss.com/mingcheng/4419.html 收红包感谢语幽默:https://www.nanss.com/yulu/4444.html 100句怼死人的话:https://www.nanss.com/yulu/4076.html 周记200字大全:https://www.nanss.com/xuexi/4137.html 大一新生寄语:https://www.nanss.com/xuexi/4235.html 烛光赞:https://www.nanss.com/xuexi/4100.html 佛经中最唯美的句子:https://www.nanss.com/yulu/4189.html 昆虫记读后感200字:https://www.nanss.com/xuexi/4377.html 农谚:https://www.nanss.com/shenghuo/4467.html 廉洁自律格言:https://www.nanss.com/gongzuo/4217.html 佛经中最有禅意的句子:https://www.nanss.com/yulu/4381.html 表达心情感慨的句子:https://www.nanss.com/yulu/4094.html 五月丁香:https://www.nanss.com/yuedu/4434.html 恐怖的名字:https://www.nanss.com/mingcheng/4384.html 名字符号:https://www.nanss.com/mingcheng/4256.html 朋友圈晒花的心情短语:https://www.nanss.com/wenan/4054.html