前面文章介绍了如何利用postgresql创建空间数据库,建立空间索引和进行路径规划。但是在真实的场景中用户进行路径规划的时候都是基于经纬度数据进行路径规划的,因为用户根本不会知道道路上节点的ID。因此文本讲述如何查询任意两点间的最短路径。
函数名定义为:pgr_fromAtoB
参数设置分别为:
输入为数据库表名,起点和终点的经纬度坐标
输出为:路段序号,gid号,道路名,消耗及道路集合体。
IN tbl varchar,--数据库表名
IN x1 double precision,--起点x坐标
IN y1 double precision,--起点y坐标
IN x2 double precision,--终点x坐标
IN y2 double precision,--终点y坐标
OUT seq integer,--
OUT gid integer,
OUT name text,
OUT heading double precision,
OUT cost double precision,
OUT geom geometry
二、计算距离起点和终点距离最近的道路节点,
可以使用的函数为
SELECT id::integer FROM minidata_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',900913) LIMIT 1
三、然后根据检索到的节点再调用pgrouting的最短路径查询函数进行计算。
可以使用的函数为:
SELECT gid,geom,name,cost,source,target,
ST_Reverse(geom) AS flip_geom FROM ' ||
'pgr_bdAstar(''SELECT gid as id,source::int,target::int,'
|| 'length::float AS cost,x1,y1,x2,y2 FROM '
|| quote_ident(tbl) || ''','
|| source || ',' || target
|| ',false,false),'
|| quote_ident(tbl) || ' WHERE id2 = gid ORDER BY seq
-- --DROP FUNCTION pgr_fromAtoB(varchar,double precision,-- double precision,double precision); CREATE OR REPLACE FUNCTION pgr_fromAtoB( 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 ) RETURNS SETOF record AS $BODY$ DECLARE sql text; rec record; source integer; target integer; point integer; BEGIN -- 查询距离出发点最近的道路节点 EXECUTE 'SELECT id::integer FROM minidata_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' || x1 || ' ' || y1 || ')'',900913) LIMIT 1' INTO rec; source := rec.id; -- 查询距离目的地最近的道路节点 EXECUTE 'SELECT id::integer FROM minidata_vertices_pgr ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' || x2 || ' ' || y2 || ')'',900913) LIMIT 1' INTO rec; target := rec.id; -- 最短路径查询 seq := 0; sql := 'SELECT gid,ST_Reverse(geom) AS flip_geom FROM ' || 'pgr_bdAstar(''SELECT gid as id,' || 'length::float AS cost,y2 FROM ' || quote_ident(tbl) || ''',' || source || ',' || target || ',' || 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; RETURN NEXT; END LOOP; RETURN; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE STRICT;