SQL Database for Points
< Back to Glossary | Edit with Form
SQL_Database_for_Points Description: Storing point data in SQL database.
Glossary Domain: {{{Glossary Domain}}}"{{{Glossary Domain}}}" is not in the list (WCS, HTAP, AQInfrastructure) of allowed values for the "Glossary Domain" property.
Related Links
Links to this page
[[Links::Configuration Help]]
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 | | ... | | | | | +----------+-----------------+------------+------+