pgrouting 做处理的备份。

---原先的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 = '';