View source for Main Page/Research/MSB/Code/detect.stops.sql

<source lang=sql>
CREATE OR REPLACE FUNCTION detect_stops (sid text, array_dd integer[], array_dt integer[]) RETURNS bool as
$BODY$
	DECLARE
		utc timestamp;
		utc_min_query text;
		utc_min timestamp;
		utc_max_query text;
		utc_max timestamp;
		buffer_query text;
		point_table text;
		point text;
		npoints integer;
		dt integer;
		dd integer;
		dd_ft double precision;
		out_table text;
		cnt integer;
		row record;
		cquery text;
		dquery text;
		iquery text;
		seq_id integer;
		t_alter text;
		newcol text;
		uquery text;
	BEGIN
		-- input table
		point_table := quote_ident(sid) || '_move_point';
		-- construct an update string str_update, now consisting of `seq_id`
		out_table := quote_ident(point_table) || 'stop_counts';
		dquery := 'DROP TABLE IF EXISTS ' || quote_ident(out_table);
		RAISE NOTICE '%', dquery;
		EXECUTE dquery;
		cquery := 'CREATE TABLE ' || quote_ident(out_table) || ' (seq_id integer)';
		RAISE NOTICE '%', cquery;
		EXECUTE cquery;		-- alter the table
		FOR i in 1..array_upper(array_dd, 1) LOOP
			dd:= array_dd[i];	
			FOR j in 1..array_upper(array_dt, 1) LOOP
				dt:= array_dt[j];	
				newcol := 'd' || dd || '_t' || dt;
				t_alter := 'ALTER TABLE ' || quote_ident(out_table) || ' ADD COLUMN ' || newcol || ' INTEGER';
				EXECUTE t_alter;
			END LOOP;
		END LOOP;

		cnt := 0;
		-- for each point in sid_move_points
		FOR row in EXECUTE 'SELECT * FROM ' || quote_ident(point_table) LOOP
			-- get the point from the database as pt_focus
			-- get the time as utc_focus
			point := ASEWKT(row.point_waspn);
			utc := row.utc;
			cnt := cnt + 1;
			seq_id := row.seq_id;
			-- alter the table
			iquery := 'INSERT INTO ' || quote_ident(out_table) || ' VALUES (' || seq_id || ')';
			--raise notice '%', iquery;
			EXECUTE iquery;
			-- for each dd in distances
			FOR i in 1..array_upper(array_dd, 1) LOOP
				dd:= array_dd[i];
				dd_ft := dd * 3.2808;
				-- for each dt in time durations
				FOR j in 1..array_upper(array_dt, 1) LOOP
					dt:= array_dt[j];
					newcol := 'd' || dd || '_t' || dt;
					-- get utc_min as utc_focus - dt
					utc_min_query := 'SELECT timestamp ' || quote_literal(utc) || ' - interval ' || quote_literal(dt) ;
					EXECUTE utc_min_query into utc_min;
					-- get utc_max as utc_focus + dt
					utc_max_query := 'SELECT timestamp ' || quote_literal(utc) || ' + interval ' || quote_literal(dt) ;
					EXECUTE utc_max_query into utc_max;					
					-- select count(*) from sid_move_points where st_dwithin(point_waspn, pt, dt_focus) and utc > utc_min and utc < utc_max as num_points as `n_dd_dt`;
					buffer_query := 'SELECT count(*) from ' || quote_ident(point_table) 
						|| ' WHERE ST_DWITHIN(point_waspn, ' || quote_literal(point) || ',' || dd_ft || ') and (utc > ' || quote_literal(utc_min) || ' and utc < ' 
						|| quote_literal(utc_max) || ')';
					EXECUTE  buffer_query into npoints;
					uquery := 'UPDATE ' || quote_ident(out_table) || ' SET ' || newcol || ' = ' || npoints || ' WHERE seq_id = ' || seq_id;
					EXECUTE uquery;
					-- str_update <- paste(str_update, `n_dd_dt`)
					--RAISE NOTICE '% d=% t=% % %', cnt, dd, dt, asEWKT(point), npoints;
					RAISE NOTICE '%', cnt;
				END LOOP;
			END LOOP;
			-- add a record to results table with str_update

		END LOOP;
	RETURN 't';
	END
$BODY$
LANGUAGE 'plpgsql' ;
</source>