cuahsi observations data model a relational database stored in access, postgresql, sqlserver, …....
TRANSCRIPT
![Page 1: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/1.jpg)
CUAHSI Observations Data Model
• A relational database stored in Access, PostgreSQL, SQLServer, ….
• Stores observation data made at points
• Access data through web interfaces
• Fill using automated data harvesting
Streamflow
Flux towerdata
Precipitation& Climate
Groundwaterlevels
Water Quality
Soil moisture
data
![Page 2: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/2.jpg)
Purposes• Hydrologic Observations Data System to Enhance
– Retrieval– Integrated Analysis– Multiple Investigators
• Standard and Scalable Format for Sharing• Ancillary information (metadata) to allow unambiguous
interpretation and use – incorporating uncertainty• Traceable heritage from raw measurements to usable
information – quality control levels
Premise• A relational database at the single observation level (atomic
model)– Querying capability– Cross dimension retrieval and analysis
![Page 3: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/3.jpg)
Community Design Requirements(from comments of 22 reviewers)
• Incorporate sufficient metadata to identify provenance and give exact definition of data for unambiguous interpretation
• Spatial location of measurements• Scale of measurements (support, spacing, extent)• Depth/Offset Information• Censored data• Classification of data type to guide appropriate
interpretation– Continuous– Indication of gaps
• Indicate data quality
![Page 4: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/4.jpg)
Scale issues in the interpretation of data
The scale triplet
From: Blöschl, G., (1996), Scale and Scaling in Hydrology, Habilitationsschrift, Weiner Mitteilungen Wasser Abwasser Gewasser, Wien, 346 p.
a) Extent b) Spacing c) Support
![Page 5: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/5.jpg)
From: Blöschl, G., (1996), Scale and Scaling in Hydrology, Habilitationsschrift, Weiner Mitteilungen Wasser Abwasser Gewasser, Wien, 346 p.
![Page 6: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/6.jpg)
Ernest To
Center for Research in Water ResourcesUniversity of Texas at Austin
20061011
What are the basic attributes to be associated with each single observation and how can these best be organized?
A data source operates an observation network A network is a set of observation sites
Data Source and Network Sites Variables Values Metadata
Depth of snow pack
Streamflow
Landuse, Vegetation
Windspeed, Precipitation
Data Delivery
Controlled Vocabulary Tables
e.g. mg/kg, cfs
e.g. depth
e.g. Non-detect,Estimated,
A site is a point location where one or more variables are measured
Metadata provide information about the context of the observation.A variable is a property describing the flow or quality of water
A value is an observation of a variable at a particular time
Data Discovery
Hydrologic Observations Data Model
See http://www.cuahsi.org/his/documentation.html
![Page 7: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/7.jpg)
Feature
Waterbody
HydroIDHydroCodeFTypeNameAreaSqKmJunctionID
HydroPoint
HydroIDHydroCodeFTypeNameJunctionID
Watershed
HydroIDHydroCodeDrainIDAreaSqKmJunctionIDNextDownID
ComplexEdgeFeature
EdgeType
Flowline
Shoreline
HydroEdge
HydroIDHydroCodeReachCodeNameLengthKmLengthDownFlowDirFTypeEdgeTypeEnabled
SimpleJunctionFeature
1HydroJunction
HydroIDHydroCodeNextDownIDLengthDownDrainAreaFTypeEnabledAncillaryRole
*
1
*
HydroNetwork
*
HydroJunction
HydroIDHydroCodeNextDownIDLengthDownDrainAreaFTypeEnabledAncillaryRole
HydroJunction
HydroIDHydroCodeNextDownIDLengthDownDrainAreaFTypeEnabledAncillaryRole
1
1
CouplingTable
SiteID (GUID)HydroID (Integer)
MonitoringPoint
SiteIDSiteCode
SiteNameLatitudeLongitude…
Hydrologic Observations Data Model
1
1
OR
Independent of, but coupled to Geographic Representation
HODM Arc Hydro
![Page 8: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/8.jpg)
Variable attributes
VariableName, e.g. dischargeVariableCode, e.g. 0060SampleMedium, e.g. waterValuetype, e.g. field observation, laboratory sampleIsRegular, e.g. Yes for regular or No for intermittentTimeSupport (averaging interval for observation)DataType, e.g. Continuous, Instantaneous, CategoricalGeneralCategory, e.g. Climate, Water QualityNoDataValue, e.g. -9999
m3/sL3/TCubic meters per second
![Page 9: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/9.jpg)
Data Types• Continuous (Frequent sampling - fine spacing)• Instantaneous (Spot sampling - coarse spacing)• Cumulative• Incremental• Average• Maximum• Minimum• Constant over Interval• Categorical
t
0
d)(Q)t(V
t
tt
d)(Q)t(V
t
tVtQ
)(
)(
![Page 10: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/10.jpg)
Groups and Derived From Associations
![Page 11: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/11.jpg)
Stage and Streamflow Example
![Page 12: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/12.jpg)
Daily Average Discharge ExampleDaily Average Discharge Derived from 15 Minute Discharge Data
![Page 13: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/13.jpg)
Offset
OffsetValue
Distance from a datum or control point at which an observation was made
OffsetType defines the type of offset, e.g. distance below water level, distance above ground surface, or distance from bank of river
![Page 14: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/14.jpg)
Water Chemistry from a profile in a lake
![Page 15: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/15.jpg)
ODM and HIS in an Observatory Setting
e.g. http://www.bearriverinfo.org
![Page 16: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/16.jpg)
ODM and HIS in an Observatory SettingIntegration of Sensor Data With HIS
ObservationsDatabase
(ODM)
Base StationComputer(s)
Data ProcessingApplications In
tern
et
Telemetry Network
Sensors
Data discovery, visualization, analysis, and modeling
through Internet enabled applications
Programmer interaction through web services
Inte
rnet
Workgroup HIS ToolsWeb Server
![Page 17: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/17.jpg)
Available National and Workgroup HIS Tools
ObservationsDatabase
(ODM)
Inte
rnet
Data discovery, visualization, analysis, and modeling through Internet
enabled applications
Programmer interaction through web services
Web Server
Other NationalDatabases
NWIS, STORET, Etc.
![Page 18: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/18.jpg)
Automated Ingestion of Sensor Data into ODM
ObservationsDatabase
(ODM)
Base StationComputer(s)
Data ProcessingApplications
Telemetry Network
Sensors Inte
rnet
Challenges
• Heterogeneity
• Establishing standards
• Sensor/system descriptions Sensor ML
![Page 19: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/19.jpg)
Data Distribution Via XML Web Services
• Machine to machine communication of data over the internet
• Users can program against database as if it were on their local machine
• Replace SQL queries to database with calls to the appropriate web service
![Page 20: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/20.jpg)
Example: Matlab use of CUAHSI Web Services% create ODM Web Service Class createClassFromWsdl('http://water.usu.edu/BRODM/brodm.asmx?WSDL');
% Discover the sites availablexmlSites=GetSites(instODM);strSites=xml_parse(xmlSites);Nsites=length(strSites.Sites)…
% Get information about a selected sitexmlSiteInfo=GetSiteInfo(instODM,scode);strSiteInfo=xml_parse(xmlSiteInfo);…
% Get the data valuesxmlValues = GetValues(instODM,scode,varcode,D1i,D2i);strValues=xml_parse(xmlValues);…% Plot the dataplot(dt,vals); datetick% Axis label from metadata from web service returnylabel(strSiteInfo.Variables(var).Variable.VariableName)
![Page 21: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/21.jpg)
ODM Next Steps
• Beta test in test beds (unanticipated requirements)
• Unit conversions
• Additional data types (vector, grid)
• Tools, incl. Data Loader
• Integration with search
• [Moving sensors (FerryMon)]
![Page 22: CUAHSI Observations Data Model A relational database stored in Access, PostgreSQL, SQLServer, …. Stores observation data made at points Access data through](https://reader035.vdocuments.us/reader035/viewer/2022062620/551b18225503462e578b5e5a/html5/thumbnails/22.jpg)
Managing Data Within ODM - ODM Tools
• Load – import existing data directly to ODM
• Query and export – export data series and metadata
• Visualize – plot and summarize data series
• Edit – delete, modify, adjust, interpolate, average, etc.