Using NetCDF for point time-series observations?

Question

I have a large growing dataset of water and climate observations for several years in regular hourly time-step. The number of locations is cca. 1000 locations with latitude, longitude coordinates. At each location up to 5 parameters (water level, discharge, rainfall, snow depth, temperature) are measured at regular hourly time-step (gaps in time-series are labeled with a special "nodatavalue" code).

Currently I am using a Microsoft SQL Server database with a following main tables:

stations (id, name, latitude, longitude, elevation)
parameters(id, name, units, scalefactor, nodatavalue)
stations_parameters (station_id, parameter_id, start_time, end_time)
waterlevel (station_id, time_utc, value)
discharge (station_id, time_utc, value)
temperature (station_id, time_utc, value)
rainfall (station_id, time_utc, value)
snow (station_id, time_utc, value)

The size of my database is 2 GB. For various reasons mainly due to database size limitation on my webhost and interoperability issues I need to move away from MS SQL Server. I would like to use the NetCDF format because I read that it is suitable for multi-dimensional data, and that it has built-in support for space and time queries. Specifically I need to run the following types of queries very fast:

Query 1: For a time-range give me the average, max, min or sum of a parameter for all stations. For example create a map of maximum temperature between 2013-07-25 and 2013-07-26.

Query 2: For a time-range, point location and parameter, give me the time-series. For example hourly time-series of temperature at station 777 from 2013-01-01 to 2013-07-25.

My questions:

1. Is it possible to use NetCDF for this type of multi-dimensional data? (note that the space is not a regular grid but rather sparse point-locations)
2. Is there any free software tool that has built-in support for visualizing a point time-series NetCDF data file? I have used Panoply and IDV but only with regular NetCDF grids.
3. If yes - What would be the structure of my NetCDF data file (dimensions, variables and their order)?

Answer 1

Yes, the CF 1.6 Conventions for NetCDF include the specification of collections of time series and it seems your data is similar to example H.2.1 "Orthogonal multidimensional array representation of time series":

If you store your data this way, IDV should be able to recognize this as "point data". Hopefully more applications in the future will take advantage of the CF conventions for point data, but at least you would also know that you are using the recommended conventions for this kind of data stored in NetCDF files!

Answer 2

Yes, the NetCDF CF Metadata Conventions version 1.6 specifies how to store point and station time series data in chapter 9 "Discrete Sampling Geometries".

Since your data has the same sample times for all stations, I agree with Rich that you can base your netCDF structure on the example in section H.2.1 "Orthogonal multidimensional array representation of time series". If your sample times where not the same for all the stations, you would have to follow the examples in section H.2.4. "Contiguous ragged array representation of time series" or H.2.5. "Indexed ragged array representation of time series".

The only two tools I know for sure that support visualizing this type of data are ToolsUI (distributed with the netCDF-Java library) and the IDV.

Below is an example file structure based on the CF H.2.1 example but using your variables names and such. Since one of your fields is named "scalefactor", I added "scale_factor" and "add_offset" attributes to the "waterlevel" variable. I would suggest reading the "Packed Data Values" section of the netCDF Best Practices document which describes how to use those attributes. You might also want to look at Section 3.3. "Standard Name" of the CF conventions for more info on the CF "standard_name" variable attribute used below.

netcdf yourData.nc {
  dimensions:
    station = 10 ;  // measurement locations
    time = UNLIMITED ;
  variables:
    short waterlevel(station,time) ;
      waterlevel:standard_name = "water_surface_height_above_reference_datum" ;
      waterlevel:units = "meters" ;
      waterlevel:add_offset = "" ;
      waterlevel:scale_factor = "" ;
      waterlevel:coordinates = "lat lon elevation" ;
    float discharge(station,time) ;
      // discharge:standard_name = "water_flux_..." ; // Didn't find an appropriate standard name
      discharge:units = "kg m-2 s-1" ;
      discharge:coordinates = "lat lon elevation" ;
    float temperature(station,time) ;
      temperature:standard_name = "surface_temperature" ;
      temperature:units = "K" ;
      temperature:coordinates = "lat lon elevation" ;
    float rainfall(station,time) ;
      rainfall:standard_name = "rainfall_flux" ;
      rainfall:units = "kg m-2 s-1" ;
      rainfall:coordinates = "lat lon elevation" ;
    float snowdepth(station,time) ;
      snowdepth:standard_name = "surface_snow_thickness" ;
      snowdepth:units = "meters" ;
      snowdepth:coordinates = "lat lon elevation" ;

    double time(time) ; 
      time:standard_name = "time";
      time:long_name = "time of measurement" ;
      time:units = "days since 1970-01-01 00:00:00" ;
    float lon(station) ; 
      lon:standard_name = "longitude";
      lon:long_name = "station longitude";
      lon:units = "degrees_east";
    float lat(station) ; 
      lat:standard_name = "latitude";
      lat:long_name = "station latitude" ;
      lat:units = "degrees_north" ; 
    float elevation(station) ;
      elevation:long_name = "height above mean sea level" ;
      elevation:standard_name = "height" ;
      elevation:units = "m";
      elevation:positive = "up";
      elevation:axis = "Z";
    char station_name(station, name_strlen) ;
      station_name:long_name = "station name" ;
      station_name:cf_role = "timeseries_id";
  attributes:
    :featureType = "timeSeries";
}

A few comments on the queries you mention. First off, the basic netCDF data model is array-based and supports subsetting but not more sophisticated queries. So you can request data subsets along the array indexes by specifying a start and end index and an optional stride. The various netCDF libraries do not support calculating avg, min, max, or sums. That would have to be done in another layer of code.

Since your sample times are the same across stations, knowing the coordinate variables (station, time, lat, lon) allows you to determine the array indexes needed for your queries. It should be pretty straight forward to access the data to satisfy both your queries. The first query dealing with min, max, etc will require some further processing.