SQL Database for Points

< Back to Glossary | Edit with Form

SQL_Database_for_Points Description: [[TermDesc::This document describes how to create views in your station point data database so that it can be service by simply configuring it. It shows how to create views to the location and data tables.]]

Glossary Domain: WCS

Related Links

Links to this page
[[Links::Data Configuration for Points Location Table for Points]]

Contributors

No Contributors

History

No History Available

Term Details

Configuration Help

Currently the datafed WCS for points supports one kind of point data: Fixed locations and regular intervals.

Point data is often stored in SQL databases. There's no standard schema, like CF-1.0 convention for NetCDF files, so it is not possible to just connect and start serving. You have to create the configuration description.

One of the most powerful ideas in relational database design is the concept of a view. You don't need to change the existing data tables, creating a view that makes your DB to look like the one needed is enough.

The Simplest Case: Configure with SQL Views

Location Table/View

The common thing between different databases is, that they need to have a location table. The current implementation is based on time series from stationary locations.

   table/view location
   +----------+-------+---------+-------+
   | loc_code | lat   | lon     | elev  |
   +----------+-------+---------+-------+
   | KMOD     | 37.63 | -120.95 |  30.0 |
   | KSTL     | 38.75 |  -90.37 | 172.0 |
   | KUGN     | 42.42 |  -87.87 | 222.0 |
   |...       |       |         |       |
   +----------+-------+---------+-------+

Here loc_code is the primary key and lat,lon is the location. Optional fields can be added. Your database may have a location table with different name and different field names, but that does not matter. CIRA VIEWS database has a location table, but it's called Site and it spells full longitude. The datafed browser uses standard names loc_code, loc_name, lat and lon for browsing; to get plug-and-play compatibility we these names are needed. In the CIRA VIEWS database, the view creation would be:

   create view location as 
   select 
       SiteCode as loc_code, 
       Latitude as lat, 
       Longitude as lon 
   from Site

The primary key is loc_code, being unique for all the locations.

Because WCS does not have a good place to describe a location table, we use WFS, Web Feature Service to do the same. Sample WFS Call.

Data Views

Each data variable needs a view. For example:

   create view TEMP_V as
   select 
       location.loc_code, 
       location.lat, 
       location.lon, 
       TEMP_base.datetime, 
       TEMP_base.temp,
       TEMP_base.flag
   from location
   inner join TEMP_base on TEMP_base.loc_code = location.loc_code

Each parameter has its own data view that looks like

   view TEMP_V
   +----------+-------+---------+------------+------+------+
   | loc_code | lat   | lon     | datetime   | temp | flag |
   +----------+-------+---------+------------+------+------+
   | KMOD     | 37.63 | -120.95 | 2009-06-01 | 87.8 | X    |
   | KMOD     | 37.63 | -120.95 | 2009-06-02 | 82.3 |      |
   | KSTL     | 38.75 |  -90.37 | 2009-06-01 | 78.6 |      |
   | ...      |       |         |            |      |      |
   +----------+-------+---------+------------+------+------+

   view DEWP_V
   +----------+-----------------+------------+------+
   | loc_code | lat   | lon     | datetime   | dewp |
   +----------+-----------------+------------+------+
   | KMOD     | 37.63 | -120.95 | 2009-06-01 | 51.4 |
   | KMOD     | 37.63 | -120.95 | 2009-06-02 | 51.4 |
   | KSTL     | 38.75 |  -90.37 | 2009-06-01 | 34.9 |
   | ...      |       |         |            |      |
   +----------+-----------------+------------+------+