PostGIS 路线规划

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

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

文章目录

- - - - 1、环境配置
      - 2、导入shp文件
      - 2.1、打开PostGis Shapefile插件
        2.2、连接数据库
        2.3、添加并导入矢量数据
        2.4、查看数据
      - 3、执行初始化命令
      - 4、创建存储函数
      - 4.1、方法一：
        4.2、方法二：
        4.3、方法三：
      - 5、查询最优路线
      - 6、计算距离

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、添加并导入矢量数据

在这里插入图片描述

注意：

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

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 whereST_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 whereST_DWithin(geom,ST_Geometryfromtext('point('|| endx || ' ' || endy ||')', 4326),0.015)order by ST_Distance(geom,ST_GeometryFromText('point('|| endx ||' ' || endy ||')', 4326))  limit 1;--如果没找到最近的线，就返回nullif (v_startLine is null) or (v_endLine is null) thenreturn 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_resFROM pgr_kdijkstraPath('SELECT gid as id, source, target, ' || costfile ||' as cost FROM daolu',v_startTarget, array[v_endSource], true, false) a,daolu bWHERE a.id3=b.gidGROUP by id1ORDER by id1;--如果找不到最短路径，就返回nullif(v_res is null) thenreturn 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_resSELECT 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 recordstrictlanguage plpgsql
as
$$
declarev_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;--如果没找到最近的线，就返回nullraise notice 'v_startLine %', v_startLine;
raise notice 'v_endLine %', v_endLine;if (v_startLine is null) or (v_endLine is null) thenreturn;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 || ' bWHERE a.id3=b.gidGROUP by id1ORDER 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 || ' bWHERE a.id3=b.gidGROUP by id1ORDER 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 || ' bWHERE a.id3=b.gidGROUP by id1ORDER 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 || ' bWHERE a.id3=b.gidGROUP by id1ORDER by id1' into v_res_d ;raise notice 'v_res_d %', v_res_d;if(ST_Length(v_res) > ST_Length(v_res_b)) thenv_res = v_res_b;end if;if(ST_Length(v_res) > ST_Length(v_res_c)) thenv_res = v_res_c;end if;if(ST_Length(v_res) > ST_Length(v_res_d)) thenv_res = v_res_d;end if;--如果找不到最短路径，就返回nullif(v_res is null) thenreturn;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) thentempnode =  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 LooplineType:=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$ 
declarev_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; --如果没找到，返回nullif (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)) thenv_res = v_res_b;end if;if(ST_Length(v_res) > ST_Length(v_res_c)) thenv_res = v_res_c;end if;if(ST_Length(v_res) > ST_Length(v_res_d)) thenv_res = v_res_d;end if;raise notice 'v_res %',  v_res;raise notice 'v_startLine %',  v_startLine;raise notice 'v_endLine %', v_endLine;--如果没找到，返回nullif(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 resulthttps://blog.csdn.net/October_zhang/article/details/103529797