Difference between revisions of "Example SQL for Stations"
| Line 30: | Line 30: | ||
=== Location table === | === Location table === | ||
| − | The Location table needs at least 3 columns: | + | The Location table needs at least 3 columns: '''loc_code''', '''lat''' and '''lon''' |
| + | |||
| + | The names are fixed. This goes with fairly popular design philosophy, '''convention''' over '''configuration'''. Rather than allowing any name, (longitude or längengrad) and then make it as longitude somewhere else, the field name is fixed to '''lon'''. This is done at the SQL View level, renaming the fields. | ||
| + | |||
| + | [[Image:Location_table.png]] | ||
=== Parameter tables === | === Parameter tables === | ||
Revision as of 10:00, July 8, 2011
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
The Design of the SQL Database
The most 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 typically 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:
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 table listing place, time and data value.
The tool that brings these two together is an SQL View. It allows the administrator to create a view, a virtual table, that can be queried with a simple filter, taking the complexity off from the WCS point access system.
Location table
The Location table needs at least 3 columns: loc_code, lat and lon
The names are fixed. This goes with fairly popular design philosophy, convention over configuration. Rather than allowing any name, (longitude or längengrad) and then make it as longitude somewhere else, the field name is fixed to lon. This is done at the SQL View level, renaming the fields.
Parameter tables
Fact - Data Table
Data Views in SQL
the importance of views, flexibility of SQL, snapshot views
creating data views
calculating statistics to filter the location table
Configuring the WCS Service and WFS Service for point data
WFS for locations
WCS for data
using python dictionaries ??
