pgRouting

-- 构建拓扑关系--
ALTER TABLE road ADD COLUMN source_id int;
ALTER TABLE road ADD COLUMN target_id int;
ALTER TABLE road ADD COLUMN edge_id int;
-- next line is to work around a pgRouting bug in update_cost_from_distance (fixed in latest CVS)
ALTER TABLE road RENAME id TO id_old;
 
SELECT assign_vertex_id('road', 0.001);
 
-- 重复检查--
SELECT * FROM (SELECT source_id, target_id, COUNT(*) AS c FROM road GROUP BY
source_id, target_id ORDER BY c)
AS foo WHERE foo.c = 2;
 
-- 删除重复的列--
CREATE TABLE doublons AS SELECT * FROM road WHERE gid  IN
(SELECT gid FROM (SELECT DISTINCT ON (source_id, target_id) source_id, gid
FROM road) AS doublon);
DELETE FROM road;
INSERT INTO road (SELECT * FROM doublons);
DROP TABLE doublons;
 
-- 构建视图--
SELECT create_graph_tables('road', 'int4');
 
--用距离赋予权重 --
SELECT update_cost_from_distance('road');
 
-- 执行shortest_path命令--
SELECT * FROM shortest_path('
SELECT gid as id,
source_id::integer as source,
target_id::integer as target,
length(the_geom) as cost
FROM road',
1, 15, TRUE, FALSE);