- Stream Team Field Manual (PDF) - important 40 page document outling all critical procedures to follow in the field.
- Uploading data in the Field We use Dropbox and/or Google Drive services to transfer data files. In the unlikely event you have special data files over 50Gb in size, we would be handling these cases either setting up secure shell access (Secure Copy, SSH tunneling) or over regular mail, sending back and fro a USB drive. Sometimes you can use email - Most data files are under 20 Mb, email is a great option to.
Submitting Data (Water Samples / Chemistry)
First, read the MCM File Format Protocols document for general guidelines on how to correctly format data for submission.
In order to ensure the database operates correctly, certain constraints are put on the values that can be entered into tables.
- We only want to put core data into the main data tables. This includes all parameters collected - major ions, DOC, Nutrients, and Field Notes. Use the Analytical Team Main Page described above to determine which samples the data manager will be expecting (all "core" samples). The "non-core" samples at the bottom of the page will also go in the database, but likely as part of a specific experiment. Contact the data manager about these samples.
- Any value you include in the STRMGAGEID column in the core data must exist in the table called STREAM_GAUGE_LOCATIONS_CORE. These are case-sensitive and must be entered exactly as the table. This should be easy since the COC Form and online COC database are already populated with these values.
- View all core stream gauge locations at the bottom of this page.
- The STRMGAGEID / DATE_TIME combination must be unique. This is very important. You may not submit multiple samples with the same stream gauge ID and Date/Time because this is the unique identifier for the sample.
Submitting Data (Stream Hydrology)
First, read the MCM File Format Protocols document for general guidelines on how to correctly format data for submission
Please refer to Stream Record Work-up Instructions below for how to work up hydrology data. Be sure to tell the data manager if a gauge was down or a stream experienced no flow for the season, as this will go in the database as well. See the pull-down links at the bottom of the page to display the format for each table.
Stream work-up are done currently using the aquarius software, please get in touch with the Data or Project manager for access to this package.
The design of the database tables is fairly consistent. All tables contain the following columns. It is very important that the columns be filled in exactly as specified - the database is case sensitive.
- DATASET_CODE – For water samples, this is the same as the Oracle table name, it is the unique identifier for that table. For hydrology, this value is always the same - "DSCRT_STRM_GAGE"
- STRMGAGEID – the name of the stream guage (or consistent location) at which the sample was taken. This value must exactly match a value entered into the Stream Gage Core Locations table at the bottom.
- LOCATION_NAME – the location in the stream at which the sample was collected. This is the LOCATION column that corresponds to the STRMGAGEID in the Stream Gage Core Locations table.
- SAMPLE_NAME – this is a free-form column for a sample name. However, this column is generally no longer needed since the sample name for core data will be the STRMGAGEID / DATE_TIME combination entered into the COC.
- DATE_TIME – the date/time should be in the format ‘MM/DD/YYYY HH24:MI’. This is the same format entered into the COC.
- COMMENTS - general comments about the sample collection, analysis, etc. Be sure to include the comments entered into the COC, comments made by the analyst, etc.
- FILE_NAME – the name of the file you’re submitting. This column is useful to locate the original file.
All data must be submitted in the same format as the database
Stream Chemistry Work-up Hints
Types of data
- Major anions and cation
- Dissolved organic carbon (DOC)
- Outside gage field measurements – flow, water temperature, specific conductance, pH, field notes
Ions, nutrients, DOC data
Compiling ion, nutrients, and DOC data
1 . Make a copy of the original data file
2 . The goal is to make the file you’re submitting look exactly like the database table, including the order of the columns, and column headers
3 . Go to the Stream Team data page above. Note the sections about submitting data (Water Samples / Chemistry) and table formats.
4. Go to the bottom of the page and choose your data type from the pull-down list and get the column headers. Save this page with a .csv extension onto your computer and open it in Excel. You may also want to do the same with the entire table contents to get an idea what the table should look like.
5 Add the consistent columns listed under the Table Formats section to your data file.
o Open up the COC database for your season (instructions on the aforementioned web page)
o Note that the values for the STRMGAGEID and DATE_TIME columns can be pulled directly from the COC database.
o Note that the LOCATION_NAME column will correspond to the STRMGAGEID in the STREAM_GAUGE_LOCATIONS_CORE table (also linked on the web page), except for Garwood and Miers. For these samples, you must specify a lat/long in the LOCATION_NAME column.
6 . Add DETECTION_LIMITS column. The detection limits should be included with the data. It’s easier to add this column and then add detection limits to the comment codes later. This column will eventually be deleted.
Delete all blanks (field and analytical) and duplicates (analytical)
Analytical blanks and duplicates are for internal quality assurance/quality control
Qualifying the data
Once the data are compiled, you will need to go through and qualify the data.
If a data point is a non detect (ND) you will need to put a comment in the comment codes column – this comment should say “non detect” and it should list the detection limit.
Check that chemical values are not below the reported detection limits. Sometimes values are below the detection limits are reported anyway by the analyst. These values should be deleted and a no detect comment should be filled in instead.
Sometimes values may be outside the calibration range – this should be noted in the comment codes field.
WHEN WRITING COMMENTS IN THE COMMENT FIELDS DO NOT USE COMMAS. The data will eventually be saved as comma-delimited and having commas messes up the download section of the website.
Nutrient data - special notes
- All nutrient data will be submitted as mass units only. There is a script in the database that will automatically calculate molar units.
- Treat PO4 (from lab) as the same as soluble reactive phosphorus (SRP) in mcmlter data table.
- Treat NH3 (from lab) as NH4 (in mcmlter data table)
Field measurement data
This involves going field note by field note to see what we wrote down. In the comment codes, it is important to note the times that various items are measured. Even though they may all go along with a sample suite – the times may differ somewhat and it’s up to the data user to decide how to make use of that information.
Making Stream Runs Table
- Copy and paste the sampling dates and times of all the stream chemistry files from that season and see how they match up.
- You will find that there are mistakes in both the times and dates. Correct them to match up to create a single date/time identifier for a suite of measurements.
- Use the COC database for the season on the web as the master list of sampling dates and times. Make sure they’re all correct and nothing is missing
Note: If an outside water temperature and specific conductance measurement are made within 10-15 minutes of the collection of a water sample, we’ll report that as a bundle of measurements i.e. just say it’s at the same time.
Submitting data to database manager
- Unhide and unfreeze worksheet and do a search for commas and eliminate all commas
- Paste data into a new worksheet and save file as comma delimited file (CSV) and submit to data manager
- Also submit original data files to manager
- File names should be based on the table name and the season. An example would be STREAM_DOC_RAW_0607.csv. Here are the table names
STREAM GAGE CORE LOCATIONS ( extended locations here )
|common_c1||Commonwealth Stream||Commonwealth Stream at C1|
|vincent_mouth||Vincent Creek||Vincent Creek at mouth|
|wharton_mouth||Wharton Creek||Wharton Creek at mouth|
|onyx_vnda||Onyx River||Onyx River at Lake Vanda Weir|
|priscu_b1||Priscu Stream||Priscu Stream at B1|
|red_bfalls||Red River||Red River at Blood Falls|
|santafe_b2||Santa Fe Stream||Santa Fe Stream at B2|
|sharp_mouth||Sharp Creek||Sharp Creek at mouth|
|vguerard_f6||Von Guerard Stream||Von Guerard Stream at F6|
|vguerard_upper||Von Guerard Stream||Von Guerard stream at upper site|
|onyx_lwright||Onyx River||Onyx River at Lower Wright Weir|
|lostseal_f3||Lost Seal Stream||Lost Seal Stream at F3|
|lyons_b4||Lyons Creek||Lyons Creek at B4|
|mariah_mouth||Mariah Creek||Mariah Creek at mouth|
|mason_mouth||Mason Creek||Mason Creek at mouth|
|mckay_mouth||McKay Creek||McKay Creek at mouth|
|mcknight_mouth||McKnight Creek||McKnight Creek at mouth|
|aiken_f5||Aiken Creek||Aiken Creek at F5|
|andrsn_h1||Andersen Creek||Andersen Creek at H1|
|andrws_mouth||Andrews Creek||Andrews Creek at mouth|
|bartlt_mouth||Bartlette Creek||Bartlette Creek at mouth|
|bohnr_lower||Bohner Stream||Bohner Stream at lower site|
|bohnr_mouth||Bohner Stream||Bohner Stream at mouth|
|harnish_mouth||Harnish Creek||Harnish Creek at mouth|
|bowles_mouth||Bowles Creek||Bowles Creek at mouth|
|uvg_f21||Von Guerard Stream||Von Guerard Stream at F21|
|canada_f1||Canada Stream||Canada Stream at F1|
|crescent_f8||Crescent Stream||Crescent Stream at F8|
|delta_f10||Delta Stream||Delta Stream at F10|
|green_f9||Green Creek||Green Creek at F9|
|harnish_f11||Harnish Creek Tributary (Relict Channel)||Harnish Creek Tributary at F11|
|harnish_f7||Harnish Creek||Harnish Creek at F7|
|house_h2||House Stream||House Stream at H2|
|huey_f2||Huey Creek||Huey Creek at F2|
|lawson_b3||Lawson Creek||Lawson Creek at B3|
|lizotte_mouth||Lizotte Creek||Lizotte Creek at mouth|
|garwood||Garwood River||Garwood River|
|miers||Miers Stream||Miers Stream