postgresql+pgrouting 最短路线sql （4）

CREATE EXTENSION postgis;  --添加postgis功能
CREATE EXTENSION pgrouting;	--添加pgRouting函数
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;


--添加起点id
ALTER TABLE public.route5 ADD COLUMN source integer;
--添加终点id
ALTER TABLE public.route5 ADD COLUMN target integer;
--添加道路权重值
ALTER TABLE public.route5 ADD COLUMN length double precision;


--为sampledata表创建拓扑布局，即为source和target字段赋值
SELECT pgr_createTopology('public.route5',0.00001, 'geom', 'gid');


--为source和target字段创建索引
CREATE INDEX source_idx ON route5("source");
CREATE INDEX target_idx ON route5("target");

--为length赋值
update route5 set length =st_length(geom);


--为beijingmodified表添加reverse_cost字段并用length的值赋值
ALTER TABLE route5 ADD COLUMN reverse_cost double precision;
UPDATE route5 SET reverse_cost =length;

--使用pgr_dijkstra算法查询
SELECT seq, id1 AS node, id2