Technical Support and Database Management for the Alsea Watershed Study Revisited Jeff Hatten, OSU FERM (PI) Amy Simmons, OSU FERM (Co-PI)
The Original Alsea Paired Watershed Study • Began in 1959 • Quantified effects of forest management on WQ and fish • Played role in establishing OR Forest Practices Act in 1971 • Examined first entry effects (older forests, newly constructed roads) with limited WQ protection 2
Alsea Watershed Study Revisited 1959 -73 Study Design Current Study Design
Alsea Watershed Revisited – Assessing Contemporary Management Impacts • 1966 - large increase in sediment and temp. and decrease in DO conc. without buffer and slope protection • 2009 – minimal exposure of bare mineral soil, RMA left on fish streams and roads managed – no large changes in WQ? I 966 2009
Physical – – Response Variables Discharge Sediment Temperature Nutrients Turbidity, temperature, and specific conductance probes on the 5 boom.
Difficulties/Opportunities • Cast of many under various umbrellas • Leadership in flux • At least 4 new faculty interested in forest hydrology related questions
Overall Objective Create a database that retains its value into “perpetuity”
Objectives 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). 5. Transition long-term storage and serving of data-set. 6. Merge smaller researcher specific data sets with the larger data-set. 7. Explore integrating biological data-sets into the larger hydrological data set. 8. Continue to add hydrological data.
Accomplishments 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. • 2. Reconcile spatial and naming differences in each of the various data Meta data pulled together (NCASI DB report) • TTS sets (e. g. DO and temperature). as those components are QA/QC’d and Q meta data still being developed 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). 5. Transition long-term storage and serving of data-set. 6. Merge smaller researcher specific data sets with the larger data-set. 7. Explore integrating biological data-sets into the larger hydrological data set. 8. Continue to add hydrological data.
Accomplishments 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). • 3. Support QA/QC of contemporary and historical discharge data and Naming complete incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). 5. Transition long-term storage and serving of data-set. 6. Merge smaller researcher specific data sets with the larger data-set. 7. Explore integrating biological data-sets into the larger hydrological data set. 8. Continue to add hydrological data.
Simplified relationship of tables in the database
Accomplishments • • 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, QA/QC of Q data still in process QA/QC’d instantaneous Q not fully incorporated into DB yet nutrients). Daily Q summaries available 5. Transition long-term storage and serving of data-set. Historical data not incorporated 6. Merge smaller researcher specific data sets with the larger data-set. 7. Explore integrating biological data-sets into the larger hydrological data set. 8. Continue to add hydrological data.
Important tables and number of records through 2013 Table_name Count of records TTS_Data 1, 557, 838 Discharge 1, 557, 838 Temperature_Loggers 954, 034 DO_Data 40, 665 Nutrient_Results 6, 473 SSC 5, 150
Accomplishments 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). • 5. Transition long-term storage and serving of data-set. PIs Major component of Amy’s time is spent developing queries for • 6. Merge smaller researcher specific data sets with the larger data-set. Query developed for each major discipline (DO, Temp, Q, Sediment, Nutrients) • Query developed for QA report (major effort) 7. Explore integrating biological data-sets into the larger hydrological data set. 8. Continue to add hydrological data.
Example of types of queries created • Summary statistics like minimum, mean, maximum, standard deviation for parameters • Integrate data from different sources on a common field like date or site • Summarize data on different time intervals like hourly, daily, monthly, or annual • Calculate discharge using observed stage and rating curves • Calculate total sediment load
Example of summary from query: Count of Samples Submitted for Suspended Sediment Analyses by Site Water Year Site Name 2007 2008 2009 2010 2011 2012 2013 DCG 170 161 189 186 85 329 380 FCG 162 171 135 185 66 423 354 NBH NBL NBU Annual Grand Total 141 129 17 461 64 131 90 164 297 26 473 8 24 205 92 388 502 258 1121 1123 16
Example code for a summary query
Accomplishments 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). 5. Transition long-term storage and serving of data-set. • 6. Merge smaller researcher specific data sets with the larger data-set. Current Long-term: DB will be stored @ OSU • 7. Explore integrating biological data-sets into the larger hydrological data Final Long-term: depends on new faculty involvement set. 8. Continue to add hydrological data.
Accomplishments 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). 5. Transition long-term storage and serving of data-set. 6. Merge smaller researcher specific data sets with the larger data-set. • 7. data are developed (e. g. modeled sediment flux) As Explore integrating biological data-sets into the larger hydrological data set. 8. Continue to add hydrological data.
Accomplishments 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). 5. Transition long-term storage and serving of data-set. 6. Merge smaller researcher specific data sets with the larger data-set. 7. Explore integrating biological data-sets into the larger hydrological data set. • 8. Continue to add hydrological data. Exploration is continuing
Accomplishments 1. Reconcile and formalize metadata, making it consistent with Hinkle and Trask data sets. 2. Reconcile spatial and naming differences in each of the various data sets (e. g. DO and temperature). 3. Support QA/QC of contemporary and historical discharge data and incorporate this data into the database. 4. Develop custom queries for each discipline (e. g. hydrology, sediment, nutrients). 5. Transition long-term storage and serving of data-set. 6. Merge smaller researcher specific data sets with the larger data-set. 7. Explore integrating biological data-sets into the larger hydrological data set. 8. Continue to add hydrological data. • Yes.
Presentations and Papers • Paper 1 – Hydrology (Stednick and Hale) – Q data is being processed • Paper 2 - Sediment Turbidity (Hatten, Segura, and Bladon – Currently compiling and QAing instantaneous Q and TTS – Assessing data quality and developing approach to analysis • Paper 3 – Nutrients (Stednick ) – Data analysis and manuscript preparation in progress • Paper 4 – Temperature (Light) – Data analysis and manuscript preparation in progress • Paper 5 – Dissolved Oxygen (Ice) – QA/QC work on most of the DO data has been completed – Query being developed (challenge: different instruments with different recording sequences) – Introduction completed
AWS Revisited and Nutrients Needle Branch • Increased at upper station • Little effect at main gauge Flynn Creek • Similar to original study
Presentations and Papers • Paper 1 – Hydrology (Stednick and Hale) – Q data is being processed • Paper 2 - Sediment Turbidity (Hatten, Segura, and Bladon – Currently compiling and QAing instantaneous Q and TTS – Assessing data quality and developing approach to analysis • Paper 3 – Nutrients (Stednick ) – Data analysis and manuscript preparation in progress • Paper 4 – Temperature (Light) – Data analysis and manuscript preparation in progress • Paper 5 – Dissolved Oxygen (Ice) – QA/QC work on most of the DO data has been completed – Query being developed (challenge: different instruments with different recording sequences) – Introduction completed
Planned Work • Monthly meetings (tele-conference) – next meeting will map out manuscripts • QA/QC Q and TTS and incorporate into DB • Support developing manuscripts and an annual report
Questions? 26
Скачать презентацию Technical Support and Database Management for the Alsea
d11c1512e0addd82e04262324cb247f5.ppt