# 一 全表路径分析查询

``````network=# select count(*) from ways;
count
---------
1250371
(1 row)
``````

``````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);
``````

# 二 矩形范围过滤

``````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);
``````

# 三 线性范围过滤

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);
``````

# 四 网格筛选过滤

``````network=# \d maps
数据表 "public.maps"
栏位  |          类型          | 修饰词
-------+------------------------+--------
mapid | integer                | 非空
geom  | geometry(Polygon,4326) |

"maps_pkey" PRIMARY KEY, btree (mapid)
"maps_geom_index" gist (geom)
network=# select * from maps limit 10;
mapid  |                                                                                                geom
--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
595756 | 0103000020E6100000010000000500000065BDBD10976F5D402753A278D7DF434065BDBD10976F5D407D776D6786EA4340C85C19549B775D407D776D6786EA4340C85C19549B775D402753A278D7DF434065BDBD10976F5D402753A278D7DF4340
555571 | 0103000020E61000000100000005000000463150849AC75C4025A93394E69F4240463150849AC75C40AB6B40C694AA42406270E6BA9DCF5C40AB6B40C694AA42406270E6BA9DCF5C4025A93394E69F4240463150849AC75C4025A93394E69F4240
615867 | 0103000020E6100000010000000500000074D6E1C79CB75D40CA83DB771895444074D6E1C79CB75D4024EAEC01C69F44407C61536399BF5D4024EAEC01C69F44407C61536399BF5D40CA83DB771895444074D6E1C79CB75D40CA83DB7718954440
615707 | 0103000020E61000000100000005000000E144B24F99775D4091A8275E2B554440E144B24F99775D403459CC9DD35F4440EE76FF50977F5D403459CC9DD35F4440EE76FF50977F5D4091A8275E2B554440E144B24F99775D4091A8275E2B554440
605752 | 0103000020E610000001000000050000007EEB1E34964F5D40911DA72A253544407EEB1E34964F5D40D20A5FA1873E4440A3C87B5192575D40D20A5FA1873E4440A3C87B5192575D40911DA72A253544407EEB1E34964F5D40911DA72A25354440
555451 | 0103000020E61000000100000005000000A71F798C97875C4093814DE7948A4240A71F798C97875C40BA5D58CC429542406E34FC7E9C8F5C40BA5D58CC429542406E34FC7E9C8F5C4093814DE7948A4240A71F798C97875C4093814DE7948A4240
595632 | 0103000020E610000001000000050000009238F1F69C0F5D40308FCA877FCA43409238F1F69C0F5D40FE13F9812DD5434081CFEE149B175D40FE13F9812DD5434081CFEE149B175D40308FCA877FCA43409238F1F69C0F5D40308FCA877FCA4340
615633 | 0103000020E610000001000000050000008947A0C997175D406F0490771A7544408947A0C997175D406A69B0A1C27F44407B9CEDFA9A1F5D406A69B0A1C27F44407B9CEDFA9A1F5D406F0490771A7544408947A0C997175D406F0490771A754440
615663 | 0103000020E610000001000000050000007DAF424697175D407346393D149544407DAF424697175D407CCE61E7BB9F44400418F9699A1F5D407CCE61E7BB9F44400418F9699A1F5D407346393D149544407DAF424697175D407346393D14954440
(10 行记录)
``````

``````network=# \d ways
数据表 "public.ways"
栏位    |           类型            |                   修饰词
------------+---------------------------+---------------------------------------------
gid        | integer                   | 非空 默认 nextval('ways_gid_seq'::regclass)
name       | character varying(128)    |
pyname     | character varying(128)    |
mapid      | integer                   |
id         | character varying(13)     |
kind_num   | character varying(2)      |
kind       | character varying(30)     |
width      | character varying(3)      |
direction  | character varying(1)      |
toll       | character varying(1)      |
const_st   | character varying(1)      |
undconcrid | character varying(13)     |
snodeid    | integer                   |
enodeid    | integer                   |
funcclass  | character varying(2)      |
detailcity | character varying(1)      |
through    | character varying(1)      |
unthrucrid | character varying(13)     |
ownership  | character varying(1)      |
special    | character varying(1)      |
uflag      | character varying(1)      |
onewaycrid | character varying(13)     |
accesscrid | character varying(13)     |
speedclass | character varying(1)      |
lanenums2e | character varying(2)      |
lanenume2s | character varying(2)      |
lanenum    | character varying(1)      |
vehcl_type | character varying(32)     |
elevated   | character varying(1)      |
structure  | character varying(1)      |
usefeecrid | character varying(13)     |
usefeetype | character varying(1)      |
spdlmts2e  | character varying(4)      |
spdlmte2s  | character varying(4)      |
spdsrcs2e  | character varying(1)      |
spdsrce2s  | character varying(1)      |
dc_type    | character varying(1)      |
nopasscrid | character varying(13)     |
geom       | geometry(LineString,4326) |
length     | double precision          |
rev_length | double precision          |
x1         | double precision          |
y1         | double precision          |
x2         | double precision          |
y2         | double precision          |

"ways_pkey" PRIMARY KEY, btree (gid)
"mapid_index" btree (mapid)
"ways_enodeid_idx" btree (enodeid)
"ways_geom_idx" gist (geom)
"ways_snodeid_idx" btree (snodeid)
network=# select a.gid,a.mapid from ways a,(select mapid from maps limit 1) b where a.mapid=b.mapid limit 10;
gid   | mapid
---------+--------
112434 | 595756
112440 | 595756
117555 | 595756
23611 | 595756
1041239 | 595756
1193746 | 595756
694218 | 595756
735844 | 595756
739260 | 595756
740230 | 595756
(10 行记录)
``````

``````--测试样本
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
``````

``````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
``````

``````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
``````

# 五 总结

当然，以上测试都是单机测试，全部思路是减少查询路网数量，是动脑经的结果，没有在数据库的配置，硬件的优化上下功夫。作者将在适当时机，移入pgxl进行规划分析测试，看集群是否有利于查询速度提升。