整体思路是用geoserver发布图层,图层为 sql,视图是利用存储过程分析出的路径结果,
shp导入数据库
路网一般为shp数据,如果有多个图层,建议利用arcgis或其他工具合并为一个图层,方便处理
数据库空间扩展
使用paAdmin3连接PostgreSQL,并执行以下语句,在新的空间数据库里添加空间扩展:
CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
使用shp2pgsql-gui.exe导入
添加路网数据,设置表名,并设置SRID为:4326
需要使用GBK编码,一定要勾选最下面一个选项。(在调用函数做测试的时候,遇到ST_Line_Locate_Point时报错:“line_locate_point : 1st arg isnt a line”,查资料解释说是函数第一个参数格式应该是LineString,我检查了一下我的v_res格式,是MultiLineString所以报错。(参考了https://www.jianshu.com/p/4b9d22406bce,在此谢谢作者大大,很详细))
表结构进行修改
1.修改表结构
--添加起点id
ALTER TABLE lproad ADD COLUMN source integer;
--添加终点id
ALTER TABLE lproad ADD COLUMN target integer;
--添加道路权重值
ALTER TABLE lproad ADD COLUMN length double precision
2.创建拓扑结构
--为lproad表创建拓扑布局,即为source和target字段赋值
SELECT pgr_createTopology('lproad ',0.000015, 'geom', 'gid');
3.创建索引
--为source和target字段创建索引
CREATE INDEX source_idx ON lproad ("source");
CREATE INDEX target_idx ON lproad ("target");
4.添加线段端点并给长度赋值
--添加线段端点坐标
ALTER TABLE lproad ADD COLUMN x1 double precision; --创建起点经度x1
ALTER TABLE lproad ADD COLUMN y1 double precision; --创建起点纬度y1
ALTER TABLE lproad ADD COLUMN x2 double precision; --创建起点经度x2
ALTER TABLE lproad ADD COLUMN y2 double precision; --创建起点经度y2
--给x1、y1、x2、y2赋值
UPDATE lproad SET x1 =ST_x(ST_PointN(geom, 1));
UPDATE lproad SET y1 =ST_y(ST_PointN(geom, 1));
UPDATE lproad SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));
UPDATE lproad SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));
--为length赋值
update lproad set length =st_length(geom);
--为lproad 表添加reverse_cost字段并用length的值赋值
ALTER TABLE lproad ADD COLUMN reverse_cost double precision;
UPDATE lproad SET reverse_cost =length;
测试数据,如果执行下面的语句有结果,则表示数据预处理成功
--通过起点号、终点号查询最短路径
--source为线表起点字段名称
--target为线表终点字段名称
--起点终点前后顺序无固定要求
--length为长度字段,也可以使用自己的评价体系
--1、9为测试使用起点号\终点号
--zy表名
--id1经过节点号
--id2经过路网线的gid
SELECT seq, id1 AS node, id2 AS edge, cost FROM pgr_dijkstra('
SELECT gid AS id,
source::integer,
target::integer,
length::double precision AS cost
FROM lproad ',
1, 4, false, false);
创建最短路径函数
--执行这个函数,最新的
CREATE OR REPLACE FUNCTION "public"."pgr_fromatoline6"(IN "tbl" varchar, IN "x1" float8, IN "y1" float8, IN "x2" float8, IN "y2" float8, OUT "seq" int4, OUT "gid" int4, OUT "name" text, OUT "heading" float8, OUT "cost" float8, OUT "geom" "public"."geometry")
RETURNS SETOF "pg_catalog"."record" AS $BODY$
DECLARE
sql text;
rec record;
source integer;
target integer;
point integer;
BEGIN
-- 查询距离出发点最近的道路节点
EXECUTE 'SELECT id::integer FROM line6_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;
source := rec.id;
-- 查询距离目的地最近的道路节点
EXECUTE 'SELECT id::integer FROM line6_vertices_pgr
ORDER BY the_geom <-> ST_GeometryFromText(''POINT('
|| x2 || ' ' || y2 || ')'',4326) LIMIT 1' INTO rec;
target := rec.id;
-- 最短路径查询
seq := 0;
sql := 'SELECT gid, geom, 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;
-- cost := rec.cost;
geom := rec.geom;
RETURN NEXT;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE STRICT
COST 100
ROWS 1000
数据发布
数据准备完成后,就需要用GeoServer来进行发布:
启动GeoServer,在浏览器中输入,http://localhost:8080/geoserver/web/,登录到GeoServer。
1.创建工作区
2.添加数据存储
配置数据库连接
3.添加图层
选择“配置新的SQL视图”,输入SQL语句:
SELECT ST_MakeLine(route.geom) FROM (SELECT * FROM pgr_fromAtoe('line2', %x1%, %y1%, %x2%, %y2%)ORDER BY seq) AS route
验证的正则表达式:直接设置为空
类型:LingString
SRID:4326
点击保存后,填入SRS,并自动计算范围
结果展示
1、wms方式加载
var viewparams = [
'x1:' + startCoord[0], 'y1:' + startCoord[1],
'x2:' + destCoord[0], 'y2:' + destCoord[1]
];
params.viewparams = viewparams.join(';');
result = new ol.layer.Image({
source: new ol.source.ImageWMS({
url: ServerUrl + '/geoserver/Routing/wms',
params: params
})
});
map.addLayer(result);
2、wfs方式加载
var vectorSource = new ol.source.Vector({
format: new ol.format.GeoJSON(),
url: function (extent) {
return ServerUrl + '/geoserver/wfs?service=WFS&' +
'version=1.1.0&request=GetFeature&typename=Routing:lproute&' +
'outputFormat=application/json&srsname=EPSG:4326&' +
'bbox=' + extent.join(',') + ',EPSG:4326&' +
'viewparams=' + viewparams.join(';');
},
strategy: ol.loadingstrategy.bbox
});
var vector = new ol.layer.Vector({
source: vectorSource,
});
map.addLayer(vector);
3、直接请求服务
var wfsurl = ServerUrl + '/geoserver/wfs?service=WFS&' +
'version=1.1.0&request=GetFeature&typename=Routing:lproute&' +
'outputFormat=application/json&srsname=EPSG:4326&' +
'viewparams=' + viewparams.join(';');
$.ajax({
url: wfsurl,
success: function (result) {
console.log(result);
}
});
4、前端页面完整demo
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>室内路径规划</title>
<link href="./ol.css" rel="stylesheet" />
<style>
#id {
width:1000px;
height:1000px;
}
</style>
<script src="./ol.js"></script>
</head>
<body>
<div id="map"></div>
<script>
var startCoord = [114.2348, 30.5563]
var destCoord = [114.2351, 30.5561]
var roadLayer = new ol.layer.Tile({
source: new ol.source.TileWMS({
url: 'http://192.168.1.99:8090/geoserver/pipegis/wms',
params: { 'LAYERS': 'pipegis:lproad', 'TILED': true },
serverType: 'geoserver'
})
})
var roadLayer2 = new ol.layer.Tile({
source: new ol.source.TileWMS({
url: 'http://192.168.1.99:8090/geoserver/pipegis/wms',
params: { 'LAYERS': 'pipegis:gd_vertices_pgr', 'TILED': true },
serverType: 'geoserver'
})
})
var map = new ol.Map({
target: document.getElementById("map"),
layers: [
roadLayer
],
view: new ol.View({
center: [114.23488, 30.55626],
projection: 'EPSG:4326',
zoom: 20
})
});
var params = {
LAYERS: 'pipegis:eee',
FORMAT: 'image/png',
};
var viewparams = [
'x1:' + startCoord[0], 'y1:' + startCoord[1],
'x2:' + destCoord[0], 'y2:' + destCoord[1]
//'x1:' + 12952117.2529, 'y1:' + 4836395.5717,
//'x2:' + 12945377.2585, 'y2:' + 4827305.7549
];
console.log(viewparams);
params.viewparams = viewparams.join(';');
result = new ol.layer.Image({
source: new ol.source.ImageWMS(
{
url: 'http://192.168.1.99:8090/geoserver/pipegis/wms',
params: params
})
});
console.info(result);
map.addLayer(result);
</script>
</body>
</html>
效果如图:
这是我自己开发的一个关于答题的两个小工具,感兴趣的可以了解下。打开微信扫描一下下面的二维码即可体验。 (也可以微信搜索 “ PK答题王 ” “科目一驾照”) 需要源码的可以联系我 qq:337882150