Wiki Navigation

SST processing example page

Table-set process

Bash script initiates SQL INSERT: data passes through the 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
Bash script calls 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.
Bash script calls 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.

SST table-set

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

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)

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;

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

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;

Download in other formats:

Plain Text