今天将上一次路径绘制的坑（上次绘制的是子路径端点的连线）给填了，本文中我将实现一个输入起点和终点经纬度，输出完整路径（包含子路径端点间的真实弯曲情况，完美贴合真实路径）的函数，能够计算准确的路径长度。
经过一番sql的洗礼我终于能够获取完整的规划路径了：
完整路径，完美贴合
路径端点连线，不完美贴合
并且我们将路径规划做成一个Sql函数，要规划路线的话只需要调用函数即可。
select whz_getPathBeta(106.7267,26.5690,106.8046,26.56904,1000);
废话不多说，上sql。
首先是生成路径的sql，参数填写起点终点经纬度和poi搜索范围。函数中首先将用户的起点终点更新入表中，然后获取路径段的数目，最后获取完整的路径图形。
-- 路网表daolu_simp
-- POI表pointsOfInterest2
CREATE OR REPLACE FUNCTION whz_getPathBeta(x1 float8, y1 float8, x2 float8, y2 float8, roadWithin numeric) RETURNS setof geometry AS
$$
declare poiId1 integer;
declare poiId2 integer;
declare segCount integer;
begin
poiId1=whz_updatePoi(x1,y1,roadWithin);
poiId2=whz_updatePoi(x2,y2,roadWithin);
select count(*) into segCount from pgr_withPoints(
'SELECT gid as id, source, target, cost_len as cost, rcost_len as reverse_cost FROM daolu_simp ORDER BY gid',
'SELECT pid as pid, edge_id, fraction, side from pointsOfInterest2',
-poiId1, -poiId2);
return query SELECT whz_getSegGeom(wp.seq,wp.node::integer,wp.edge::integer, segCount,-poiId1,-poiId2) as the_geom FROM pgr_withPoints(
'SELECT gid as id, source, target, cost_len as cost, rcost_len as reverse_cost FROM daolu_simp ORDER BY gid',
'SELECT pid as pid, edge_id, fraction, side from pointsOfInterest2',
-poiId1, -poiId2) as wp;
end;
$$
LANGUAGE plpgsql;
;
然后是往里面插入POI（起点、终点、途径点）的sql:
-- POI表pointsOfInterest2
-- 指定SRID=4326
-- 指定改点在路段的两侧
CREATE OR REPLACE FUNCTION whz_updatePoi(xo float8, yo float8, roadWithin numeric) RETURNS integer AS
$$
declare idRet numeric;
begin
INSERT INTO pointsOfInterest2(x, y, edge_id, side, fraction, the_geom, newPoint)
(SELECT xo, yo,gid,'b',ST_LineLocatePoint(geom, concat('SRID=4326;POINT(',xo,' ',yo,')')::geometry) as fraction,
st_makePoint(xo,yo),
ST_LineInterpolatePoint(geom, ST_LineLocatePoint(geom, concat('SRID=4326;POINT(',xo,' ',yo,')')::geometry))
FROM daolu_simp
WHERE ST_DWithin(geom, concat('SRID=4326;POINT(',xo,' ',yo,')')::geometry, roadWithin)
order by geom <-> concat('SRID=4326;POINT(',xo,' ',yo,')')::geometry
limit 1)
RETURNING pid into idRet;
return idRet;
end;
$$
LANGUAGE plpgsql;
;
最后是获取每一段子路径的完整路径的sql：
-- POI表pointsofinterest2
-- 路网节点表daolu_simp_vertices_pgr
-- 路网表daolu_simp
-- 本方法试用于起点终点都在poi，且一起点一终点
CREATE OR REPLACE FUNCTION whz_getSegGeom(myseq integer, mynode integer, myedge integer, node_num integer,poiId1 integer,poiId2 integer) RETURNS geometry AS
$$
declare geomRet geometry;
declare startFraction float8;
declare endFraction float8;
declare edgeidForPoi integer;
declare pointTmp geometry;
declare nodeTmp integer;
declare edgeTmp geometry;
begin
IF(mynode<0 AND myseq=1)THEN
SELECT node into nodeTmp FROM pgr_withPoints(
'SELECT gid as id, source, target, cost_len as cost, rcost_len as reverse_cost FROM daolu_simp ORDER BY gid',
'SELECT pid as pid, edge_id, fraction, side from pointsOfInterest2',
poiId1, poiId2) as wp1
where wp1.seq= 2;
select pointsofinterest2.fraction into endFraction
from pointsofinterest2
where pointsofinterest2.pid=-mynode
limit 1;
select pointsofinterest2.edge_id into edgeidForPoi
from pointsofinterest2
where pointsofinterest2.pid=-mynode
limit 1;
select the_geom into pointTmp
from daolu_simp_vertices_pgr
where daolu_simp_vertices_pgr.id= nodeTmp
limit 1;
select geom into edgeTmp
from daolu_simp
where daolu_simp.gid=edgeidForPoi
limit 1;
startFraction=st_linelocatepoint(edgeTmp, pointTmp);
if(startFraction>=endFraction) THEN
geomRet=ST_LineSubstring(edgeTmp, endFraction,startFraction);
ELSE geomRet=ST_LineSubstring(edgeTmp, startFraction,endFraction);
end if;
ELSIF(mynode<0 AND myseq=node_num)THEN
SELECT node into nodeTmp FROM pgr_withPoints(
'SELECT gid as id, source, target, cost_len as cost, rcost_len as reverse_cost FROM daolu_simp ORDER BY gid',
'SELECT pid as pid, edge_id, fraction, side from pointsOfInterest2',
poiId1, poiId2) as wp2
where wp2.seq= myseq-1;
select edge_id into edgeidForPoi
from pointsofinterest2
where pointsofinterest2.pid=-mynode
limit 1;
select fraction into endFraction
from pointsofinterest2
where pointsofinterest2.pid=-mynode
limit 1;
select the_geom into pointTmp
from daolu_simp_vertices_pgr
where daolu_simp_vertices_pgr.id= nodeTmp
limit 1;
select geom into edgeTmp
from daolu_simp
where daolu_simp.gid=edgeidForPoi
limit 1;
startFraction=st_linelocatepoint(edgeTmp, pointTmp);
if(startFraction>=endFraction) THEN
geomRet=ST_LineSubstring(edgeTmp, endFraction,startFraction);
ELSE geomRet=ST_LineSubstring(edgeTmp, startFraction,endFraction);
end if;
ELSIF(mynode>0 AND myseq<node_num-1)then
select geom into geomRet
from daolu_simp
where daolu_simp.gid=myedge;
END IF;
return geomRet;
end;
$$
LANGUAGE plpgsql;
注意，这个sql里面的表名和相关参数是写死的，实际使用的时候需要根据情况调整。
- 实际应用
例如可以借此实现Cesium的路径规划。在Web端的业务中，使用AJAX请求后端（例如GeoServer），后端再使用Mybatis或者Sql视图（Sql视图在GeoServer中配置），调用数据库中写好的该路径规划函数，函数将返回路径的几何图形，后端再将这些几何图形返回给前端进行渲染。
在倾斜摄影上渲染路径规划结果
