PostGIS 路线规划

❤️通过SQL存储过程，利用PostGIS实现路线规划❤️

pgRouting扩展了PostgreSQL/PostGIS地理空间数据库，以提供地理空间路由功能。

1、环境配置

--添加postgis扩展
CREATE EXTENSION postgis;
--添加 pgrouting扩展
CREATE EXTENSION pgrouting;
--创建拓扑扩展 
CREATE EXTENSION postgis_topology;
--添加fuzzystrmatch扩展	fuzzystrmatch模块提供多个函数来判断字符串之间的相似性和距离。
CREATE EXTENSION fuzzystrmatch;
--创建平面坐标
CREATE EXTENSION address_standard

2、导入shp文件

2.1、打开PostGis Shapefile插件

2.2、连接数据库

数据库地址可以本地数据库地址也可以是远程数据库地址

2.3、添加并导入矢量数据

注意：

1. 一定要设置数据的坐标系（可以不是该数据原坐标系），否则导入到数据库中数据是无坐标系的。
2. 数据量数据本地存放的路径名称不能含有中文或特殊符号的，否则导入的时候会报错。

2.4、查看数据

--查询字段类型
select st_srid(geom) from daolu; 
--查询数据
select st_asewkt(geom) from daolu;

3、执行初始化命令

--添加起点
alter table daolu add column source integer;
--添加终点
alter table daolu add column target integer;
--正向道路长度
alter table daolu add column length double precision;
--创建拓扑图表，存放道路所有节点，并给source和target赋值，同时将路网相交点的空间数据放入daolu_vertices_pgr表
select pgr_createtopology('daolu', 0.00001, 'geom', 'gid');
--给source和target添加索引增加查询速度
create index source_idx on daolu("source");
create index targer_idx on daolu("target");
--给每一条道路赋值权重
update daolu set length = st_length(geom);
--反向道路长度
alter table daolu add column reverse_cost double precision;
--将正向道路的权重赋值给反向道路
update daolu set reverse_cost = length;

4、创建存储函数

推荐方法二，距离更远

4.1、方法一：

create function _myShortPath(startx float, starty float,endx float,endy float,costfile varchar)
returns  geometry as
$body$
declare
	--离起点最近的线
	v_startLine geometry;
	--离终点最近的线
	v_endLine geometry;
	--距离起点最近线的终点
	v_startTarget integer;
	--距离终点最近线的起点
	v_endSource integer;
	
	--在v_startLine上距离起点最近的点
	v_statpoint geometry;
	--在v_endLine上距离终点最近的点
	v_endpoint geometry;
	--最短路径分析结果
	v_res geometry;

	--v_statpoint在v_res上的百分比
	v_perStart float;
	--v_endpoint在v_res上的百分比
	v_perEnd float;
	--最终结果
	v_shPath geometry;
begin

	--查询离起点最近的线
	select geom ,target into v_startLine ,v_startTarget from  daolu where
	ST_DWithin(geom,ST_Geometryfromtext('point('|| startx ||' ' || starty ||')', 4326),0.015)
	order by ST_Distance(geom,ST_GeometryFromText('point('|| startx ||' '|| starty ||')', 4326))  limit 1;

	--查询离终点最近的线
	select geom,source into v_endLine,v_endSource from  daolu where
	ST_DWithin(geom,ST_Geometryfromtext('point('|| endx || ' ' || endy ||')', 4326),0.015)
	order by ST_Distance(geom,ST_GeometryFromText('point('|| endx ||' ' || endy ||')', 4326))  limit 1;

	--如果没找到最近的线，就返回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;


        --最短路径
	SELECT st_linemerge(st_union(b.geom)) into v_res
	FROM pgr_kdijkstraPath(
	'SELECT gid as id, source, target, ' || costfile ||' as cost FROM daolu',
	v_startTarget, array[v_endSource], true, false
	) a,
	daolu b
	WHERE a.id3=b.gid
	GROUP by id1
	ORDER by id1;

	--如果找不到最短路径，就返回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;

	--截取v_res
	SELECT ST_Line_SubString(v_res,v_perStart, v_perEnd) into v_shPath;

	return v_shPath;
end;
$body$
LANGUAGE plpgsql VOLATILE STRICT;

例子

--例子：
select st_astext(_myshortpath(117.370023799659, 26.3370107998085,117.385395500302, 26.3350246004051,'length'));

4.2、方法二：

create function pgr_road(tbl character varying, startx double precision, starty double precision, endx double precision, endy double precision, OUT linetype integer, OUT geom geometry) returns SETOF record
    strict
    language plpgsql
as
$$
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;

    startpoint geometry;
    endpoint geometry;

    v_shPath1 geometry;--一次结果
    v_shPath2 geometry;--二次结果
    star_line geometry; --起点到最近点的线
    end_line geometry; --终点到最近点的线
    geoARR geometry[];

    geoType integer[];



    ii integer;

begin

    --查询离起点最近的线
    --4326坐标系
    --找起点15米范围内的最近线

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

                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('||         startx ||' ' || starty||')'',4326),0.015)
                             order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326))  limit 1'

                            into v_startLine, v_startSource ,v_startTarget;

raise notice '%',  v_startSource;
raise notice '%', v_startTarget;

    --查询离终点最近的线
    --找终点15米范围内的最近线

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

                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',4326),0.015)

                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326))  limit 1'

                            into v_endLine, v_endSource,v_endTarget;
raise notice '%',  v_endSource;
raise notice '%', v_endTarget;


    --如果没找到最近的线，就返回null

raise notice 'v_startLine %', v_startLine;
raise notice 'v_endLine %', v_endLine;
    if (v_startLine is null) or (v_endLine is null) then

        return;

    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;

raise notice 'v_statpoint %', v_statpoint;
raise notice 'v_endpoint %', 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 ;

raise notice 'v_res %', 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 ;

raise notice 'v_res_b %', 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 ;

raise notice 'v_res_c %', 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 ;

raise notice 'v_res_d %', 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;

    end if;



    --将v_res,v_startLine,v_endLine进行拼接

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

    --return v_res;

    select  ST_LineLocatePoint(v_res, v_statpoint) into v_perStart;

    select  ST_LineLocatePoint(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_shPath1;

    --接下来进行
    --找线的端点

    select ST_SetSRID( ST_MakePoint(startx , starty),4326 )into startpoint;
    select ST_SetSRID( ST_MakePoint(endx , endy),4326 )into endpoint;
    select ST_MakeLine( v_statpoint,startpoint) into star_line;
    select ST_MakeLine( v_endpoint,endpoint) into end_line;

    geoARR :=array[end_line,v_shPath1,star_line];
    geoType :=array[1,2,1];

    FOR ii IN 1..3 Loop

    lineType:=geoType[ii];
    geom:=geoARR[ii];
    raise notice '%', '返回数据';
    return next;
    end loop;
 return;

end;

$$;

alter function pgr_road(varchar, double precision, double precision, double precision, double precision, out integer, out geometry) owner to postgres;

例子

--例子：
select pgr_road('daolu',117.129149399825, 26.3420375002305,117.209062000311, 26.356417999691);

4.3、方法三：

--DROP FUNCTION public.pgr_fromatob(tbl varchar,startx float,starty float,endx float,endy float);
 
CREATE OR REPLACE FUNCTION "public"."pgr_fromatob"(tbl varchar, startx float8, starty float8, endx float8, endy float8)
    RETURNS "public"."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_Geomfromtext(''POINT('|| startx ||' ' || starty||')'',4326),15)
                            order by ST_Distance(geom,ST_GeomFromText(''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_Geomfromtext(''point('|| endx || ' ' || endy ||')'',4326),15)
                            order by ST_Distance(geom,ST_GeomFromText(''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_Geomfromtext('point('|| startx ||' ' || starty ||')',4326)) into v_statpoint; 
    raise notice 'v_statpoint %',  v_statpoint;
 
    -- 查询终点和终点最近的先的点
    select  ST_ClosestPoint(v_endLine, ST_GeomFromText('point('|| endx ||' ' || endy ||')',4326)) into v_endpoint; 
    raise notice 'v_endpoint %',  v_endpoint;
 
    -- ST_Distance 
    --从开始的起点到结束的起点最短路径 
    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
    'FROM pgr_kdijkstraPath ( 
    ''SELECT gid as id, source::integer, target::integer, length::double precision 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::integer, target::integer, length::double precision 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::integer, target::integer, length::double precision 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::integer, target::integer, length::double precision 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;
 
    raise notice 'v_res %',  v_res;
    raise notice 'v_startLine %',  v_startLine;
    raise notice 'v_endLine %', v_endLine;
    
    --如果没找到，返回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_LineLocatePoint(v_res, v_statpoint) into v_perStart; 
   select  ST_LineLocatePoint(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  COST 100
;
 
ALTER FUNCTION "public"."pgr_fromatob"(tbl varchar, startx float8, starty float8, endx float8, endy float8) OWNER TO "postgres";

--参考地址：https://www.jianshu.com/p/eaea02568815

例子

--例子：
select pgr_fromatob('daolu',117.129149399825, 26.3420375002305,117.209062000311, 26.356417999691);
select pgr_fromatob('daolu',117.229149399825, 26.3420375002305,117.209062000311, 26.356417999691);

5、查询最优路线

select _myshortpath(117.165833333, 26.3536111111,108.916666667, 34.2166666667,'length');

-- select st_srid(geom) from daolu;  查询字段类型

其他

--EPSG:4326 (WGS84)转EPSG:3857 (Pseudo-Mercator)
--参考网址：https://blog.csdn.net/October_zhang/article/details/103529797
select  st_astext(st_transform(geom)) from daolu;

select st_astext(geom) ,target from daolu where ST_DWithin(geom,ST_Geometryfromtext('point(13068466.05 3040575.7748)',3857),15)
order by ST_Distance(geom,ST_GeometryFromText('point(13067264.4537 3040464.6936)',3857)) limit 1;

6、计算距离

-- 计算两点之间的距离，结果是米
select
ST_Distance(
    ST_SetSRID(ST_MakePoint(117.129149399825, 26.3420375002305),4326)::geography,
    ST_SetSRID(ST_MakePoint(117.209062000311, 26.356417999691),4326)::geography
);

-- 计算线的长度，结果是米
select ST_Length(Geography(ST_GeomFromText('LINESTRING(117.229149399825 26.3420375002305,117.209062000311 26.356417999691)')));


-- 判断点是否与面面相交
select ST_Contains(St_Astext(ST_Buffer(geography(st_geomfromtext('MULTILINESTRING((117.048605999567 26.4415170003094,117.049979999977 26.441910999593,117.051406000087 26.44157400024,117.052468999645 26.4402419999684,117.053297000057 26.4385019997675,117.054616999775 26.4384199995833,117.056773000274 26.4380529997462,117.057517000409 26.4378880002311,117.057711100187 26.43778440013,117.058206400005 26.4375315998035,117.058470100115 26.4374001000347,117.058664100068 26.4372974999797,117.058860199938 26.4371856000355))')),1000.00)),
    st_astext(geography(st_geomfromtext('POINT(117.048605999567 26.4415170003094)')))) as result

https://blog.csdn.net/October_zhang/article/details/103529797

❤️❤️参考地址 ❤️❤️
❤️❤️ PostGIS中的常用函数❤️❤️
❤️ ❤️postgis常用操作❤️ ❤️
❤️ ❤️PostGIS官方教程汇总目录❤️ ❤️
❤️ ❤️六大路径规划算法❤️ ❤️