Add DEPTH Masl Values

=======May 11===============

Note BB data is at best until 12/13 season (Dec 12 for FRX and Feb 13 for HOR)... pending more BB data (stage)

BACT ENUM : No data aftr 2011
WLB : no BB!
ELB 364 / 508 -- may need interp

BACT_PROD :
Fryxell : Done, about 40 records (< 2013 )

===may 10===

many ways to do this - anyhow.

we actually need to feed Perl with LL type of files. And really, we only
need the Date-time, Stage and optionally, the 'flag' (estim vs. manual).
In reality, it does it matter to know whether the value is estimated?
we can say so in the metadata, describe all this process and then no
need to keep track of the flag.

all we need date-time and stage: datetime needed mostly needs
24 hours resolution.

to calculate the values, use the BB and LL data, although for recent
years, it seems that BB should suffice.

last adjustments went up to Oct-29-2011, although some files need
larger adjustments, many need from there on (3 years)

once we have stage-date-time files for the lakes in TV, we can simply
adapt the Perl script which would produces ".sql" files to update the
data-tables "DEPTH_MASL" columns.

To ensure my interpolations are good, see whether the process produces
interpolations made by Maciek before.

I downloaded the DATE_TIME, STAGE from BB where Date> last date (2011)
Divided those in 4 TV lakes (ELB, WLB, Fryx, Hoare )

Now, retouch script... retouched.

Now, download data from database. Need queries like this:
select "LOCATION NAME",DATE_TIME,"DEPTH (m)" from LIMNO_BACT_ENUM where DATE_TIME>=to_date('10/29/2011','MM/DD/YYYY') and "LOCATION NAME" like '%Fryx%' order by DATE_TIME;

and dump into file, which will be passed to Perl as ARG.

FILES:

=== update May 5===

would help to keep a list of tables and status (needs column, pending, post-2012, complete)

- - - - - - - - - name - - - - - - - - - - - - - ||- - - - - - - - - - - - - - status - - - - - - - - - - - - -
ancillary downling lakes || post-2012
ANCILLARY_SNYDER_HALOGENCHEM || post-2012
ANCILLARY_SNYDER_IODINESED || post-2012
LIMNO_ANCILLARY_ADCP || pending
LIMNO_BACT_ENUM || post-2012
LIMNO_BACT_PROD || post-2012
LIMNO_BATH_HYPSO_FUNC || pending *
LIMNO_BATH_VALUES || needs column *
LIMNO_BATH_VALUES_DIGITIZED || pending *
LIMNO_CHEMISTRY_RAW* || post-2012
LIMNO_CHL_A_DEPTH || post-2012
LIMNO_CHL_A_HOARE_BENTHIC || pending *
LIMNO_CTD || post-2012
LIMNO_DIC || post-2012
LIMNO_DOC_RAW* || post-2012
LIMNO_FLAGELLATE_GRAZING || post-2012
..
..

Back and forth with Maciek -- we do not really have lookup tables, we need to build them using
a combo of the BB 'stage' data and the manual measurements. Although, the BB may be enough,
Maciek says.

The idea is to linearly interpolate for a given dataset. Have the "date". make a date-time handable
in matlab, interpolate, etc. then we insert in the database. This is sort of crude. There are
helping notes,. seudo code and code.

Here is the last email exchange:

> Finally today, I have been working some more about whether to
> 'automate' this in the database, or interpolate, make a lookup-table,
> and load extra data in each table that needs a depth referenced to the
> Sea level.
>
> I think the later would be more desirable, for performance reasons -
> calculating on the fly thousands of number may be too tolling, plus,
> there is some complexity on the queries and logic. Perhaps is best to
> just append the values to the new Depth MASL columns.

I didn't really think about this and I guess I forgot that every time someones obtains data from the database, it is calculating things on the fly. So yes, appending it sounds like a much better idea. Now, I'm assuming that you will be able to simply (maybe not so simply) use the BB stage file to calculate DASL column, correct?

>
> I have the Perl scrip to do update existing tables from this
> lookup-tables.

Ok, I think you just answered my question above.

> However, Ive been looking into recreating the stage files the way you
> provided me (except that standardized to dec-10-2008). First is the
> source data -- you seem to be using a mix of values from the Blue Box
> and the Lake Levels for the "Measured" stages.
>
> Question is, do you calibrate the BB to the "manual" measurement that
> is reported in the Lake Levels (lately by Hilary), if so, then I see
> how is totally OK to use both source of data for the interpolation.
> Id object if those mixed.

Yes, I have been using two sources (BB and 'manual'). BB stage is calibrated to the 'manual' lake levels so they are comparable. As a matter a fact, stage without manual data would be meaningless. So usage of both sources is ok. To be honest, the only reason to use 'manual' data is when there are long-term gaps in stage data. However, lately we've been getting pretty good stage record (I think) so 'manual' is not required. I wouldn't worry about it and/or include it in your Perl script

>
> As for the matlab, what I'd do is user "interp1" (a linear
> interpolation)..
>
> if we have a measured stage at time "t_a" "S(t_a)" and a stage at a
> later time "S(t_b)", then i do a query to see how many time points are
> between t_a and t_b. is there are 27433,
>
> this query is to see how many time steps are in the measure stage gaps
> for the BB data...
>
> SQL> select count(date_time) from limno_blue_box where
> date_time>=to_date('11/09/2008','mm/dd/yyyy') and
> date_time<=to_date('11/25/2009','mm/dd/yyyy') and "LOCATION NAME" like
> '%oare%'order by date_time;
>
> SQL> 27433
>
> this is a command line in matlab to get me corresponding interpolated
> stage for the gapped data.
> --
> >> t_coarse = [1, 27433 ]
> >> stage_at_B = 74.59;
> >> stage_at_A = 74.3365;
> >> stage_coarse = [ stage_at_A,stage_at_B]
> >> t_fine = 1:27433;
> >> stage_fine = = interp1(t_coarse, stage_coarse, t_fine );
>
> and then I can plug the estimated stage values in the database...
>
> i suppose this pseudo - script can be refined using actual dates, and
> the "datenum" function, that way there is perhaps less worry about
> non-contiguous dates, but if it is just a lookup table, i dont really
> care.
>
> i'd just calculate how many 20minute- spaced points are between the
> time-of-measured-stage_at_A and time-of-measured-stage at B.
>
> If you already have an m-script, send it my way. otherwise, just
> confirm this is how you calculate the interpolated values,
> specifically, your SOURCE stage-time pairs, whether you are using Blue
> Box and the manual values, and whether those are congruent sources.

This is pretty much what I've been doing for the missing data and associated dates (ie. linear interpolations). To be honest, missing data is easy to handle but when the dataset has missing dates, it becomes unnecessary complicated to deal with it. I have a Matlab script to fix it (see attached). It's not really clean or pretty but it does the job. This script is meant to be run over the ENTIRE dataset. Inputs are start date and end date. It takes LTER date format and converts to Matlab serial time. Then it generates time increments based on the start and end date (this accounts for the leap year). Next, the script indexes the original file and generates a vector full of NaNs for the a complete time series. Finally, it insets the data to the NaN vector, which is a complete time series, leaving missing data as NaN but outputting missing dates! However, it only works on vectors, no matrices. You'll have to modify it but this is a backbone of it and I hope it will help.

Thanks,
-Maciek

===March 2014 ===

There is some urgency to extend the MASL data to all lakes, all times, just requested Kyle and Maciek for LL_Adjustment data

UPdate---

Except for the PAR measurements, all the tables with Depth had the addition of the Depth MASL,
using the stage at noon of the 2 lakes for which we have values.

Metadata needs to be updated further, but the data was added.

Would be good to revisit for the PAR, those have more time granularity.

0-----------------------

For Lake Hoare and Fryxell, we have the stage values over time. Some are estimated, some are measured, estimation of the stage for dates when there is no direct measurement was based on a linear interpolation. Files were provided by Maciek, for Lake Hoare (LH_LL_adjustment) and Fryxel (LF_LL_adjustment).

The files were broken in two excell csv files, note, date times where provided as MM/DD/YY for 3 or the 4 files.

I wrote a script that created a lookup table for the stage for a given day for each lake. Then it created a Depth_MASL value by adding the Stage value on that day to the depth measured in that day (depth was relative to the top of the lake).

The output is a series of "SQL" update statements of this sort:
UPDATE HOARE.LIMNO_DO_TEMP SET "DEPTH MASL"= 31.07288098 WHERE ( "LOCATION NAME"='Lake Fryxell' AND DATE_TIME=to_date('12/22/1999','MM/DD/YYYY') AND "DEPTH (m)"=14 );

I ran these collections using Toad for Oracle "Editor" function, the option of "Use as script" (a bolt over paper icon), opening the sql file in the editor. I committed after each file update.

Notes: Only the last stage for a given day was used.

Notes: Some tables has the "LOCATION NAME" column as LOCATION_NAME, the SQL script will give an error.

Notes: Some ancillary data in EML points to "dat" files. Majority of those ancillary are not in the DB, but a few are. What is the plan here?

This is how the script looks
#########################################################################
##
## Perl script add DEPTH MASL Inigo San Gil, June 2012
##
##########################################################################
##
## We need to populate the new column "DEPTH MASL" with the corrected
## Depth values, that is, values referred to the Average Mean Sea Level
## reference, expressed by the Datum.
##
## For that we need the "stage" at the given date time, the depth relative
## to the lake ice cap "DEPTH (m)", and the date time.##
##
## The formula is:
## Depth_masl(t) = Depth_m(t) + Stage(t)
##
## The stage data is in Dropbox files _LL_adjustment_72_95 and _v2
##
###########################################################################

my %lfsta=(); my %lhsta=();
open(LL,'LF_LL_adjustment_72_95.csv') or die "coulndt open file LF_LL_adjustment_72_95.csv \n";
LL:
while($line=){
if ($line=~/DATASET_CODE/){
}else{
@lldat=split(/,/,$line);
$dtime=$lldat[3];
$stage=$lldat[4];
$flag=$lldat[6];
$lfsta{$dtime}=$stage;
#print "TIME $dtime, STAG $stage, FLAG $flag \n";
}# print $line;
next LL;
}
close(LL);

open(LL,'LF_LL_adjustment_v2.csv') or die "coulndt open file LF_LL_adjustment_v2.csv \n";
LL:
while($line=){
if ($line=~/DATASET_CODE/){
}else{
@lldat=split(/,/,$line);
$dtime=$lldat[3];
$stage=$lldat[4];
$flag=$lldat[6];
if($dtime=~/\s/){$dtime=$`;}
$dtime=~/(\d+)\/(\d+)\/(\d+)/;
$m=$1;$d=$2;$y=$3;
if($y>90){
$y=$y+1900;
}else{
$y=$y+2000;
}
$dtime=$m.'/'.$d.'/'.$y;
$lfsta{$dtime}=$stage;
#print "TIME $dtime, STAG $stage, FLAG $flag \n";
}# print $line;
next LL;
}
close(LL);

open(LL,'LH_LL_adjustment_72_94.csv') or die "coulndt open file LH_LL_adjustment_72_94.csv \n";
LL:
while($line=){
if ($line=~/DATASET_CODE/){
}else{
@lldat=split(/,/,$line);
$dtime=$lldat[3];
$stage=$lldat[4];
$flag=$lldat[6];
$dtime=~/(\d+)\/(\d+)\/(\d+)/;
$m=$1;$d=$2;$y=$3;
if($y>90){
$y=$y+1900;
}else{
$y=$y+2000;
}
$dtime=$m.'/'.$d.'/'.$y;
$lhsta{$dtime}=$stage;
#print "TIME $dtime, STAG $stage, FLAG $flag \n";
}# print $line;
next LL;
}
close(LL);

open(LL,'LH_LL_adjustment_v2.csv') or die "coulndt open file LH_LL_adjustment_v2.csv \n";
LL:
while($line=){
if ($line=~/DATASET_CODE/){
}else{
@lldat=split(/,/,$line);
$dtime=$lldat[3];
$stage=$lldat[4];
$flag=$lldat[6];
if($dtime=~/\s/){$dtime=$`;}
$dtime=~/(\d+)\/(\d+)\/(\d+)/;
$m=$1;$d=$2;$y=$3;
if($y>90){
$y=$y+1900;
}else{
$y=$y+2000;
}
$dtime=$m.'/'.$d.'/'.$y;
# print "TIME $dtime LHSTAG $stage \n";
$lhsta{$dtime}=$stage;
#print "TIME $dtime, STAG $stage, FLAG $flag \n";
}# print $line;
next LL;
}
close(LL);

open(DP,$ARGV[0]) or die "coulndt open file $ARGV[0] \n";
$ARGV[0]=~/(\w+)\.txt/;
$tmp=$1;
$fn= uc $tmp;

UP:
while ($lr=){
chop($lr);
if ($lr=~/LOCATIO/){
}else{
@dat=split(/,/,$lr);
$tim=$dat[1];
$lake=$dat[0];
$depth=$dat[2];
}

if($lake=~/Fryxell/){
$stag=$lfsta{$tim};
# print "TIME $tim LFSTAG $stag \n";
}elsif($lake=~/Hoare/){
$stag=$lhsta{$tim};
# print "TIME $tim LHSTAGE $stag \n";
}else{
next UP;
}
$depth_masl= $depth + $stag;
if ($depth=~/NULL/i){
}else{
print "UPDATE HOARE.$fn SET \"DEPTH MASL\"= $depth_masl WHERE ( \"LOCATION NAME\"=\'$lake\' AND DATE_TIME=to_date(\'$tim\',\'MM/DD/YYYY\') AND \"DEPTH (m)\"=$depth );\n";
}
}
close(F);

Status: 

Priority: 

High