Appendix_M_TES_EO_and_survey_data_exchange_user_guide.pdf

Threatened, Endangered and Sensitive Plants Element Occurrence

Data Exchange User Guide United States Department of Agriculture Forest Service Natural Resource Manager February 2008 Updated March 2012

TES Element Occurrence Data Exchange

USDA – Forest Service - NRM iii

Table of Contents

Introduction

Purpose of the Guide

Skills Required

General Format of Data Exchange Tables

Steps in the Process

Setting the Data Category and Populating Data Exchange Tables

Spatial Data

Data Cleaning, Conversion and Loading

NRP_DX_EO_DATA

NRP_DX_EO_SITE_HAB

NRP_DX_EO_ASSC_SPEC

NRP_DX_IMAGES

NRP_DX_EO_LOCATION

NRP_DX_PEOPLE

Data Validation after Loading

USDA – Forest Service - NRM 1

Introduction

The NRM Data Exchange is a tool developed to assist users in moving data from a variety of electronic data sources into NRM databases at the Forest Service’s Data Center in Kansas

City. The Data Exchange supports a variety of Data Categories including data about

Threatened, Endangered and Sensitive Plants Element Occurrences (TESP-EO). The tool provides a set of interim database tables, called Data Exchange tables, used as staging areas for conversion and cleaning of legacy data, before loading them into NRM. This processing insures that the data are compatible with mandated protocols and database requirements of the NRM applications.

Purpose of the Guide

This guide’s purpose is to provide users with what they need to know for most effectively using the NRM Data Exchange to load the data collected using the TESP-EO Protocol. It includes process steps to populating interim staging tables specific to TESP-EO, documentation on the design of the various database tables, and design details of the

Oracle output views used for post-loading validation. This guide does not provide step-by-step directions on how to use the NRM Data Exchange and its Cleaning Tools. For that, users will want to download and refer to the NRM Data Exchange User Guide.

Skills Required

Users working with the NRM Data Exchange should have a thorough understanding of the TESP-EO Protocol and a working knowledge of the NRM Data Exchange Cleaning

Tools. In addition, it will be beneficial for users to have a basic understanding of database design principles, and a working knowledge of Microsoft Access©. Although not required, additional skills in Oracle SQLPlus can increase the effectiveness of using the tool.

General Format of Data Exchange Tables

Data Exchange tables are similar to spreadsheets in that they do not have constraints on what is entered and lack physical relationships among the tables. This allows the greatest flexibility for users moving data from existing electronic systems into the Data Exchange tables. Users can access and update information within these tables directly through the

Oracle views, or the views can be accessed via the NRM Data Cleaning Tools through the Open MS Access Database for this Dataset button on the main menu of the Data

Cleaning Tool.

http://sforge.fs.fed.us/sf/docman/do/downloadDocument/projects.nris_dex/docman.root.user_guides/doc6437

USDA – Forest Service - NRM 2

Steps in the Process

Setting the Data Category and Populating Data Exchange Tables

Before any data can be put into the Data Exchange tables, the user must select a Data

Category. On the main screen of the Data Cleaning Tools set the Data Category to TES

PLANTS ELEMENT OCCURRENCE, click the Create New Dataset button, then populate the information in the dialog box. This will create a new blank dataset in the

Data Exchange.

The TESP-EO Data Exchange includes six interim staging tables, described in Table 1, below.

Table 1 - Descriptions of TES Element Occurrence Data Exchange Tables

TABLE

ORDER

VIEW NAME TABLE DESCRIPTION UNIQUE KEY

1 NRP_DX_EO_DATA Table stores basic element occurrence data for TES plants.

SITE_ID

START_DATE

2 NRP_DX_EO_SITE_HAB Information related to either the site or the habitat occupied by the Element Occurrence.

SITE_ID

START_DATE

3 NRP_DX_EO_ASSC_SPEC Information related to the associated plant species occupying the Element Occurrence site.

SITE_ID

START_DATE

PLANT_CODE

4 NRP_DX_IMAGES Table to store images associated with features.

SITE_ID

START_DATE

IMAGE_ID

USDA – Forest Service - NRM 3

5 NRP_DX_EO_LOCATION Table to store location information related to features on the ground.

SITE_ID

START_DATE

6 NRP_DX_PEOPLE Table that stores names of people associated with various activities.

LAST_NAME

FIRST_NAME

The TABLE ORDER column indicates the order in which users should enter data into the Data Exchange tables. The NRP_DX_EO_DATA table contains the fundamental information about the Element Occurrence. This table must be populated in order for any of the other EO data to load into NRM through the Data Exchange. Table 1 also contains a column labeled UNIQUE KEY, identifying columns in the table that must be unique for each record in the table. For example, in the NRP_DX_EO_DATA table, the columns SITE_ID and START_DATE make up the unique key, so every record must have a different combination of those values (see examples below).

Example 1: Valid data for unique key Example 2: *Invalid data for unique key

SITE_ID START_DATE SITE_ID START_DATE

06010400001 6/1/2005 *06010400001 6/1/2005

06010400001 6/15/2006 *06010400001 6/1/2005

06010400002 6/15/2006 06010400002 6/15/2006

In Example 1 there are two rows with the same SITE_ID (06010400001), but with different start dates. They indicate that the same EO site was inventoried on 6/1/2005, then re-inventoried on 6/15/2006. Site 06010400002 was only inventoried once, on

6/15/2006. Example 2 shows slightly different information, and indicates which records will turn out to be invalid. Although users can enter duplicate data like this (same ID and

DATE) in the Data Exchange tables, they will show up as errors during the data cleaning process.

The UNIQUE KEY will also indicate how tables within a Data Category are related.

Figure 1 is a relationships diagram of various tables in the TES Plants Element

Occurrence Data Category:

USDA – Forest Service - NRM 4

Figure 1- TES Element Occurrence Table Relationships Diagram

Based on the relationships diagram in Figure 1, the NRP_DX_EO_DATA table must contain all the element occurrence sites. In addition, if there is an inventory of associated species found in a particular EO, then there should be a set of corresponding records in the NRP_DX_EO_ASSC_SPEC table with the same SITE_ID and START_DATE. As indicated in Table 1, the unique key columns for NRP_DX_EO_ASSC_SPEC is a combination of SITE_ID, START_DATE and PLANT_CODE which means there can be records in this table for the same site so long as each record has a different plant code.

Example 3 shows data from the NRP_DX_EO_ASSC_SPEC table. Notice site ID

06010400001 has an inventory date of 6/1/2005, and has multiple associated species.

The NRP_DX_PEOPLE table is shown multiple times in figure 1. This indicates the table NRP_DX_PEOPLE is used as a reference table for multiple columns.

Example 3: Valid data for NRP_DX_EO_ASSC_SPEC_VM unique key

SITE_ID START_DATE PLANT_CODE

06010400001 6/1/2005 PSMEG

06010400001 6/1/2005 PIPO

06010400001 6/1/2005 LIBO2

06010400002 6/15/2006 PSMEG

USDA – Forest Service - NRM 5

Spatial Data

A spatial feature is required for every TESP-EO site in NRM. The GIS data to be used with the Data Exchange are in a standard ESRI Shapefile format. Shapefiles users create for this particular business area must have two standard columns with the following definitions:

Name Alias Type Length Precision Scale

SITE_ID Site ID Text 30 0 0

EO_DATE Visit Date Date 36 0 0

This Data Category supports both polygon and point features; there should be only one feature per record in the Data Exchange tables. Polygon features may be either single polygons or multi-part polygons.

The relationship between the shapefile and data in the Data Exchange staging tables is through the NRP_DX_EO_DATA table. For every record in the NRP_DX_EO_DATA table there must be a corresponding record in the shapefile with the same information in the columns, as shown in the table below. For example, if there is a EO with a site id of

06010400001 and an inventory date of 6/1/2005, then that information must reside in

NRP_DX_EO_DATA and in the corresponding columns in the shapefile.

Column match between the Data Exchange and the Shapefile

DATA EXCHANGE COLUMN

(NRP_DX_EO_DATA)

SHAPEFILE COLUMN

SITE_ID SITE_ID

EO_DATE EO_DATE

Based on the current Protocols for TESP, only one species is allowed per EO (point or polygon).

Data Cleaning, Conversion and Loading

As noted in the Purpose statement, this guide is not designed to teach users the steps in data cleaning, conversion and loading using the Data Exchange; however, listed below are some important aspects of each table.

NRP_DX_EO_DATA

This is the main table holding core EO data. Every EO site entered in any of the other tables should already be in this table.

There should be a spatial feature for every site in this table.

The CANOPY_COVER_METHOD column tells whether cover percent or a cover classification was used to describe canopy and ground cover at the EO (see Table 2 below). The value in this column, therefore, dictates the values used to describe all cover measurements at the EO. For example, COVER PERCENT limits cover data to actual percentages, so columns that refer to cover code or cover class should be

USDA – Forest Service - NRM 6 null. If there are values in these columns, the DX Cleaning Tool will identify them as errors.

Table 2 - List of Valid Values for CANOPY_COVER_METHOD

VALUE DESCRIPTION

COVER PERCENT Percent canopy cover

DAUBEN Daubenmire's Canopy Cover Classes

GYA Noxious Weeds - Greater Yellowstone Codes. National Rangeland Noxious Weed Classes.

NRMCOV Cover Ten Codes - National Rangeland Canopy Cover Classes

NRP_DX_EO_SITE_HAB

This table stores physical site and habitat characteristics.

The table contains a series of columns to link terrestrial classifications in the database

(e.g., existing vegetation or wetland classifications) to the EO.

The cover class percent and cover class code columns should be populated appropriately based on the CANOPY_COVER_METHOD identified in the

NRP_DX_EO_DATA table.

NRP_DX_EO_ASSC_SPEC

This table is a many-to-one relationship to DATE in the NRP_DX_EO_DATA table;

i.e., there can be many different species (rows) in this table for each site/date.

NRP_DX_IMAGES

Images taken of an EO are supported by the NRM TESP-IS application, but the DX system can only be used to bring in some ancillary information about an image, not the images themselves. Users who want to store actual images will need to do so through the NRM TESP-IS application.

NRP_DX_EO_LOCATION

This table stores data and metadata about an EO’s physical location. Because a spatial feature is required for each site, location information is optional in the TESP application.

NRP_DX_PEOPLE

This table is used to add new people to the list of those who do the field work collecting TESP data. Individuals in this list can then be linked to an EO record.

Data Validation after Loading

The DX includes a series of Oracle views to display the data loaded through the DX procedure. The views have the same design as the original Data Exchange tables used to load the data. These views will display all the data in the database: data just loaded through the Data Exchange application and all other data already in the database. Table 3

USDA – Forest Service - NRM 7 provides a list of Oracle view names and descriptions to be used for post-loading validation.

Table 3 – Oracle Output View Names and Descriptions

VIEW NAME VIEW DESCRIPTION

NRP_DXO_EO_DATA_VM View to display basic element occurrence data for TES plants.

NRP_DXO_EO_EXAMINERS_VM View displays the list of examiners by site as individual rows (normalized), who inventoried the Element Occurrence site.

Examiners are also listed in the NRP_DXO_EO_DATA_VM view as columns in the view (de-normalized), if there are 3 or less examiners.

NRP_DXO_EO_COLLECTORS_VM View displays the list of people who collected plants for each Element Occurrence site as individual rows (normalized) of data.

Collectors are also listed in the NRP_DXO_EO_DATA_VM view as columns in the view (de-normalized), if there are 3 or less examiners.

NRP_DXO_EO_SITE_HAB_VM Information related to either the site or the habitat occupied by the Element Occurrence.

NRP_DXO_EO_ASSC_SPEC_VM Information related to the associated plant species occupying the Element Occurrence site.

NRP_DXO_EO_IMAGES_VM View to store data related to images associated with features.

NRP_DXO_EO_LOCATION_VM View to display location information related to features on the ground.

NRP_DXO_EO_PEOPLE_VM View to store data related to images associated with features.

There are two potential ways to join views to display interrelated data.

Figure 2 - TES Relationships Diagram using TESP Primary Foreign Key Relationships

USDA – Forest Service - NRM 8

Figure 2 shows a relationships diagram based on the primary/foreign keys as established in the NRM TESP-IS application. This method joins single fields together, so performance using queries joined with this method will produce faster results.

Figure 3 - TES Relationships Diagram using TESP Logical Foreign Key Relationships

Figure 3 shows a relationships diagram that is closer to the way the original table relationships work in the Data Exchange tables for TESP-EO. Although joins set up between views as shown in Figure 3 should produce the same results as with the first method, the results may not be returned to the user as quickly.

TESP/Invasive Plant Survey

Data Exchange User Guide United States Department of Agriculture Forest Service Natural Resource Manager February 2008 Updated March 2012

TES/Invasives Plant Survey Data Exchange

USDA – Forest Service - NRM iii

Table of Contents

Introduction

Purpose of the Guide

Skills Required

General Format of Data Exchange Tables

Steps in the Process

Setting the Data Category and Populating Data Exchange Tables

Spatial Data

USDA – Forest Service - NRM 1

Introduction

The NRM Data Exchange is a tool developed to assist users in moving data from a variety of electronic data sources into NRM databases at the Forest Service’s Data Center in Kansas

City. The Data Exchange supports a variety of Data Categories, including TES/Invasive

Plant Surveys. The tool provides a set of interim database tables, called Data Exchange tables, used as staging areas for conversion and cleaning of legacy data, before loading them into NRM. This processing insures that the data are compatible with mandated protocols and database requirements of the NRM applications.

Purpose of the Guide

This guide’s purpose is to provide users with what they need to know for most effectively using the NRM Data Exchange to load the data collected using the TES/Invasive Plant

Survey Protocol. It includes process steps to populating interim staging tables specific to

TES/Invasive Plant Surveys, documentation on the design of the various database tables, and design details of the Oracle output views used for post-loading validation. This guide does not provide step-by-step directions on how to use the NRM Data Exchange and its Cleaning Tools. For that, users will want to download and refer to the NRM Data

Exchange User Guide.

Skills Required

Users working with the NRM Data Exchange should have a thorough understanding of the TES/Invasive Plant Survey Protocol and a working knowledge of the NRM Data

Exchange Cleaning Tools. In addition, it will be beneficial for users to have a basic understanding of database design principles, and a working knowledge of Microsoft

Access©. Although not required, additional skills in Oracle SQLPlus can increase the effectiveness of using the tool.

General Format of Data Exchange Tables

Data Exchange tables are similar to spreadsheets in that they do not have constraints on what is entered and lack physical relationships among the tables. This allows the greatest flexibility for users moving data from existing electronic systems into the Data Exchange tables. Users can access and update information within these tables directly through the

Oracle views, or the views can be accessed via the NRM Data Cleaning Tools through the Open MS Access Database for this Dataset button on the main menu of the Data

Cleaning Tool.

http://sforge.fs.fed.us/sf/docman/do/downloadDocument/projects.nris_dex/docman.root.user_guides/doc6437 http://sforge.fs.fed.us/sf/docman/do/downloadDocument/projects.nris_dex/docman.root.user_guides/doc6437

USDA – Forest Service - NRM 2

Steps in the Process

Setting the Data Category and Populating Data Exchange Tables

Before any data can be put into the Data Exchange tables, the user must select a Data

Category. On the main screen of the Data Cleaning Tools set the Data Category to TES-

INVASIVES SURVEY, click the Create New Dataset button, then populate the information in the dialog box. This will create a new blank dataset in the Data Exchange.

The TES-INVASIVES SURVEY Data Exchange includes seven interim staging tables, described in Table 1, below.

Table 1 - Descriptions of Plant Survey Data Exchange Tables

TABLE

ORDER

VIEW NAME TABLE DESCRIPTION UNIQUE KEY

1 NRP_DX_SURVEY Core data related to a survey for various plant species.

SURVEY_ID

2 NRP_DX_SURVEYORS_DATE List of surveyors and dates surveyed for each survey area.

SURVEY_ID

VISIT_DATE

*LAST_NAME

*FIRST_NAME

*MIDDLE_NAME

3 NRP_DX_SURVEY_TARGET_SP List of target plant species for each survey area.

SURVEY_ID

*TESP_PLANT_CODE

*INPA_PLANT_CODE

*HABITAT_FOUND

4 NRP_DX_SURVEY_AREA_SP List of all plant species by survey id found during the survey.

SURVEY_ID

PLANT_CODE

*LIFE_FORM

*HABITAT

5 NRP_DX_SURVEY_RD_TRAILS List of roads and trails surveyed for various plants.

SURVEY_ID

ROAD_TRAIL_FLAG

*ROAD_TRAIL_NUM

*ROAD_TRAIL_NAME

6 NRP_DX_SURVEY_LOCATION Location information related to features on the ground.

SURVEY_ID

7 NRP_DX_PEOPLE Table that stores names of people associated with various activities.

LAST_NAME

FIRST_NAME

*MIDDLE_NAME

* Optional fields.

The TABLE ORDER column indicates the order in which users should enter data into the Data Exchange tables. The NRP_DX_SURVEY table contains the fundamental information about the Survey. This table must be populated in order for any of the other

USDA – Forest Service - NRM 3

Survey data to load into NRM through the Data Exchange. Table 1 also contains a column labeled UNIQUE KEY, identifying columns in the table that must be unique for each record in the table. For example, in the NRP_DX_SURVEY table, the column

SURVEY_ID is the unique key, so every record must have a unique ID value (see examples below).

Example 1: Valid data for unique key Example 2: *Invalid data for unique key

SURVEY_ID SURVEY_ID

06010400001 *06010400001

06010400002 *06010400001

The UNIQUE KEY will also indicate how tables within a Data Category are related.

Figure 1 shows a relationships diagram of the various tables in the TES/Invasives Plant

Survey Data Category:

Figure 1- TES/Invasives Plant Survey Table Relationships Diagram

These relationships indicate that the NRP_DX_SURVEY table must contain records for all surveys. Plant surveys may be conducted over days, weeks and even months. The protocol allows the user to record each day of field survey. So, if a particular survey area

USDA – Forest Service - NRM 4 has a series of differing survey dates, there will be a set of corresponding records in the

NRP_DX_SURVEYORS_DATE table. As indicated in Table 1, the unique key for

NRP_DX_SURVEYORS_DATE is a combination of the SURVEY_ID, SURVEY_DATE, LAST_NAME and FIRST_NAME columns. This means there may be multiple records in this table for the same survey so long as each has a different date and, if needed, different surveyors. Example 3 (below) shows data that could be found in the

NRP_DX_SURVEYORS_DATE table.

Example 3: Valid data for NRP_DX_SURVEYORS_DATE unique key

SURVEY_ID SURVEY_DATE LAST_NAME FIRST_NAME MIDDLE_NAME

06010400001 6/1/2005 Buck John

06010400001 6/1/2005 Doe Jayne M

06010400001 6/2/2005 Hill Rocky

06010400002 6/15/2006

The example shows data for two different surveys (06010400001, 06010400001). The first survey was done over two days (6/1/2005, 6/2/2005), the first day with two surveyors (Buck, Doe), the second day with only one surveyor. The second survey

(06010400001) was done on 6/15/2006 with no surveyors listed (note: if

SURVEY_STATUS = Complete for Survey 06010400002, the lack of a surveyor record will trigger an error in the Data Cleaning Tool).

Spatial Data

For every survey, a spatial feature is required in NRM. The GIS data to be used with the

Data Exchange are in a standard ESRI Shapefile format. Only polygon features are supported; they may be single polygons or multi-part polygons. Shapefiles users create for this particular business area must have one standard column with the following definitions:

Name Alias Type Length Precision Scale

SURVEY_ID SURVEY_ID Text 30 0 0

The relationship between the shapefile and data in the Data Exchange staging tables is through the NRP_DX_SURVEY table. For every record in the table there must be a corresponding record in the shapefile with the same information in the columns as listed in the table below. For example, if there is a survey with an ID of 06010400001, then that information must reside as a row in the NRP_DX_SURVEY and in the corresponding column in the shapefile.

Column match between the Data Exchange and the Shapefile

DATA EXCHANGE COLUMN (NRP_DX_SURVEY) SHAPEFILE COLUMN

SURVEY_ID SURVEY_ID