create or replace function findregion(sqltablename varchar,simid varchar,mininterval double precision,maxdistance double precision)
returns setof resulttable as
$body$
declare
id bigint; --车辆记录的id号
row0 record;
typerow record;
r resulttable%rowtype; --resulttable的记录(行)结构
--vehiclesimid varchar:='806584018900';
vehiclesimid varchar:=simid;
tablename varchar:='resulttable'; --计算结果的表名
sqltable varchar:=sqltablename; --轨迹所在的表名
sqlcount varchar; --表中记录数
sqlinsert varchar; --插入记录
sqltruncate varchar; --清空表
sqlresult varchar;
sqltype varchar; --创建新数据类型
sqlresulttable varchar; --最终的结果表
segNumber integer; --分割数目(整个轨迹被停留点所分割)或称为停留次数
i integer; --循环计数器
result integer; --判断结果表是否存在
total integer; --车辆轨迹总数
flag integer; --短距离停留次数标记
stoptime timestamp without time zone; --离开停留区的时刻
tmptime timestamp without time zone; --中间变量
starttime timestamp without time zone; --进入停留区的时刻
stopid bigint; --离开停留区的最后一个记录的id号
tmpid bigint; --中间变量
startid bigint; --进入停留区的开始记录的id号
isstart integer; --是否进入停留区
d double precision; --两点间的距离
d0 double precision; --最小时间间隔和最大距离间隔推演出来的基准距离
x0 double precision; --t0时刻的经度
y0 double precision; --t0时刻的纬度
sampledistance double precision; --采样点间的距离
maxdistance0 double precision; --最大判断距离
sampleinterval double precision; --采样时间间隔
lasttime bigint; --上一时刻点
currenttime bigint; --当前时刻点
mininterval0 double precision; --最小时间间隔
ratio double precision; --最小时间间隔与采样时间间隔的比率
startx double precision; --进入停留区该时刻的经度
starty double precision; --进入停留区该时刻的纬度
tmpx double precision;
tmpy double precision;
midx double precision;
midy double precision;
pt point ; --停留区的中心点
stopx double precision; --离开停留区该时刻的经度
stopy double precision; --离开停留区该时刻的纬度
xi double precision; --ti时刻的经度
yi double precision; --ti时刻的纬度
xj double precision; --tj时刻的经度
yj double precision; --tj时刻的纬度
--cur cursor for select * from gps_table2;--初始化游标
cur refcursor;
begin
sqlcount:='select count(*) from '||quote_ident(sqltable)||' where vehiclesimid=$1';
EXECUTE sqlcount INTO total USING vehiclesimid; --获取总记录数
--RAISE NOTICE '该车辆全天总记录数%',total;
--判断自定义数据类型是否存在
select * into typerow from pg_type where typname='timepoint';
if not found then
create type timepoint as(
x double precision, --tij时刻的经度
y double precision, --tij时刻的纬度
xytime timestamp without time zone --tij时刻
);
end if;
--判断数据库中,结果表是否存在
sqlresult:='select count(*) from pg_class where relname=$1';
EXECUTE sqlresult INTO result USING tablename;
sqlresulttable:='create table resulttable (id bigint,pointin timepoint,pointmid point,pointout timepoint)'; --结果表,用来存储计算结果
sqltruncate:='truncate table '||quote_ident(tablename);
--sqltruncate:='truncate table resulttable';
if result=0 then --如果结果表不存在
EXECUTE sqlresulttable; --创建一个新表
else
EXECUTE sqltruncate; --存在,清空表
end if;
--变量初始化
i:=1;
flag:=0;
segNumber:=0;
isstart:=1;
--mininterval0:=10; --10秒
--maxdistance0:=0.00012; --10秒对应15米
mininterval0:=mininterval; --10秒
maxdistance0:=maxdistance/100000; --10秒对应15米
--open cur ; --打开游标
OPEN cur FOR EXECUTE 'select * from '||quote_ident(sqltable);
fetch cur into row0; --使用游标,取得第一行记录
x0:=row0.gpslongitude;
y0:=row0.gpslatitude;
--RAISE NOTICE 'gpstime2:%',row0.gpstime2;
--RAISE NOTICE '经纬度:%,%',x0,y0;
while i<total loop --遍历整辆车的全天GPS记录
tmptime:=row0.gpstimestamp;
tmpid:=row0.id;
tmpx:=row0.gpslongitude;
tmpy:=row0.gpslatitude;
lasttime:=row0.gpstime2;
fetch cur into row0; ----使用游标,取得下一行记录
-- RAISE NOTICE 'id:%',row0.id;
i:=i+1;
xi:=row0.gpslongitude;
yi:=row0.gpslatitude;
currenttime:=row0.gpstime2;
sampleinterval:=currenttime-lasttime;
ratio:=sampleinterval/mininterval0;
d0:=ratio*maxdistance0;
d:=sqrt((xi-x0)*(xi-x0)+(yi-y0)*(yi-y0));
x0:=xi;
y0:=yi;
--RAISE NOTICE 'ratio*maxdistance0:%,d:%',d0,d;
-- if d<0.0005 then --0.001为经验距离d,单位是度,约为100米
if d<d0 then
flag:=flag+1;
if isstart>0 then
starttime:=tmptime;
startid:=tmpid;
startx:=tmpx;
starty:=tmpy;
end if;
isstart:=-1; --进入停留区后,关闭开关
if flag>0 then --停留15次以上,车辆id被记录
stopid:=row0.id;
stoptime:=row0.gpstimestamp;
stopx:=row0.gpslongitude;
stopy:=row0.gpslatitude;
end if;
else
isstart:=1;--离开停留区后,开关打开
if flag>0 then
segNumber:=segNumber+1; --聚集划分数目
midx:=(startx+stopx)/2; --计算停留区域中心点经度
midx:=round(cast(midx as numeric),6);
midy:=(starty+stopy)/2; --计算停留区域中心点纬度
midy:=round(cast(midy as numeric),6);
pt:=(midx,midy); --停留区域中心点
sqlinsert:='insert into resulttable values($1,row($2,$3,$4),$5,row($6,$7,$8))';
EXECUTE sqlinsert USING segNumber,startx,starty,starttime,pt,stopx,stopy,stoptime;
end if;
flag:=0; --循环窗口清零
end if;
end loop;
--读出结果表中的数据
for r in select * from resulttable where resulttable.id>0
loop
return next r;
end loop;
return;
end;
$body$
language plpgsql;
PL/pgSQL检索移动轨迹停留区域
最新推荐文章于 2020-08-27 11:14:17 发布