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>