Encoding Relational Tables in NetCDF

Back to Questions and Comments about CF-1.6 Station Data Convention

back to Questions and Comments about CF-1.6

Rational Behind this Proposal

The WCS service has potential to grow beyond regular, gridded coverages. Coverage types that need to be described are for example:

• station point data
• station trajectory data
• lidar data
• moving station data, like aeroplane or ship

With a relational system, it is easy to design a schema that captures all the data and the metadata.

The netCDF data format was designed to contain mainly gridded multidimensional data. The format does not have a special support for tables, which sounds like a severe restriction, but it's not.

• It's possible to agree about a simple way to encode tables in a netCDF file.
  • Tables with multiple fields.
  • Proper primary keys for tables.
  • Foreign keys for referential integrity.
  • Common relationships: many-to-many, 1-to-many, 1-to-1.

• It's possible to be efficient, the encoding does not have to be one size fits all.
  • Sparse tables can be encoded row-by-row manner.
  • Dense tables can be encoded as multidimensional hypercubes.
  • Mix and match the two above at will.

• It's possible to be elegant.
  • No mysterious indirections.
  • No complex offset calculations.
  • A reader-writer library can take care of following the rules, letting a programmer to operate on higher level.

Encoding Relational Tables to NetCDF

• There must be a dimension for the table, to enumerate the records.
• A column is a variable with that dimension and an attribute table_name
• A table is made of columns with the same table_name attribute.
• Primary keys are declared with primary_key = "T" attribute. A table can have multiple keys as a primary key
• Foreign keys are declared with either using the dimension of another table, or declaring a field to be a reference to another table.

Examples:

Here's how you encode a minimal station table, with all the CF attributes omitted:

   dimensions:
       station = 10 ;  // station table dimension
       code_strlen = 4 ; // artificial dimension for station code, with max four character length.
   variables:
       float lon(station) ; 
           lon:table_name = "station";
       float lat(station) ; 
           lat:table_name = "station";
       char station_code(station, code_strlen) ;
           station_code:table_name = "station";
           station_code:primary_key = "T";

The stations have now index 0-9.

Since we have data with time dimension, let's add the time table, again omitting the CF attributes like units = "days since 1970-01-01 00:00:00"

   dimensions:
       ... as above
       time = UNLIMITED ;
   variables:
       ... as above
       double time(time) ; 
           station_code:table_name = "time";
           station_code:primary_key = "T";

Time can be indexed just as station, 0..(count-1).

So far we have the two unrelated tables for our two dimensions: location and time. Now let's add humidity and temperature, which refers to both of the dimensions:

   dimensions:
       ... as above
       time = UNLIMITED ;
   variables:
       ... as above
       float humidity(time, station) ;
           station_code:table_name = "data";
       float temperature(time, station) ;
           station_code:table_name = "data";

This is identical to CF-1.6 encoding. Both variables are 2-dimensional cubes.

The humidity and temperature are now both in the table named data, which has four fields: time, station, humidity and temperature

This is a very compact encoding if every station has data for every time step, but wasteful if data is sparse, since the cubes will then contain mainly null values. That's why CF drafts have had encodings like ragged arrays.

With multiple tables there's an easy way to encode sparse tables. Instead of above, we'll describe the record dimension:

   dimensions:
       ... as above
       data = 800 ; // 800 records
   variables:
       ... as above
       int data_station(data) ;
           data_station:table_name = "data";
           data_station:foreign_key = "station";
       int data_time(data) ;
           data_station:table_name = "data";
           data_station:foreign_key = "time";
       float humidity(data) ;
           humidity:table_name = "data";
       float temperature(data) ;
           temperature:table_name = "data";

The table still has the four columns, but the encoding is not a cube anymore. The time and station dimensions are not netcdf dimensions, but variables like data_station which contains an index to the station table.

To print out a row 5 from data table:

• read the data_station(5). It may contain number 8, which is referring to the station 8 in the station table.
  • read station_code(8,all), lat(8) and lon(8)
• read the data_time(5). It may contain number 112, which is referring to the time 112 in the time table.
  • read time(112), which contains number 35523, which means 35523 days from 1970-01-01
• read humidity(5) which represents itself.
• read temperature(5)

This is a simple and powerful encoding.

• Any typical schema from SQL-databases can be encoded.
• If the data is dense, foreign keys can be expressed as dimensions, resulting in vastly smaller files than flat CSV would be.
• If the data is sparse, foreign keys can be expressed as variables.
• There can be any combination of the two, enabling the best of both worlds whatever your data will look like.