pgrouting示例

奥飞火千万电

今天搞定pgrouting，记录
感谢 网友 前进
以前用pgdijkstra，遇到一些问题，后来出了pgrouting，使用还是有些问题
主要问题是

ERROR: relation with OID 51307 does not exist SQL state: 42P01 Context: SQL statement "SELECT id, the_geom FROM vertices_tmp WHERE distance(the_geom, $1 ) < $2 " PL/pgSQL function "point_to_id" line 7 at select into variables PL/pgSQL function "assign_vertex_id" line 50 at assignment
网上别人的办法
Ok found out that the problem was due to that the function gets compiled only once per connection, so I had to reconnect
而我的情况是，我直接在pgadmin里执行assign_vertex_id函数，会报上面的错，而从上面方法启示，在psql的命令行方式下执行sql语句，成功了，呵呵，相信如果我直接用php连pgrouting就不会出现这么多麻烦了


下面是pgrouting完整的解决方案：
这里用的版本是postgresql8.2 ,pgrouting_1.0.0_win32
1.postgresql和postgis安装略过
2.安装pgrouting，将pgrouting下doc lib share三个文件夹靠到postgresql文件夹下
3.执行routing_core.sql,routing_core_wrappers.sql,安装pgrouting，这里安装了dijkstra算法包，另外还有driving distance,travling sales man两个算法包，可以选择安装(routing_dd.sql,routing_dd_wrappers.sql,routing_tsp.sql,routing_tsp_wrappers.sql)
4.导入示例数据    psql -d pgrouting -U postgres -f d:\data\victoria.sql             示例数据在官网可下载
5.为victoria表添加the_geom字段
ALTER TABLE victoria RENAME COLUMN the_geom TO geom;
SELECT AddGeometryColumn('victoria','the_geom',54004,'MULTILINESTRING',2);
UPDATE victoria SET the_geom=geom;
ALTER TABLE victoria DROP COLUMN geom;
6.为victoria赋  source,target值，执行   
SELECT assign_vertex_id('victoria', 0.001, 'the_geom', 'gid');
0.001为阈值范围，可视具体情形设定，计算好后，会生成一张vertex_tmp的临时表（psql执行sql命令哦）
7.一切准备好后，就可以计算最短路径了，以dijkstra算法为例，这里用长度当作权重,求起点248到终点1455的最短路径
SELECT * FROM shortest_path('
SELECT gid as id,
source::integer,
target::integer,
length::double precision as cost
FROM victoria',
248, 1455, true, false);

得到结果

vertex_id    edge_id    cost
248    82102    110.559379577637
84    57984    141.242736816406
1252    193310    52.5332260131836
1970    207837    80.3645401000977
100    180746    284.944580078125
1720    176290    143.326675415039
910    35578    139.427536010742
911    143636    140.143005371094
1495    204813    139.011123657227
430    143222    123.869613647461
1594    155785    253.544082641602
684    26341    243.902267456055
685    56973    141.959945678711
1239    195168    48.8763236999512
1147    112572    161.616928100586
1839    119499    303.865966796875
1759    188892    56.5326347351074
1575    143214    77.3971405029297
2050    140591    158.934661865234
2051    216067    115.035369873047
1950    122139    11.0751705169678
1060    44613    182.172515869141
1061    148709    111.463470458984
908    211592    12.3400468826294
771    29588    126.651756286621
774    63990    175.560150146484
1338    142395    101.610443115234
1978    125346    79.8519439697266
1268    58800    91.4863662719727
1269    83787    87.1205139160156
1613    96196    98.0832748413086
586    22142    275.695068359375
33    108489    152.655151367188
1455  -1    0



#drop table tmp;
select * into tmp from vertices_tmp as vt where vt.id in (SELECT vertex_id FROM shortest_path('SELECT gid as id,source::integer,target::integer,length::double precision as cost FROM road',248, 400, true, false));
或者
SELECT sp.vertex_id,vt.the_geom  into tmp FROM shortest_path('SELECT gid as id,source::integer,target::integer,length::double precision as cost FROM road',123, 334, true, false) as sp,vertices_tmp as vt where sp.vertex_id=vt.id

ALTER TABLE tmp ADD CONSTRAINT tmp_pk PRIMARY KEY (id);
这样可以直接在qgis里打开tmp表来看最短路径的结果了