root/RS_timestamps/trunk/RS_timestamps/get_times.pl

#! /usr/bin/perl -wT

## NAME: get_times.pl
##
## Gets latest RS overpass timestamp from SC web page
##
## This script gets the latest RS timestamps from a SC web page to insert into a local DB.  It then queries the DB for the overapss tiemstamps for various hourly offsets.
## These offset text files are used for labeling on the NCCOOS Interactive Map and  also for labeling the RS images generated by SEACOOS cachebot.

use lib ".";
use LWP::Simple;
use Date::Format;
use Date::Parse;
use DBI;

$dest_dir='/var/www/html/rs_timestamps/';  

### get html file
$url_base='http://nautilus.baruch.sc.edu/seacoos_misc/show_sea_coos_obs_time_ranges.php';
$target_raw = $dest_dir.'rs_timestamp.html';
getstore($url_base,$target_raw);

### parse through file and cut out relevant timestamps
@layers = ('avhrr_sst','modis_sst','modis_rgb_composite','oi_sst');

if(-e $target_raw)
{

my ($this_layer_time);
foreach (@layers) 
        {
        $this_layer = $_;
        $search_string = '\?layer_name='.$this_layer.'.+';

        open (TIME, $target_raw);
        @t = readline TIME;
        @timestring = grep(/$search_string/, @t);
        close TIME;

        ### cut out timestamp string only

        $timestring[0] =~ m/max_time_stamp=(.*?")/;
        $this_layer_time = $1;
        $this_layer_time =~ s/:00"$//;

        if ($this_layer =~ /avhrr_sst/) 
                {
                        ## insert TS into DB 
                        my ($dbh, $sql, $sth);
                        $dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
                                {
                                        PrintError => 1,
                                        RaiseError => 0
                                }) || die "$DBI::errstr";

                                $sql = "INSERT INTO avhrr_timestamps VALUES ('$this_layer_time')";                                                                                                                          
                                $dbh->do( $sql );
                                $dbh->disconnect();
                }
        elsif ($this_layer =~ /modis_sst/) 
                {
                        ## insert TS into DB 
                        my ($dbh, $sql, $sth);
                        $dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
                                {
                                        PrintError => 1,
                                        RaiseError => 0
                                }) || die "$DBI::errstr";
                                
                        $sql = "INSERT INTO modis_sst_timestamps VALUES ('$this_layer_time')";                                                                                                                          
                        $dbh->do( $sql );
                        $dbh->disconnect();
                }
                elsif ($this_layer =~ /modis_rgb_composite/) 
                {
                        ## insert TS into DB 
                        my ($dbh, $sql, $sth);
                        $dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
                                {
                                        PrintError => 1,
                                        RaiseError => 0
                                }) || die "$DBI::errstr";
                        $sql = "INSERT INTO modis_rgb_timestamps VALUES ('$this_layer_time')";                                                                                                                          
                        $dbh->do( $sql );
                        $dbh->disconnect();
                }
                elsif ($this_layer =~ /oi_sst/) {
                        ## insert TS into DB 
                        my ($dbh, $sql, $sth);
                        $dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'jcleary', '',
                                {
                                        PrintError => 1,
                                        RaiseError => 0
                                }) || die "$DBI::errstr";
                        $sql = "INSERT INTO oi_sst_timestamps VALUES ('$this_layer_time')";                                                                                                                          
                        $dbh->do( $sql );
                        $dbh->disconnect();
                }
                else 
                {}
        }

## Timestamps extraction for cached image labels
my ($timestamp,$raw_timestamp,$time,$pass_timestamp);
@offsets = ('2','3','4','5','6','8','7');

foreach (@offsets) 
        {
        $offset = $_;           

        ## Date subtraction and formatting for query
        my ($sec,$min,$hour,$mday,$mon,$year) = gmtime(time-60*60*$offset);
        my $format_year = $year + 1900;
        my $format_month = sprintf ("%02d",$mon+1);
        my $format_day = sprintf ("%02d",$mday);
        my $format_hour = sprintf ("%02d",$hour);

        # format query timestamp
        $timestamp = $format_year."_".$format_month."_".$format_day."_".$format_hour."_".$min."_00";
        
        @layers = ('avhrr','modis_sst','modis_rgb','quikscat');
        foreach (@layers) 
                {

                $this_layer = $_;
        
                ## lookup time in DB
                my ($dbh, $sql, $sth);
                $dbh = DBI->connect ( 'dbi:Pg:dbname=seacoos_test;host=coriolis.marine.unc.edu', 'lookup', 'lookup',
                        {
                                PrintError => 1,
                                RaiseError => 0
                        }) || die "$DBI::errstr";

                #select data from DB
                my $this_table = $this_layer."_timestamps";
                $sql= "select $this_layer from $this_table"
                ." where abs(extract(epoch from to_timestamp('$timestamp','YYYY_MM_DD_HH_MI_SS'))"
                ." - extract(epoch from $this_layer))"
                ." = (select min(abs((extract(epoch from to_timestamp('$timestamp','YYYY_MM_DD_HH_MI_SS')))"
                ." - extract(epoch from $this_layer)))"
                ." from $this_table)"
                ." and abs(extract(epoch from to_timestamp('$timestamp','YYYY_MM_DD_HH_MI_SS'))"
                ." - extract(epoch from $this_layer))"
                ." <= 60*60*24*2";

                $sth= $dbh->prepare( $sql );
                $sth->execute();
                $sth->bind_columns( \$raw_timestamp );
                $sth->fetch();

                $sth->finish;
                $dbh->disconnect;
                
                $time = str2time($raw_timestamp);
                $pass_timestamp = time2str("%m/%d/%Y %R", $time);
                        
                if ($this_layer =~ /avhrr/)                                                                                                                                               
                        {
                                open (FOO, ">$dest_dir/avhrrsst$offset.txt");
                                print {FOO } $pass_timestamp." UTC";
                                close FOO;
                        }
                elsif ($this_layer =~ /modis_sst/)                                                                                                                                            
                        {
                                open (FOO, ">$dest_dir/modissst$offset.txt");                                                                                                           
                                print {FOO } $pass_timestamp." UTC";                                                                                                                                              
                                close FOO;     
                        }               
                elsif ($this_layer =~ /modis_rgb/)                                                                                                                                            
                        {       
                                open (RGB, ">$dest_dir/modisrgb$offset.txt");
                                print {RGB } $pass_timestamp." UTC+";
                                close RGB;

                                open (CA, ">$dest_dir/modisca$offset.txt");                                                                                                                    
                                print {CA } $pass_timestamp." UTC";                                                                                                 
                                close CA;

                                open (ERGB, ">$dest_dir/modisergb$offset.txt");                                                                                                                    
                                print {ERGB } $pass_timestamp." UTC";                                                                                                                   
                                close ERGB;                                                                                                                        
                        }
                        elsif ($this_layer =~ /quikscat/)                                                                                                                                            
                        {
                                open (FOO, ">$dest_dir/quikscat$offset.txt");                                                                                                           
                                print {FOO } $pass_timestamp." UTC";                                                                                                                                              
                                close FOO;
                        }
                }       
        }
}

exit 0;