POSTGIS+GEOSERVER+OPENLAYERS路径规划导航_postgres 路径规划

1、添加字段

--添加起点id
ALTER TABLE roa_4m ADD COLUMN source integer; 
--添加终点id 
ALTER TABLE roa_4m ADD COLUMN target integer; 
--添加道路权重值
ALTER TABLE roa_4m ADD COLUMN length double precision;

2、创建拓扑结构

```javascript
--为sampledata表创建拓扑布局，即为source和target字段赋值
SELECT pgr_createTopology('roa_4m',0.0001, 'geom', 'gid');

1
2
3
4
5
6
7
8
9
10
11
12
13

3、创建索引

--为source和target字段创建索引

CREATE INDEX source_idx ON roa_4m("source");

CREATE INDEX target_idx ON roa_4m("target");


ALTER TABLE roa_4m ADD COLUMN x1 double precision;		--创建起点经度x1
ALTER TABLE roa_4m ADD COLUMN y1 double precision;		--创建起点纬度y1
ALTER TABLE roa_4m ADD COLUMN x2 double precision;		--创建起点经度x2
ALTER TABLE roa_4m ADD COLUMN y2 double precision;		--创建起点经度y2
		
UPDATE roa_4m SET x1 =ST_x(ST_PointN(geom, 1));	
UPDATE roa_4m SET y1 =ST_y(ST_PointN(geom, 1));	
UPDATE roa_4m SET x2 =ST_x(ST_PointN(geom, ST_NumPoints(geom)));	
UPDATE roa_4m SET y2 =ST_y(ST_PointN(geom, ST_NumPoints(geom)));	--给x1、y1、x2、y2赋值


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

4、给索引赋值

--为length赋值
--设置为双向
update roa_4m set length =st_length(geom);
--将长度值赋给reverse_cost，作为路线选择标准
ALTER TABLE roa_4m ADD COLUMN reverse_cost double precision;
UPDATE roa_4m SET reverse_cost = st_length(geom);


1
2
3
4
5
6
7
8

5、路径函数的生成

-- Function: pgr_fromctod(character varying, double precision, double precision, double precision, double precision)
 
-- DROP FUNCTION pgr_fromctod(character varying, double precision, double precision, double precision, double precision);
 
CREATE OR REPLACE FUNCTION pgr_fromctod(
    tbl character varying,
    startx double precision,
    starty double precision,
    endx double precision,
    endy double precision)
  RETURNS geometry AS
$BODY$  
 
declare 
 
    v_startLine geometry;--离起点最近的线 
 
    v_endLine geometry;--离终点最近的线 
 
     
    v_startTarget integer;--距离起点最近线的终点
 
    v_startSource integer;
 
    v_endSource integer;--距离终点最近线的起点
 
    v_endTarget integer;
 
 
    v_statpoint geometry;--在v_startLine上距离起点最近的点 
 
    v_endpoint geometry;--在v_endLine上距离终点最近的点 
 
     
    v_res geometry;--最短路径分析结果
 
    v_res_a geometry;
 
    v_res_b geometry;
 
    v_res_c geometry;
 
    v_res_d geometry; 
 
    v_perStart float;--v_statpoint在v_res上的百分比 
 
    v_perEnd float;--v_endpoint在v_res上的百分比 
 
    v_shPath_se geometry;--开始到结束
 
    v_shPath_es geometry;--结束到开始
 
    v_shPath geometry;--最终结果
 
    tempnode float;      
 
begin
 
    --查询离起点最近的线 
    --4326坐标系
    --找起点15米范围内的最近线
 
    execute 'select geom, source, target  from ' ||tbl||
 
                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('||startx ||' ' || starty||')'',4326),15)
                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| startx ||' '|| starty ||')'',4326))  limit 1'
 
                            into v_startLine, v_startSource ,v_startTarget; 
 
    --查询离终点最近的线 
    --找终点15米范围内的最近线
 
    execute 'select geom, source, target from ' ||tbl||
 
                            ' where ST_DWithin(geom,ST_Geometryfromtext(''point('|| endx || ' ' || endy ||')'',4326),15)
                            order by ST_Distance(geom,ST_GeometryFromText(''point('|| endx ||' ' || endy ||')'',4326))  limit 1'
 
                            into v_endLine, v_endSource,v_endTarget; 
 
    --如果没找到最近的线，就返回null 
 
    if (v_startLine is null) or (v_endLine is null) then  
        return null; 
    end if ;  
	    select  ST_ClosestPoint(v_startLine, ST_Geometryfromtext('point('|| startx ||' ' || starty ||')',4326)) into v_statpoint; 
	 
	    select  ST_ClosestPoint(v_endLine, ST_GeometryFromText('point('|| endx ||' ' || endy ||')',4326)) into v_endpoint; 
	 	   	 
	   -- ST_Distance 

	    --从开始的起点到结束的起点最短路径	 
	    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
	    'FROM pgr_kdijkstraPath( 
	    ''SELECT gid as id, source, target, length as cost FROM ' || tbl ||''',' 
	 
	    ||v_startSource|| ', ' ||'array['||v_endSource||'] , false, false 
	    ) a, ' 
	 
	    ||tbl|| ' b 
	    WHERE a.id3=b.gid   
	    GROUP by id1   
	    ORDER by id1' into v_res ;
 
	    --从开始的终点到结束的起点最短路径	 
	    execute 'SELECT st_linemerge(st_union(b.geom)) ' || 
	    'FROM pgr_kdijkstraPath( 
	    ''SELECT gid as id, source, target, length as cost FROM ' || tbl ||''',' 	 
	    ||v_startTarget|| ', ' ||'array['||v_endSource||'] , false, false 
	    ) a, ' 	 
	    ||tbl|| ' b 
	    WHERE a.id3=b.gid   
	    GROUP by id1   
	    ORDER by id1' into v_res_b ; 
	    --从开始的起点到结束的终点最短路径	 
	    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||	 
	    'FROM pgr_kdijkstraPath( 
	    ''SELECT gid as id, source, target, length as cost FROM ' || tbl ||''',' 	 
	    ||v_startSource || ', ' ||'array['||v_endTarget||'] , false, false 
	    ) a, ' 	 
	    || tbl || ' b 
	    WHERE a.id3=b.gid   
	    GROUP by id1   
	    ORDER by id1' into v_res_c ;
	 	 
	    --从开始的终点到结束的终点最短路径
	 
	    execute 'SELECT st_linemerge(st_union(b.geom)) ' ||
	 
	    'FROM pgr_kdijkstraPath( 
	    ''SELECT gid as id, source, target, length as cost FROM ' || tbl ||''',' 
	 
	    ||v_startTarget || ', ' ||'array['||v_endTarget||'] , false, false 
	    ) a, ' 
	    || tbl || ' b 
	    WHERE a.id3=b.gid   
	    GROUP by id1   
	    ORDER by id1' into v_res_d ;
	 
	    if(ST_Length(v_res) > ST_Length(v_res_b)) then	 
	       v_res = v_res_b; 
	    end if;	 
	    if(ST_Length(v_res) > ST_Length(v_res_c)) then	 
	       v_res = v_res_c; 
	    end if; 
	    if(ST_Length(v_res) > ST_Length(v_res_d)) then 
	       v_res = v_res_d;
	    end if;
	 
	    --如果找不到最短路径，就返回null 
	    --if(v_res is null) then 
	    --    return null; 
	    --end if;    
	 
	    --将v_res,v_startLine,v_endLine进行拼接  
	    select  st_linemerge(ST_Union(array[v_res,v_startLine,v_endLine])) into v_res;

	    select  ST_Line_Locate_Point(v_res, v_statpoint) into v_perStart; 
	 
	    select  ST_Line_Locate_Point(v_res, v_endpoint) into v_perEnd; 
	 
	    if(v_perStart > v_perEnd) then 
	 
	        tempnode =  v_perStart;
	 
	        v_perStart = v_perEnd;
	 
	        v_perEnd = tempnode;
	 
	    end if;
	 

	    --截取v_res 
	    --拼接线
	 
	    SELECT ST_Line_SubString(v_res,v_perStart, v_perEnd) into v_shPath;

	    return v_shPath; 

	end; 
	 
	$BODY$
	  LANGUAGE plpgsql VOLATILE STRICT
	  COST 100;
	ALTER FUNCTION pgr_fromctod(character varying, double precision, double precision, double precision, double precision)
	  OWNER TO postgres;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185

（上面函数是不是不知道怎么定义，基本套路在这里postgresql存储过程）生成后的函数，可以在public中函数中查看。

三、PostGIS连接Geoserver发布数据，以及发布视图

1、geoserver连接postgis

点击数据存储---->添加新的数据存储

选择新建数据源 点击postgis

j将红线框选中部分，填写

数据名称--->要连接的数据库名称----->用户名------->密码------->保存

2、发布roa_4m表格为服务

点击图层----->添加新的资源----新建图层（下拉框中选择刚才上面所间的资源）

 

选择我们在postgis中数据表，点击发布即可

3、创建sql视图

方法和上述发布roa_4m表格类似，只不过在一步，选择 配置新的SQL视图。

编辑视图

1）填写视图名称（自己自定义）

2)SQL语句

select * from pgr_fromctod('roa_4m',%x1%, %y1%, %x2%, %y2%)

 
 
1
2

3）点击从SQL猜想的参数，D的默认值，全部为0，验证的正则表达式全部为^-?[\d.]+$

4）点击刷新，类型选为LineString类型，SRD为4326

5）点击保存，然后发布

四、OL调用demo

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8" />
    <title>室内路径规划</title>
    <link href="../script/ol4/ol.css" rel="stylesheet" />
    <style>
        #id {
            width:1000px;
            height:1000px;
        }
    </style>
    <script src="../script/ol4/ol.js"></script>
</head>
<body>
    <div id="map"></div>
    <script>
 
        var roadLayer = new ol.layer.Tile({
            source: new ol.source.TileWMS({
                url: 'http://localhost:8080/geoserver/cite/wms',
                params: { 'LAYERS': 'cite:roa_4', 'TILED': true },
                serverType: 'geoserver'
            })
        })
        var map = new ol.Map({
            target: document.getElementById("map"),
            layers: [ 
                roadLayer
            ],
            view: new ol.View({
                center: [117.34211730957, 49.6271781921387],
                projection: 'EPSG:4326',
                zoom: 4
            })
        });
        var startCoord = [117.34211730957, 49.6271781921387]
        var destCoord = [127.216133117676, 45.7485237121582]
        var params = {
            LAYERS: 'cite:testSqlView',
            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://localhost:8080/geoserver/cite/wms',
                    params: params
                })
        });
        console.info(result);
        map.addLayer(result);
    </script>
</body>
</html>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63

五、效果图（蓝色线段）