Index: RS_timestamps/trunk/RS_timestamps/get_times.pl
===================================================================
---  (revision )
+++ RS_timestamps/trunk/RS_timestamps/get_times.pl (revision 94)
@@ -1,0 +1,194 @@
+#! /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;