pgrouting两个经纬度点之间最优路径规划

部分参考于：http://blog.csdn.net/longshengguoji/article/details/46350355

1、数据导入

关于安装配置以及路网数据导入数据库，这里不再说明，这里我导入的表为newroad部分参考

 

注意：在导入的选项卡中，将编码设置为GBK，并勾选“生成简单的几何图形而不是多个几何图形”，确保导入的路网数据的类型为Geometry(LineString)。

2、创建拓扑

ALTER TABLE newroad ADD COLUMN source integer;		--添加起点id字段
ALTER TABLE newroad ADD COLUMN target integer;		--添加终点id字段
ALTER TABLE newroad ADD COLUMN length double precision;	--添加道路权重字段

SELECT pgr_createTopology('newroad',0.00001, 'geom', 'gid'); 	--为source和target赋值，并创建拓扑点表newroad_vertices_pgr

update newroad set length =st_length(geom);			--为length赋值

CREATE INDEX source_idx ON newroad("source");		--为source字段创建索引
CREATE INDEX target_idx ON newroad("target");			--为target字段创建索引

ALTER TABLE newroad ADD COLUMN x1 double precision;		--创建起点经度x1
ALTER TABLE newroad ADD COLUMN y1 double precision;		--创建起点纬度y1
ALTER TABLE newroad ADD COLUMN x2 double precision;		--创建起点经度x2
ALTER TABLE newroad ADD COLUMN y2 double precision;		--创建起点经度y2
            
UPDATE newroad SET x1 =ST_x(ST_PointN(geom, 1));	
UPDATE newroad SET y1 =ST_y(ST_PointN(geom, 1));	
UPDATE newroad SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));	
UPDATE newroad SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));	--给x1、y1、x2、y2赋值

3、查询

最终的查询函数为：

--  
--DROP FUNCTION pgr_fromAtoB(varchar, double precision, 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 newroad_vertices_pgr   
            ORDER BY the_geom <-> ST_GeometryFromText(''POINT('   
            || x1 || ' ' || y1 || ')'',900913) LIMIT 1' INTO rec;  
    source := rec.id;  
      
    -- 查询距离目的地最近的道路节点  
    EXECUTE 'SELECT id::integer FROM newroad_vertices_pgr   
            ORDER BY the_geom <-> ST_GeometryFromText(''POINT('   
            || x2 || ' ' || y2 || ')'',900913) LIMIT 1' INTO rec;  
    target := rec.id;  
  
    -- 最短路径查询   
        seq := 0;  
        sql := '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';  
  
  
    -- 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;  

4、geoserver发布图层
a.发布路网基础图
先发布数据库中的路网表newroad，再发布newroad_vertices_pgr，再将两图层叠加到一起，形成路径规划的基础图。

b.发布路径规划图
新建图层，选择数据存储为postGIS的存储，再选择“配置新的SQL视图”，在SQl语句中添加以下语句，创建视图

SELECT ST_MakeLine(route.geom) FROM (
   SELECT geom FROM pgr_fromAtoB('newroad',%x1%, %y1%, %x2%, %y2%)
   ORDER BY seq) AS route

然后添加参数x1、y1、x2、y2，将参数的正则表达式验证都填写为^-?[\d.]+$
并在属性中，将属性的类型改为LineString，坐标系id改为4326

5、展示路径分析结果

在Openlayers中，我们通过TileWMS的方式来加载路网基础和路径规划

路网基础服务：

 var roadLayer = new ol.layer.Tile({
          source: new ol.source.TileWMS({
            url: 'http://localhost:8080/geoserver/test/wms',
            params: {'LAYERS': 'test:newroad', 'TILED': true},
            serverType: 'geoserver'
          })
        })
  map.addLayer(roadLayer);

路径规划服务：（动态参数）

var routeLayer = new ol.layer.Tile({
		  source: new ol.source.TileWMS({
				url: 'http://localhost:8080/geoserver/test/wms',
				params: {'LAYERS': 'test:newroad_route','TILED': true,'viewparams':'x1:'+coords1[0]+
				';y1:'+coords1[1]+';x2:'+coords2[0]+';y2:'+coords2[1]},
				serverType: 'geoserver'
		  })
	})
    map.addLayer(routeLayer);

最终结果：