??本文內(nèi)容默認(rèn)已在PostGIS中添加了PgRouting擴(kuò)展模塊帖渠。
1. 數(shù)據(jù)準(zhǔn)備
??路徑分析功能是針對(duì)路網(wǎng)圖層進(jìn)行的空間分析應(yīng)用思币，在將shp文件導(dǎo)入數(shù)據(jù)庫(kù)之前诉濒，需要對(duì)其進(jìn)行拓?fù)錂z驗(yàn)钞诡，主要是道路的自相交以及路段之間的道路節(jié)點(diǎn)的相離的檢驗(yàn)镐捧。可以利用ArcGIS對(duì)文件進(jìn)行拓?fù)涞臉?gòu)建和檢驗(yàn)臭增，保證數(shù)據(jù)的準(zhǔn)確性懂酱。
??利用PostGIS自帶的工具將文件入庫(kù)。如下圖所示誊抛，1是將編碼方式設(shè)置為GBK列牺，一般Shapfile文件的編碼方式均為GBK，若選用其他編碼方式拗窃，可能會(huì)出現(xiàn)亂碼瞎领。2是對(duì)線要素進(jìn)行簡(jiǎn)化，Pgrouting不支持對(duì)多線的路徑計(jì)算随夸。
設(shè)定空間參考系統(tǒng)標(biāo)識(shí)（SRID）常用的3857或者4326九默，見下圖。
2. 最短路徑功能
??要生成路徑宾毒，首先要生成合法的拓?fù)渫招蕖Ｍㄟ^(guò)創(chuàng)建拓?fù)潢P(guān)系，可以完成最短路徑查詢函數(shù)的編寫。
2.1 構(gòu)建拓?fù)潢P(guān)系：pgr_createTopology
??這里創(chuàng)建的是路段道路節(jié)點(diǎn)與弧段之間的關(guān)系乙各，通過(guò)節(jié)點(diǎn)間可達(dá)性可以完成路徑的導(dǎo)航墨礁。
1、 添加起止點(diǎn)耳峦，存儲(chǔ)線段的首尾編號(hào)
ALTER TABLE prjroad ADD COLUMN source integer;
ALTER TABLE prjroad ADD COLUMN target integer;
2恩静、添加道路權(quán)重值
ALTER TABLE prjroad ADD COLUMN length double precision;
3、創(chuàng)建拓?fù)涠卓溃删€段的首尾編號(hào)
SELECT pgr_createTopology(prjroad,0.00001, 'geom', 'gid');
//表名驶乾、容差、線段列名循签、gid
第一步默認(rèn)創(chuàng)建起止點(diǎn)的索引轻掩，如果沒有創(chuàng)建，可以執(zhí)行以下sql創(chuàng)建
CREATE INDEX source_idx ON prjroad ("source");
CREATE INDEX target_idx ON prjroad ("target");
4懦底、為權(quán)重賦值，這里將路段的長(zhǎng)度賦值給權(quán)重值
UPDATE prjroad SET length =st_length(geom);
5罕扎、回程成本設(shè)置聚唐，則可支持回程
ALTER TABLE prjroad ADD COLUMN reverse_cost double precision;
UPDATE prjroad SET reverse_cost = length;
2.2 驗(yàn)證可行性
pgr_dijkstra函數(shù)詳見：
http://docs.pgrouting.org/2.2/en/src/dijkstra/doc/pgr_dijkstra.html#pgr-dijkstra
pgr_dijkstra(
text sql, __用于計(jì)算最佳路徑的數(shù)據(jù)來(lái)源，用SOL表示
integer source, __規(guī)劃路徑的起點(diǎn)
integer target, __規(guī)劃路徑的終點(diǎn)
Boolean directed, __ if the graph is directed 有向圖
Boolean has_rcost __if true 需要添加reverse_cost計(jì)算回程路徑
)
查詢節(jié)點(diǎn)2到節(jié)點(diǎn)36之間的最短路徑腔召，寫入表dijkstra_res中杆查。
SELECT seq, id1 AS node, id2 AS edge, cost,geom into dijkstra_res FROM pgr_dijkstra('
SELECT gid AS id,
source::integer,
target::integer,
length::double precision AS cost
FROM prjroad',
2, 36, false, false) as di
join prjroad pt
on di.id2 = pt.gid;
將表導(dǎo)出页滚，加載到qgis中缴挖，可看到最短路徑如圖2.2：
2.3 功能函數(shù)fromAtoB
1、若使用A*算法焰雕，則需要知道起止點(diǎn)的坐標(biāo)值浊仆，可以為prjroad表添加字段并賦值
ALTER TABLE prjroad ADD COLUMN x1 double precision;
ALTER TABLE prjroad ADD COLUMN y1 double precision;
ALTER TABLE prjroad ADD COLUMN x2 double precision;
ALTER TABLE prjroad ADD COLUMN y2 double precision;
UPDATE prjroad SET x1 =ST_x(ST_PointN(geom, 1));
UPDATE prjroad SET y1 =ST_y(ST_PointN(geom, 1));
UPDATE prjroad SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));
UPDATE prjroad SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));
2客峭、創(chuàng)建函數(shù)
//create function
--
--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 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
-- 查詢距離出發(fā)點(diǎn)最近的道路節(jié)點(diǎn)
EXECUTE 'SELECT id::integer FROM line_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',3857) LIMIT 1' INTO rec;
source := rec.id;
-- 查詢距離目的地最近的道路節(jié)點(diǎn)
EXECUTE 'SELECT id::integer FROM line_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x2 || ' ' || y2 || ')'',3857) LIMIT 1' INTO rec;
target := rec.id;
-- 最短路徑查詢
seq := 0;
sql := 'SELECT gid, geom, cost, source, target,
ST_Reverse(geom) AS flip_geom FROM ' ||
'pgr_dijkstra(''SELECT gid as id, source::int, target::int, '
//這里可以省略坐標(biāo)值 || '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;
cost := rec.cost;
geom := rec.geom;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
坐標(biāo)系要保持一致性。
3. 路徑分析功能實(shí)現(xiàn)
3.1 發(fā)布服務(wù)
在geoserver中發(fā)布服務(wù)
數(shù)據(jù)存儲(chǔ)——添加新的數(shù)據(jù)存儲(chǔ)——PostGIS
輸入相關(guān)信息抡柿，點(diǎn)擊保存
如圖3.2舔琅，選擇“配置新的SQL視圖”
1、 輸入視圖名稱
2洲劣、 輸入SQL語(yǔ)句
SELECT ST_MakeLine(route.geom) geom FROM (
SELECT geom FROM pgr_fromAtoB_3('prjroad', %x1%, %y1%, %x2%, %y2%)
ORDER BY seq) AS route
3备蚓、 從SQL猜想的參數(shù)
4、 獲取到參數(shù)囱稽，輸入默認(rèn)值和正則表達(dá)式
5郊尝、 刷新，得到SQL語(yǔ)句返回的結(jié)果
6战惊、 設(shè)定類型和SRID
7流昏、 保存
8、 計(jì)算邊框，保存
3.2 功能實(shí)現(xiàn)
傳入起止點(diǎn)坐標(biāo)横缔，計(jì)算最短路徑
核心代碼如下：
var viewparams = ['x1:' + cordx, 'y1:' + cordy, 'x2:' + cordx1, 'y2:' + cordy1];
viewparams = viewparams.join(';');
testLayer1 = new OpenLayers.Layer.Vector("result3", {
request: new OpenLayers.Request.GET({
url: "http://192.168.15.97:8080/hgisserver/network/wfs?service=WFS&version=1.1.0&request=GetFeature" + "&typeName=network:newview&outputformat=JSON" + "&viewparams=" + viewparams,
success: function (req) {
var jsonStr = req.responseText;
var jsonFormat = new OpenLayers.Format.GeoJSON();
var featureArray = jsonFormat.read(jsonStr);
testLayer1.addFeatures(featureArray);
testLayer1.redraw();
}
})
});
testLayer1.style = {
strokeWidth: 3,
strokeOpacity: 1,
strokeColor: "#FF0000"
}
map.addLayer(testLayer1); //添加圖層
