geoserver+postgis 最短路径规划

写在前面

使用到的软件版本信息

软件名称	版本号	功能
AutoCAD	2010	二维图形绘制
ArcGis	10.5	shpfile转换
PostgreSql	12	路径规划函数
PostGis	3.0	shpfile文件导入
Geoserver	2.16.2	图层展示
OpenLayers	4.6.5	效果展示

最终效果展示

完整资源

对应资源链接

AutoCAD二维制图

dwg二维图形绘制

底图绘制

路径绘制

注意：路径上的每个拐点都需要单独作为折点，不能绘制完整体路径后再单独打点

ArcGis矢量地图转换

创建文件夹连接

打开arcmap软件，选择右侧“目录”–“文件夹连接”，将CAD所在文件夹连接至arcmap中

dwg图形导入

shpfile导出

shpfile编辑

开启编辑


去除底图线，并保存关闭编辑

PostgreSql导入

PostGis插件导入shpfile

导入前，需要再PostgreSql中创建数据库，添加插件

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer; 

使用postgis工具导入shp文件

添加起止点以及拓扑关系

-----------------------------------------
--以起始点坐标为参数进行最短路径分析
--以route表作为实例-- route 为表名
-----------------------------------------
ALTER TABLE route ADD COLUMN source integer;--起点 
ALTER TABLE route ADD COLUMN target integer;--终点
ALTER TABLE route ADD COLUMN length double precision;--增加路线长度字段（根据长度设置权重）
UPDATE route SET length = ST_Length(geom);--计算路线长度
select pgr_createTopology('route', 0.0001, 'geom', 'gid');--创建拓扑

--添加起始点坐标x,y字段
ALTER TABLE route ADD COLUMN x1 double precision;
ALTER TABLE route ADD COLUMN y1 double precision;
ALTER TABLE route ADD COLUMN x2 double precision;
ALTER TABLE route ADD COLUMN y2 double precision;
--计算起始点坐标
UPDATE route SET x1 =ST_x(ST_PointN(geom, 1));
UPDATE route SET y1 =ST_y(ST_PointN(geom, 1));
UPDATE route SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));
UPDATE route SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));

创建最短路径存储函数

-----------------------------------------
--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,--起点x坐标
                IN y1 double precision,--起点y坐标
                IN x2 double precision,--终点x坐标
                IN y2 double precision,--终点y坐标
                OUT seq integer,--道路序号
                OUT gid integer,
                OUT node 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 '|| quote_ident(tbl) ||'_vertices_pgr 
            ORDER BY the_geom <-> ST_GeometryFromText(''POINT(' 
            || x1 || ' ' || y1 || ')'',4326) LIMIT 1' INTO rec;
    source := rec.id;
    
    -- 查询距离目的地最近的道路节点
    EXECUTE 'SELECT id::integer FROM '|| quote_ident(tbl) ||'_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, node, cost, source, target, 
                ST_Reverse(geom) AS flip_geom FROM ' ||
                        'pgr_astar(''SELECT gid as id, source::int, target::int, '
                                        || 'length::float AS cost,x1,y1,x2,y2 FROM '
                                        || quote_ident(tbl) || ''', '
                                        || source || ', ' || target 
                                        || ' ,false), '
                                || quote_ident(tbl) || ' WHERE edge = 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;
                node    := rec.node;
                cost    := rec.cost;
                geom    := rec.geom;
                RETURN NEXT;
        END LOOP;
        RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT;
-----------------------------------------

--测试
SELECT gid, geom, node, cost, source, target, 
    ST_Reverse(geom) AS flip_geom FROM 
 pgr_astar('SELECT gid as id, source::int, target::int, length::float AS cost,x1,y1,x2,y2 FROM route',
						2, 17 ,false),route
                                 WHERE edge = gid ORDER BY seq

Geoserver导入

添加PostGis数据源

发布底图图层

发布函数图层

Sql语句
SELECT ST_MakeLine(route.geom) FROM (SELECT * FROM pgr_fromAtoB('route', %x1%, %y1%, %x2%, %y2%)ORDER BY seq) AS route
参数默认值 0
参数表达式 ^-?[\d.]+$
返回类型  lineString
坐标系    4326

发布前，修改最短路径查询结果显示样式

创建图层组

创建样式

OpenLayers效果展示

<!doctype html>
<html lang="en">
  <head>
    <link rel="stylesheet" href="./ol.css" type="text/css">
    <style>
      .map {
        height: 800px;
        width: 1000px;
		border: 1px solid black;
      }
    </style>
    <script src="./ol.js" type="text/javascript"></script>
    <title>OpenLayers example</title>
  </head>
  <body>
	<div>
		<button id="clear">清除结果</button>
	</div>
	<div id="map" class="map"></div>
	<script type="text/javascript">
		var format = 'image/png';
		var bounds = [29.1337776184082, 19.6063480377197,
                    46.7637786865234, 35.0863494873047];//边界可在geoserver中获取
		var mousePositionControl = new ol.control.MousePosition({
			className: 'custom-mouse-position',
			target: document.getElementById('map'),
			coordinateFormat: ol.coordinate.createStringXY(5),
			undefinedHTML: '&nbsp;'
		});
	  
		var params1 = {
            //LAYERS: 'shp:map',
			LAYERS: 'result01',//图层组名称
            FORMAT: 'image/png',
			VERSION: '1.1.1',  
            exceptions: 'application/vnd.ogc.se_inimage',
        };
		
		var baseMap = new ol.layer.Image({
            source: new ol.source.ImageWMS({
                    url: 'http://localhost:8080/geoserver/wms',
                    params: params1
                })
        });
	 
		var projection = new ol.proj.Projection({
			code: 'EPSG:4326',//坐标系
			units: 'degrees',
			global: true
		});
		var map = new ol.Map({
			controls: ol.control.defaults({
				attribution: false
			}).extend([mousePositionControl]),
			target: 'map',
			layers: [
				baseMap //路径视图
			],
			view: new ol.View({
				projection: projection
			})
		});

		map.getView().fit(bounds, map.getSize());
		
		
		
		var startPoint;
		var destPoint;
		var vectorLayer;
		var result;
		
		startPoint = new ol.Feature();
        destPoint = new ol.Feature();
		
		vectorLayer = new ol.layer.Vector({
			source: new ol.source.Vector({
			features: [startPoint, destPoint]
			}),
			style:new ol.style.Style({
				image:new ol.style.Icon(({
					size:[24,36],
					anchor:[0.5,0.75],
					anchorXUnits:'fraction',
					anchorYUnits:'fraction',
					src:'./marker.png'
				}))
			})
		});
		map.addLayer(vectorLayer);
		
		//清空路径规划结果
		var clearButton = document.getElementById('clear');
		clearButton.addEventListener('click', function(event) {
			// Reset the "start" and "destination" features.
			startPoint.setGeometry(null);
			destPoint.setGeometry(null);
			// Remove the result layer.
			map.removeLayer(result);
		});
		
		var params = {
			LAYERS: 'shp:shortRoute01',//路径规划函数图层
			FORMAT: 'image/png',
			VERSION: '1.1.1',  
            exceptions: 'application/vnd.ogc.se_inimage',
		};
		
		function clickMap(event)
		{
			if (startPoint.getGeometry() == null) {
				// First click.
				startPoint.setGeometry(new ol.geom.Point(event.coordinate));console.info(event.coordinate);
			} else if (destPoint.getGeometry() == null) {
				// Second click.
				destPoint.setGeometry(new ol.geom.Point(event.coordinate));
				// Transform the coordinates from the map projection (EPSG:3857)
				// to the server projection (EPSG:4326).
				var startCoord = (startPoint.getGeometry().getCoordinates());
				var destCoord = (destPoint.getGeometry().getCoordinates());
				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:'http://localhost:8080/geoserver/shp/wms',  
					params: params
				})
				});
				map.addLayer(result);
			}
		}
		
		map.on('click', clickMap);
		
    </script>
  </body>
</html>

写在后面

文中大多数内容取自其他博客，通过实际操作后整理，如有侵权，请联系