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

## 一.全表路径分析查询
![](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进行规划分析测试，看集群是否有利于查询速度提升。