GISNOTE
GISNOTE
FORS7690

Query local FIA Database (MS Access EVALIDator Report tool)

by |Published -Updated

(Updated October 10, 2021 – I was unable to import state tables into the FIADB using the most recent version of MS Access (v 2109 Build 16.0.14430…) on my computer at home.  It might be a version issue since the download site reverences MS Access 2007.  I am checking into it. )

(Updated October 11, 2021 – I was able to import state tables into the FIADB using my office computer.  The October 10, 2021 issues are most likely due to a MS Access settings issue on my home computer.  I have high hopes for the GIS lab machines…)

Below, I will walk you through a typical FIADB query.  The three main sections are 1) create your workspace folder structure,2) update the MS Access FIADB, and 3) execute your query.  I show you how to submit a query for growth of live trees by Georgia Congressional districts and and major specie groups.  I then request you resubmit a query on your own for removals of live trees by Georgia Congressional districts and major specie groups.  The final step is to display the query results in ArcMap…

Create your workspace folder structure:

Create a working directory, call it FIADB, and subfolders to store data…

  • FIADB
  • FIADB/reference
  • FIADB/georgia
  • FIADB/georgia/ga<<download date>> where <<download date>> is the date you download this file
  • FIADB/georgia/output (this is not shown in the video; this is where you will save your EVALIDATOR queries)
  • FIADB/gis (this is not shown in the video; this is where you will extract your GIS data)

Download MS Access DB: https://apps.fs.usda.gov/fia/datamart/datamart_access.html

  • Download the Georgia MS Access file (click on the state)
  • Extract this to your FIADB/georgia directory (right-click > 7Zip > Extract Here)

Download updated data files: https://apps.fs.usda.gov/fia/datamart/CSV/datamart_csv.html

  • Download the reference files – the ‘All of the reference files zipped together’ link
    • Extract to your FIADB/reference directory (right-click>7Zip>Extract Here)
  • Download the file linked under GEORGIA GA.zip (GA.zip)
    • Extract the contents to your FIADB/georgia/ga directory

Done with downloading and setting up your workspace.

IF DOWNLOADS ARE TAKING TOO LONG, DOWNLOAD MY LOCAL REPOSITORY HERE.

  • If you use this download, make sure you go through each directory and unzip the files (right-click>7Zip>Extract Here).

Update your Access DB…

  1. Open your GA.accdb
  2. Add exception to your MS Access trusted locations
    • File > Options > Trust Center > Trust Center Settings… >Trusted Locations tab on the left
    • Hit the ‘Add new location…’ button, browse to and add your FIADB folder as a trusted location
      • If “Sub Folders:” is Disallowed, Click on the Modify… button and tick the box next to “Subfolders of this location are also trusted”
    • OK all the way back to the Access interface
  3. Select ‘Forms’ in the dropdown on the left pane
  4. Run (double-click) ‘Delete Data in Tables’ query
    • “Delete All Data in Reference Tables”
    • “Delete All Data in State Tables”
    • Run the Compact and Repair Tool (if it wants to save out as a new DB, then let it)
    • Close and then re-open your database and proceed
  5. Run ‘Load a State’s FIADB tables’ (this might take two or three minutes to execute)
    • I received an run-time error 3049 when I tried to load the state tables on my computer at home.  I am not sure what is causing this error.  However, the desktop EVALIDator queries I tested worked, so we will proceed with caution.  I’ve submitted a help request and will update this document when I hear from the FIA folks.
    • This process did, however, work on my computer here at school.
    • If you give up, then download my updated MS Access FIADB HERE.
  6. Run ‘Load FIADB reference tables’ and follow the prompt
  7. File Save

Done with updating your DB…

EVALIDATOR queries on the desktop…

Users can submit queries to their local database snapshot using an interface similar to the web based EVALIDATOR application.

GLOSSARY of FIA Terms

RUN THE MS ACCESS EVALIDator QUERY TIIL

  • Run the ‘EVALIDatorReports’ tool (find it in the Forms pane on the left).

SELECT ESTIMATE:

Select the Numerator and a Denominator if you desire a ratio estimate. Common options are:

  • Area of forestland or timberland
  • Number of live trees, growing-stock trees, standing-dead trees, live trees (on forestland or timberland)
  • Dry or green weight of… (on forestland or timberland)
  • Net or gross volume of live trees, growing-stock trees, saw-log portion of trees (on forestland or timberland)
  • Average annual net growth of … (on forestland or timberland)
  • Average annual mortality of … (on forestland or timberland)
  • Average annual removals of … (on forestland or timberland)
  • Many more queries on number of live seedlings, above and belowground carbon, harvest removals, etc…
  • Select “025 Average annual net growth of live trees (at least 5 inches…) …”.
  • Select “000 Population estimate only” for the denominator.
  • Hit the Step1 button

SELECT THE PANEL

Unless you have a specific need, scroll down and select the latest panel. Recall that approximately 20% of a State’s FIA plots are measured each year. A panel is the most recent five-year span with all of the plots measured. The latest panel of data for Georgia (represented in our database) is 2015 – 2019.

  • Select “33 131703 Georgia 2015.2016.2017.2018.2019”

SELECT YOUR PAGE, ROWS, & COLUMNS

Rows and Columns are the variables that make up the, ahem, rows and columns of your output table. If you select a Pages variable, then an output table will be generated for each unique entry in that variable. For example, there are 14 Congressional districts in Georgia, so if you select “Congressional district” as your Pages variable, then you will be presented with individual tables for districts 1 – 14.

  • Pages: None
  • Rows: Congressional district
  • Columns: Species group major
  • Hit Step 2 – Run
  • Hit YES if a pop-up appears…
  • Save the output in your FIADB/georgia/output directory; call it “AverageAnnNetGrowth_LiveTrees”
  • Hit Reset to run your next query

Done with your first query against your local FIA database…

Let’s do a quick growth/removal comparison and display the results in ArcMap.

  • Submit a second query for “039 Average annual removals of live trees (at least 5 inches…)…”. Call the output “AverageAnnRemovals_LiveTrees”
  • Composite these in Excel
    • Open the HTML outputs, copy the Estimate tables and paste them in Excel
    • Remove the commas in the number fields
  • In Excel
    • First row should be the properly formatted field names
      • CongDistrict, AvAnnGrow, AvAnnSGrow, AvAnnHGrow, AvAnnRemov, AvAnnSRemov, AvAnnHRemov
    • Remaining rows should be data
    • Create new fields “GRatTotal”, “GRatSoft”, “GRatHwd”
      • GRatTotal Total Growth / Total Removals
      • GRatSoft Softwood Growth / Softwood Removals
      • GRatHwd Hardwood Growth / Hardwood Removals
    • Create a field for your unique identifier “CDUID”
      • Georgia’s FIPS is 13
      • Congressional districts are two-characters (01, 02, …, 13, 14)
      • Your CDUID values should be a concatenation of the two (1301, 1302, …, 1313, 1314)
    • Save your Excel document in your workspace
    • Save your Excel worksheet out as a CSV or TXT
  • Download the 116th Congressional district shapefiles from the Census website
  • In ArcMap
    • Load your 116th Congressional district shapefile
    • Load the CSV or TXT containing your EVALIDATOR report
    • Join the external EVALIDATOR results to the Congressional district shapefile

Which Congressional districts

  • have the “We’re growing more trees than we’re cutting…” bragging rights?
  • have reason for concern because the growth/removal ratio is lower than their neighbors?

It would be cool if you could create a mapbook showing Georgia Growth/Removal rates. Here is a link to the 116th Congress men and women ( http://clerk.house.gov/member_info/excel-labels-116.xlsx ).

incorporate this information, too.