Difference between revisions of "SQL Database for Points"
(3 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
{{WCS Glossary | {{WCS Glossary | ||
− | |TermDesc= | + | |Glossary Domain=WCS |
− | |Links=[ | + | |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_Table_for_Points|location]] and [[Data_Configuration_for_Points|data]] tables. |
+ | |Links=[[Data_Configuration_for_Points|Data Configuration for Points]] [[Location_Table_for_Points|Location Table for Points]] | ||
}} | }} | ||
+ | [http://wiki.esipfed.org/index.php/WCS_Wrapper_Configuration Configuration Help] | ||
Currently the datafed WCS for points supports one kind of point data: Fixed locations and regular intervals. | Currently the datafed WCS for points supports one kind of point data: Fixed locations and regular intervals. |
Latest revision as of 13:36, September 6, 2010
< 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
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 | | ... | | | | | +----------+-----------------+------------+------+