PL/pgSQL检索移动轨迹停留区域

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;