PostGIS 结合Openlayers以及Geoserver实现最短路径分析（二）

guzicheng1990

已于 2022-06-13 16:59:14 修改

阅读量2.3k

点赞数 2

分类专栏： PostGIS 文章标签： gis

于 2019-10-12 17:50:28 首次发布

本文链接：https://blog.csdn.net/guzicheng1990/article/details/102524953

版权

PostGIS 专栏收录该内容

30 篇文章 12 订阅

订阅专栏

前文讲述了怎么用ArcMap制作了测试数据，并导入了PostGIS，接下来我们需要结合PgRouting插件，对入库的数据再进行一下处理。

1、在pgAdmin中，执行下面的sql语句

 --添加起点字段source
ALTER TABLE zy ADD COLUMN source integer;

--添加终点字段target
ALTER table zy add column target integer;

--添加道路权重值字段
ALTER TABLE zy ADD COLUMN length double precision;

--建立拓扑关系，这里会为source和target字段赋值（表明，阈值，要素字段名，主键id）
SELECT pgr_createTopology('zy', 0.00001, 'geom', 'gid');
###这里在创建完拓扑，source和target赋值了以后，可以给上一篇文章中创建的点图层预留的Id字段赋值了（参看第5步）

--为source和target字段创建索引（加快查询速度）
CREATE INDEX source_idx ON zy("source");
CREATE INDEX target_idx ON zy("target");

--添加线段端点坐标
ALTER TABLE ZY ADD COLUMN x1 double precision;        --创建起点经度x1
ALTER TABLE ZY ADD COLUMN y1 double precision;        --创建起点纬度y1
ALTER TABLE ZY ADD COLUMN x2 double precision;        --创建起点经度x2
ALTER TABLE ZY ADD COLUMN y2 double precision;        --创建起点经度y2
--给x1、y1、x2、y2赋值
UPDATE ZY SET x1 =ST_x(ST_PointN(geom, 1));    
UPDATE ZY SET y1 =ST_y(ST_PointN(geom, 1));    
UPDATE ZY SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));    
UPDATE ZY SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));

--为length赋值（以长度作为权重）
UPDATE zy SET length =st_length(geom);

--将长度值赋给reverse_cost，作为路线选择标准（消耗）
ALTER TABLE zy ADD COLUMN reverse_cost double precision;
UPDATE zy SET reverse_cost = st_length(geom);

2、测试数据，如果执行下面的语句有结果，则表示数据预处理成功

--通过起点号、终点号查询最短路径
--source为线表起点字段名称
--target为线表终点字段名称
--起点终点前后顺序无固定要求
--length为长度字段，也可以使用自己的评价体系
--1、9为测试使用起点号\终点号
--zy表名
--id1经过节点号
--id2经过路网线的gid
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM zy',
                1, 4, false, false);

3、创建最短路径查询函数（核心）

-- Function: pgr_shortestpath(character varying, double precision, double precision, double precision, double precision)
  -- DROP FUNCTION pgr_shortestpath(character varying, double precision, double precision, double precision, double precision);

CREATE OR REPLACE FUNCTION pgr_shortestpath(

    tbl character varying,
    startx double precision,
    starty double precision,
    endx double precision,
    endy double precision)
  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 
    --查询离起点最近的线 
    --3857坐标系
    --找起点15米范围内的最近线 
    execute 'select geom, source, target  from ' ||tbl|| 
                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('||startx ||' ' || starty||')'',3857),15)
                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',3857))  limit 1' 
                            into v_startLine, v_startSource ,v_startTarget;  
    --查询离终点最近的线 
    --找终点15米范围内的最近线 
    execute 'select geom, source, target from ' ||tbl|| 
                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',3857),15)
                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',3857))  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 ||')',3857)) into v_statpoint;  
    select  ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',3857)) into v_endpoint;  
   -- ST_Distance  （下面是否可以直接用pgr_dijkstra函数，不做端点判断，待研究）
    --从开始的起点到结束的起点最短路径 
    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; 
    raise notice '%',  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; 
    raise notice '%',  v_shPath; 
    return v_shPath;  
end;  
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT
  COST 100;
ALTER FUNCTION pgr_shortestpath(character varying, double precision, double precision, double precision, double precision)
  OWNER TO postgres;

4、测试

在调用函数做测试的时候，遇到ST_Line_Locate_Point时报错：“line_locate_point : 1st arg isnt a line”，查资料解释说是函数第一个参数格式应该是LineString，我检查了一下我的v_res格式，是MultiLineString所以报错。（参考了PostGIS--线路合并方法比较 - 简书，在此谢谢作者大大，很详细）

知道原因以后怎么解决呢？还是个头疼的问题。我把查询出来的MultiLineString放到ArcMap中，图形化的显示出来，发现这两段竟然是不连通的两端，最后分析应该是数据不连通导致的，两点间查不到结果，没有最短路径。

在ArcMap中利用网络分析工具(networkAnalyse tool)试了一下也不行，果然是这原因，后来换了两个连通的点查询结果就没问题，所以函数没有问题，结果控制一下就行。

5、在创建拓扑时，有时候会遇到数据量非常大的情况，一次执行会非常消耗内存，这里建议分批创建

##推荐分批拓扑



select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid < 50000')

select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid >= 50000 and gid < 100000')

select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid >= 100000 and gid < 150000')

select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid >= 150000 and gid < 200000')

select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid >= 200000 and gid < 250000')

select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid >= 250000 and gid < 300000')

select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid >= 300000 and gid < 350000')

select pgr_createTopology('zy',0.1,source:='source',id:='gid',target:='target',the_geom:='geom',rows_where:='gid >= 350000 and gid < 450000')

6、至此数据预处理结束了，接下来在下一篇中介绍Geoserver中发布数据并调用

guzicheng1990

关注

2
点赞
踩
9

收藏

觉得还不错? 一键收藏
7
评论
PostGIS 结合Openlayers以及Geoserver实现最短路径分析（二）

前文讲述了怎么用ArcMap制作了测试数据，并导入了PostGIS，接下来我们需要结合PgRouting插件，对入库的数据再进行一下处理。1、在pgAdmin中，执行下面的sql语句 --添加起点字段sourceALTER TABLE zy ADD COLUMN source integer;--添加终点字段targetALTER table zy add column tar......
复制链接

扫一扫