GeoServer+PostgreSQL+PostGIS+pgRouting实现最短路径查询

admin0条评论 106 次浏览

GeoServer+PostgreSQL+PostGIS+pgRouting实现最短路径查询

一、软件安装

GeoServer下载地址:

http://geoserver.org/download/

PostgreSQL下载地址:

https://www.postgresql.org/download/

paAdmin3下载地址:

https://www.pgadmin.org/download/pgadmin-3-windows/

PostGIS下载地址:

http://postgis.net/windows_downloads/

pgRouting已经包含在安装程序中。

所有下载程序如下:

 

安装过程不再详述。

二、数据制作

我使用的是ArcMap来绘制路网数据,也可以使用其它的GIS软件,但好像没有这么方便,地理坐标系使用GCS_WGS_1984,编号为:4326。

 

注意:所有线段连接的地方都需要断开,这样便于以后的分析。

三、数据处理

使用paAdmin3连接PostgreSQL,并执行以下语句,在新的空间数据库里添加空间扩展:

CREATE EXTENSION postgis;

CREATE EXTENSION pgrouting;

CREATE EXTENSION postgis_topology;

CREATE EXTENSION fuzzystrmatch;

CREATE EXTENSION postgis_tiger_geocoder;

CREATE EXTENSION address_standardizer;

制作完路网数据后,需要使用 来把路网数据导入到PostgreSQL中去。

 

点击“View connection details….”在弹出的窗口中填入PostgreSQL的账号和密码,以及Database。

连接成功后,需要设置一下“Optionns…”

 

需要使用GBK编码,并勾选最下面一个选项。

添加路网数据,并设置SRID为:4326

 

导入完成后,会在数据库中创建一个对应的表:

 

在查询表中分别执行下列SQL,对表结构进行修改:

1.修改表结构

–添加起点id

ALTER TABLE public.road_xblk ADD COLUMN source integer;

–添加终点id

ALTER TABLE public.road_xblk ADD COLUMN target integer;

–添加道路权重值

ALTER TABLE public.road_xblk ADD COLUMN length double precision

2.创建拓扑结构  

–为sampledata表创建拓扑布局,即为source和target字段赋值

SELECT pgr_createTopology(‘public.road_xblk’,0.0001, ‘geom’, ‘gid’);

3.创建索引

–为source和target字段创建索引

CREATE INDEX source_idx ON road_xblk(“source”);

CREATE INDEX target_idx ON road_xblk(“target”);

4.给长度赋值

–为length赋值

update road_xblk set length =st_length(geom);

–为road_xblk表添加reverse_cost字段并用length的值赋值

ALTER TABLE road_xblk ADD COLUMN reverse_cost double precision;

UPDATE road_xblk SET reverse_cost =length;

5.创建最短路径函数

—创建查询随意两点之前的最短路径的函数(新建查询-运行)

DROP FUNCTION pgr_fromAtoB(tbl varchar,startx float, starty float,endx float,endy float);

CREATE OR REPLACE function pgr_fromAtoB(tbl varchar,startx float, starty float,endx float,endy float)

returns geometry as

$body$

declare

v_startLine geometry;–离起点最近的线

v_endLine geometry;–离终点最近的线

v_startTarget integer;–距离起点最近线的终点

v_startSource integer;

v_endSource integer;–距离终点最近线的起点

v_endTarget integer;

v_statpoint geometry;–在v_startLine上距离起点最近的点

v_endpoint geometry;–在v_endLine上距离终点最近的点

v_res geometry;–最短路径分析结果

v_res_a geometry;

v_res_b geometry;

v_res_c geometry;

v_res_d geometry;

v_perStart float;–v_statpoint在v_res上的百分比

v_perEnd float;–v_endpoint在v_res上的百分比

v_shPath_se geometry;–开始到结束

v_shPath_es geometry;–结束到开始

v_shPath geometry;–最终结果

tempnode float;

begin

–查询离起点最近的线

execute ‘select geom, source, target from ‘ ||tbl||

‘ where ST_DWithin(geom,ST_Geometryfromtext(”point(‘|| startx ||’ ‘ || starty||’)”,4326),15)

order by ST_Distance(geom,ST_GeometryFromText(”point(‘|| startx ||’ ‘|| starty ||’)”,4326)) limit 1′

into v_startLine, v_startSource ,v_startTarget;

–查询离终点最近的线

execute ‘select geom, source, target from ‘ ||tbl||

‘ where ST_DWithin(geom,ST_Geometryfromtext(”point(‘|| endx || ‘ ‘ || endy ||’)”,4326),15)

order by ST_Distance(geom,ST_GeometryFromText(”point(‘|| endx ||’ ‘ || endy ||’)”,4326)) limit 1′

into v_endLine, v_endSource,v_endTarget;

–如果没找到最近的线,就返回null

if (v_startLine is null) or (v_endLine is null) then

return null;

end if ;

select ST_ClosestPoint(v_startLine, ST_Geometryfromtext(‘point(‘|| startx ||’ ‘ || starty ||’)’,4326)) into v_statpoint;

select ST_ClosestPoint(v_endLine, ST_GeometryFromText(‘point(‘|| endx ||’ ‘ || endy ||’)’,4326)) into v_endpoint;

— ST_Distance

–从开始的起点到结束的起点最短路径

execute ‘SELECT st_linemerge(st_union(b.geom)) ‘ ||

‘FROM pgr_kdijkstraPath(

”SELECT gid as id, source, target, length as cost FROM ‘ || tbl ||”’,’

||v_startSource || ‘, ‘ ||’array[‘||v_endSource||’] , false, false

) a, ‘

|| tbl || ‘ b

WHERE a.id3=b.gid

GROUP by id1

ORDER by id1’ into v_res ;

–从开始的终点到结束的起点最短路径

execute ‘SELECT st_linemerge(st_union(b.geom)) ‘ ||

‘FROM pgr_kdijkstraPath(

”SELECT gid as id, source, target, length as cost FROM ‘ || tbl ||”’,’

||v_startTarget || ‘, ‘ ||’array[‘||v_endSource||’] , false, false

) a, ‘

|| tbl || ‘ b

WHERE a.id3=b.gid

GROUP by id1

ORDER by id1’ into v_res_b ;

–从开始的起点到结束的终点最短路径

execute ‘SELECT st_linemerge(st_union(b.geom)) ‘ ||

‘FROM pgr_kdijkstraPath(

”SELECT gid as id, source, target, length as cost FROM ‘ || tbl ||”’,’

||v_startSource || ‘, ‘ ||’array[‘||v_endTarget||’] , false, false

) a, ‘

|| tbl || ‘ b

WHERE a.id3=b.gid

GROUP by id1

ORDER by id1’ into v_res_c ;

–从开始的终点到结束的终点最短路径

execute ‘SELECT st_linemerge(st_union(b.geom)) ‘ ||

‘FROM pgr_kdijkstraPath(

”SELECT gid as id, source, target, length as cost FROM ‘ || tbl ||”’,’

||v_startTarget || ‘, ‘ ||’array[‘||v_endTarget||’] , false, false

) a, ‘

|| tbl || ‘ b

WHERE a.id3=b.gid

GROUP by id1

ORDER by id1’ into v_res_d ;

if(ST_Length(v_res) > ST_Length(v_res_b)) then

v_res = v_res_b;

end if;

if(ST_Length(v_res) > ST_Length(v_res_c)) then

v_res = v_res_c;

end if;

if(ST_Length(v_res) > ST_Length(v_res_d)) then

v_res = v_res_d;

end if;

–如果找不到最短路径,就返回null

–if(v_res is null) then

— return null;

–end if;

–将v_res,v_startLine,v_endLine进行拼接

select st_linemerge(ST_Union(array[v_res,v_startLine,v_endLine])) into v_res;

select ST_Line_Locate_Point(v_res, v_statpoint) into v_perStart;

select ST_Line_Locate_Point(v_res, v_endpoint) into v_perEnd;

if(v_perStart > v_perEnd) then

tempnode = v_perStart;

v_perStart = v_perEnd;

v_perEnd = tempnode;

end if;

–截取v_res

SELECT ST_Line_SubString(v_res,v_perStart, v_perEnd) into v_shPath;

return v_shPath;

end;

$body$

LANGUAGE plpgsql VOLATILE STRICT;

 

四、数据发布

数据准备完成后,就需要用GeoServer来进行发布:

启动GeoServer,在浏览器中输入,http://localhost:8080/geoserver/web/,登录到GeoServer。

1.创建工作区

 

添加xblk名称的工作区。

2.添加数据存储:

 

填入对应的连接信息:

 

3.添加图层:

 

注意红框中的内容。

4.添加路径查询服务,添加图层,选择“配置新的SQL视图”:

 

视图名称:navigation

SQL语句:

SELECT * FROM pgr_fromAtoB(‘road_xblk’, %x1%, %y1%, %x2%, %y2%)

验证的正则表达式:^-?[\d.]+$

类型:LingString

SRID:4326

点击保存后,填入SRS,并自动计算范围:

 

五、结果展示

使用OpenLayer进行结果展示,代码请直接下载,结果如下:

 

OpenLayer展示代码下载

调用方法:http://localhost:8098/geoserver/LocalhostTest/ows?service=WFS&version=1.0.0&request=GetFeature&typeName=LocalhostTest:roadway&maxFeatures=50&outputFormat=application%2Fjson&viewparams=x1:113.30498456954956;y1:23.141277489870415;x2:113.3072590827942;y2:23.13703522379429

说明:这个最短路径的算法有一定的问题,在特定的条件下,查找的不一定是最短的路径,需要对数据进行再处理,或者对算法进行优化。