Query local FIA Database (MS Access EVALIDator Report tool)


Print Friendly, PDF & Email

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

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
  • 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, TRY DOWNLOADING MY LOCAL REPOSITORY 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… >Trused Locations tab on the left
    • Hit the ‘Add new location…’ button and add the E:\ drive as a trusted location
    • OK all the way back to the Access interface
  3. Select ‘Forms’ in the dropdown on the left pane
  4. Run ‘Delete Data in Tables
    • “Delete All Data in Reference Tables”
    • “Delete All Data in State Tables”
  5. Run ‘Load FIADB reference tables’ and follow the prompt
  6. Run ‘Load a State’s FIADB tables’ (this might take two or three minutes to execute)
  7. File Save

Done with updating your DB…



EVALIDATOR querys on the desktop…

Users can submit queries to their local database snapshot using an interface similar to the web based EVALIDATOR application. Run the ‘EVALIDatorReports’ tool (find it in the Forms pane on the left).

GLOSSARY of FIA Terms

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 2013 – 2017.

  • Select “33 131703 Georgia 2013.2014.2015.2016.2017”

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 identifyer “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
  • SaDownload 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.

Upload your well-formatted map showing the Georgia Congressional districts colorized by their Growth/Removal Ratio (no need for a mapbook today).  Don’t forget all of the needed map elements.  Also, indicate on the map which ONE district has the “We are growing more than we are removing…” bragging rights and Why.  Insert this information into a text box on your map.  ELC Assignment Dropbox (Congressional District Bragging Rights)  Hint: consider the number of plots used for each estimate…