轨迹纠偏函数

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
--平滑轨迹
CREATE OR REPLACE FUNCTION GetSmoothGpsPt () RETURNS void AS $$
DECLARE vSmoothSpan integer;
declare rec  record;
declare tempRec record;
declare Wi float;
declare Wx float;
declare Wy float;
declare Wa float;
declare sumWX float;
declare sumWY float;
declare sumWA float;
declare sumW float;
declare Latitude float;
declare Longitude float;
declare TimeGap integer;
declare angle float;
BEGIN
vSmoothSpan := 30 ; 
for rec in select *,ST_Azimuth(LAG (the_geom) OVER ( ORDER BY update_time),LEAD (the_geom) OVER (ORDER BY update_time))/(2 * pi()) * 360 angle from nts_io_postgis_2d order by update_time loop
sumWX:= 0; sumWY:= 0; sumWA:= 0;sumW:=0;
--高斯滤波，已Gps点位前后三十秒的数据进行加权平滑
for tempRec in select  *,ST_Azimuth(LAG (the_geom) OVER (ORDER BY update_time),LEAD (the_geom) OVER (ORDER BY update_time))/(2 * pi()) * 360 angle from nts_io_postgis_2d t where t.update_time::time BETWEEN rec.update_time::time- interval '10 S' and  rec.update_time::time+ '10 S' loop
--raise notice '正在处理Longitude:%',tempRec.angle;
TimeGap:=extract(epoch FROM (rec.update_time :: TIME - tempRec.update_time :: TIME ));
Wi:=exp((-1) * TimeGap * TimeGap / (2 * vSmoothSpan * vSmoothSpan));
Wx:= Wi * st_x(tempRec.the_geom);
Wy:= Wi * st_y(tempRec.the_geom);
Wa:=Wi*coalesce(tempRec.angle,0);
sumWX = sumWX+Wx;
sumWY = sumWY+Wy;
sumWA=sumWA+Wa;

sumW=sumW+ Wi;
end loop;
Longitude:= sumWX / sumW;
Latitude:= sumWY / sumW;
angle:=sumWA/sumW;

--raise notice '正在处理angle:%',sumWA;
--raise notice '正在处理Longitude:%,Latitude:%,angle:%',Longitude,Latitude,tempRec.angle;
--raise notice '正在处理geom:%',st_astext(ST_GeomFromText('POINT('||Longitude||' '||Latitude||')',4326));
--平滑后的数据入库
INSERT INTO public.gps_data_smooth ("datetime","geom","angle") VALUES ( rec.update_time,ST_GeomFromText('POINT('||Longitude||' '||Latitude||')',4326),angle);
end loop; 
END ; 
$$ LANGUAGE plpgsql;