2019 Desert Tortoise Rangewide Monitoring_Data Management Plan.pdf

Desert Tortoise Monitoring

Data Management Plan

18 September 2018

Linda Allison Desert Tortoise Monitoring Coordinator US Fish and Wildlife Service 1340 Financial Blvd, Ste. 234 Reno, NV 89502

Melissa Brenneman TopoWorks 110 Dry Run Drive Noblesville, IN 46060

Rohit Patil Great Basin Institute

PMB #263

7250 S. Durango Dr. Ste. 130 Las Vegas, NV 89113-2255

2019 DESERT TORTOISE MONITORING DATA QA/QC PLAN 18 SEPTEMBER 2018

Table of Contents

INTRODUCTION

TYPES OF QA/QC CHECKS

DESIGN PHASE: COLLECTION SYSTEM AND CENTRALLY MAINTAINED INFORMATION

ELECTRONIC DATA COLLECTION SYSTEM

PHOTO COLLECTION SYSTEM

PAPER DATA COLLECTION SYSTEM

PHASE I: DATA COLLECTION AND CORRECTION

PREPARE LAPTOP SYSTEMS AND SOFTWARE

NEXUS PHONE PREPARATION AND RECOVERY FROM SYSTEM FAILURE

TRANSFER OF DATA FROM FIELD CREWS TO DATA SPECIALISTS - INITIATING FIRST LEVEL QA/QC

IMPORTING COLLECTION DATABASES INTO CORRECTION DATABASES

SPECIFIC, AUTOMATED QA/QC CHECKS FOR ERRORS AND INCONSISTENCIES

PROCEDURES FOR ADDRESSING ERRORS

NON-AUTOMATED PROCESSING STEPS AND CHECKS FOR ERRORS AND INCONSISTENCIES

FINAL PROCESSING STEPS: DATA BACKUP AND DELIVERY

DATA MANAGEMENT CHECKLIST (DEVELOPED FOR WORKING USE BASED ON ABOVE PROCEDURES)

PHASE II: DATA INTEGRATION AND FIELD COMPLETION

CREATION OF WORKING DATABASES

STEPS FOR PROCESSING THE TRANSECTS DATABASE (SEASON AND PRESEASON)

G0 DATABASE

FOR ALL OBSERVATION RECORDS, POPULATE THE ELEV_M FIELD BY EXTRACTING VALUES FROM THE

DEM. FORMATS TO APPLY

NON-AUTOMATED CHECKS FOR ERRORS

PROCEDURES FOR ADDRESSING ERRORS

FINAL DATA SUBMISSION

PHASE III: DATA FINALIZATION

INITIAL PROCESSING STEPS

SPECIFIC QA/QC CHECKS FOR ERRORS AND INCONSISTENCIES

PROCEDURES FOR ADDRESSING ERRORS

NON-AUTOMATED OVERALL DATABASE CHECKS

FINAL PROCESSING STEPS

MAINTENANCE PHASE: DATA DISSEMINATION AND LONG-TERM STORAGE

APPENDIX B: DATA DICTIONARIES

Introduction

Desert tortoise monitoring data are organized into two periods of time when data are collected:

Preseason (before actual tortoise monitoring begins) and Season (during tortoise monitoring). The preseason data include two core databases: Training Lines and Practice Transects. The season data include two core databases: Transects and G0 Observations.

Data are collected by multiple survey organizations then combined and processed in a series of phases to create final database products. Data are collected in both electronic and hardcopy format. Collection data forms, sheets, applications and databases are designed. Data quality assurance and quality control (data QA/QC, also known as verification and validation) is performed during the data collection, data integration and data finalization phases. In addition, during the second phase of data processing, after combining data from separate groups, some attribute fields are added and all fields are formatted for final processing. Data finalization occurs in phases, with consolidation and resolution of data inconsistencies representing the first phase. Data are subsequently subject to preliminary analysis to confirm that the results and outputs are consistent with the field season narrative. Once this test and the annual reporting have been completed, the data bases and paper sheets are stored and maintained for ready access during the course of the long-term (25+ years) project. Electronic data are actively hosted and put in a format available for download from the internet. The following diagram describes the overall data flow.

General roles and responsibilities for each phase.

Phase Responsible Parties Design Phase:

Collection System

GBI – Development of Collection database schemas for each database and their associated forms and checks according to U.S. Fish and Wildlife Service (USFWS) specifications. Includes any updating of database dictionaries that describe fields, field formats, acceptable values, etc.

USFWS – Development of database dictionary for use of GBI in design phase. Design of paper data sheets as the hard copy version to match electronic field data. Setting specifications for Transect Tracking and Planning application to be developed by TopoWorks.

TopoWorks (TW) – Development of Transect Tracking and Planning application.

Phase I:

Data Collection and Correction

GBI – Development of scripts to import the populated Collection databases into their corresponding Correction databases and to automate some of the first level QA/QC checks.

Survey Organizations – Responsible for o collecting data in the field on paper and electronic data collection devices (Nexus phones).

o running the synchronization operations to integrate data collected on each individual Nexus phone into its appropriate single, populated Collection database (Preseason and Season).

o using the import script to bring records from the Collection databases into their Correction databases, running first level QA/QC checks on their Correction databases, correcting errors, and delivering a weekly set of complete Collection and Correction databases, along with completed and scanned paper datasheets and extracted photos.

The electronic data are submitted on a weekly basis for interim assessments (below) prior to final delivery. Includes addressing and correcting items in the interim assessments. Includes timely delivery of Phase I products to USFWS and Topoworks.

USFWS – Performing interim assessments of each survey organization’s populated Correction database. Verification of Phase I product delivery.

Phase II:

Data Integration and Field Completion

TW – Compilation of electronic data submitted by Survey Organizations into a combined, well-organized set of (Preseason and Season) databases and associated photos and scanned data sheets. Includes review of surveyor products, combination, consolidation, formatting, generating additional data fields, and preparing Phase II products for data finalization.

Phase III:

Data Finalization

TW – Creation of final Preseason and Season spatial and non-spatial products. Includes any additional processing steps beyond the Data Integration and Field Completion phase for standardization across all databases and supporting data sheets, creation of FGDC-compliant metadata, independent review of Integrated Data products from previous phases, generating the final set of data fields, performing QA/QC checks and corrections, identifying and resolving data integrity issues with USFWS, and finalizing spatial and non-spatial electronic data. The Preseason practice transects databases are only minimally reviewed and included as non-spatial products.

Types of QA/QC checks

As outlined in the following sections, each phase of data collection and processing involves performing QA/QC checks and corrections. Errors can enter the process at data collection, but also during later steps such as data editing and during processing steps. For this reason, many checks are performed in more than one phase of data processing. Following are the main types of QA/QC checks that will be performed.

Type Description Examples of Errors Database relationships

Identifies orphans or deviations in the expected number of features related to another feature

Waypoints with no transect.

More than 26 waypoints related to a transect

Field domains Identifies values that are not within a specified range or set

Tortoise mcl > 400

Duplicate records

Identifies duplicate records Duplicate transects

Multi-field attribute conditions

Identifies records that do not meet specific conditions for attribute values

Observer does not match the lead or follow for the last waypoint

“Gsub0:visibility = Visible” but no behavior recorded

Spatial conditions

Identifies records that do not satisfy a spatial relationship

Transects that do not intersect their monitoring strata

Observations that are more than 50 meters away from their related transect

Data are collected simultaneously by the two different members of each transect crew, and sequentially by the single telemetry technician working at G0 sites. The two sets of data, paper and electronic are used to verify and correct one another. During each step of data processing, the electronic data are systematically reviewed (often using automated procedures). If a discrepancy is found in the electronic form, and a different entry is available on the paper form, the paper data take precedence. If an error is suspected on the paper data sheet, the crew recording the data is questioned and other evidence is considered to determine whether there was an error on the paper data sheet. If a discrepancy between the paper and electronic data forms cannot be corroborated one way or the other, the paper data sheet stands as the definitive data entry.

Design Phase: Collection System and Centrally Maintained Information

Electronic data collection system GBI is responsible for developing the collection database schemas for each type of database (training lines, transects, and G0) and their associated forms and checks according to USFWS specifications, which includes creation of database dictionaries that describe fields, field formats, acceptable values, etc.

Pendragon Collection database forms are built for Nexus phones based on database dictionaries for training lines, transects, and G0. The forms constrain data entry for some fields and require data entry for others (see Appendix A: Database Dictionaries for fields describing required entry and Pendragon physical domains for constraining entry). The 2019 system for Nexus phones was finalized in 2016.

Photo collection system The electronic data collection system is integrated with digital photo collection. To minimize data entry errors from individually naming then copying file names to the electronic and paper data systems, these photos are integrated into the database.

Paper data collection system

Paper datasheets are created by USFWS after completion of and in order to match the electronic data collection system.

Phase I: Data Collection and Correction

Survey organizations are responsible for collecting data in the field on paper and on Nexus phones. In addition to QA/QC steps associated with collected data, specialists prepare and maintain data collection hardware. The ability of others to evaluate and use the data also depends on the timing and format of databases and scanned paper datasheets that are delivered from Phase I. Much of this section details QAQC procedures, but their teams and the project rely on the specialists for many other associated activities.

Phase I includes implementing the synchronization operation to integrate data collected on each individual Nexus phone into the appropriate single, populated Collection database (training lines, transects, or G0) for multiple organizations. In addition, they are responsible for correcting errors.

GBI is responsible for developing scripts to import the populated collection database into Correction databases and developing scripts to automate some of the first-level QA/QC checks.

Survey organization responsibilities include the following QA/QC steps:

o Importing the data from the populated Collection databases into the Correction databases.

o Running scripts to perform the required automated checks designed to identify common errors that can be best corrected by the survey organization. It is the survey organization’s responsibility to perform an initial level of QA/QC and correct these errors (see specific checks below).

o Performing final, non-automated visual checks for errors. Examples of errors include odd times, incomplete fields, fields missing data, etc.

o Making and documenting corrections in an Errors table. Corrections to records should be clearly stated and include the fields and values that were changed. For example, instead of “corrected time”, use “changed time to match datasheet.” Unusual entries that are not corrected should also be documented in the violations table with a resolution field value explaining why the entry could not be corrected. For example, in a case where a live tortoise was found, but its MCL was not recorded, for the resolution field use “MCL could not be recorded because tortoise retreated into burrow.” It would also be helpful to include this comment in the “comments” field on the observations table. Time and date fields may be edited to reflect the correct time, but the TimeStamp fields populated by the Nexus phone should not be changed. Instead, in the Violations table, describe the origin of any anomalies in the TimeStamp fields, and indicate “exception allowed.”

o Responding to and making corrections identified in the USFWS interim assessments of their data. The interim assessments are not automated and it is difficult to identify in advance all the possible corrections that may be required. However, examples from previous years include: incorrect times; sex record M or F, but comment field indicating uncertainty; mixing observers between teams; inconsistency in recording of tag numbers; G0 tortoises that are scored “not visible” but behavior is not “unknown”,;

incorrect numeric entries into text fields that cannot therefore limit numeric entry errors (e.g. waypoint “21” recorded as waypoint “21.”); duplicate waypoints; etc.

o Delivering complete Phase I products, Collection and Correction databases (training lines, transects, and G0), for the next phase of data processing. Data collection and correction must be completed by survey organizations and delivered to USFWS and Topoworks within 10 days of final data collection, no later than 7 June 2019.

The training lines, transect, and G0 databases will be packaged in a single database and will contain data from preseason and monitoring season. The following first level processes and QA/QC checks are described for each database (training lines, transect, and/or G0), irrespective of whether the final product will be a preseason or monitoring season database.

Prepare laptop systems and software At least 2 weeks before the beginning of training, QA/QC specialists should have the following system in place, prepared to install software provided by USFWS and the database developer:

Hardware:

• Intel Pentium Dual Core processor (2.0 GHz+) or newer

• at least 4GB of RAM

• at least 10 GB of free hard drive space

Software:

• Windows 7/ 32 bit or 64 bit with all updates installed

• Microsoft Access 2010

• ArcGIS Desktop 10 (ArcView level license) (for machines involved in transect planning)

NEXUS PHONE Preparation and Recovery from System Failure The recovery procedure should only be performed by QA/QC personnel if restarting the phone does not resolve the issue. This method clears all the data from the device and then does a fresh install of the Pendragon software.

Data specialists are responsible for checking and conditioning the phones, then configuring the phones with the season’s software and forms, and logging into Pendragon software on each phone with the phone’s unique username and single password assigned to all phones in the project before handing over the phone for data collection. Conditioning procedures include cleaning the screen covers, recharging the phones, and identifying any units from the outset that do not hold charge well or have faulty accessories, such as charging cables. Initial preparations on all team phones can take several days, so leave sufficient time. Preparation of phones with the season’s software and forms mirrors procedures used later to recover individual phones from system failure.

Nexus phone set-up and recovery procedures need a wireless internet connection (Wi-Fi), Android APK installer for Pendragon, and the username and password for our Pendragon account. “Sync” operations in this text refer to the interaction between the Nexus phone and the internet.

Setting up Nexus phones at the start of the season, also used for recovery from system failure

This procedure will delete all Pendragon data collected on the Nexus phone. Make sure you the phone is connected to internet before you perform this operation.

1. Install Pendragon on the phone (If Pendragon forms is already installed then skip to step 2)

Make sure the Nexus phone is connected to the internet and then open the Chrome browser.

Navigate to http://pendragonforms.com/support.html. Look for “Download Pendragon Forms APK v1.956A” link and click on it to download the latest Pendragon Forms APK. Tap on app drawer icon (the middle icon on the bottom of the phone, white circle with six dots), open “Downloads,” and then click on “PendragonForms-v1.956A.apk” to install the Pendragon application.

2. Clear all the data, server address, username, and password from Pendragon software On the Nexus phone, tap on the app drawer icon (a white circle with six dots on the bottom of the phone in the middle) then scroll down and open the phone settings by tapping the “gear” icon. Scroll down to “Apps” and tap it. Scroll down on the list of applications and click on “Pendragon Forms”.

Click on “Storage (xx MB used in Internal Storage)” and then click the “CLEAR DATA” button. On the confirmation dialog “Delete App Data?” tap “Ok” to delete all the data from “Pendragon forms.”

3. Sync Pendragon and load the collection database forms Open the Pendragon application. You will be prompted to “Please enter your server address”. Make sure “Pendragon Account #” is selected and then enter “2951” and tap on ”Connect.” It will redirect to the Pendragon login screen. Enter the username and password for the current phone and tap “Ok”.

The phone will automatically sync and download all the latest forms for the username.

4. Test Phone Setup Open the Pendragon application, click on the Forms button, then select the Transects_17 form. Click on the + sign to create a new record. Collect sample records to confirm that the perpendicular distance calculation and GPS grabs are working and that the crew names are correct and complete for your group. Make sure you delete these sample records on the Nexus phone before you hand it over to the crews. The device is now ready for field data collection.

Updating the Collection database on the Nexus phone

During training or early in the field season, small updates are often made to the collection database. Use the following procedure to replace the older version of the Pendragon database on the Nexus phone with the updated Pendragon database version. Also, any time the phones are synched to upload data, any database updates will automatically be downloaded as described below.

1. Material to have at-hand

• The Nexus phone connected to the internet using Wi-Fi

2. Install the newest version of the Pendragon forms.

• Open the Pendragon application on the Nexus phone. Tap on the “Sync” button to download the latest collection database forms to the phone.

Transfer of data from field crews to data specialists - Initiating first level QA/QC

Whenever paper data are transferred to the QA/QC specialist (also during training), the following procedures are applied:

While crews are present, the QAQC specialist should:

• Evaluate legibility of handwriting

• Confirm that the Nexus phone is functioning correctly

• Review paper sheets for any blank fields http://pendragonforms.com/support.html

• Check whether the transect drawing indicates standard or non-standard o If standard, check that this matches the associated field o If non-standard, check that this matches the associated field, and that at least one of the 3

“obstacles” fields has been used to indicate the reason for using a non-standard transect

• For transects with interruptions o Check that the transect was marked as non-standard o Check that interruption points and new transect records are marked on datasheets If any tortoises are indicated as “mcl_ greater_180=unknown”, question the crew for any further, potentially discriminating information, and remind them that every effort should be made to collect information indicating “yes” or “no” for this field.

Importing Collection databases into Correction databases To download the latest data into the collection database, open the FORMS32K.accdb database located in “C:\PendragonForms\” folder. After opening, navigate to the “External Data” tab on top and click on “ODBC Database”. In the Dialog, select the first option to Import the source data and then select the “LDS_Database” Data Source Name from the “Machine Data Source” tab. Click the “Ok” button. A list of tables available in the online database is displayed. Select all the tables starting with vw_noimages and tables starting with form_id that have not already been selected and click “Ok”. Access will copy all tables from the online database into the current database. This will complete the collection database download procedure.

Before running the QA/QC scripts for the first time make sure that the Correction database (Import_QAQC.mde) exists in the same folder on the machine (“C:\PendragonForms”) as the Collection database. If not, copy the latest version of the Correction database to this folder.

Open the QA/QC scripts database by double-clicking “Import_Correction.mde” file.

Under “Forms” on the left-hand list of objects, double click on “2018_LDS_Import_QAQC” to open it.

Click on “Import Raw Data” at the bottom of the form to import the Collection database into the Correction database. If the training database also needs to be imported, check the "Import Training Data" checkbox before clicking "Import Raw Data". The following automatic functions are executed as part of the import process without further user input:

Operations performed throughout all tables in all databases

• Null string values in the Pendragon Collection database are assigned “null“ in the Correction database

• Null numeric values in the Pendragon Collection database are assigned -99 in the Correction database

• Null date and time values in the Pendragon Collection database are assigned 1:00AM in the Correction database

Training database (polystyrene tortoise model transects and observations)

• Populate trial_number, team_number, training_line_color, transect, training_date, tran_bearing fields in the child form from transect form.

Transect databases

• Populate transect number, stratum, team_num, group_, date_ fields in all child forms from transect form. For practice transects at the Large Scale Translocation Site, the stratum should be LSTS.

• Populate duplicate_tran in the Transects table Initiate with the value “N”. (QAQC specialists will subsequently run the duplicate check, then the duplicate_tran field would be changed to Y. Any transects that are duplicates will not be processed like the interrupted transects even though they have a decimal (.9) in their tran_num.

• Populate latitude and longitude values from gps grab in all forms

• Populate transect start and end time from Wpt 1 and 99.

• Populate drop off time and return time from Wpt 0 and 100.

G0 database

• Populate start_time and end_time fields for G0 table from first and last G0 observations.

• Populate latitude and longitude values from gps grab in all forms

• Populate date, time_, G0_site, and group_ in all child forms from G0 forms.

All errors encountered during the import process are logged into the file “LDS_Import_Log.txt” located in “C:\Program Files\Forms3”. Check this file after each import to see if any errors were encountered during the import process. This year photos will only be present in the collection database (not available in the correction database). A separate procedure will export photos from the collection database into jpg files.

If the database cannot be imported, there are two identified issues: 1) Presence of single- or double-quotes in the photo file or comments fields. These characters must be edited in the Collection database before it can be imported. 2) Comments string has too many characters. The string needs to be shortened in the Collection database before it can be imported.

Once data has been imported into the Correction database, the Collection database should be updated so that records in each table are marked as exported (exported = 1). Each time records are imported into the Correction database, the number of records between the Correction and Collection databases should reconcile, and this should be checked before executing a query to mark records as exported. To confirm that the databases are consistent, the data specialist should track the number of records they create or delete in the Correction database. After accounting for these records, the number of exported records in the Collection database should match the number of records in the Correction database.

Once the records in each table are checked, a query is manually executed in the Collection database to indicate all records have been imported to the Correction database

• Click “View & Download”

• Click the “view/edit data” button for the table

• Click the SQL tab and enter (form_id_8000000## refers to the table id):

UPDATE ‘form_id_8000000##’ SET ‘exported’ = 1 WHERE 1

• Click the “Go” button.

• This needs to be done for each table in the database. You can access the next table by exiting out of the current one, then clicking on the next table, or you can change the form id (form_id_8000000##) in the current query to match the form id of the next table.

Note: It is important to avoid importing data while crews are actively syncing in the field. Otherwise, it is possible that records that were not exported will be marked as though they were. Any records synched to the Collection database between confirming concordance with the Correction database and running queries to indicate exported = 1 will actually not be in the Correction database but will not be imported the next time Import Raw Data procedure is run.

Specific, automated QA/QC checks for errors and inconsistencies The following describes the specific required scripts that will be executed during first level QA/QC. At a minimum these scripts should be run and errors addressed on a weekly basis.

After executing the QA/QC checks all errors encountered are logged into the Error tables. For subsequent QA/QC checks, all uncorrected errors will be logged again into the Errors table unless the error status field is marked as “exception allowed”, “unresolved” or is blank.. All errors from Transect database tables (Transects, Waypoints, OppLiveObs, OppCarcObs, TranLiveObs and TranCarcObs) are logged into "Errors_Transects" table, all errors from G0 database tables (G0_Start, G0_Obs and

G0_OppLiveObs) are logged into "Errors_G0" table and all errors from Training database tables (Tran_Train and Tran_Obs) are logged into "Errors_Training" table.

Training Database Checks

Training transects (training)

Tables included in check: Train_Tran.

• Checks for duplicate transects for the same team on the same day with the same trial_number and same line color.

Error description: duplicate training transect.

Tortoise ID (training) Tables included in check: Train_Obs.

• Checks for duplicate tortoise_id in same day, same team, same line_color. Error description: contains duplicate tortoise_id, trial_number, team_number, and training_line_color values

Observer name and position (training) Tables included in check: Train_Tran, Train_Obs.

• Checks for observer_name and observer_position not matching lead and follow fields in the Train_Tran table.

Error description: observer_name and observer_position do not match lead or follow in Train_Tran table.

Transect segment number (training)

• Checks for transect_seg_num not matching calculated value from tran_bearing and start_post.

Error description: transect_seg_num does not match tran bearing and start post.

Time (training) Tables included in check: Train_Tran, Train_Obs.

• Checks for training_start_time not before training_end_time.

Error description: training_start_time is after training_end_time

• Checks for observation_time not between training_start_time and training_end_time.

Error description: observation_time is not between training_start_time and training_end_time

Radial Distance (training)

• Checks for radial_distance_m with more than one decimal place.

Error description: radial_distance_m has more than one decimal place

Bearing (training)

• Checks for local_bearing not within 40 degrees of tran_bearing.

Error description: local_bearing is not within 40 degrees of tran_bearing

Perpendicular distance (training)

• Checks for perp_distance_m greater than radial_distance_m.

Error description: perp_dist_m is greater than radial_distance_m.

• Checks for perp_distance_m greater than 25 m.

Error description: perp_distance_m is greater than 25 m.

Transects and G0 Database checks

Database relationship checks o Missing waypoints (transects)

Tables included in check: Waypoints

• For non-interrupted transects, identify transects that are missing drop off, start, end, or return waypoints. The Error record should be recorded for the Transects table, giving the tran_prime_key for the transect with the missing waypoint.

Error description: missing waypoint xxx.

• For interrupted transects (those with end_tran_part = Y, note: do not base this on the xxx.x format as that format is also used for duplicate walks of a transect), identify main transect (xxx) with missing drop off and start waypoint and for the last part (xxx.x) identify missing end and return waypoints. The Error record should be recorded for the Transects table, giving the tran_prime_key for the transect with the missing waypoint.

Error description: missing waypoint xxx.

o Orphan records (transects and G0) Tables included in check: Waypoints, OppCarcObs, OppLiveObs, TranCarcObs, TranLiveObs, G0_Obs

• Check for orphan records created in sub-forms when the transect from the transects table has been deleted, G0_start record has been deleted, or training transect record has been deleted.

Error description: missing parent record.

Duplicate record checks o Duplicate transects (transects)

Tables included in check: Transects

• check for duplicate transect numbers.

Error description: duplicate tran_num.

Duplicate transects should be numbered by concatenating the original tran_num with “.9”. If more than one duplicate is walked, continue by added extra decimal values of 9 (.99, .999, etc.). Duplicate transects that also have interruptions should be numbered first with the .9 for the duplicate transect, then a following decimal to describe the portion of the transect the record represents (e.g. 341.9, 341.91, 341.92).

o Duplicate waypoints (transects) Tables included in check: Waypoints

• check for duplicate waypoints in the same transect.

Error description: duplicate waypoint.

Multi-field attribute condition checks o Lead and Follow (transects)

Tables included in check: Transects

• Check for transects that have the same observer name for ‘observer1’ and ‘observer2’ fields.

Error description: observer1 and observer2 are the same.

• Check for null ‘observer1’ and ‘observer2’ values;

Error description 1: observer 1 is Null Error description 2: observer2 is Null.

o Time (transects, and G0)

Tables included in check: Transects, Waypoints, TranCarcObs, TranLiveObs, G0_Start, G0_Obs

• Check all time values against these logical domains from database dictionary:

Table time_field Begin domain End domain Transects do_time 4:00am 10:00am Transects tran_start_time 5:00am 10:00am Transects tran_end_time 8:00am 6:30pm Transects ret_do_time 8:00am 6:30pm Waypoints time_ 5:00am 6:30pm G0_Obs time_ 5:00am 6:30pm TranCarcObs time_ 5:00am 6:00pm TranLiveObs time_ 5:00am 6:00pm G0_Start tran_start_time 5:00am 10:00am G0_Start tran_end_time 8:00am 6:30pm

Error description: [time_field] is not within domain [beg] to [end]. (Substitute the field name and domain values, Example: do_time is not within domain 4:00:00 AM to

10:00:00 AM.

• For interrupted transect segments other than the first main transect record, do not apply start_time and end_time checks.

o Date (transects, and G0)

Tables included in check: Transects, Waypoints, TranCarcObs, TranLiveObs, G0_Start,

• Check all date values against logical domain from database dictionary

Error description: [date_field] is not within domain [beg] to [end]. (Substitute the field name and domain values, Example: date is not within domain 3/01/2019 to 5/31/2019.

o MCL (transects and G0) Tables included in check: OppCarcObs, OppLiveObs, TranCarcObs, TranLiveObs, G0_Obs

• Check for mcl_mm greater than 180, but mcl_greater_180 not yes Error description: inconsistency between mcl_mm and mcl_greater_180.

• Check for mcl_mm less than 180, but mcl_greater_180 not no Error description: inconsistency between mcl_mm and mcl_greater_180.

• Check for mcl_mm greater than 0, but mcl_greater_180 is unknown.

Error description: inconsistency between mcl_mm and mcl_greater_180.

• Check for mcl_mm equal to 0 if temperature is less than 36°C.

Error description: Mcl_mm is 0.

• Check for carc_condition intact, but mcl_mm is Null.

Error description: inconsistency between carc_condition and mcl_mm.

• Check for carc_condition disarticulated, but mcl_mm is not Null.

Error description: inconsistency between carc_condition and mcl_mm.

• Check for tortoise location is not burrow or RockBurrow or SoilBurrow, and temperature is less than 36°C, but mcl_mm is null.

Error description: inconsistency between tort_location and mcl_mm.

o Temperature (transects and G0) Tables included in check: OppLiveObs, TranLiveObs

• Check for inconsistency between temp_C and temp_greater_35C, Error Description: inconsistency between temp_C and temp_greater_35C o Visibility (G0) Tables included in check: G0_Obs

• If visibility is “no” then behavior must be “unknown” (G0).

Error description: Inconsistency between visibility and behavior.

Spatial condition checks

• UTM zone

Tables included in check: Waypoints, OppCarcObs, OppLiveObs, TranCarcObs, TranLiveObs, G0_Obs

• Check for strata other than “BD” and “GB” having zone 12 or if gps zone and manual zone are missing.

Error description1: gps_zone or manual_zone are 12, but stratum is not BD or GB.

Error Description2: missing gps zone or manual zone.

o Easting and northing Tables included in check: Waypoints, TranCarcObs, TranLiveObs, G0_Obs

• Check for easting or northing coordinates that fall outside of the assigned monitoring stratum. Coordinates for this check are in the Stratum_Info table of the database and should match coordinates in this plan (see Spatial condition checks for Phase II in this Plan for stratum and G0 coordinates).

Error description1: easting or northing are not within stratum boundary.

Error description2: easting or northing are not within G0 site boundaries.

o Missing location data Tables included in check: Waypoints, OppCarcObs, OppLiveObs, TranCarcObs, TranLiveObs, G0_Obs

• Check records with missing automatic and manual gps coordinates (transects, G0 observations).

Error description: missing gps or manual easting or northing.

Procedures for addressing errors Whether an error was identified automatically or by visual examination, at least one and possibly more error records must be created to document these inconsistencies and any resulting changes to the database. It is common to change a database only to later discover that the original data were unusual but not incorrect, or that a different correction is required. In these cases, revisions are straightforward if you have documented your edits, and are a headache if you have not.

Error tables and error records All errors encountered are logged into either Errors_Transects, Errors_G0, or Errors_Training tables in the Correction database. Following are the fields in each of the error tables. All the error tables share "Common fields for all error tables," but also have specific fields (d1, d2, etc.) depending on the error table. Fields (a)-(f) and all specific fields are automatically populated by the scripts, whereas the (g)-(j) fields are manually entered by QA/QC specialists as they address each error.

Common fields for all error tables

a. ID – auto-number used to identify record

b. date – the date when the QA/QC scripts were run

c. table_name – table name of the error record.

d. fields specific to error table.

e. prime_key – primary key of the table with error record

f. error_desc – short description on type of error found

g. old_value – old incorrect value of the field

h. new_value – null, new correct value entered by QA/QC specialist

i. resolution – null, steps taken by QA/QC specialist to resolve the error

j. resolver – null, name of agency correcting the errors

k. error_status – null, status of error after correction

Specific fields in Transects Error table d1. tran_date – date when the transect was walked d2. tran_num – transect number for error record d3. stratum – stratum for the error record d4. team_num – team number for the error record d5. wp_obs_num – waypoint or observation number depending on error table

Specific fields in G0 Error Table d1. G0_date – date when the G0 observation was recorded d2. G0_site– G0 site for the error record, null if not applicable d3. group_ – group for the error record, null if not applicable d4. tort_obs_num - tortoise ID or observation number depending on error table

Specific fields in Training Error table d1. training_date – date when the training record was collected d2. trial_num – trail number for error record d3. team_num – team number for the error record d4. training_line_color– training line color for the error record, null if not applicable d5. transect - transect number for the error record.

d6. tortoise_id – tortoise_id for the error record, null if not applicable

Records with errors in the data tables can be identified using the information in table_name, prime_key, tran_num, stratum team_num, wp_obs_num and tran_date fields of the Errors table. If information is not available, the associated fields will be either null (text field), -99 (numeric field), or 1:00AM (time fields). As also described in the sequence of procedures below, if one of the above identifier fields such as date, tran_num, or stratum was part of the identified error, you will ultimately also edit these identifier fields in both the data table and the error table so they both reflect the correct information; these edits allow the error record to continue to match its associated and repaired record in the data tables.

Add error records for errors not identified by QA/QC scripts For errors that were identified during systematic visual inspection of tables (see Non-automated checks for errors and inconsistencies, below), the error records are obviously not generated automatically in the Errors table; a manual error record will need to be created. The QAQC specialist will have to populate fields (b)-(k) and fields specific to the associated Error table for each record that is manually created. Care should be taken to enter the information accurately as these fields are critical for identifying the correct record.

Decide whether the identified error is correctable To determine whether an identified error can be corrected, review the paper datasheets or contact crew members, if necessary. The two sets of data, paper and electronic, are used to verify and correct one another. During each step of QA/QC, the electronic data are systematically reviewed (often using automated procedures). If a discrepancy is found in the electronic form, and a different entry is available on the paper form the paper data take precedence. If an error is suspected on the paper data sheet, the crew recording the data should be questioned and any other evidence considered to determine whether an error was committed on the paper data sheet. If a discrepancy between the paper and electronic data forms cannot be corroborated one way or the other, the paper data sheet stands as the definitive data entry.

Correct errors in the QA/QC Database If the error can be corrected, correct the erroneous record in the data table and prepare to use the new information to update the associated error record.

Update the error table resolution fields and the record’s error_status field to explain action taken After the error has been fixed or determined that it cannot be fixed, the old_value, new_value, resolution, resolver and status fields in the appropriate Errors table (Training, Transects, or G0 Error table) must be manually completed for the error record. Note that if one of the identifier fields such as date, tran_num, or stratum was part of the identified error (it is now reported as an “old_value”), you must edit these identifier fields in both the data and error tables to reflect the correct new_value; these edits allow the error record to continue to match its associated and repaired record in the data tables.

Whenever you make a determination on the error_status, you are a “resolver”, and this field should contain the name of the person/agency addressing the error. The “resolution” column should clearly mention steps taken to correct the error or should explain why the error could not be corrected.

Error_status = Resolved Any changes to data records should be represented by error records with an error_status value of “resolved”. If you need to make a correction to a record for which there is no error record, then you must manually add the error record and use the resolution field to describe the correction, as described above. If you have thoroughly reviewed the database records and paper datasheets, as well as consulted the field monitors, and a correction is still not possible, you should mark the record as “exception allowed” or “unresolved” and use the resolution field to explain why the exception is being allowed or why the record cannot be resolved during QAQC I. The “old_value” columns, if automatically populated, should contain the original incorrect value of the field and “new_value” column should contain the correct value replaced.

For example if the time field is corrected, the resolution should be “changed time to match the datasheet” and the “old_value” field should contain 6:00PM and “new_value” should contain

6:00AM.

Error_status = Unresolved If the error cannot be corrected during QAQC I, but needs further evaluation during QAQC II or III, its error_status is “unresolved.” You are still the “resolver” for QAQC I because you addressed the error, so the resolver field should contain your name or initials. Marking the status to “unresolved” will skip the error (and not log it again in error tables) in subsequent QA/QC checks. The resolution field should describe why the error_status is unresolved and provide any information that might help evaluation during QAQC II or III.

Error_status = Exception allowed An exception may be allowed because a correction could not be made (i.e. the paper datasheet matched the electronic data or the field monitors could not explain the discrepancy) or because the record did not represent an error (i.e. the perpendicular distance really was greater than 25m, the tortoise retreated into a burrow, the tortoise was too small to tag).

It is especially important to describe why error records are being left as exceptions. For example, if an mcl_mm field is blank, it matches the paper datasheet, and could not be resolved by consulting with the field monitors, the resolution field should be “mcl_mm not measured, matched datasheet, team could not explain”. Another example might be that one of the Nexus phone units failed and the data had to be entered into the electronic units later in the day. When this happens, there will be error records generated because the TimeStamp when the data was entered will not match the times entered from the paper datasheets. In these cases, the error records should be marked as “exception allowed” and the resolution field should note the device failure such as “Nexus phone failed, data was entered manually later in the day”. If a crew forgets their Nexus phone and only records the data on the paper datasheets while walking the transect, these errors would also be marked as “exception allowed” but the resolution would be “team forgot Nexus phone, data was entered manually later in the day”. All records marked as “exception allowed” should have an explanation in the resolution field. Ideally, the associated records in the database table (Transects, Waypoints, etc.) would also have an explanation in the comments field. Make any explanations in database table comments fields as clear and concise as possible. They do not need to include all of the details that you might include in the error table resolution field. If you changed any part of a record, the associated error record should not be marked as “exception allowed”.

Marking the status to “exception allowed” will skip the error (and not log it again in error tables) in subsequent QA/QC checks.

Error_status = Script error In this case, an error record has been generated due to overly restrictive rules in an automated check. No edit to the original record is required, and if possible a revised version of the QAQC database will be created to avoid this mistake. If that happens, the error records can be removed from the Error table; the error records will not be regenerated the next time error scripts are run. The QAQC database has been revised in the past when interrupted transects are documented with new electronic records for each segment; the start time for the segments may be later than scripted, although the transect was started in the correct window of time.

Alternatively, if the QAQC database is not revised, these resolutions are equivalent to “exception allowed,” signaling that no further review of this case is required. For this reason, it is important to use this resolution deliberately and to alert all other data management cooperators to this original issue. The QAQC database may not be revised simply to address a transmittered animal that has wandered beyond the scripted UTM polygon that was anticipated before the field season.

Update the paper data sheets to reflect changes and match the electronic database If an error on the paper data sheet is corroborated, the QA/QC specialist identifying and determining the data error draws a single line through the erroneous data on the paper sheet, neatly prints in ink the correct data above the erroneous data, then initials and dates the correct data entry. This data correction is then input into the errors table of the digital database to maintain a record of the correction.

After the paper sheet has been scanned, it should no longer be edited by hand. Instead, all further corrections will be made electronically as Adobe “sticky notes.” These notes are anchored to the point where they are inserted, and automatically indicate the creation date. Although an identifier is added for the commenter, care should also be taken to type in the commenter’s initials at the start of the note.

Necessary edits are documented by using language like, "Change field "zzzzz" from xxxx to yyyy."

If multiple related changes need to be made, a general explanation is provided first: "Crew confirmed verbally that the tortoise was in a pallet, not a burrow. Change "tortoise_location" field from pallet to burrow. Change field "burrow_visibility" from high to null. Change field "tortoise_in_burrow_visibility" from high to null." The edited version should be given the same name as the original file so that it will automatically replace earlier versions of the scanned file (see “Deliver Paper Datasheets,” below)..

Non-automated processing steps and checks for errors and inconsistencies The following lists describe checks for common errors that may not be identified by the automated checks, or that may have appeared in the process of making corrections for the automated checks. The lists are not exhaustive, but illustrate ways to methodically examine data tables for missing or inconsistent values.

When an error or inconsistency is found, it should be manually entered into the appropriate Error table, taking care to correctly identify the record by its TableName and TableRecord_ID fields. The error description should follow a similar format as the automated checks, and due to the types of checks that are possible manually, will often be of the form: Invalid [field name] (for instance, “Invalid team_num”, “Invalid observer_name”). Many of the non-automated checks are purposefully redundant of the automated checks above or involve checking for unusual entries in fields that are populated via a pull-down list. This provides a last review of the data to catch errors or inconsistencies that might have been introduced earlier in the QA/QC I process. Redundant checks are in italic below.

Checks based on format type and common to all tables

• Check that timestamps are valid by sorting them in ascending and descending order. The TimeStamp fields are never edited, but descriptions of the cause for invalid times should be provided in the Violations table and the violation status should be ‘exception allowed’.

Error description: Invalid TimeStamp

• Check numeric fields for errors (e.g. decimals after numbers, zero preceding number). Sort by field. These errors can occur where the field type is text, such as tortoise IDs (Transects and G0 forms), and Easting/Northing fields.

Error description; Invalid number format

• Use the dropdown arrow for each text or categorical field to view unique entries looking for typos or values that are not consistent, such as observer names with typos, entries with extra punctuation, values that are abbreviations, etc.

Error description; Invalid text entry

• Sort to view records with any comments, looking for indications that there are questions about field values.

Train_Tran Table

• Sort by total_time, look for unusually high or low numbers

• Sort by team_number, look for unusually high or low numbers

• Sort start_post descending, look for letters higher than ”L”

• Sort transect_seg_num descending, look for numbers higher than 8

• Sort training_date ascending and descending, look for dates outside of training sessions

• View unique values for lead and follow, look for misspellings of observer names

Train_Obs Table

• View unique values for observer_name, look for misspellings of observer names

• Sort tortoise_id in descending order, look for ID#s larger than the known highest ID#(288)