Stream Team Data and Protocols - from the field to the database

Field Protocols

  • 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.

  1. 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.
  2. 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.
  3. 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.

Table Formats

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
  • Nutrients
  • 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_CHEMISTRY_RAW

§  STREAM_DOC_RAW

§  STREAM_NUTRIENTS_RAW

§  STREAM_FIELD_MEASUREMENTS

 

   STREAM GAGE CORE LOCATIONS ( extended locations here )

STRMGAGEID STREAM_NAME LOCATION
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