postgresql+postgis+pgrouting实现最短路径查询(1)---线数据的处理和建立拓扑

时间:2021-11-17 09:42:28

准备一个线shp数据,并将其导入postgres里面,postgres安装postgis和pgrouting两个插件(方法见http://www.cnblogs.com/nidaye/p/4553522.html)。线数据的字段如下:注意字段的名称,省的出现不必要的麻烦。

1、ALTER TABLE beijing_line ADD COLUMN source integer;

ALTER TABLE beijing_line ADD COLUMN target integer;

ALTER TABLE beijing_line ADD COLUMN length double precision;

UPDATE beijing_line SET length = ST_Length(the_geom);

postgresql+postgis+pgrouting实现最短路径查询(1)---线数据的处理和建立拓扑

ps:执行createTopology这个函数之前一定得对数据库执行以下三句sql查询:

CREATE EXTENSION postgis;

CREATE EXTENSION postgis_topology;

CREATE EXTENSION fuzzystrmatch;

2、select pgr_createTopology('beijing_line',0.001,source:='source',id:='gid',target:='target',the_geom:='the_geom');

select pgr_createTopology('beijing_line',0.1,source:='source',id:='gid',target:='target',the_geom:='the_geom’);

把容差值设置的大一点,可能结果会好点

3、   SELECT * FROM pgr_dijkstra('

SELECT gid as id,

source::integer,

target::integer,

length::double precision as cost

FROM beijing_line',

30, 60, false, false);

无查询结果就执行下面这几个试一试,也可能是两点之间就没有路径

select source from beijing_line;

select target from beijing_line;

select length from beijing_line;

4、查询所经过的所有点:

SELECT st_astext(the_geom) FROM pgr_dijkstra('

SELECT gid AS id,

source::integer,

target::integer,

length::double precision AS cost

FROM beijing_line',

30, 60, false, false) as di

join beijing_line pt

on di.id2 = pt.gid;

5、将路径写入一个几何文件内:

SELECT seq, id1 AS node, id2 AS edge, cost,the_geom into dijkstra_res FROM pgr_dijkstra('

SELECT gid AS id,

source::integer,

target::integer,

length::double precision AS cost

FROM beijing_line',

30, 60, false, false) as di

join beijing_line pt

on di.id2 = pt.gid;

经历过以上的步骤,如果一切顺利,应该能看到结果,这里不再截图。这时初步的准备工作已经完成。

参考博文如下():

1、基于pgrouting的路径规划之一

2、pgrouting进行路径规划之入门二(1和2是一个求最短路径博文系列,挺不错的可以看看,但可能是因为机器配置环境的不同,原封不动的可能无法运行别人的代码,一定搞懂原理,然后可对前人的代码改进)

3、Pgrouting运用基础

4、使用pgrouting2.0求最短路径