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表来看最短路径的结果了
感谢 网友 前进
以前用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表来看最短路径的结果了
