现在的位置: 首页 > 综合 > 正文

PGRouting初探

2013年06月19日 ⁄ 综合 ⁄ 共 9263字 ⁄ 字号 评论关闭

这篇文章其实也并算不上是原创,也谈不上是翻译,主要是把pgrouting的workshop中的一些东西做一些简单的总结。

 

一、下载和安装

PGRouting要配合PostgreSQLPostgis使用,所以安装PGRouting之前要先安装PostgreSQLPostgis,它们的官网地址分别是:

PostgreSQL         

http://www.postgresql.org/

Postgis               

http://www.postgis.net/

PGRouting         

http://pgrouting.org/

另外在安装好PostgreSQL之后,程序本身会提供一个工具Application Stack Builder,我们也可以在这个工具中下载对应版本的Postgis

PostgreSQLPostgiswindows下的安装相对简单,在这里说一下PGRouting的安装。

在官网下载和本机PostgreSQL对应版本的PGRouting,我这里的版本的PostgreSQL 9.2,这个版本可以使用的PGRouting对应版本是2.0

下载PGRouing之后,可以看到里面有3个文件夹(binlibshare)和5个文件,以后可能会有变动,将这三个文件夹拷贝到PostgreSQL的安装目录下,和同名文件夹合并。

我们可以创建一个新的数据库,暂时起名为routingtest,可以在pgAdmin III中创建,也可以利用控制台命令createdb创建,然后让这个数据库支持PostgisPGRouting,使用下面两条命令

# add PostGIS functions

CREATE EXTENSION postgis;

# add pgRouting core functions

CREATE EXTENSION pgrouting;

这两条命令成功运行之后你的PGRouting就可以在routingtest这个数据库上起作用了。

其实这两条命令对应的sql文件在share文件夹下面都可以找到,分别是postgis.sqlpgrouting.sql,我们也可以直接运行这两个sql来使数据库支持PostgisPGRouting

二、数据准备

现在在routingtest这个数据库上布置一份可以在后面查询中使用的数据,解压文件夹中的SHP.rar,使用PostGIS Shapefile and DBF loader这个工具,将加压出来的sampledata.shp导入数据库,会生成一个sampledata的表,然后按下面的步骤完善数据。

 

--sampledata表添加sourcetarget字段

ALTER TABLE sampledata ADD COLUMN "source" integer;

ALTER TABLE sampledata ADD COLUMN "target" integer;

 

--sampledata表创建拓扑布局,即为sourcetarget字段赋值

SELECT pgr_createTopology('sampledata', 0.00001, 'geom', 'gid');

 

--sourcetarget字段创建索引

CREATE INDEX source_idx ON sampledata("source");

CREATE INDEX target_idx ON sampledata("target");

 

--sampledata表添加reverse_cost字段并用length的值赋值

ALTER TABLE sampledata ADD COLUMN reverse_cost double precision;

UPDATE sampledata SET reverse_cost = length;

到此,一份可以供给查询的数据就在数据库中存在了。

三、尝试查询

--使用pgr_dijkstra算法查询

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('

               
SELECT gid AS id,

                        
source::integer,

                        
target::integer,

                        
length::double precision AS cost

                       
FROM sampledata',

               
30, 60, false, false);

/*

pgr_dijkstra的定义是pgr_costResult[]
pgr_dijkstra(text sql, integer source, integer target, boolean directed, boolean has_rcost);

directed是否限制方向,has_ rcost作用未知,返回值为pgr_costResult

pgr_costResultworkshop中的解释是

A set of records to describe a path result with cost attribute

一组带有消耗属性的用于描述路径结果的记录的集合。

其定义如下

CREATE TYPE pgr_costResult AS

(

    seq integer, -- row sequence

    id1 integer, -- node ID

    id2 integer, -- edge ID (-1 for the last row)

    cost float8 -- cost to traverse from id1 using id2

);

*/

将这个表导出为shp,再在mapinfo中转换为tab打开,可以看到上面的结果如下图所示

四、继续丰富数据

--sampeldata表添加x1 y1 x2 y2字段

ALTER TABLE sampledata ADD COLUMN x1 double precision;

ALTER TABLE sampledata ADD COLUMN y1 double precision;

ALTER TABLE sampledata ADD COLUMN x2 double precision;

ALTER TABLE sampledata ADD COLUMN y2 double precision;

--为新添加的四个字段自动赋值

UPDATE sampledata SET x1 = ST_x(ST_PointN(geom, 1));

UPDATE sampledata SET y1 = ST_y(ST_PointN(geom, 1));

UPDATE sampledata SET x2 = ST_x(ST_PointN(geom, ST_NumPoints(geom)));

UPDATE sampledata SET y2 = ST_y(ST_PointN(geom, ST_NumPoints(geom)));

然后我们就可以使用其他的查询函数了

 

--AStar查询                                                                  

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_astar('

               
SELECT gid AS id,

                        
source::integer,

                        
target::integer,

                        
length::double precision AS cost,

                        
x1, y1, x2, y2

                       
FROM ways',

                30, 60, false, false);

--kdijistra查询消耗

SELECT seq, id1 AS source, id2 AS target, cost FROM pgr_kdijkstraCost('

               
SELECT gid AS id,

                        
source::integer,

                        
target::integer,

                        
length::double precision AS cost

                       
FROM ways',

               
10, array[60,70,80], false, false);

--kdijistra查询具体路径

SELECT seq, id1 AS path, id2 AS edge, cost FROM pgr_kdijkstraPath('

               
SELECT gid AS id,

                        
source::integer,

                        
target::integer,

                        
length::double precision AS cost

                       
FROM ways',

               
10, array[60,70,80], false, false);

五、单向限制路段

在之前的表里我们已经丰富了足够的字段,其中包括一个reverse_cost(反向距离)字段,利用这个字段,我们就可以做路段的单向限制了,具体的做法就是把这个路段的reverse_cost的值设置为10000

然后以pgr_dijkstra函数为例,在我们查询的时候将directedhas_rcost两个字段设为true即可。

如果在查询的时候遇到这条错误:

错误
Error, reverse_cost is used, but query did't return 'reverse_cost' column

         请注意对比用之前的pgr_dijkstra查询语对应一下下面的语句,注意绿字部分

SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('

               
SELECT gid AS id,

                        
source::integer,

                        
target::integer,

                        
length::double precision AS cost,

                        
reverse_cost::double precision AS reverse_cost

                       
FROM sampledata',

               
30, 60, true, true);

         添加反向距离的查询即可。

         比如将之前的12311232这段路设置为单向通行,再次查询到的结果则可以看到区别了。

        

当然现在将最后两个参数设为false的话,我们是同样可以得到和之前没有绿字部分一样的结果的。

六、Weighted costs

在现实中,我们在做路径规划的时候,可能并非是只寻找最短路径,比如我们可能需要查询的是花费节省的路径,比如我们会选择坐公交车或者地铁而不是出租车,这时候class_id这个字段的真实意义就显现出来了。下面这段是直接从workshop的第七章截取出来的

When we convert data from OSM format using the osm2pgrouting tool, we get two additional tables for road
types and road
classes
:

Note

We switch now to the database we previously generated with osm2pgrouting. From within PostgreSQL shell this is possible with the
\c
routing command.

Run: SELECT*FROMtypesORDERBYid;

 id |   name

----+-----------

  2
|
cycleway

  1
|
highway

  4
|
junction

  3
|
tracktype

(4
rows
)

Run: SELECT*FROMclassesORDERBYid;

 id  | type_id
|       name       
|
cost | priority
|
default_maxspeed

-----+---------+-------------------+------+----------+------------------

 100
|
       1 | road             
|      |       
1 |              
50

 101
|
       1 | motorway         
|      |       
1 |              
50

 102
|
       1 | motorway_link    
|      |       
1 |              
50

 103
|
       1 | motorway_junction
|      |       
1 |              
50

 104
|
       1 | trunk            
|      |       
1 |              
50

 105
|
       1 | trunk_link       
|      |       
1 |              
50

 106
|
       1 |
primary          
|
      |       
1
|              
50

 107
|
       1 | primary_link     
|      |       
1 |              
50

 108
|
       1 | secondary        
|      |       
1 |              
50

 109
|
       1 | tertiary         
|      |       
1 |              
50

 110
|
       1 | residential      
|      |       
1 |              
50

 111
|
       1 | living_street    
|      |       
1 |              
50

 112
|
       1 | service          
|      |       
1 |              
50

 113
|
       1 | track            
|      |       
1 |              
50

 114
|
       1 | pedestrian       
|      |       
1 |              
50

 115
|
       1 | services         
|      |       
1 |              
50

 116
|
       1 | bus_guideway     
|      |       
1 |              
50

 117
|
       1 | path             
|      |       
1 |              
50

 118
|
       1 | cycleway         
|      |       
1 |              
50

 119
|
       1 | footway          
|      |       
1 |              
50

 120
|
       1 | bridleway        
|      |       
1 |              
50

 121
|
       1 | byway            
|      |       
1 |              
50

 122
|
       1 | steps            
|      |       
1 |              
50

 123
|
       1 | unclassified     
|      |       
1 |              
50

 124
|
       1 | secondary_link   
|      |       
1 |              
50

 125
|
       1 | tertiary_link    
|      |       
1 |              
50

 201
|
       2 | lane             
|      |       
1 |               50

 202
|
       2 | track            
|      |       
1 |              
50

 203
|
       2 | opposite_lane    
|      |       
1 |              
50

 204
|
       2 | opposite         
|      |       
1 |              
50

 301
|
       3 | grade1           
|      |       
1 |              
50

 302
|
       3 | grade2           
|      |       
1 |              
50

 303
|
       3 | grade3           
|      |       
1 |              
50

 304
|
       3 | grade4           
|      |       
1 |              
50

 305
|
       3 | grade5           
|      |       
1 |              
50

 401
|
       4 | roundabout       
|      |       
1 |              
50

(36
rows
)

The road class is linked with the ways table by class_id field. After importing data the
cost attribute is not set yet. Its values can be changed with an
UPDATE query. In this example cost values for the classes table are assigned arbitrary, so we execute:

UPDATE classes
SET cost=1 ;

UPDATE classes
SET cost=2.0
WHERE name IN ('pedestrian','steps','footway');

UPDATE classes
SET cost=1.5
WHERE name IN ('cicleway','living_street','path');

UPDATE classes
SET cost=0.8
WHERE name IN ('secondary','tertiary');

UPDATE classes
SET cost=0.6
WHERE name IN ('primary','primary_link');

UPDATE classes
SET cost=0.4
WHERE name IN ('trunk','trunk_link');

UPDATE classes
SET cost=0.3
WHERE name IN ('motorway','motorway_junction','motorway_link');

For better performance, especially if the network data is large, it is better to create an index on the class_id field of the ways table and eventually on the
id field of the
types
table.

CREATE
INDEX
ways_class_idx ON ways (class_id);

CREATE
INDEX
classes_idx ON classes (id);

The idea behind these two tables is to specify a factor to be multiplied with the cost of each link (usually length):

SELECT seq, id1
AS node, id2 AS edge, cost
FROM pgr_dijkstra('

                SELECT gid AS id,

                         source::integer,

                         target::integer,

                         length * c.cost AS cost

                        FROM ways, classes c

                        WHERE class_id = c.id',

               
30
, 60, false,
false);

 

七、需要注意的地方

提供查询的原始表应该要包含的几个字段和类型: gid(numeric)class_id(numeric)length(numeric)name(varchar)osm_id(varchar)geom(geometry),除了class_id和osm_id之外,其余的字段都是必须要包含的,其他的字段我们都可以通过这些字段来丰富。当然对于其中一些字段,命名并不是一定要是这样。以上字段的意义:

gid:主键、路段id

class_id:优先级

length:路段长度

name:路名

osm_id:id,一条路可以分为几条路段

geom:空间属性,相对于路来说的话就是线

 

八、其他

关于PGRouting的文档可以在这里下载http://docs.pgrouting.org/

GeoLive

http://live.osgeo.org/en/index.html

OpenLayers3

http://ol3js.org/

具体SQL语句见pgrouting_demo.sql

抱歉!评论已关闭.