文章摘要
这篇文章介绍了PostgreSQL函数`pgr_shortest_river`的实现，该函数用于计算两点之间的最短路径，并返回结果。以下是文章的主要内容总结：
1. **函数功能**：
`pgr_shortest_river`函数接受起点和终点的坐标（`startx`, `starty`, `endx`, `endy`）作为输入，并通过PostgreSQL的几何操作计算两点之间的最短路径。
2. **核心操作**：
- 查询离起点最近的线段和对应的点位置。
- 查询离终点最近的线段和对应的点位置。
- 计算从起点到终点的最短路径，并获取路径上各点的几何信息、河流名称以及路径成本。
3. **实现步骤**：
- 使用PostgreSQL的几何函数（如`ST_DWithin`、`ST_ClosestPoint`、`ST_DistanceSphere`等）进行几何计算。
- 通过PostgreSQL的`pgr_dijkstra`算法计算最短路径。
- 将计算结果转换为GeoJSON格式，并返回路径、成本和河流名称。
4. **输出结果**：
函数返回`SETOF pg_catalog.record`，包含路径、河流名称和成本信息。
文章通过具体的SQL语句展示了函数的实现细节，并解释了其核心逻辑。
CREATE OR REPLACE FUNCTION “public”.”pgr_shortest_river”(IN “startx” float8, IN “starty” float8, IN “endx” float8, IN “endy” float8, OUT “river_name” varchar, OUT “v_shpath” varchar, OUT “cost” float8)
RETURNS SETOF “pg_catalog”.”record” AS $BODY$
declare
v_startLine geometry;–离起点最近的线
v_endLine geometry;–离终点最近的线
v_startTarget integer;–距离起点最近线的终点
v_endSource integer;–距离终点最近线的起点
v_statpoint geometry;–在v_startLine上距离起点最近的点
v_endpoint geometry;–在v_endLine上距离终点最近的点
v_res geometry;–最短路径分析结果
v_perStart float;–v_statpoint在v_res上的百分比
v_perEnd float;–v_endpoint在v_res上的百分比
v_rec record;
first_name varchar;
end_name varchar;
first_cost double precision;
end_cost double precision;
begin
–查询离起点最近的线
execute ‘select (st_dump(geom)).geom as geom,target as target,name from singleriver where
ST_DWithin(geom,ST_Geometryfromtext(”point(‘|| startx ||’ ‘ || starty||’)”),0.01)
order by ST_Distance(geom,ST_GeometryFromText(”point(‘|| startx ||’ ‘|| starty ||’)”)) limit 1′
into v_startLine ,v_startTarget,first_name;
raise notice ‘起点线段%’,v_startLine;
raise notice ‘起点位置%’,v_startTarget;
raise notice ‘河流名称%’,first_name;
–查询离终点最近的线
execute ‘select (st_dump(geom)).geom as geom,”source” as source,name from singleriver
where ST_DWithin(geom,ST_Geometryfromtext(”point(‘|| endx || ‘ ‘ || endy ||’)”),0.01)
order by ST_Distance(geom,ST_GeometryFromText(”point(‘|| endx ||’ ‘ || endy ||’)”)) limit 1′
into v_endLine,v_endSource,end_name;
–如果没找到最近的线，就返回null
if (v_startLine is null) or (v_endLine is null) then
return;
end if ;
select ST_ClosestPoint(v_startLine, ST_Geometryfromtext(‘point(‘|| startx ||’ ‘ || starty ||’)’)) into v_statpoint;
select ST_ClosestPoint(v_endLine, ST_GeometryFromText(‘point(‘|| endx ||’ ‘ || endy ||’)’)) into v_endpoint;
–计算距离起点最近线上的点在该线中的位置
select st_linelocatepoint(st_linemerge(v_startLine), v_statpoint) into v_perStart;
select st_linelocatepoint(st_linemerge(v_endLine), v_endpoint) into v_perEnd;
select st_distancesphere(v_statpoint,ST_PointN(ST_GeometryN(v_startLine,1), ST_NumPoints(ST_GeometryN(v_startLine,1)))) into first_cost;
select st_distancesphere(ST_PointN(ST_GeometryN(v_endLine,1),1),v_endpoint) into end_cost;
if (ST_Intersects(st_geomfromtext(‘point(‘|| startx ||’ ‘|| starty ||’) ‘), v_startLine) and ST_Intersects(st_geomfromtext(‘point(‘|| endx ||’ ‘|| endy ||’) ‘), v_startLine)) then
select st_distancesphere(v_statpoint, v_endpoint) into first_cost;
select st_linelocatepoint(st_linemerge(v_startLine), v_endpoint) into v_perEnd;
for v_rec in
select st_linesubstring(st_linemerge(v_startLine), v_perStart,v_perEnd) as point,COALESCE(end_name,’无名河流’) as name,end_cost as cost loop
v_shPath:=ST_AsGeoJSON(v_rec.point);
cost:=v_rec.cost;
river_name:=v_rec.name;
return next;
end loop;
return;
end if;
–最短路径
for v_rec in
(select st_linesubstring(st_linemerge(v_startLine),v_perStart,1) as point,COALESCE(first_name,’无名河流’) as name,first_cost as cost
union all
SELECT st_linemerge(b.geom) as point,COALESCE(b.name,’无名河流’) as name,st_length(geom, false) as cost
FROM pgr_dijkstra(
‘SELECT gid as id, source, target, st_length(geom, false) as cost FROM singleriver
where st_intersects(geom,st_buffer(st_linefromtext(”linestring(‘||startx||’ ‘ || starty ||’,’|| endx ||’ ‘ || endy ||’)”),0.05))’,
v_startTarget, v_endSource , false
) a, singleriver b
WHERE a.edge=b.gid
union all
select st_linesubstring(st_linemerge(v_endLine),0,v_perEnd) as point,COALESCE(end_name,’无名河流’) as name,end_cost as cost)
loop
v_shPath:=ST_AsGeoJSON(v_rec.point);
cost:=v_rec.cost;
river_name:=v_rec.name;
return next;
end loop;
end;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100
ROWS 1000