---原先的in选择方法。结果无顺序。
SELECT *
FROM roads
WHERE gid
IN (SELECT edge_id AS gid
FROM shortest_path('SELECT gid AS id,fr_node AS source,to_node AS target,cost FROM roads',70025,68295,false,false))
---新的join选择方法。结果按照path 顺序输出。但是没有在coldfusion测试。
SELECT edge_id,cn_rdname,fr_node,to_node,route.cost
FROM shortest_path('select gid AS id,fr_node AS source,to_node AS target,cost FROM roads',70025,68295,false,false) route,roads
WHERE route.edge_id = roads.gid
----选择最短路径的edge_id 根据roads表。
SELECT edge_id FROM shortest_path('select rd_id AS id,fr_node AS source,to_node AS target,cost FROM roads',81470,81470,false,false)
----选择最短路径的edge_id 根据bjg_rdnt表。
SELECT edge_id FROM shortest_path('select rd_id AS id,fr_node AS source,to_node AS target,rd_length as cost FROM bjg_rdnt',81470,81470,false,false)
---更新final_hotel_djkstra的from_to字段,形式为 SRC_NODE,DES_NODE
update final_hotel_djkstra
set
from_to = src_node || ',' || des_node
---选择唯一的src_node和 des_node 并创建表。walking table
create table dis_djkstra as select distinct src_node,des_node from final_hotel_djkstra;
----选择唯一的src_node和 des_node 并创建表。driving table
create table dis_djkstra_dir as select distinct src_node,des_node from final_hotel_djkstra;
---更新新表from_to字段
update dis_djkstra_dir
set
from_to = src_node || ',' || des_node
----测试结果
select * from dis_djkstra where gid > 2000 and gid < 2011 ;
----更新htel_djkstra表的segment_id。
update htel_djkstra
set
segment_id = (select segment_id from dis_djkstra where htel_djkstra.from_to = dis_djkstra.from_to)
---清除字段内容
update dis_djkstra
set
segment_id = ''
---清除字段内容
update htel_djkstra
set
segment_id = ''
---更新roads表,对rcost(reverse_cost)字段赋值。双向为cost,单行(one way)为cost*10000。
update roads
set
rcost = cost
where direction = 0
update roads
set
rcost = cost * 10000
where direction = 1
---最短路径选择。考虑one way(将one way的reverse_cost设置为高值)。
SELECT * FROM shortest_path('select rd_id AS id,fr_node AS source,to_node AS target,cost,rcost as reverse_cost FROM roads',64102,64540,false,true)
---测试
select * from dis_djkstra_dir where gid > 0 and gid < 10;
----创建新的中间过程表。
create table final_hotel_dj_dir as select * from final_hotel_djkstra
---清除segment_id的内容。
update final_hotel_dj_dir
set
segment_id = ''
---更新final_hotel_dj_dir表的segment_id
update final_hotel_dj_dir
set
segment_id = (select segment_id from dis_djkstra_dir where final_hotel_dj_dir.from_to = dis_djkstra_dir.from_to)
---测试
select * from final_hotel_dj_dir where segment_id = '';