= SST processing example page =

[wiki:SeacoosDB back to SEACOOS DB documentation]

== Table-set process ==
 1. Bash script initiates SQL INSERT: data passes through the [wiki:SeacoosDB/SSTExample#Functionsst_labels_and_times sst_labels_and_times() function:] 
   * eliminate data that are too old (> 2 days)
   * rough range checking 
   * convert C to F
   * round time_stamp (TS) to nearest hour -> report_time_stamp (RTS)
   * populates value fields - like "15 deg C at 5m" string.
   * station_ID, TS, and RTS are inserted into '''sst_changes''' table
   * all new fields are inserted into '''sst_prod''' table
 1. Bash script calls [wiki:SeacoosDB/SSTExample#Functionset_sst_prod_show set_sst_prod_show() function:]
   * for each unique station_id and RTS in '''sst_changes''' table
   * uses RTS and TS to determine a single observation to serve as hourly value for that station.
   * sets top_of_hour field to 1 (yes) or null for each row in '''sst_prod'''
   * truncate (drop all data) '''sst_changes''' table. 
 1. Bash script calls [wiki:SeacoosDB/SSTExample#Scriptpopulate_sst_map.sql populate_sst_map.sql script]
   * truncate (drop all data) '''sst_map''' 
   * inserts values from '''sst_prod''' into '''sst_map''' where top_of_hour is 1.

[[br]]

== SST table-set ==

http://trac.secoora.org/datamgmt/attachment/wiki/SeacoosDB/sst_tableset.jpg?format=raw

[[br]]

== SST table-set indexes ==

'''sst_prod''' indexes:
||'''Name '''||'''Definition''' ||''' Constraints '''||
||pk_id_time_stamp ||	CREATE UNIQUE INDEX pk_id_time_stamp ON sst_prod USING btree (station_id, time_stamp)||	Primary key ||
||sst_prod_gist ||	CREATE INDEX sst_prod_gist ON sst_prod USING gist (the_geom) 	|| ||
||sst_prod_id_z_rep_time ||	CREATE INDEX sst_prod_id_z_rep_time ON sst_prod USING btree (station_id, z, report_time_stamp) 	|| ||
||sst_prod_report_time_stamp ||	CREATE INDEX sst_prod_report_time_stamp ON sst_prod USING btree (report_time_stamp) || ||	
||sst_prod_seq ||	CREATE INDEX sst_prod_seq ON sst_prod USING btree (seq) 	|| ||
||sst_prod_station_id ||	CREATE INDEX sst_prod_station_id ON sst_prod USING btree (station_id) || ||	
||sst_prod_time_stamp ||	CREATE INDEX sst_prod_time_stamp ON sst_prod USING btree (time_stamp) 	|| ||
||sst_prod_top_of_hour ||	CREATE INDEX sst_prod_top_of_hour ON sst_prod USING btree (top_of_hour) || ||	

'''sst_map''' table indexes:
||'''Name''' ||'''Definition''' ||'''	Constraints '''||	
||bottom_water_temp_map_report_time_stamp ||CREATE INDEX bottom_water_temp_map_report_time_stamp ON sst_map USING btree (report_time_stamp) || ||
||bottom_water_temp_map_report_time_stamp_z ||CREATE INDEX bottom_water_temp_map_report_time_stamp_z ON sst_map USING btree (report_time_stamp, label_z)||||
||sst_map_report_time_stamp|| CREATE INDEX sst_map_report_time_stamp ON sst_map USING btree (report_time_stamp) || ||
||sst_map_report_time_stamp_z|| CREATE INDEX sst_map_report_time_stamp_z ON sst_map USING btree (report_time_stamp, label_z) || ||
||sst_map_seq_key|| CREATE UNIQUE INDEX sst_map_seq_key ON sst_map USING btree (seq) ||Unique key ||

'''sst_changes''' table indexes:
||'''Name''' ||'''Definition''' ||'''	Constraints '''||
||sst_changes_id ||	CREATE INDEX sst_changes_id ON sst_changes USING btree (station_id) || ||	
||sst_changes_report_time_stamp ||	CREATE INDEX sst_changes_report_time_stamp ON sst_changes USING btree (report_time_stamp) || ||	
||sst_changes_time_stamp ||	CREATE INDEX sst_changes_time_stamp ON sst_changes USING btree (time_stamp)|| ||

'''sst_time_stamp_range''' table indexes:
||'''Name''' ||'''Definition''' ||'''	Constraints '''||
||pk_time_stamp || CREATE UNIQUE INDEX pk_time_stamp ON sst_time_stamp_range USING btree (time_stamp) ||	Primary key||

[[br]]

== Table constraints == 
'''sst_prod''' table constraints:
||'''Name '''||'''Definition''' ||
||$1 ||	CHECK (srid(the_geom) = -1)||
||$2 ||	CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)||
||pk_id_time_stamp ||PRIMARY KEY (station_id, time_stamp)||
||positive_up_down ||CHECK (positive::text = 'up'::character varying::text OR positive::text = 'down'::character varying::text OR positive::text = ''::character varying::text)||

'''sst_map''' table constraints:
||'''Name''' ||'''Definition''' ||	
||$1  ||CHECK (srid(the_geom) = -1) || 
||$2  ||CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL)||
||sst_map_seq_key ||UNIQUE (seq)||

[[br]]

== Function sst_labels_and_times() ==
Run on INSERT/UPDATE to table '''sst_prod'''. 
 * eliminate data that are too old (> 2 days)
 * rough range checking 
 * convert C to F
 * round time_stamp (TS) to nearest hour -> report_time_stamp (RTS)
 * populates value fields - like "15 deg C at 5m" string.
 * station_ID, TS, and RTS are inserted into '''sst_changes''' table
 * all new fields are inserted into '''sst_prod''' table

Trigger definition for table '''sst_prod''', defined in PostgreSQL (plpgsql):

sst_labels_and_times_trig => CREATE TRIGGER sst_labels_and_times_trig BEFORE INSERT OR UPDATE ON sst_prod FOR EACH ROW EXECUTE PROCEDURE sst_labels_and_times()

{{{

DECLARE
  my_new_time_stamp_char varchar;
  my_new_time_stamp timestamp with time zone;
  mysecs INTEGER;
  my_report_time_stamp timestamp without time zone;

  my_label_z float;
  
BEGIN
  -- check to see if incoming time_stamp is > now or < 2 days ago
  my_new_time_stamp_char := new.time_stamp || '-00';
  my_new_time_stamp := my_new_time_stamp_char;
  if my_new_time_stamp > now() or my_new_time_stamp < (now() - interval '2 days') then
    raise exception '% time_stamp is out of bounds', new.time_stamp;
  end if;
  if new.temperature_celcius > 33.16 or new.temperature_celcius < -2.1 then
    raise exception '% temperature is out of bounds', new.temperature_celcius;
  end if;

  new.temperature_fahrenheit = round((new.temperature_celcius * 9.0 / 5.0 + 32.0)::numeric,2);
  
  -- for SST, we_re going to make all z_s below SS positive
  -- We could just take the absolute value of z (or in this case negate all vals),
  -- but this needs to be similar to the other variables; so be consistent.  
  -- Essentially, force z_s to reflect that a positive means down.
  if (new.positive = 'up') then
    my_label_z := - (new.z);  -- hope we don_t have SST_s from above SS, though!
  else
    my_label_z := new.z;
  end if;
  new.label_z := my_label_z;
  
  new.lon  := round(X(new.the_geom)::numeric,2);
  new.lat := round(Y(new.the_geom)::numeric,2);
  
  new.secs_from_time_stamp := extract(epoch from new.time_stamp - date_trunc('hour',new.time_stamp));
  
  mysecs := extract(epoch from new.time_stamp - date_trunc('hour',new.time_stamp));
  
  if (mysecs >= 0 and mysecs <= 15 * 60) THEN  
    new.report_time_stamp := date_trunc('hour',new.time_stamp);
    my_report_time_stamp := date_trunc('hour',new.time_stamp);
  ELSE
    new.report_time_stamp := date_trunc('hour',new.time_stamp) + interval '1 hour';
    my_report_time_stamp := date_trunc('hour',new.time_stamp) + interval '1 hour';
  END IF;
  
  new.secs_from_report_time_stamp := extract (epoch from new.time_stamp - my_report_time_stamp);
  
  -- summary column for query results
  new.value_temperature_celcius := round(new.temperature_celcius::numeric,2) || ' deg C ' 
    || ' @ ' || my_label_z || 'm';
  new.value_temperature_fahrenheit := round((new.temperature_celcius * 9.0 / 5.0 + 32.0)::numeric,2) || ' deg F ' 
    || ' @ ' || my_label_z || 'm';    
    
  insert into sst_changes (station_id, report_time_stamp) values (new.station_id, my_report_time_stamp);
  
  return new;

END;
}}}
[[br]]

== Function set_sst_prod_show() ==
Run from the command line via psql during aggregation process.  Function defined in PostgreSQL, written in plpgsql 
 * for each unique station_id and RTS in sst_changes table
 * uses RTS and TS to determine a single observation to serve as hourly value for that station.
 * sets top_of_hour field to 1 (yes) or null for each row in sst_prod
 * truncate (drop all data) sst_changes table. 
{{{
declare
  first_pass int;
  unique_obs RECORD;
  obs_to_refresh RECORD;
  my_station_id varchar;
  my_report_time_stamp timestamp without time zone;
  my_time_stamp timestamp without time zone;
  my_abs_secs_from_report_time_stamp float;
  num_rec int;
  show_these_obs varchar;
  hide_these_obs varchar;
  num_show int;
    
BEGIN 
  num_show := 0;
  DROP trigger sst_labels_and_times_trig ON sst_prod;
  first_pass := 1;
  
  FOR obs_to_refresh IN select distinct station_id, report_time_stamp from sst_changes LOOP
  
    FOR unique_obs IN select station_id, report_time_stamp, abs(secs_from_report_time_stamp) as abs_secs_from_report_time_stamp,
 time_stamp from sst_prod where station_id = obs_to_refresh.station_id and report_time_stamp = obs_to_refresh.report_time_stamp
 order by station_id, report_time_stamp, abs(secs_from_report_time_stamp), time_stamp LOOP

      IF (first_pass = 1) or (unique_obs.station_id <> my_station_id) THEN
        first_pass := 0;
        my_station_id := unique_obs.station_id;
        my_report_time_stamp := unique_obs.report_time_stamp;
        num_rec := 0;
      ELSIF (unique_obs.report_time_stamp <> my_report_time_stamp) THEN
        my_report_time_stamp := unique_obs.report_time_stamp;
        num_rec := 0;
      END IF;

      IF (num_rec = 0) THEN
        my_abs_secs_from_report_time_stamp := unique_obs.abs_secs_from_report_time_stamp;
        my_time_stamp := unique_obs.time_stamp;
        update sst_prod set top_of_hour = 1 where sst_prod.station_id = unique_obs.station_id and sst_prod.time_stamp = my_time_stamp;
        num_rec := 1;
        num_show := num_show + 1;
      ELSE
        IF (unique_obs.abs_secs_from_report_time_stamp = my_abs_secs_from_report_time_stamp) THEN
          update sst_prod set top_of_hour = 0 where sst_prod.station_id = unique_obs.station_id and sst_prod.time_stamp = my_time_stamp;
          num_show := num_show + 1;
          update sst_prod set top_of_hour = 1 where sst_prod.station_id = unique_obs.station_id and sst_prod.time_stamp = unique_obs.time_stamp;
        ELSE
          update sst_prod set top_of_hour = 0 where sst_prod.station_id = unique_obs.station_id and sst_prod.time_stamp = unique_obs.time_stamp;
          num_show := num_show - 1;
        END IF;
      END IF;

    END LOOP;

  END LOOP;
  
  truncate sst_changes;
  
  create trigger sst_labels_and_times_trig 
    before INSERT OR UPDATE ON sst_prod
    FOR EACH ROW EXECUTE PROCEDURE sst_labels_and_times();
  
  return 'SET_sst_PROD_SHOW ' || num_show;

END;

Properties
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER

}}}

[[br]]

== Script populate_sst_map.sql ==
Run from the command line via psql during aggregation process.
 * truncate (drop all data) '''sst_map''' 
 * inserts values from '''sst_prod''' into '''sst_map''' where top_of_hour is 1.
{{{
begin;

truncate sst_map;

drop index sst_map__report_time_stamp;
drop index sst_map__report_time_stamp_z;

insert into sst_map (
  station_id,
  time_stamp,
  z,
  label_z,
  temperature_celcius,
  temperature_fahrenheit,
  lon,
  lat,
  title,
  institution,
  institution_url,
  institution_dods_url,
  source,
  refs,
  contact,
  report_time_stamp,
  the_geom,
  value_temperature_celcius,
  value_temperature_fahrenheit
)
select
  station_id,
  time_stamp,
  z,
  label_z,
  temperature_celcius,
  temperature_fahrenheit,
  lon,
  lat,
  title,
  institution,
  institution_url,
  institution_dods_url,

  source,
  refs,
  contact,
  report_time_stamp,
  the_geom,
  value_temperature_celcius,
  value_temperature_fahrenheit
from sst_prod
where top_of_hour = 1;

--
-- indexes
--

create index sst_map__report_time_stamp on sst_map(report_time_stamp);
create index sst_map__report_time_stamp_z on sst_map(report_time_stamp,label_z);

end;

}}}