grouting(2.4)+postgis 寻求三条最短路径方法
来源:互联网 发布:展锋股票指标源码 编辑:程序博客网 时间:2024/05/16 11:38
其他创建数据及拓扑请参考网上他人已有教程
CREATE OR REPLACE FUNCTION pgr_fromAtoBKSP(
IN tbl varchar,
IN x1 double precision,
IN y1 double precision,
IN x2 double precision,
IN y2 double precision,
OUT seq integer,
OUT gid integer,
OUT name text,
OUT heading double precision,
OUT cost double precision,
OUT geom geometry,
OUT pathid integer
)
RETURNS SETOF record
AS $BODY$
DECLARE
sql text;
rec record;
source integer;
target integer;
point integer;
BEGIN
-- 查询距离出发点最近的道路节点
EXECUTE 'SELECT id::integer FROM road_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;
source := rec.id;
-- 查询距离目的地最近的道路节点
EXECUTE 'SELECT id::integer FROM road_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;
target := rec.id;
-- 最短路径查询
seq := 0;
sql := 'SELECT gid, geom, name, cost, source, target,path_id,
ST_Reverse(geom) AS flip_geom FROM ' ||
'pgr_KSP(''SELECT gid as id, source::int, target::int, '
|| 'road_length::float AS cost,x1,y1,x2,y2 FROM '
|| quote_ident(tbl) || ''', '
|| source || ', ' || target
|| ' ,3,false,false ), '
|| quote_ident(tbl) || ' WHERE edge = gid ORDER BY seq';
-- Remember start point
point := source;
FOR rec IN EXECUTE sql
LOOP
-- Flip geometry (if required)
IF ( point != rec.source ) THEN
rec.geom := rec.flip_geom;
point := rec.source;
ELSE
point := rec.target;
END IF;
-- Calculate heading (simplified)
EXECUTE 'SELECT degrees( ST_Azimuth(
ST_StartPoint(''' || rec.geom::text || '''),
ST_EndPoint(''' || rec.geom::text || ''') ) )'
INTO heading;
-- Return record
seq := seq + 1;
gid := rec.gid;
name := rec.name;
cost := rec.cost;
geom := rec.geom;
pathid :=rec.path_id;
RETURN NEXT; END LOOP;
RETURN; END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT;
create table ksp as SELECT * FROM pgr_fromAtoBKSP('road',x1,y1,x2,y2);
CREATE OR REPLACE FUNCTION pgr_fromAtoBKSP(
IN tbl varchar,
IN x1 double precision,
IN y1 double precision,
IN x2 double precision,
IN y2 double precision,
OUT seq integer,
OUT gid integer,
OUT name text,
OUT heading double precision,
OUT cost double precision,
OUT geom geometry,
OUT pathid integer
)
RETURNS SETOF record
AS $BODY$
DECLARE
sql text;
rec record;
source integer;
target integer;
point integer;
BEGIN
-- 查询距离出发点最近的道路节点
EXECUTE 'SELECT id::integer FROM road_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;
source := rec.id;
-- 查询距离目的地最近的道路节点
EXECUTE 'SELECT id::integer FROM road_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;
target := rec.id;
-- 最短路径查询
seq := 0;
sql := 'SELECT gid, geom, name, cost, source, target,path_id,
ST_Reverse(geom) AS flip_geom FROM ' ||
'pgr_KSP(''SELECT gid as id, source::int, target::int, '
|| 'road_length::float AS cost,x1,y1,x2,y2 FROM '
|| quote_ident(tbl) || ''', '
|| source || ', ' || target
|| ' ,3,false,false ), '
|| quote_ident(tbl) || ' WHERE edge = gid ORDER BY seq';
-- Remember start point
point := source;
FOR rec IN EXECUTE sql
LOOP
-- Flip geometry (if required)
IF ( point != rec.source ) THEN
rec.geom := rec.flip_geom;
point := rec.source;
ELSE
point := rec.target;
END IF;
-- Calculate heading (simplified)
EXECUTE 'SELECT degrees( ST_Azimuth(
ST_StartPoint(''' || rec.geom::text || '''),
ST_EndPoint(''' || rec.geom::text || ''') ) )'
INTO heading;
-- Return record
seq := seq + 1;
gid := rec.gid;
name := rec.name;
cost := rec.cost;
geom := rec.geom;
pathid :=rec.path_id;
RETURN NEXT; END LOOP;
RETURN; END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT;
create table ksp as SELECT * FROM pgr_fromAtoBKSP('road',x1,y1,x2,y2);
阅读全文
0 0
- grouting(2.4)+postgis 寻求三条最短路径方法
- postgis最短路径查询
- PostGIS 操作geometry方法
- postgis
- PostGis
- postgis
- Postgis
- PostGIS
- postGIS
- Floyd算法-寻求最短路径
- shape数据导入postgis的方法
- 寻求下面算法的破解方法
- 我在寻求工作治理的方法
- java解析XML文件时找不到路径,寻求解决!
- PostGIS安装与SHP数据入库(两种方法)
- Shapefile导入PostGIS的两种方法介绍及比较
- 寻求有效的TrojanWin32木马查杀方法
- C#返回byte[]给dll的方法,寻求帮助
- java for循环习题
- ORB-SLAM(二)性能
- JSP指令
- Unable to get browser (Selenium::WebDriver::Error::NoSuchWindowError)
- 特征值和特征向量的实际意义
- grouting(2.4)+postgis 寻求三条最短路径方法
- 职业化和个人能力培养
- ORB-SLAM(三)地图初始化
- Codeforce Round #422(Div 2) A
- 0. Rxjava系列目录及说明
- Java入门学习-学会使用日期函数和日期的计算方法,理解什么是时间戳
- nio 非阻塞式数据传输,服务端
- ORB-SLAM(四)追踪
- VR GunJack 学习制作