Status

1
Status Trend Monitoring Databasefor
Salmonids in the Wenatchee Subbasin
Dr. Chris Jordan Project Sponsor Richard Kang
Team Leader Jeff Cowen - GIS Analyst Rod Davidson
Database Designer Martin Park Application
Developer Yazan Suleiman DBA and Java Programmer
2
Project Goals

• Develop a database to establish the baseline
  status of salmonids and their habitat in the
  Wenatchee subbasin of the Columbia Basin
• Determine population trends
• Evaluate the effectiveness of restoration
  projects
• Track protocols for data collection
• Integrate with GIS
• Provide easy access to the data through a web
  interface
• Serve biologists, natural resource managers and
  land use planners

3
Project Participants

• Database design and GIS support
• NOAA NW Fisheries Science Center
• Bureau of Reclamation
• Data Collectors
• US Forest Service
• WA Department of Ecology
• WA Department of Fish and Wildlife
• Chelan County Conservation District
• Yakama Nation
• Contract Manager
• Mike Ward of Terraqua Inc

4
The Wenatchee Subbasin
5
Establishing Sites

• 300 Sites Established
• in 2004 - 50 sites visited
• In 2005 and beyond
• 25 revisits
• 25 new sites

(Site WC503432-159 White River)
6
Survey Types

• Habitat Quality
• (Including Thalweg Profiles)

• Water Quality

• Steelhead Redds

• Smolt Traps

• Macroinvertebrates

7
Data Flow
Data Collection Contracts Awarded 2004
Data Delivered to Contract Manager Winter 2005
Field Data Collection Summer Fall 2004
Data Delivered to SDM Spring 2005
Data Loaded into Database Summer 2005
Data QA/QC SDM and Contract Manager Summer 2005
Data Access Interface Fall 2005
8
Anatomy of a Site

• A Stream Reach (500 meters or less), with its
  X-Site, will have a center line, 11 cross-section
  transects, plot frames, and numerous measure
  points along the center line and along each
  transect.

X
9
Data Collection
10
HABITAT Data
11
Other Data Submissions
12
Protocol Manager
Monitoring Program
Revise Protocols/ Methods
Document Protocols/ Methods
Evaluate Protocols/ Methods
Protocol
Data Collection Methods
Data Validation Methods
Data Summary Methods
Data Analysis
Status Trend Monitoring Database
Field Forms
Repository Database
Field Data Tables
13
Geometric Context

• Whole Reach Values (X-Sites)
• Transect Values
• Transect Direction Values
• Profile Segment Values
• Thalweg Segment Values
• Thalweg Substrate Codes

14
Data Loading Steps

• Stream Reaches are identified on a Stream in the
  field and in the database
• X-Sites with Site IDs and GPS coordinates are
  loaded into the database
• Coordinates are converted to GIS points and
  related to DB via Network ID
• The Reference Monuments are loaded and connected
  to their Sites
• Instruments and Team Member names are stored to
  support building EVENTS from field data forms
• Events are created for combinations of Team
  Members, Instruments, Time-of-occurrence and
  Site-ID
• The Measurements are stored with connection to
  the EVENT.

15
Inside the Database
16
Data Loading Challenges/Issues

• Common Excel spreadsheets not in identical
  formats.
• Site-Identifiers not consistent for all datasets
  (X-Site)
• i.e. Some were by Lats/Lons, Some were by
  Site-ID, Some were slightly different Lats/Lons.
• Determine the geometry of some measures and how
  to represent that geometry in the DB.
• Determine if an Integer item might also be
  Floating Point value.
• Capture all the codes and code-translations for
  each survey type. For example, devising a way to
  accommodate the ITIS and Non-ITIS codes for the
  macroinvertebrate taxonomy.

(Continued)
17
Data Loading Challenges/Issues(Cont.)

• Establish Wenatchee place holders for
  attributes not included in prepared Protocol
  Definitions.
• Determine when a constant value should be
  TIMESTAMPED because it might be re-measured.
• Distinguish between REAL Raw-Data and summations
  or averages of other data.

18
Matching the Site Coordinates between Water
Quality and Habitat Datasets
19
Wenatchee Data Received
Total Observations 224,747
20
(No Transcript)
21
(No Transcript)
22
Tying It Together
23
(No Transcript)
24
Habitat Queries

• Channel Morphology
• Stream Profile
• Substrate Size
• Substrate Composition
• Fish Cover
• Large Woody Debris
• Canopy Cover
• Riparian Vegetation
• Human Disturbance

25
A Rudimentary Query Fish Cover
26
A Rudimentary Query - Macroinvertebrates
27
New Data Sets

• 2005 Wenatchee field data 25 revisits and 25
  new
• 2004 EMAP and PiBo data for John Day
• Historic water quality and temperature data for
  Wenatchee
• Incorporate new version of Protocol Manager
• Expand web interface Java, HTML DB, Discoverer

28
Feedback

• Data Quality Is the data correct?
• Data Queries Are we on target?
• Web Interface Is it useful?
• Data Sharing Who has access?