PostGIS+GeoServer+OpenLayers最短路径分析 || 记录Bug

谢长留

已于 2024-04-23 21:36:54 修改

阅读量1.7k

点赞数 3

分类专栏： OpenLayers 文章标签： postgresql 笔记 bug 前端 javascript

于 2022-04-30 16:59:46 首次发布

本文链接：https://blog.csdn.net/weixin_45862553/article/details/124444424

版权

OpenLayers 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

学习PostGIS+GeoServer+OpenLayers最短路径分析，记录遇到的奇奇怪怪Bug。
主要参考：
https://www.cnblogs.com/giser-s/p/11599562.html（如何准备数据）
https://blog.csdn.net/fyc__iOS/article/details/104182358?spm=1001.2014.3001.5506
https://blog.csdn.net/weixin_42232745/article/details/107841879（一些代码有注释，更好懂）

1

CREATE EXTENSION pgrouting;

出现：could not open extension control file “D/pgrouting.control”: No such file or directory

原因：安装文件中没有pgrouting扩展包（14版本）
尝试换版本（找到我的arcgis支持9.5、9.6 、10版本，但都出现There has been an error.Error running C:\Windows\System32\icacls “C:\Users{UserName}\AppData\local\Temp/postgresql”…的问题，同时这个方法对我无效，而且换来换去真的麻烦，不建议）https://blog.csdn.net/u013070710/article/details/115337399?spm=1001.2014.3001.5506
直接安装11.2版本：https://blog.csdn.net/feriman/article/details/119519772?spm=1001.2014.3001.5506

2

没有postgis shapefile import/管理器（哪个版本我都没有过）
没有PostGIS Bundle 3 for PostgreSQL（11.2版本有，但是没有libinitl.9打不开；将libinitl.8改为.9也不行，说得运行，然后运行还出错，把我电脑搞得黑屏闪退，无语）

换种方式导入shp数据（我用的方法三qgis导入，没失败。方法二我只生成了0kb的空文件）https://blog.csdn.net/m0_51392995/article/details/122087515?spm=1001.2014.3001.5506

3

--为lproad表创建拓扑布局，即为source和target字段赋值
SELECT pgr_createTopology('route ',0.000015, 'geom', 'gid');

结果返回fail
ERROR: Unexpected Null value in column source；
（source没有值，因为拓扑fail。我还以为我的数据处理错误，我原先用‘要素转线’工具打断的线，于是跟着https://blog.csdn.net/qq_22174779/article/details/90294813进行‘打断相交线’，fail）
网上找了好几个小时的资料：官网说明：https://docs.pgrouting.org/3.1/en/pgr_createTopology.html官方案例：https://blog.csdn.net/GliangJu/article/details/118416634?spm=1001.2014.3001.5506（就是因为我运行这个，拓扑OK，说明我环境没问题，应该）
看其他人的代码，换了几次pgr_createTopology（）的参数设置（其实我尝试挺多但是我电脑一直闪退，好多记录都没了，经常保存啊！保存）https://blog.csdn.net/yifei1989/article/details/14004661

--为lproad表创建拓扑布局，即为source和target字段赋值
--SELECT pgr_createTopology('route ',0.000015, 'geom', 'gid');
--SELECT  pgr_createTopology('route', 0.001,source:='source', id:='id', target:='target', the_geom:='the_geom');
--SELECT pgr_createTopology(‘route’,0.001);
SELECT  pgr_createTopology('route',0.001,'geom','gid','source','target');

几个小时后突然发现最后一种成功了！离谱！

4

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);

①ERROR: function pgr_dijkstra(unknown, integer, integer, boolean, boolean) do…（意思是函数参数类型不对，让转换类型）
将false，false改为directed:=false；
②ERROR: column “id1” does not exist
改为select * from…
在这里插入图片描述
看到select * 后的列名，我有点怀疑id1、id2是不是node、edge的重命名，于是改成了

SELECT seq, node AS id1, edge AS id2, cost FROM pgr_dijkstra('
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM route ',
                1, 4, directed:=false);

在这里插入图片描述
可能原博写错了？？？

5

前端图层加载空白
我先F12看了一下，查好久，看不懂，突发奇想预览了一下geoserver上的sql视图，发现了ERROR，改正。（所以一定要每一步都检查确保无误）（其实改正了前端还是没加载出来，后来发现layers图层名忘写工作空间了，冤种）（但还是要确保geoserver图层预览一定要成功）

其他参考文章：
https://blog.csdn.net/weixin_39836943/article/details/112705138（前端调用代码的注释，更好懂）
https://blog.csdn.net/qgbihc/article/details/108635912（似乎涉及到了一点vue，可以看看）
https://blog.csdn.net/u011365716/article/details/84431630
https://hanbo.blog.csdn.net/article/details/78625246（跟其他的没差别，随便看看）
https://blog.csdn.net/longshengguoji/article/details/46350675（讲了openlayers3和openlayers2似乎不一样，可以看看）
在这里插入图片描述

至此，全部运行成功，接下来就是把它应用到我的设计中了，估计还会出现意想不到的bug吧
最后感谢所有前辈的分享。
附上代码：

CREATE EXTENSION postgis;
CREATE EXTENSION pgrouting;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
CREATE EXTENSION address_standardizer;
--添加起点id
ALTER TABLE route ADD COLUMN source integer;
 
--添加终点id
ALTER TABLE route  ADD COLUMN target integer;
 
--添加道路权重值
ALTER TABLE route  ADD COLUMN length double precision
--为lproad表创建拓扑布局，即为source和target字段赋值
--SELECT pgr_createTopology('route ',0.000015, 'geom', 'gid');
--SELECT  pgr_createTopology('route', 0.001,source:='source', id:='id', target:='target', the_geom:='the_geom');
--SELECT pgr_createTopology(‘route’,0.001);
SELECT  pgr_createTopology('route',0.001,'geom','gid','source','target');
select source from route
--为source和target字段创建索引
CREATE INDEX source_idx ON route ("source");
CREATE INDEX target_idx ON route ("target");
--添加线段端点坐标
ALTER TABLE route ADD COLUMN x1 double precision;        --创建起点经度x1
ALTER TABLE route ADD COLUMN y1 double precision;        --创建起点纬度y1
ALTER TABLE route ADD COLUMN x2 double precision;        --创建起点经度x2
ALTER TABLE route ADD COLUMN y2 double precision;        --创建起点经度y2
--给x1、y1、x2、y2赋值
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)));
 
--为length赋值
update route set length =st_length(geom);
 
--为lproad 表添加reverse_cost字段并用length的值赋值
ALTER TABLE route ADD COLUMN reverse_cost double precision;
UPDATE route 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 route ',
                1, 4, directed:=false);
SELECT seq, node AS id1, edge AS id2, cost FROM pgr_dijkstra('
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM route ',
                1, 4, directed:=false);
SELECT * FROM pgr_dijkstra('
                SELECT  gid AS id,
                         source::integer,
                         target::integer,
                         length::double precision AS cost
                        FROM route ',
                1, 4, directed:=false);		
--执行这个函数，最新的
-----------------------------------------
--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',
						3, 17 ,false),route
                                 WHERE edge = gid ORDER BY seq

<!doctype html>
<html lang="en">
<head>
    <!--必要的脚本引用-->
    <link href="../css/ol.css" rel="stylesheet" type="text/css" />

    <style>
        .map {
            height: 800px;
            width: 1000px;
            border: 1px solid black;
        }
    </style>
    <script src="../libs/ol/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 = [121.31274255300002, 30.73874852600005,
            121.79028330000003, 31.019819839000053];//边界可在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: 'fx:est_path',//图层组名称
            FORMAT: 'image/png',
            VERSION: '1.3.0',
            exceptions: 'application/vnd.ogc.se_inimage',
        };

        var baseMap = new ol.layer.Image({
            source: new ol.source.ImageWMS({
                url: 'http://localhost:8080/geoserver/fx/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,
                projection:'EPSG:4326',
            })
        });
        
        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: 'fx:func_estpath',//路径规划函数图层
            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/fx/wms',
                        params: params
                    })
                });
                map.addLayer(result);
            }
        }

        map.on('click', clickMap);

    </script>
</body>
</html>