Using the Grid DataBlade

Make sure that the Informix database has been properly configured.(see ConfiguringInformix).

Please refer to InstallingGridDataBlade for configuring the Grid DataBlade and its associated libraries.

The following is a detailed description of our experiences so far with Grid DataBlade.

Grid Import-Export Format (GIEF)

The Grid DataBlade reads and writes a subset of NetCDF files directly on a client machine, simply through the execution of the appropriate SQL statement. The statements can be executed by a user-written client program (using JDBC, ODBC, or ESQL/C) or simply by statements entered into any command line based database tool, (such as the Informix-supplied dbaccess program). The subset of NetCDF supported is a dialect called GIEF (Grid Import-Export Format).

GIEF is a NetCDF dialect that is general enough to represent any grid of primitive scalar data elements (i.e., 1, 2 or 4-byte integers, 4 or 8-byte floating point values). It allows source grid files of various formats to have one internal uniform representation. Conversion programs can be employed to convert grid files between other formats (e.g., GeoTiff, GRIB, and dialects of NetCDF) and one or more GIEF files.

As compared to the NetCDF format, the GIEF format has its set of specifications:

• Each grid must have exactly 4 dimensions (time,level,row,column) corresponding to (t,z,y,x) respectively.To support 1D, 2D, or 3D grid, one or more of these dimensions will have a value of 1. The dimensions must be declared in the same order in all grid variable declarations in a particular gief file.

• The Grid Variables are 4D arrays that store the value of a particular parameter type at a particular time, level, row, and column.

• If both time and level dimensions are nonuniform then there must be 2 Coordinate Variables: time and level.The level variable must be defined as float or double. The time variable must be defined as double.

• Missing Data is represented by _FillValue field

• srtext is the OGC well-known-text form of a spatial reference system.

• The translation attribute denotes the location of the first grid element in the spatial projection. It has four double precision values.

• Affine transformation specifies which dimension varies fastest and by how much.

Mapping names from GIEF to the Database

When a GIEF file is loaded into a row of a database table, the information contained in its variables and attributes must be mapped to columns in the database. This mapping is as follows:

• 1D variables define the nonuniform axis characteristics of a grdvalue stored in a column called grid
• Srtext, translation, and affine_transformation global attributes define the SRID and basis vector attributes of the grdvalue
• Variables containing grids are represented as fields in the grdvalue. The names of the variables are stored in a column called field_names as a comma-separated list. For example, if the GIEF file contained the variables pressure, temperature, and salinity, the field_names column might have the value “pressure, temperature, salinity” (the exact order of the names in the list depends on the order in which they appear in the GIEF file)
• The names of the dimensions are stored in a column called dim_names as a comma-separated list
• The names of the dimensions are stored in a column called dim_names as a comma-separated list
• A global attribute named $t is mapped to a database column called g_$t. For example, a global attribute called center_id would be mapped to a column called g_center_id
• The values of variable specific attribute (i.e., FillValue attribute) of all variables are stored in a collection column called fillvalues. Users should make sure that it is declared as a list column
• An attribute called $t of a variable called $v is mapped to a column called l_$v__$t (note that there are two underscores separating $v and $t). For example, an attribute called range of a variable called depth would be mapped to a column called l_depth__range

An example GIEF file metadata

netcdf Tempvar {
dimensions:
time = 1 ;
depth = 1 ;
lat = 179 ;
lon = 360 ;

variables:
double time(time) ;
double depth(depth) ;
float TMP(time, depth, lat, lon) ;
TMP:_FillValue = -999.f ;

// global attributes:
:Conventions = "GIEF" ;
:srtext = "GEOGCS[\"GCS_WGS_1984\",DATUM[\"D_WGS_1984\",SPHEROID[\"WGS_1984\",6378137,298.257223563]],
PRIMEM[\"Greenwich\",0],UNIT[\"Degree\",0.0174532925199433]],PROJECTION[\"Equidistant_Cylindrical\"],
PARAMETER[\"False_Easting\",0],PARAMETER[\"False_Northing\",0],PARAMETER[\"Central_Meridian\",0],
PARAMETER[\"Standard_Parallel_1\",60],UNIT[\"Meter\",1]]" ;
:affine_transformation = 0., 0., 0., 1., 0., 0., 1., 0., 0., 1., 0., 0., 1., 0., 0., 0. ;
:translation = 0., -89., 0., 0. ;

Program for converting NetCDF file to GIEF format

A simple java program for converting a FOAM one degree NetCDF file is given below. Please make sure that you have the latest NetCDF libraries (http://www.unidata.ucar.edu/packages/netcdf/index.html) before running this program.

import ucar.nc2.*;
import ucar.ma2.*;
import ucar.nc2.dataset.*;

import java.io.*;
import java.util.*;
import java.nio.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

public class FOAMOne
{
public void writeToFile(NetcdfFile ds, String origFilename, String fileName, String[] variable,
 double fillValue, double days) throws IOException
  {

String srtext = "GEOGCS[\"GCS_WGS_1984\",DATUM[\"D_WGS_1984\",SPHEROID[\"WGS_1984\",6378137.0,
298.257223563]],PRIMEM[\"Greenwich\",0.0],UNIT[\"Degree\",0.0174532925199433]],
PROJECTION[\"Equidistant_Cylindrical\"],PARAMETER[\"False_Easting\",0.0],PARAMETER[\"False_Northing\",
0.0],PARAMETER[\"Central_Meridian\",0.0],PARAMETER[\"Standard_Parallel_1\",60.0],UNIT[\"Meter\",1.0]]";

      String myTime = null;

      /* rank (number of dimensions) for each variable */
      int RANK_time = 1;
      int RANK_level = 1;
      int RANK_TMP = 4;

      /* attribute vectors */
      double [] TMP__FillValue  =  new double[1];
      double [] cdf_translation =  new double[4];
      double [] cdf_affinetrans =  new double[16];

      //starting point is at longitude 0 and latitude -89.0
      cdf_translation[0] = 0.;
      cdf_translation[1] = -89.0;
      cdf_translation[2] = 0.;
      cdf_translation[3] = 0.;

      //longitude varies fastest followed by latitude and each change by one degree
      cdf_affinetrans[0] = 0.;
      cdf_affinetrans[1] = 0.;
      cdf_affinetrans[2] = 0.;
      cdf_affinetrans[3] = 1.;
      cdf_affinetrans[4] = 0.;
      cdf_affinetrans[5] = 0.;
      cdf_affinetrans[6] = 1.;
      cdf_affinetrans[7] = 0.;
      cdf_affinetrans[8] = 0.;
      cdf_affinetrans[9] = 1.;
      cdf_affinetrans[10] = 0.;
      cdf_affinetrans[11] = 0.;
      cdf_affinetrans[12] = 1.;
      cdf_affinetrans[13] = 0.;
      cdf_affinetrans[14] = 0.;
      cdf_affinetrans[15] = 0.;

      //shape of variables with 4 dimension
      int [] varShape = new int[4];

      /* enter define mode */
      NetcdfFileWriteable ncfile = new NetcdfFileWriteable();
      ncfile.setName(fileName);

      // copy dimensions
      HashMap dimHash = new HashMap();
      Iterator iter = ds.getDimensionIterator();

      varShape[0] = 1;

      int k=1;

      while (iter.hasNext())
      {
           Dimension oldD = (Dimension) iter.next();
           Dimension newD = ncfile.addDimension(oldD.getName(), 
                                         oldD.isUnlimited() ? -1 : oldD.getLength());
           dimHash.put( newD.getName(), newD);
           varShape[k] = oldD.getLength();
           k++;
      }

      /*store time and depth as variables*/
      Dimension [] array_time_dim =  new Dimension[RANK_time];
      Dimension  time_dim = ncfile.addDimension("time", 1);
      array_time_dim[0] = time_dim;
      ncfile.addVariable("time", double.class, array_time_dim);

      Variable depthVar = ds.findVariable("depth");
      Dimension[] depthDim = new Dimension[ depthVar.getRank()];
      List depthList = depthVar.getDimensions();

      for (int i=0; i< depthList.size(); i++)
      {
          Dimension tempD = (Dimension) depthList.get(i);
          depthDim[i] = (Dimension) dimHash.get( tempD.getName());
      }
      ncfile.addVariable( depthVar.getName(), depthVar.getElementType(), depthDim);
      ArrayAbstract dataArrLevel = (ArrayAbstract) depthVar.read();

      for (int i=0; i<variable.length; i++)
      {
         Variable tempVar = ds.findVariable(variable[i]);

         // copy dimensions
         // assuming file does not contain time as a dimension
         // but has depth, lon, and lat

         Dimension[] data4dim = new Dimension[ tempVar.getRank() + 1];
         List dimvList = tempVar.getDimensions();
         int t=1;
         data4dim[0] = time_dim;

         for (int j=0; j< dimvList.size(); j++)
         {
             Dimension oldD = (Dimension) dimvList.get(j);
             data4dim[t] = (Dimension) dimHash.get( oldD.getName());
             t++;
         }
         ncfile.addVariable( tempVar.getName(), tempVar.getElementType(), data4dim);

         /* assign attributes */
         TMP__FillValue[0] = fillValue;
         ncfile.addVariableAttribute(tempVar.getName(),"_FillValue", TMP__FillValue);

         List attList2 = ds.getGlobalAttributes();
         for (int j=0; j<attList2.size(); j++)
         {
             Attribute att2 = (Attribute) attList2.get(j);
             if (att2.getName() == "field_date") //  mm/dd/yyyy
             {
                myTime = att2.getStringValue();
             }
         }

         double [] timetesp = {days};

         /* assign global attributes */
         ncfile.addGlobalAttribute("translation", cdf_translation);
         ncfile.addGlobalAttribute("affine_transformation", cdf_affinetrans);
         ncfile.addGlobalAttribute("srtext", srtext);
         ncfile.addGlobalAttribute("Conventions", "GIEF");
      }

      /* now store time */
      double [] time = new double[1];
      time[0] = days;

      ArrayAbstract dataArrTime = ArrayAbstract.factory(time);

      /* leave define mode */
      ncfile.create();

      /*store data here*/
      ncfile.write("time", dataArrTime);
      ncfile.write(depthVar.getName(), dataArrLevel);

      for (int i=0; i<variable.length; i++)
      {
         Variable tempVar = ds.findVariable(variable[i]);
         ArrayAbstract data2D = (ArrayAbstract) tempVar.read();
         float[] data1D = (float[])data2D.copyTo1DJavaArray();
         ArrayAbstract data4D = ArrayAbstract.factory(float.class,varShape,data1D);
         ncfile.write(tempVar.getName(), data4D);
       }
      ncfile.close();
   }

    public static void main (String[] args)
    {
      try
      {
         double days = 6940;
         String [] filenames =
         {
            "FOAM_20030101.0.nc",
         };

         for (int i=0; i<filenames.length; i++)
         {
            String path = "/data/" + filenames[i];
            FOAMOne myFOAMOne = new FOAMOne();
            String newFilename = filenames[i];
            NetcdfFile ds = new NetcdfFile(path);
            File giefFile = new File(newFilename);
            double fillValue = -999.0;
            System.out.println(days);
            String [] variables = {"TMP"};
            myFOAMOne.writeToFile(ds,path,giefFile.getPath(),variables,fillValue,days);
            days++;
          }
      }
      catch (Exception e)
      {
         e.printStackTrace();
      }
    }
}

Preparing the database

Before accessing information in a database, client applications must connect to the database server environment. To connect to and disconnect from a database server, SQL statements can be issued from the following client programs can be issued using DB-Access, SQL Editor, IBM Informix ESQL/C, IBM Informix ODBC Driver or IBM Informix JDBC Driver.

In this tutorial, we are going to use the DB-Access client program as well as the IBM Informix JDBC Driver.

For information about creating databases, see IBM Informix Database Design and Implementation Guide and IBM Informix Guide to SQL: Tutorial. For information about how to use client applications, see the IBM Informix DB-Access User's Guide,IBM Informix ESQL/C Programmer's Manual, IBM Informix ODBC Driver Programmer's Manual, or IBM Informix JDBC Driver Programmer's Guide.

Starting the database

If the database server is not up and running, execute the oninit command. This starts the database server and leaves it in online mode.

Launching DB-Access

There are several ways to use DB-Access. Please refer to DB-Access User's Guide for more information. If your path includes $INFORMIXDIR/bin, dbaccess is the simplest way to start DB-Access. Without arguments, the single word dbaccess starts the main menu with no database selected and no options activated. You can then select submenus from the main menu.

Creating a table

We need to create a table that contains at least a column of type GRDValue. The GRDValue datatype stores a 4D grid of user data along with some metadata.

create table foamvar
(
   grid_id SERIAL PRIMARY KEY,
   varname varchar(15),
   startDate DATE,
   endDate DATE,
   grid  grdvalue,
   dim_names varchar(50,0),
   field_names varchar(25,0),
   fillvalues list(float not null),
   g_conventions varchar(5,0)
);

Loading data into table

The following script can be run from DB-Access or in a shell script.It loads a single grid in the table.

grdfromgief("/opt/informix/extend/Grid.1.0.1.0/examples/test/timeseries/converter/NATL_1_01012004.nc",
"foamvar");

In case, we want to store data from more than one file in same grid, then we can use :

#!/bin/csh
set CURDIR = `pwd`

set GRID_FILES = ( \
FOAM_20030101.0.nc \
FOAM_20030102.0.nc \
FOAM_20030103.0.nc \
FOAM_20030104.0.nc \
FOAM_20030105.0.nc \
FOAM_20030106.0.nc \
FOAM_20030107.0.nc \
FOAM_20030108.0.nc \
FOAM_20030109.0.nc \
FOAM_20030110.0.nc \
FOAM_20030111.0.nc \
FOAM_20030112.0.nc \
FOAM_20030113.0.nc \
FOAM_20030114.0.nc \
FOAM_20030115.0.nc \
FOAM_20030116.0.nc \
FOAM_20030117.0.nc \
FOAM_20030118.0.nc \
FOAM_20030119.0.nc \
FOAM_20030120.0.nc \
FOAM_20030121.0.nc \
FOAM_20030122.0.nc \
FOAM_20030123.0.nc \
FOAM_20030124.0.nc \
FOAM_20030125.0.nc \
FOAM_20030126.0.nc \
FOAM_20030127.0.nc \
FOAM_20030128.0.nc \
FOAM_20030129.0.nc \
FOAM_20030130.0.nc \
FOAM_20030131.0.nc )

set NUM_TO_LOAD = $#GRID_FILES

echo starting load of all but first file

dbaccess grdtest - << XXX

delete from tempvar;

execute procedure
grdfromgief("${CURDIR}/$GRID_FILES[1]","tempvar");

--
-- make sure we don't end up with duplicate grids in the database.
--

insert into foamvar
select
    13,
    "TMP",
    "01/01/2003",
    "01/31/2003",
    GRDExtend(grid ,'((dim_sizes ${NUM_TO_LOAD} 1 1 1))'),
    dim_names,
    field_names,
    fillvalues,
    g_Conventions
from tempvar;

XXX
if( $status ) then
   echo master grid creation failed
   exit -1
endif

echo starting load of all but first file

foreach AFILE ( $GRID_FILES[2-] )

  echo starting load of file $AFILE
  dbaccess grdtest - << XXX

  delete from tempvar;

  execute procedure
  grdfromgief("${CURDIR}/$AFILE","tempvar");

  update foamvar set grid = (select unique grid from tempvar)
  where
       grid_id = 13;

XXX
end

Extracting data from the database

Data can be extracted using SQL expressions, from applications using Java or using Native interfaces offered by the grid DataBlade and Informix APIs respectively. In this tutorial, we shall consider SQL expressions and Java programs. In case of Java, the programs are dependent on the following classes (usually set in classpath) :

setenv CLASSPATH ${INFORMIXDIR}/jdbc/ifxjdbc.jar:${INFORMIXDIR}/extend/Grid.1.0.1.0/clientlibs/grid.jar:.
 

SQL expressions

Extracting a timeseries

select grdextract(grid,'((translation -30.0 50.0 0 0)(dim_names time depth lat lon)(dim_sizes 175 1 
1 1)(affine_transformation 0 0 0 1 0 0 1 0 0 1 0 0 1 0 0 0)(nonuniform time 7305 7306 7307 7308 
....7479)(nonuniform depth 5)(interpolation (time linear)))'::GRDSpec) from foamvar where grid_id <= 6;
 

The following expression extracts a timeseries for 175 days (roughly 6 months) at longitude -30 degrees and latitude 50 degrees. In the present setup, each table is designed to store a month's data and therefore to extract data from several grids, linear interpolation on the time axis has been enabled.

Exporting a grid to a file

select grdrowtogief('${curdir}/temperature1.nc', 'foamvar', rowid, '((translation 0.0 -89.0  0  0)
(dim_names time depth lat lon)(dim_sizes 1 1 179 360)(affine_transformation 0 0 0 1 0 0 1 0 0 1 0 0 
1 0 0 0)(nonuniform time  6940.0)(nonuniform depth 5)))'::grdspec) from foamvar where grid_id = 13;
 

The following expression exports a subset of the grid from the database to a GIEF file. In this case, a grid of temperature starting at longitude 0 degrees, latitude -89 and extended to longitude 360 and latitude 89 every one degree sampled at level 5 at time 6940 (1st January 2003).

• The resulting file was then viewed using the ncBrowse graphics package (http://www.epic.noaa.gov/java/ncBrowse/).

Java program

This is the Java version of the SQL expression used to extract timeseries from previous example. With little modification, the program can be used to generate a subset of the grid as well. In addition to this, we are using the Scientific Graphics Toolkit (http://www.epic.noaa.gov/java/sgt/) for generating a timeseries graph from the data.

• Extracting a timeseries

import java.awt.*;
import java.awt.event.*;
import javax.swing.*;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.util.*;
import com.barrodale.grid.*;

import gov.noaa.pmel.sgt.dm.SGTData;
import gov.noaa.pmel.sgt.dm.SGTMetaData;
import gov.noaa.pmel.sgt.dm.SimpleLine;
import gov.noaa.pmel.sgt.dm.SimpleGrid;

import gov.noaa.pmel.util.GeoDateArray;
import gov.noaa.pmel.util.GeoDate;
import gov.noaa.pmel.util.IllegalTimeValue;
import gov.noaa.pmel.sgt.swing.JPlotLayout;
import gov.noaa.pmel.util.Point2D;

public class testDB extends JFrame
{
   String url = null;
   Connection conn = null;

   private JPlotLayout layout_;

   public static void main (String args[])
   {
      new testDB();
   }

   public void generate(double[] axis, float[] dataD)
   {

      SimpleLine line = null;
      SGTMetaData xMeta = null;
      SGTMetaData yMeta = null;

      xMeta = new SGTMetaData("X axis", "days");
      yMeta = new SGTMetaData("Y axis", "temperature");

      /**
      * Create a SimpleLine from the time coordinate and data variable.  Set the
      * metadata.
      */
      double [] myData = new double[dataD.length];

      for (int i=0; i<dataD.length; i++)
         myData[i] = dataD[i];


      line = new SimpleLine(axis, myData, "Temperature");
      line.setXMetaData(xMeta);
      line.setYMetaData(yMeta);

      /*
      * Create JPlotLayout instance for SGTLine data.  X and Y axes
      * are NOT time, and no image is provided.
      */

      layout_ = new JPlotLayout(false, false, false,
                            "Temperature Data",
                             null,
                             false);

      layout_.setBatch(true);

      layout_.setTitles("Temperature Timeseries Data", "", "");

      layout_.setTitleHeightP(0.2, 0.2);
      getContentPane().setLayout(new BorderLayout());
      getContentPane().add(layout_, BorderLayout.CENTER);
      pack();
      setVisible(true);

      SGTData data = line;

      layout_.addData(data, data.getTitle());
      layout_.setBatch(false);

    }

    testDB()
    {
       url = "jdbc:informix-sqli://marlow:1527/grdtest:INFORMIXSERVER=reading;
                                                            user=informix;password=passwd";
       // --------------
       // Loading driver
       // --------------
       try
       {
           Class.forName("com.informix.jdbc.IfxDriver");
       }
       catch (java.lang.ClassNotFoundException e)
       {
           System.out.println("\n***ERROR: " + e.getMessage());
           e.printStackTrace();
           return;
       }

       // ------------------
       // Getting connection
       // ------------------
       try
       {
           conn = DriverManager.getConnection(url);
       }
       catch (SQLException e)
       {
          e.printStackTrace();
          return;
       }

       try
       {
          // get the type maps
          java.util.Map customtypemap = conn.getTypeMap();
          if (customtypemap == null)
          {
          }
          customtypemap.put("grdvalue", com.barrodale.grid.GRDValue.class);
          customtypemap.put("grdspec", com.barrodale.grid.GRDSpec.class);

          // registering the classes.
          int xSize = 1;
          int ySize = 1;
          int zSize = 1;
          int tSize = 175;

          double [] time = new double[tSize];

          for (int i=0; i<tSize; i++)
             time[i] = 7305 + i;

          StringBuffer t = new StringBuffer();
          for(int i = 0; i < time.length; i++ )
          {
             t.append(" "+ time[i]);
          }
          String times_as_a_string = t.toString();

          GRDSpec theSpec = new GRDSpec();
          theSpec.setNumSamples( new int[] {tSize,zSize,ySize,xSize});
          theSpec.setDimNames( new String[] {"time","depth","lat","lon"});
          theSpec.setStartPt( new double [] { -30.0, 50.0, 0, 0});
          theSpec.setBasisVector(0, new double[] {0,0,0,1});
          theSpec.setBasisVector(1, new double[] {0,0,1,0});
          theSpec.setBasisVector(2, new double[] {0,1,0,0});
          theSpec.setBasisVector(3, new double[] {1,0,0,0});
          theSpec.setNonUniformDims(0, time);
          theSpec.setNonUniformDims(1, new double[]{5});
          theSpec.addInterpolation("time", 1);

          String query = "Select GRDExtract(grid,?) from foamvar where grid_id <= 6";

          PreparedStatement pstmt = conn.prepareStatement(query);
          pstmt.setObject(1, theSpec);
          ResultSet results = pstmt.executeQuery();

          int count = 0 ;
          float[] dataArray = null;
          float[] data = new float[xSize*ySize*tSize];

          while (results.next())
          {
            GRDValue value;
            value = (GRDValue) results.getObject(1);
            AbstractDataField f = value.getFieldByPosition(0);
            dataArray = (float[])f.getElements();

            for (int j=0; j<dataArray.length; j++)
            {
               if( f.isValid(j))
               {
                  data[count] = dataArray[j];
                  count++;
               }
               else
               {
               }
            }
            count = count - (xSize*ySize);
            count--;
          }
          generate(time, data);
          results.close();
          pstmt.close();
       }
       catch (SQLException e)
       {
          System.out.println("error binding types");
          e.printStackTrace();
       }

       try
       {
         conn.close();
       }
       catch (SQLException e)
       {
         System.out.println("***\nERROR: " + e.getMessage());
         e.printStackTrace();
       }
    }
}