1. One-Stop Shopping for MBARI Upper Water Column Data : Google Earth dynamic queries This page last changed on May 17, 2010 by mccann. Here is the setup procedure for creating a dynamic query from a relational database directly into Google Earth (following view-based refresh directions posted at http://code.google.com/apis/kml/documentation/kml_tut.html#network_links). The following two files are all that is needed to provide the capability viewable at http://dods.mbari.org/uwcd/viewUWCD.kml Create a "master" .kml file with the refresh parameters and a network link to the .cgi file:
       > pwd
       /var/www/dods_html/uwcd
       <187 elvis.shore.mbari.org /dods_html/uwcd> cat viewUWCD.kml
       <?xml version="1.0" encoding="UTF-8"?>
       <kml xmlns="http://earth.google.com/kml/2.2"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://earth.google.com/kml/2.2
           http://code.google.com/apis/kml/schema/kml22beta.xsd">

         <Folder>
           <name>MBARI Upper Water Column Data</name>
           <visibility>0</visibility>
           <open>0</open>
           <description>Check boxes to see data in your view.
           Queries begin 2 seconds after you stop navigating.</description>
           <NetworkLink>
             <name>BOG Bottle Data</name>
             <visibility>0</visibility>
             <open>0</open>
             <description>Query for Bottle Data</description>
             <refreshVisibility>0</refreshVisibility>
             <flyToView>0</flyToView>

             <Link>
               <href>http://elvis.shore.mbari.org/cgi-bin/bogKML.cgi</href>
               <refreshInterval>2</refreshInterval>
               <viewRefreshMode>onStop</viewRefreshMode>
               <viewRefreshTime>1</viewRefreshTime>
               <viewBoundScale>0.75</viewBoundScale>
             </Link>
           </NetworkLink>

         </Folder>
       </kml>
2. Create the .cgi file with the spatially constrained query and KML output:
       > pwd
       /var/www/cgi-bin
       <195 elvis.shore.mbari.org /www/cgi-bin> cat bogKML.cgi
       #!/usr/bin/perl

       # Script to query BOG Bottle data from Google Earth
       #
       # Mike McCann  16 april 20009
       # MBARI

       use DBI;

       #
       # Debugging stuff:  All debugging print statements are commented out with
       # '##'.  Must turn on text/html to see the debugging output, should you
       # need to do that.
       #
       ##print "Content-type: text/html\r\n\r\n";
       ##print "Hello, World.";

       ##print "String = $ENV{'QUERY_STRING'}\n\n<p>";
       @values = split(/&/, $ENV{'QUERY_STRING'});
       my %qVars = ();
       foreach $i (@values) {
               ($varname, $mydata) = split(/=/, $i);
               $qVars{lc $varname} = $mydata;
               ##print "The value of $varname is $mydata\n\n<p>";
       }

       #
       # Get parameters from Query String.  GE will provide bbox.  qsql is for
       # potential future use.
       #

       $bbox = $qVars{'bbox'};
       $qsql = $qVars{'qsql'};

       ##print "bbox = $bbox\n";

       if ( $bbox ) {
                       ($west, $south, $east, $north) = split(',', $bbox);
       }
       elsif ( $qsql ) {
                       # Use this SQL for the query below
       }
       else {  # Some default bounding box - for testing
                       ($west, $south, $east, $north) = (-124, 35.2, -122, 36);
       }

       #
       # Set up database connection (replace <...> entries with actual connection parameters)
       #
       ##print "\nConnecting to the Database<br>";
       my $dbh = DBI->connect("DBI:Sybase:server=<db_server>", "<ro_login>", "<ro_password>", {PrintError => 1});
       $dbh->do("use BOG");

       #
       # Count the records for the query and set up the stride for subsampling
       # - careful that we use the same join and where clause as we do below...
       #
       my $countSQL = <<EOS;
       SELECT     count(*)
       FROM       dbo.BCTD INNER JOIN
                     dbo.EXPEDITION ON dbo.BCTD.EXPD = dbo.EXPEDITION.expd
       WHERE     (dbo.EXPEDITION.dec_lat > $south) AND (dbo.EXPEDITION.dec_lat < $north) AND
                 (dbo.EXPEDITION.dec_long > $west) AND (dbo.EXPEDITION.dec_long < $east)
       EOS

       ##print "\nExecuting countSQL: $countSQL";
       my $sth = $dbh->prepare($countSQL);
       $sth->execute;
       while ( my $rrow = $sth->fetchrow_arrayref() ) {
               $numRows = $$rrow[0];
       }
       ##print "<br>numRows = $numRows\n";
       $bottlesToDisplay = 500;
       $stride = int($numRows / $bottlesToDisplay);
       $stride = 1 if $stride < 1;
       ##print "<br>stride = $stride\n";

       #
       # Construct SQL statements with DateTime given in ISO8601 format.  If SQL passed in then
       # construct that query with the additional ISO8601 date format.  Apply the stride from above.
       #
       my $sql;
       if ( $qsql ) {          # Add DateTime and put in some newlines for cleaner display in the description
                       # Need to add ISO date time to SELECT
                       $qsql =~ s/FROM/, rtrim(convert(char, CollectionEventDTG,126)) + 'Z' as DateTime \nFROM/;
                       $qsql =~ s/WHERE/\nWHERE/;
                       $qsql =~ s/ORDER/\nORDER/;
                       $sql = $qsql;
       }
       else {
       ##SELECT     dbo.EXPEDITION.dec_lat AS Latitude, dbo.EXPEDITION.dec_long AS Longitude,
       ##           dbo.BCTD.BOTTLE, dbo.EXPEDITION.cruise, dbo.BCTD.DEPTH, dbo.BCTD.CTRB_ID,
                       $sql = <<EOS;
       SELECT     *,
                  rtrim(convert(char, dbo.EXPEDITION.date_time,126)) + 'Z' as DateTime
       FROM       dbo.BCTD INNER JOIN
                     dbo.EXPEDITION ON dbo.BCTD.EXPD = dbo.EXPEDITION.expd
       WHERE     (dbo.EXPEDITION.dec_lat > $south) AND (dbo.EXPEDITION.dec_lat < $north) AND
                 (dbo.EXPEDITION.dec_long > $west) AND (dbo.EXPEDITION.dec_long < $east) AND
                 BCTD.CTD_BOTTLE_ID % $stride = 1
       ORDER BY  dbo.EXPEDITION.date_time
       EOS

       }

       #
       # Execute the SQL, and build the placemarks
       #
       ##print "\nExecuting sql: $sql";
       $sth = $dbh->prepare($sql);
       $sth->execute;

       my $placemarks = '';
       my $count = 0;
       while ( my $rrow = $sth->fetchrow_hashref() ) {
               ##foreach my $k ( keys %$rrow ) {
                       ##print "k = $k, v = " . $$rrow{$k} . "\n";
               ##}
               ##print "\n<br>";

               $lat = $$rrow{'dec_lat'};
               $lon = $$rrow{'dec_long'};
               $depth = $$rrow{'DEPTH'};

               unless ($lat && $lon && $depth) {
                               next;
               }

               $isodate = $$rrow{'DateTime'};
               $bottle = $$rrow{'BOTTLE'};
               $cruise = $$rrow{'cruise'};

               $name = "$cruise $bottle";

               $placemarks .= "<Placemark>\n<name>$name</name>\n";
               $placemarks .= "<TimeStamp>\n<when>$isodate</when>\n</TimeStamp>\n";
               $placemarks .= "<description><![CDATA[";
               $placemarks .= "<div>$name</div><br><br>\n";
               foreach my $k ( sort keys %$rrow ) {
                       $placemarks .= "<div><b>$k:</b> " . $$rrow{$k} . "</div>\n";
               }
               $placemarks .= "]]></description>\n";
               $placemarks .= "<styleUrl>#square_pair</styleUrl>\n";
               $placemarks .= "<Point>\n<altitudeMode>absolute</altitudeMode>\n";
               $placemarks .= "<coordinates>$lon,$lat,-${depth}</coordinates>\n</Point>\n";
               $placemarks .= "</Placemark>\n";

               $count++;

       }
       $dbh->disconnect;

       #
       # Build up KML and deliver it to the client
       #
       my $kml = <<EOK;
       <?xml version="1.0" encoding="UTF-8"?>
       <kml xmlns="http://earth.google.com/kml/2.1"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://earth.google.com/kml/2.1
           http://code.google.com/apis/kml/schema/kml21.xsd">

       <Folder>
       EOK
               if ( $qsql ) {
                       $kml .= "<name>BOG DB Bottle Query</name>\n";
               }
               else {
                       $kml .= "<name>Bottles</name>\n";
               }

               $kml .= <<EOK;
       <Document>
       <name>Individual Bottles</name>
               <Style id="sn_square_copy29">
                       <IconStyle>
                               <color>ffffff00</color>
                               <scale>0.5</scale>
                               <Icon>
                                       <href>http://maps.google.com/mapfiles/kml/shapes/square.png</href>
                               </Icon>
                       </IconStyle>
                       <LabelStyle>
                               <scale>0.7</scale>
                       </LabelStyle>
               </Style>
               <Style id="sh_square_copy13">
                       <IconStyle>
                               <color>ffffff00</color>
                               <scale>0.66</scale>
                               <Icon>
                                       <href>http://maps.google.com/mapfiles/kml/shapes/square.png</href>
                               </Icon>
                       </IconStyle>
                       <LabelStyle>
                               <scale>0.77</scale>
                       </LabelStyle>
                       <BalloonStyle>
                         <bgColor>ffffffff</bgColor>
                         <textColor>ff000000</textColor>
                         <text>\$[description]</text>
                         <displayMode>default</displayMode>
                       </BalloonStyle>
               </Style>
               <StyleMap id="square_pair">
                       <Pair>
                               <key>normal</key>
                               <styleUrl>#sn_square_copy29</styleUrl>
                       </Pair>
                       <Pair>
                               <key>highlight</key>
                               <styleUrl>#sh_square_copy13</styleUrl>
                       </Pair>
               </StyleMap>

       <visibility>0</visibility>
       <open>0</open>
       <description><![CDATA[$numRows Bottles in this view, showing every $stride<br><br><br><br>Resulting from query<br><pre>$sql</pre>]]></description>
       $placemarks
       </Document>
       </Folder>
       </kml>
       EOK

       print "Content-type: application/vnd.google-earth.kml+xml\r\n\r\n";
       print $kml;

Document generated by Confluence on Feb 04, 2026 08:40