pgr_fromAtoB_Point

最新推荐文章于 2023-05-20 10:23:32 发布

jsboy123

最新推荐文章于 2023-05-20 10:23:32 发布

阅读量753

点赞数

本文链接：https://blog.csdn.net/jsboy123/article/details/50318955

版权

本文介绍了一个使用PGRouting实现的从A点到B点的路径查询函数pgr_fromAtoB_Point。该函数通过查找最近节点并利用Dijkstra算法计算最短路径，返回路径上的几何信息和井名等数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--
DROP FUNCTION pgr_fromAtoB_Point(varchar, varchar,double precision, double precision,
double precision, double precision);

CREATE OR REPLACE FUNCTION pgr_fromAtoB_Point(
IN tbl varchar,
IN tbl2 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 wellName text,
OUT wellCode text
)
RETURNS SETOF record AS
$BODY$
DECLARE
sql text;
rec record;
source integer;
target integer;
point integer;
rec2 record;
seq integer;
gid integer;
--OUT name text,
heading double precision;
--OUT cost double precision,
geom geometry;

BEGIN
-- Find nearest node
EXECUTE 'SELECT id::integer FROM wellline_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;
source := rec.id;

EXECUTE 'SELECT id::integer FROM wellline_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;
target := rec.id;

-- Shortest path query (TODO: limit extent by BBOX)
seq := 0;
sql := 'SELECT gid, geom, source, target,
ST_Reverse(geom) AS flip_geom FROM ' ||
'pgr_dijkstra(''SELECT gid as id, source::int, target::int, '
|| 'length::float AS cost FROM '
|| quote_ident(tbl) || ''', '
|| source || ', ' || target
|| ' , false, false), '
|| quote_ident(tbl) || ' WHERE id2 = 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;
FOR rec2 IN EXECUTE 'select wellcode from '|| quote_ident(tbl2) || ' t where st_DWithin(t.geom, ''' || geom::text || ''',0.00001)'
LOOP
wellcode :=rec2.wellcode;
return next;
END LOOP;
--RETURN NEXT;
END LOOP;

RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;