Example SQL for Stations

Back to WCS Access to netCDF Files

A Real life example how to serve station timeseries point data through WCS data access protocol.

AIRNOW

Daily PM 2.5, PM 10 and Ozone data can be downloaded from date encoded folders. The data and location tables are in text files.

Folder for 2011-07-05: http://www.epa.gov/airnow/2011/20110705/

PM 10 observations: http://www.epa.gov/airnow/2011/20110705/pm10_070511.obs

Active sites: http://www.epa.gov/airnow/2011/20110705/active-sites.dat

View in datafed catalog and datafed borser

The Design of the SQL Database

The most common and flexible method of storing station time series point data is an SQL database. Typically, the collector of the data designs a schema, that captures all the measured data, metadata of the measurements, station instrument information etc. This requires a database with lots of tables and relationships between them. There is no one-size-fits-all design around, because requirements for data collecting systems are different.

Fortunately, relational databases were invented just to solve this standardization problem:

Data Views

The data is published as views, letting the physical data structure to be whatever it needs to be. The picture below shows a SQL view with columns loc_code, lat, lon, datetime and pm10. It could also contain quality metadata for pm10.

The complex database on the left is the real data storage, which is not an easy thing to understand. On the right, is the requirement for the WCS: a flat, simple view of place, time and data value. This means, that the real configuration of the WCS system is in the SQL views, that publish the data in this flat form. Configuring the WCS is then just assigning a view to a parameter.

They key fields here are loc_code and datetime, each row can be identified by a unique combination of them.

The field names are standardized, This goes with fairly popular design philosophy, convention over configuration. Rather than allowing any name, longitude or längengrad, and then mark it to be the longitude column somewhere else, the field name is fixed to lon. On the other hand the data and metadata column names can be anything.

Because this view is a result of a SQL join, the tables can be properly normalized. The tables don't need to repeat lat and lon columns over and over again.

Location View

The Location view needs at least 3 columns: loc_code, lat and lon

The fixed field names are loc_code, loc_name (optional), lat, lon, elev (optional). It is important to use these names, since client software, like datafed browser, expects them.

Any other fields can be added. The system will just copy the additonal data to output.

Notes on Physical Fact - Data Table

There are numerous ways to organize the physical data tables. In the AIRNOW database the parameters are pmfine(PM 2.5), pm10 and super(Ozone)

• Big Wide table: One big table with columns loc_code datetime pmfine pm10 super
  • Fast to query all the three data fields, no joining.
  • Needs NULLs for missing data
• Long and skinny table: loc_code datetime param_code param_value
  • The param_code is either pmfine, pm10 or super, telling what data the param value is.
  • No need for NULLs
• Own table for each parameter. The pm10 table contains fields loc_code datetime pm10
  • fastest to query single parameters
  • No need for NULLs
  • More difficult for system administrator.

There is no universally best solution, pros and conses must be weighted for your case.

The main point is Physical - Logical Separation!

The concept of a view is of ultimate importance in database practice. It allows the system administrator to change the real data tables, without breaking existing software, because the views can be maintained separately. For example, you can change the physical table structure from wide table to long and skinny table and still maintain the pm10 view above.

Sometimes, the views have to do so much joining and filtering, that the performance drop to unacceptable level. In this case, it's advisable to create snapshot tables. A straightforward method is to create a location table with integer based loc_id, and the data tables that use this loc_id as a foreign key, then create an efficient data view for the WCS use.

Configuring the WCS Service and WFS Service for point data

WFS, Web Feature Service for locations

The simplest way to configure location table is just point to the view and it's columns.

   view_alias : location
   columns    : loc_code, loc_name, lat, lon

Many locations don't contain any data of some parameters. Therefore, it's very useful to be able to get all the locations that contain some parameter.

In this case, the location view needs to contain parameter configuration contains:

   view_alias  : location_with_statistics
   columns     : loc_code, loc_name, lat, lon
   view_filter : param_abbr='pm10' and data_count > 0

Here is an example of AIRNOW locations filtered by pm10