Wechat: yu389741| Email: gisdqy@163.com

sf2gis@163.com

2015年7月1日

# 2 方法：pgRouting。

Dijkstra：从起点开始计算到各点的最短路径，逐个取出。找到最短路径。

http://baike.baidu.com/view/1712262.htm?fromtitle=Dijkstra%E7%AE%97%E6%B3%95&fromid=215612&type=syn

A*:与Dijkstra类似，从起点开始计算与各点的最短路径和终点的估计最短路径之和，逐个取出。找到最短路径。效率较高。

## 2.1 方法：安装

### 2.1.2添加postgis和pgRouting扩展

Create Extension Postgis;

Create Extension pgRouting;

## 2.3 方法：Dijkstra计算最短路径，pgr_dijkstra()。

dijstra算法需要提供每个元素的id,source,target,cost；路径起点，终点。

select seq,id1 asnode,id2 as edge,cost into dijkstra from pgr_dijkstra(

‘select gid asid,source,target,cost::double precision from road_noded’,1,8,false,false

);

create tabledijkstraResult as

select seq,id1 asnode ,id2 as edge,a.cost,the_geom from pgr_dijkstra(

‘select gid asid,source,target,cost::double precision from road_noded’,

1,8,false,false) a

left join

on

a.id2=b.gid;

## 2.4 方法：A*计算最短路径

A*算法需要使用每个线段的起点x,y和终点x,y坐标。

select seq,id1 asnode,id2 as edge,cost into astar from pgr_astar(

);

selectthe_geom,id,id1,id2,di.cost into astar2 from pgr_astar(

) as di joinroad_noded pt on di.id2=pt.gid;

# 3  方法：发布WMS路径查询服务

## 3.1 生成最短路径查询函数：生成sql函数pgr_framAtoB()。

— Function:pgr_fromatob(character varying, double precision, double precision, doubleprecision, double precision)

— DROP FUNCTIONpgr_fromatob(character varying, double precision, double precision, doubleprecision, double precision);

CREATE OR REPLACEFUNCTION pgr_fromatob(

IN tbl character varying,

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 cost double precision,

OUT geom geometry)

RETURNS SETOF record AS

\$BODY\$

DECLARE

sql    text;

rec    record;

source    integer;

target    integer;

point     integer;

BEGIN

— Find nearest node

ORDER BY the_geom <->ST_GeometryFromText(”POINT(‘

|| x1 || ‘ ‘ || y1 ||’)”,4326) LIMIT 1′ INTO rec;

source := rec.id;

ORDER BY the_geom <->ST_GeometryFromText(”POINT(‘

|| x2 || ‘ ‘ || y2 ||’)”,4326) LIMIT 1′ INTO rec;

target := rec.id;

— Shortest path query (TODO: limit extentby BBOX)

seq := 0;

sql := ‘SELECT gid, the_geom, name,a.cost, source, target,

ST_Reverse(the_geom) ASflip_geom FROM ‘ ||

‘pgr_dijkstra(”SELECTgid as id, source::int, target::int, ‘

||’cost::float AS cost FROM ‘

||quote_ident(tbl) || ”’, ‘

|| source ||’, ‘ || target

|| ‘,false, false) a, ‘

||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.the_geom := rec.flip_geom;

point := rec.source;

ELSE

point := rec.target;

END IF;

EXECUTE ‘SELECT degrees( ST_Azimuth(

ST_StartPoint(”’ ||rec.the_geom::text || ”’),

ST_EndPoint(”’ ||rec.the_geom::text || ”’) ) )’

— Return record

seq     := seq + 1;

gid     :=rec.gid;

name    := rec.name;

cost    := rec.cost;

geom    := rec.the_geom;

RETURN NEXT;

END LOOP;

RETURN;

END;

\$BODY\$

LANGUAGE plpgsql VOLATILE STRICT

COST 100

ROWS 1000;

ALTER FUNCTIONpgr_fromatob(character varying, double precision, double precision, doubleprecision, double precision)

OWNER TO postgres;

## 3.2 发布WMS的postgis sqlview

### 3.2.1设置sqlview的sql及相关属性。

SELECT ST_MakeLine(route.geom)FROM (

SELECT geom FROM pgr_fromAtoB(‘road_noded’,%x1%, %y1%, %x2%, %y2%

) ORDER BY seq) AS route

### 3.2.2测试：使用layer preview显示openlayers，添加参数查看。

http://localhost:8080/geoserver/pgrouting/wms?service=WMS&version=1.1.0&request=GetMap&layers=pgrouting:pgrouting&styles=&bbox=-10.0,-10.0,10.0,10.0&width=768&height=768&srs=EPSG:4326&format=application/openlayers&viewparams=x1:-3;y1:1.6;x2:-2.7;y2:1.7

# 4 方法：OpenLayers中使用最短路径

## 4.1 构建WMS的viewparams参数。

//pgrouting test

var params = {

LAYERS: ‘pgrouting:pgrouting’,

FORMAT: ‘image/png’,

CRS:’EPSG:4326′

};

var viewparams = [

‘x1:’ +startPoint.getGeometry().getCoordinates()[0], ‘y1:’ +startPoint.getGeometry().getCoordinates()[1],

‘x2:’ +destPoint.getGeometry().getCoordinates()[0], ‘y2:’ +destPoint.getGeometry().getCoordinates()[1]

];

params.viewparams = viewparams.join(‘;’);

## 4.2 示例:设置起止点，自动生成最短路径。

<!DOCTYPE HTML PUBLIC”-//W3C//DTD HTML 4.0 Transitional//EN”>

<html>

<style>

.map2{height:400px;width:100%;}

</style>

<script src=”D:/Data/test/openlayers/jquery-1.11.1.min.js”></script>

<scriptsrc=”D:/ProgramData/OpenLayers_SDK_v3.6.0/apidoc/scripts/bootstrap.min.js”></script>

<scriptsrc=”D:/ProgramData/OpenLayers_SDK_v3.6.0/build/ol.js”></script>

<title>testOpenLayers</title>

<body>

<div id=”map2″class=”map2″>Map</div>

<script type=”text/javascript”>

/************************************/

//base map

map=new ol.Map

({

target:’map2′,

layers:[new ol.layer.Image({source:newol.source.ImageWMS({

url:’http://localhost:8080/geoserver/testShp/wms’,

params: {‘LAYERS’: ‘states’},

serverType:’geoserver’,

projection:ol.proj.get(‘EPSG:4326’),

})

})],

view:new ol.View

({

zoom:4,

projection: new ol.proj.get(‘EPSG:4326’),

center:[0,0]

})

});

var bounds=[-180,-10,10,60];

map.getView().fitExtent(bounds, map.getSize());

/************************************/

// The “start” and “destination” features.

var startPoint = new ol.Feature();

var destPoint = new ol.Feature();

startPoint.setGeometry(new ol.geom.Point([-3,1.6]));//([-3.8,0.67]));//([-3,1.6]));

destPoint.setGeometry(newol.geom.Point([-0.84,1.5]));//([-2.7,1.7]));

// The vector layer used to display the “start” and”destination” features.

var vectorLayer = new ol.layer.Vector({

source: new ol.source.Vector({

features: [startPoint, destPoint]

})

});

//pgrouting test

var params = {

LAYERS: ‘pgrouting:pgrouting’,

FORMAT: ‘image/png’,

CRS:’EPSG:4326′

};

var viewparams = [

‘x1:’ + startPoint.getGeometry().getCoordinates()[0],’y1:’ + startPoint.getGeometry().getCoordinates()[1],

‘x2:’ + destPoint.getGeometry().getCoordinates()[0], ‘y2:’+ destPoint.getGeometry().getCoordinates()[1]

];

params.viewparams = viewparams.join(‘;’);

result = new ol.layer.Image({

source: new ol.source.ImageWMS({

url:’http://localhost:8080/geoserver/pgrouting/wms’,

params: params

})

});

</script>

</body>

</html>