vue整合openlayers.zip

CREATE OR REPLACE FUNCTION "public"."pgr_shortpath"("tbl" varchar, "startx" float8, "starty" float8, "endx" float8, "endy" float8) RETURNS "public"."geometry" AS $BODY$ DECLARE v_startLine geometry;--离起点最近的线 v_endLine geometry;--离终点最近的线 v_startPoint geometry;--在v_startLine上距离起点最近的点 v_endPoint geometry;--在v_endLine上距离终点最近的点 v_startLine_pre geometry;--起点到v_startPoint的线 v_endLine_next geometry;--终点到v_endPoint的线 v_startPoint_pre geometry;--起点 v_endPoint_pre geometry;--终点 v_startTarget INTEGER;--距离起点最近线的目标节点 v_startSource INTEGER;--距离起点最近线的源节点 v_endSource INTEGER;--距离终点最近线的源节点 v_endTarget INTEGER;--距离起点最近线的目标节点 v_start_x1 DOUBLE PRECISION; v_start_y1 DOUBLE PRECISION; v_start_x2 DOUBLE PRECISION; v_start_y2 DOUBLE PRECISION; v_end_x1 DOUBLE PRECISION; v_end_y1 DOUBLE PRECISION; v_end_x2 DOUBLE PRECISION; v_end_y2 DOUBLE PRECISION; v_startLine_Source geometry; v_startLine_Target geometry; v_endLine_Source geometry; v_endLine_Target geometry; v_res geometry;--最短路径分析结果 v_res_a geometry; v_res_b geometry; v_res_c geometry; v_res_d geometry; v_shPath geometry;--最终结果 BEGIN--查询离0.01度(大约1000米)范围内起点的最近线 EXECUTE'select geom, source, target,x1,y1,x2,y2 from ' || tbl || ' where ST_DWithin(geom,ST_Geometryfromtext(''point(' || startx || ' ' || starty || ')'',4326),0.01) order by ST_Distance(geom,ST_GeometryFromText(''point(' || startx || ' ' || starty || ')'',4326)) limit 1' INTO v_startLine, v_startSource, v_startTarget, v_start_x1, v_start_y1, v_start_x2, v_start_y2; --查询离终点0.01度(大约1000米)范围内的最近线 EXECUTE'select geom, source, target,x1,y1,x2,y2 from ' || tbl || ' where ST_DWithin(geom,ST_Geometryfromtext(''point(' || endx || ' ' || endy || ')'',4326),0.01) order by ST_Distance(geom,ST_GeometryFromText(''point(' || endx || ' ' || endy || ')'',4326)) limit 1' INTO v_endLine, v_endSource, v_endTarget, v_end_x1, v_end_y1, v_end_x2, v_end_y2; --如果没找到最近的线，就返回null IF ( v_startLine IS NULL ) OR ( v_endLine IS NULL ) THEN RETURN NULL; END IF; --创建查询的起点和终点 SELECT ST_SetSRID ( ST_MakePoint ( startx, starty ), 4326 ) INTO v_startPoint_pre; SELECT ST_SetSRID ( ST_MakePoint ( endx, endy ), 4326 ) INTO v_endPoint_pre; --查询线上最接近某点的点（此点在线上） SELECT ST_ClosestPoint ( v_startLine, v_startPoint_pre ) INTO v_startPoint; SELECT ST_ClosestPoint ( v_endLine, v_endPoint_pre ) INTO v_endPoint; --根据长度占比，生成前后两部分线段 SELECT ST_MakeLine ( v_startPoint, ST_SetSRID ( ST_MakePoint ( v_start_x1, v_start_y1 ), 4326 ) ) INTO v_startLine_Source; SELECT ST_MakeLine ( v_startPoint, ST_SetSRID ( ST_MakePoint ( v_start_x2, v_start_y2 ), 4326 ) ) INTO v_startLine_Target; SELECT ST_MakeLine ( v_endPoint, ST_SetSRID ( ST_MakePoint ( v_end_x1, v_end_y1 ), 4326 ) ) INTO v_endLine_Source; SELECT ST_MakeLine ( v_endPoint, ST_SetSRID ( ST_MakePoint ( v_end_x2, v_end_y2 ), 4326 ) ) INTO v_endLine_Target; --计算最短路径（开始的起点/终点 --- 结束的起点/终点） SELECT pgr_shortpath_node ( tbl, v_startSource, v_endSource, v_startLine_Source, v_endLine_Source ) INTO v_res_a; SELECT pgr_shortpath_node ( tbl, v_startTarget, v_endSource, v_startLine_Target, v_endLine_Source ) INTO v_res_b; SELECT pgr_shortpath_node ( tbl, v_startSource, v_endTarget, v_startLine_Source, v_endLine_Target ) INTO v_res_c; SELECT pgr_shortpath_node ( tbl, v_startTarget, v_endTarget, v_startLine_Target, v_endLine_Target ) INTO v_res_d; v_res = v_res_a; IF ((v_res_b is not null) AND (v_res is null OR ST_Length ( v_res ) > ST_Length ( v_res_b ) )) THEN v_res = v_res_b; END IF; --raise notice'----v_res1 % ',st_astext(v_res); IF ( v_res_c is not null AND (v_res is null OR ST_Length ( v_res ) > ST_Length ( v_res_c ))) THEN v_res = v_res_c; END IF; --raise notice'----v_res2 % ',st_astext(v_res); IF ( v_res_d is not null AND (v_res is null OR ST_Length ( v_res ) > ST_Length ( v_res_d ))) THEN v_res = v_res_d; END IF; --raise notice'----v_res3 % ',st_astext(v_res); --创建查询起点和终点到最近的线之间的线 SELECT ST_MakeLine ( v_startPoint, v_startPoint_pre ) INTO v_startLine_pre; SELECT ST_MakeLine ( v_endPoint, v_endPoint_pre ) INTO v_endLine_next; SELECT st_union ( ARRAY [ v_endLine_next, v_res, v_startLine_pre ] ) INTO v_shPath; RETURN v_shPath; END; $BODY$ LANGUAGE plpgsql VOLATILE STRICT COST 100