FIREMON Database and Analysis Software

Document Sample
FIREMON Database and Analysis Software Powered By Docstoc
					        Query Builder and External Data Analysis Exercises

These exercises are designed to make you familiar with the different parts of
Query Builder tool. They are not meant to describe all the functionality of the
tool.

In these exercises you will:

Part 1 – Query Builder: Filtering data and adding fields
 1) Use Query Builder to view data for the Surface Fuels – Fine method.
 2) Use Query Builder to view data for the Trees - Individuals method.
 3) Use Query Builder to view data for the Cover – Species Composition method.
   Add fields from the macroplot table and master species list to the query.

Part 2 – Query Builder: Calculations
 4) Use Query Builder to calculate seedling density by status (live / dead) using the
   Trees – Seedlings (Height Class) method. Export the query results as a CSV file.
 5) Use Query Builder to calculate basal area by dbh classes using the Trees –
   Individuals method.
 6) Use Query Builder to calculate cover by life form using the Cover – Line
   Intercept protocol.
 7) Use Query Builder to calculate cover by life form for the Cover – Points protocol.
 8) Use Query Builder to calculate frequency by species using the Cover/Frequency
   protocol.

Part 3 – External Data Analysis
 9) Import the Query Builder results for seedling density by status (live / dead) and
   view an analysis report and graph.




                                                                                  12/20/10
                       Query Builder and External Data Analysis Exercises

Initial Query Builder Steps

To begin each query determine if you want to query all the data in the administration
unit or only data for a specific project unit, then select the appropriate radio button in the
left pane. For these exercises you will query only the FOREST project so click the
Selected Project Units radio button and then highlight the FOREST project by clicking
on it once. On the Filter tab select a protocol from the Protocol dropdown list. In this
example the Surface Fuels protocol is selected.




Next, highlight the method you want to query in the box beneath the Protocol field. In
the image below the Surface Fuels protocol and Surface Fuels – Fine method have
been selected. After you’ve selected a protocol and highlighted the desired method, four
tabs will be displayed: 1) Filter, 2) Additional/ Calculated Fields, 3) Classify and 4)
Grouping and Summary Calculations. Not all tabs will be used in every query.




The Filter and Additional/Calculated Fields tabs allow users to filter data for the
selected sampling method and to add fields from other FFI data tables.

Data can be filtered by Macroplot, Monitoring Status, Sample Event, and Species
attributes. Fields may be added to the query from the Macroplot, Sample Event,
Monitoring Status, and Master Species List tables.

Data for the selected method are displayed in the Query Builder data grid in the bottom
half of the Query Builder screen.


                                          2 of 32
                             Query Builder and External Data Analysis Exercises



  Part 1 - Query Builder: Filtering Data and adding fields

  Exercise 1: Use Query Builder to view data for the Surface Fuels – Fine method.


  1.1       On the Filter tab:
            1) Under Method, select the Surface Fuels protocol from the dropdown list and
                   then the Surface Fuels - Fine method by clicking on the name once.
            2) Under Macroplot, select plots TestForest1 and TestForest3 by clicking each
                   macroplot name.
            3) Under Sample Event, select monitoring statuses PreTreatment and
                   ReMeasurementYear1 by clicking each.
            4) Click Apply Selections
            5) The raw Surface Fuels – Fine method data is displayed in the data grid. If
                   desired, the information can be saved in comma delimited format (.csv)
                   using the Export command (right above the filter tab).


                              
                                                         




                             
Sample Attribute data
Method Attribute data




  For each Macroplot and Sample Event, the sample attribute data are displayed in the
  first row followed by one or more rows of method attribute data. The “sample attributes”
  contain information related to how the data were sampled, such as visited, the number
  of transects and transect lengths. The “method attributes” are data specific to the
  method, such as the transect number and piece counts.




                                                3 of 32
                     Query Builder and External Data Analysis Exercises

Exercise 2: Use Query Builder to view data for the Trees - Individuals method.

2.1   On the Filter tab:
      1) Under Protocol, select the Trees protocol and the Trees - Individuals
             method.
      2) Under Macroplot, select plot TestForest1
      3) Under Sample Event, select monitoring status ReMeasurementYear1.
      4) Click Apply Selections



                      
                                        

                                                   




2.2   If you want to see only live trees; under Species, check the Live perennials
      and all annuals checkbox and click Apply Selections. Scroll to the right in the
      data grid and you will see only trees where Status = L




                                        4 of 32
                     Query Builder and External Data Analysis Exercises



2.3   If you want to see trees of a certain species select the desired species code
      under Picklist and click Apply Selections. For example, select PICO
      (lodgepole pine) and click Apply Selections. Data for live lodgepole pine will be
      displayed in the data grid. Note that if you uncheck Live perennials and all
      annuals the data grid reverts to show all statuses and species. You must select
      a species code again to re-filter the data grid view.




                                        5 of 32
                     Query Builder and External Data Analysis Exercises

Exercise 3: Use Query Builder to view data for the Cover – Species Composition
method. Add fields from the macroplot table and master species list to the query.


3.1   On the Filter tab:
      1) Select the Cover – Species Composition protocol and method.
      2) Select plots TestForest1 and TestForest3
      3) Select monitoring statuses PreTreatment and ReMeasurementYear1.
      4) Click Apply Selections to see all the species on the selected plots and
      sample events.




                                        6 of 32
                       Query Builder and External Data Analysis Exercises

3.2      Select Graminoid in the Lifeform list box and click Apply Selections. This will
filter the data to display only graminoids.




3.3    Select Festuca in the Genus list box and click Apply Selections This will filter
the data to display only grasses in the genus Festuca; in this case only Festuca
idahoensis (Idaho fescue).




                                          7 of 32
                     Query Builder and External Data Analysis Exercises

3.4 Once you have filtered your data you can add other data columns to the data grid
using options on the Additional/Calculated Fields tab.

      1) On the Filter tab unselect Festuca and Graminoid by clicking once on each.
      2) Click Apply Selections
      3) Click on the Additional/Calculated Fields tab
      4) Under Macroplot Fields check Elevation and Aspect to add those fields to the
      query.
      5) Add Scientific Name and Common Name under Species Fields.
      6) Click Apply Selections
      7) Scroll to the right to see the four new fields.



                       
                                                 




                                                                          




                                        8 of 32
                      Query Builder and External Data Analysis Exercises



Part 2: Query Builder: Calculations

Query Builder provides calculations for density, cover, frequency, and basal area.
These calculations work for all the sampling methods provided with the FFI software.
They will also work with any new sampling methods created by FFI users provided the
new methods contain the appropriate sample and method attributes. Because they offer
the flexibility to work with existing and new sampling methods, the query builder
calculations require users to input the appropriate fields for each calculation. Users
must have some familiarity with how the appropriate sample attributes and method
attributes are used to summarize the data.

The following exercises provide examples of how to use the calculations for cover,
density, frequency, and basal area with various FFI sampling methods. They also
emphasize the classification and grouping options in query builder, which offer
additional data summary capabilities than are available using the standard data
summary reports. Finally, these exercises demonstrate how to export Query Builder
data for input into the FFI External Data Analysis Tool. Using Query Builder results with
the External Data Analysis Tool allows users to analyze data summarized differently
than in the standard data summary reports.




Exercise 4: Use Query Builder to calculate seedling density by status (live / dead)
using the Trees – Seedlings (Height Class) method. Export the query results as a
CSV file.

In order to calculate density by status, we first need to group the seedlings by status
class (live or dead). Next, we must sum the number of seedlings in each status class
and then we can calculate density by dividing the number of seedlings in each status
class by the area sampled. The area sampled is the macroplot area if grouping data by
macroplot and sample event, or it is the sum of macroplot areas if grouping data for
multiple macroplots and/or sample events. Finally, we need to provide a conversion
factor to display the data in the desired units (per acre or per hectare).




                                         9 of 32
                     Query Builder and External Data Analysis Exercises

4.1   Click the Filter tab and select the Trees protocol and the Trees - Seedlings
      (Height Class) method, and click Apply Selections.




4.2   On the Additional/Calculated Fields tab check monitoring status Order under
      Monitoring Status Fields and click Apply Selections. Monitoring status is
      necessary when exporting data for input into the FFI External Data Analysis Tool.




                                        10 of 32
                      Query Builder and External Data Analysis Exercises

4.3   On the Grouping and Summary Calculations tab you will need to calculate
      density by tree status.

      1) Under Macroplot Group By select Macro Plot and Monitoring Status.

      2) Under Method Fields to Group By - Method Attributes group the data by
      Status (this is the tree status class – live or dead).

      3) Under Method Fields to Sum select the Sample Attribute Plot Size and the
      Method Attribute Count. These fields are required to perform the density
      calculation.

      4) The grouping and calculations selected in the first three steps must be applied
      before the density calculations can be made. Click Apply Selections to create
      the requested groups and summaries, and refresh the data grid.


                              
                                                     
                              




                                         11 of 32
                  Query Builder and External Data Analysis Exercises

5) Under Calculations - Density fill in the appropriate fields for the Density
calculation text boxes.

         a) Select the field sum_MicroPlotSize in the Area textbox. MicroPlotSize is
         the sampled area for seedlings for each macroplot and sample event.

         b) The Method Attribute (Subplot Fraction) is optional here. It is only
         required when some seedlings are sampled within a fraction of the
         sampling area. Leave blank for this exercise.

         c) Select sum_Count in the Count Field text box. This field sums the
         number of seedlings in each status (live or dead).

         d) Select Acres – Area (acres) in the Unit Conversions text box. This
         conversion factor will calculate density as the number of seedlings per
         acre given that the sampling area was recorded in acres.

         e) Check the Calculate Density checkbox.

6) Click Apply Selections to make the final density calculation and add it to the
query data grid.

7) Export the Query Builder results as a CSV file and save on your desktop.



           
                                                                        




                                     12 of 32
                      Query Builder and External Data Analysis Exercises

Exercise 5: Use Query Builder to calculate basal area by dbh classes using the
Trees – Individuals method.

In order to calculate basal area by dbh classes we need to know the DBH2 value of
each tree so we can calculate the basal area of each tree as a constant * DBH2. The
constant is derived from the equation area = (DBH/2)2. The constant differs depending
on basal area units (ft2/acre or m2/ha) and whether you recorded DBH in inches or cm.

Next we must classify the actual DBH values into our desired classes. Then we will sum
the basal area by DBH classes. We will calculate basal area by our DBH classes by
dividing the sum of basal area for each DBH class by the area sampled. The area
sampled is the macroplot area if grouping the data by macroplot and sample event, or it
is the sum of macroplot areas if grouping data for multiple macroplots and/or sample
events. Finally, we will provide a conversion factor to display the data in the desired
units (ft2/acre or m2/ha).


5.1    On the Filter tab select the Trees protocol and the Trees-Individuals method, and
click Apply Selections.




                                         13 of 32
                      Query Builder and External Data Analysis Exercises

5.2   On the Additional/Calculated Fields tab check monitoring status Order under
      Monitoring Status Fields. Monitoring Status Order is needed if Query Builder
      data are to be used with the External Data Analysis Tool.

      Under Calculated Fields select DBH in the DBH field and check Calculate
      DBH2. This will calculate DBH2 for calculating basal area. Click Apply
      Selections and scroll right in the data grid to see the DBH^2 field.




5.3   On the Classify tab, make two classes of trees based on DBH: Pole (<=9” DBH)
      and Overstory (>9” DBH).

      1) Select DBH from Field to Classify dropdown list.

      2) On the first line of the classification grid type “9” in the Upper Cutoff field and
      “Pole” in the Class Label field.

      3) On the second line type “Overstory” in the Class Label field.

      4) Click the Apply button under the Field to Classify dropdown.

      5) Click Apply Selections.




                                         14 of 32
                     Query Builder and External Data Analysis Exercises

      Scroll to the right in the data grid and you will see that the numeric DBH values
      have been replaced with their classified value. This classified value will be
      carried over to the Grouping and Summary Calculations tab. (The data are not
      changed in the database when using the Classify function, only the values in the
      query data grid and data exported from query builder are changed.)




5.4   On the Grouping and Summary Calculations tab you will calculate basal area
      by DBH class for each Macroplot and Monitoring Status.

      1) Under Macroplot Group By check Macro Plot and select Monitoring Status

      2) Under Method Fields to Group By – Method Attributes select DBH.

      3) Under Method Fields to Sum – Sample Attributes select Plot Size.

      4) Click Apply Selections to create the requested groups and summaries, and
      refresh the data grid.




                                        15 of 32
                Query Builder and External Data Analysis Exercises



5) Under Calculations scroll down to the Basal Area section and fill in the
appropriate fields.

       a) Select the field sum_MacroplotSize in the Area textbox. This will give
       us the total plot area sampled.

       b) The Method Attribute (Subplot Fraction) is optional here. It is only
       required when some trees are sampled on a fraction of the macroplot
       area. Leave blank for this exercise.

       c) Select sum_DBH^2 in the DBH Squared text box. This field will provide
       the sum of DBH2 for calculating basal area.

       d) Select “Sq. Ft. / Acre – DBH (in), Area (acres)” for the Unit
       Conversion. This will apply the appropriate unit conversion to calculate
       basal area in ft2/acre given that DBH was recorded in inches and
       macroplot area was recorded in acres.

       e) Check the Calculate Basal Area checkbox.

       f) Click Apply Selections basal area is calculated and added to the data
       grid.




6) Export the Query Builder results as a CSV file, if desired.




                                   16 of 32
                       Query Builder and External Data Analysis Exercises



Exercise 6: Use Query Builder to calculate cover by lifeform using the Cover –
Line Intercept protocol.

In order to calculate cover using the Cover – Line Intercept protocol, we must know how
many feet (or meters if using the metric protocol) of transect were sampled for each
macroplot. We will calculate the total length of line sampled per macroplot by multiplying
the number of transects by the length of each transect. Next we will sum the total
intercept for each species on a plot. We will calculate cover by dividing the sum of
intercepts for each species by the total length of transects sampled and multiply by 100.
If we are interested in calculating cover by life form, we can group species into life forms
before we perform the cover calculation.

6.1    On the Filter tab select the Cover – Line Intercept protocol and method, and click
       Apply Selections.




                                          17 of 32
                     Query Builder and External Data Analysis Exercises

6.2   On the Additional/Calculated Fields tab:

      1) Under Monitoring Status Fields check monitoring status Order.

      2) Under Number of Subsamples and Area:

            a) Select Num. Transects in the Num. Transects dropdown

            b) Select Tran. Length in the Num. Quad./Num. Pts./Tran. Len.
            dropdown.

            c) Check Calculate Number of Subsamples. In this example the number
            of subsamples is the total number of feet of tape sampled for each
            macroplot and sample event. When you calculate the number of
            subsamples the total number of feet sampled is calculated by multiplying
            the number of transects by the transect length.

            d) Click Apply Selections. Scroll to the right in the data grid to see the
            NumSubsamples field. In this example the NumSubsamples field is the
            total amount of transect on the macro plot (num. transect x tran. len.).




                                        18 of 32
                      Query Builder and External Data Analysis Exercises

6.3   On the Grouping and Summary Calculations tab gather the information
      required to perform the cover calculation:

      1) Under Macroplot Group By check Macro Plot and select Monitoring Status,

      2) Under Method Fields to Group By – Method Attributes select Subplot
      Fraction. (Subplot fraction is optional in this example because it is always equal
      to 1, meaning that all species on a macroplot were sampled along the same
      length of transect).

      3) Under Species Attributes select Lifeform.

      4) Under Method Fields to Sum – Method Attributes select Intercept.

      5) Click Apply Selections to create the requested groups and summaries, and
      refresh the data grid.




                                         19 of 32
                Query Builder and External Data Analysis Exercises

6) Under Calculations scroll to the Cover section and fill in the appropriate
fields.

       a) Select the field “sum_NumSubsamples” in the Number of Subsamples
       textbox. This will give us the total length of line transects sampled.

       b) The Method Attribute (Subplot Fraction) is optional here, but we will
       use it just as an example. Select the field “SubFrac” for this textbox.

       c) Select “sum_Int” in the Cover Field textbox. This field will provide the
       sum of intercept sampled by life form.

       d) Check the Calculate Cover checkbox.

       e) Click Apply Selections to calculate cover by lifeform for each macro
       plot and monitoring status.




6) Export the Query Builder results as a CSV file, if desired.




                                   20 of 32
                       Query Builder and External Data Analysis Exercises

Exercise 7: Use Query Builder to calculate cover by lifeform for the Cover –
Points protocol.

In order to calculate cover using the Cover – Points protocol, we must know how many
points were sampled for each macroplot. We will calculate the total number of points
sampled per macroplot by multiplying the number of transects by the number of points
sampled along each transect. Next we will sum the total number of unique “hits” at each
point for each species. Unique hits are recorded once per species at a point, thus
multiple hits at a point for a species are only counted as one. We will calculate cover by
dividing the number of unique hits for each species by the total number of points
sampled and multiply by 100. If we are interested in calculating cover by life from, we
can group species into life forms before we perform the cover calculation.


7.1    On the Filter tab select the Cover – Points (metric) protocol and method, and
click Apply Selections.




                                          21 of 32
                    Query Builder and External Data Analysis Exercises

7.2   On the Additional/Calculated Fields tab

      1) Select Order under Monitoring Status Fields

      2) Under Number of Subsamples and Area:

            a) Select Num. Transects in the Num. Transects dropdown

            b) Select Num. Pts./Tran. in the Num. Quad./Num. Pts./Tran. Len.
            dropdown

            c) Check Calculate Number of Subsamples. The number of subsamples
            is the total number of points sampled for each macroplot and sample
            event. When you calculate the number of subsamples the total number of
            points sampled is calculated by multiplying the number of transects by the
            number of points per transect.

            d) Click Apply Selections.




                                       22 of 32
                     Query Builder and External Data Analysis Exercises

7.3   On the Grouping and Summary Calculations tab calculate cover by lifeform:

      1) Under Macroplot Group By group check Macro Plot and select Monitoring
      Status.

      2) Under Method Fields to Group By – Method Attributes select Transect and
      Point. Grouping by Transect and Point is necessary for this method in order to
      determine which points have multiple hits per species. Cover is calculated using
      only unique hits at each point. These fields are required to perform the cover
      calculation.

      3) Under Species Attributes select Lifeform.

      4) Click Apply Selections to create the requested groups and summaries, and
      refresh the data grid.




                                        23 of 32
                Query Builder and External Data Analysis Exercises

5) Under Calculations scroll to the Cover section and fill in the appropriate
fields:

       a) Select the sum_NumSubsamples in the Number of Subsamples
       dropdown. This will give us the total number of points sampled.

       b) The Method Attribute (Subplot Fraction) is optional here. It is only
       required if some species on a macroplot were sampled with fewer points.
       Leave blank for this exercise.

       c) Select RowCount in the Cover Field dropdown. This field will provide
       the number of unique hits for a species along each transect.

       d) Check the Calculate Cover checkbox.

       e) Click Apply Selections and the total hits, unique hits, and cover values
       by lifeform, macro plot and monitoring status are added in the data grid.




6) Export the Query Builder results as a CSV file, if desired.




                                   24 of 32
                      Query Builder and External Data Analysis Exercises




Exercise 8: Use Query Builder to calculate frequency by species using the
Cover/Frequency protocol.

In order to calculate frequency using the Cover – Frequency protocol, we must know
how many quadrats were sampled for each macroplot. We will calculate the total
number of quadrats sampled per macroplot by multiplying the number of transects by
the number of quadrats sampled along each transect. Next, we will sum the total
number of quadrats in which a species occurs. We will calculate frequency by dividing
the number of quadrats in which a species occurs by the total number of quadrats
sampled and multiply by 100.

8.1   On the Filter tab select the Cover/Frequency protocol and method, and click
Apply Selections.




                                         25 of 32
                    Query Builder and External Data Analysis Exercises

8.2   On the Additional/Calculated Fields tab:

      1) Under Monitoring Status Fields check Order.

      2) Under Number of Subsamples and Area select Num. Transects in the Num.
      Transects dropdown, Num. Quad./Tran. in the Num. Quad./Num. Pts./Tran.
      Len. Dropdown, check Calculate Number of Subsamples and click Apply
      Selections.

      The number of subsamples is the total number of quadrats sampled for each
      macroplot and sample event. When you calculate the number of subsamples, the
      total number of quadrats sampled is calculated by multiplying the number of
      transects by the number of quadrats per transect.




                                       26 of 32
                    Query Builder and External Data Analysis Exercises

8.3   Calculate frequency by species on the Grouping and Summary Calculations
      tab.

      1) Under Macroplots Group By check Macro Plot and select Monitoring Status.

      2) Under Species Attributes select Species.

      3) Click Apply Selections to create the requested groups and summaries, and
      refresh the data grid.




                                       27 of 32
                Query Builder and External Data Analysis Exercises

4) Fill in the appropriate fields in the Frequency section under Calculations:

       a) Select the field sum_NumSubsamples in the Number of Subsamples
       dropdown. This will give us the total number of quadrats sampled.

       b) Select RowCount in the Frequency Field dropdown. This field will
       provide the number of quadrats in which a species occurs.

       c) Check the Calculate Frequency checkbox, click Apply Selections and
       the frequency values are added to the data grid.




5) Export the Query Builder results as a CSV file, if desired.




                                   28 of 32
                      Query Builder and External Data Analysis Exercises

Part 3: External Data Analysis

Exercise 9: Import the Query Builder results for seedling density by status (live /
dead) and view an analysis report and graph.

9.1   In Exercise 4 you created a CSV file and saved it to your desktop. Open that file
      in Excel and save it on the desktop in Excel spreadsheet format (.xls or xlsx).

9.2   In FFI, click on the Toolbox function at the lower left of the screen. Click File,
      Open.




9.3   Select the spreadsheet you want to use and click Open.




                                         29 of 32
                         Query Builder and External Data Analysis Exercises



9.4       Select the worksheet from the Excel Worksheet dropdown and the data grid will
          be populated with the query data.




9.3       Select the Analysis Variables.
         Strata = Status
         Monitoring Status Order = Status Order
         Monitoring Status = Monitoring Status
         Macroplot = MacroPlot Name
         Data Attribute = Density_acres


9.4       Type in the Report Headers.
         Project Unit = Test
         Summary Report = Trees - Seedlings
         Report Attribute = Density
         Units of Measure = Seedlings per acre




                                            30 of 32
                          Query Builder and External Data Analysis Exercises




9.5       Select the Analysis Settings.
         Statistical Analysis
         Parametric
         Alpha Value = 0.05
         Precision = 1.0




9.6       To view a data analysis report click
          Analysis > View Report.




Dead seedlings are reported under Strata: D and live seedlings under Strata: L.




                                             31 of 32
                      Query Builder and External Data Analysis Exercises



9.7   To view a data analysis graph click Analysis > View Graph.




For more information about interpreting FFI analysis reports and graphs see the
Reports and Analysis exercises.

If you Exported the query results for exercises 5 through 8 practice importing the data
and running more reports, if desired. Note that most attributes do not have sufficient
data for statistical comparison.

Ex. 5 Strata = DBH
      Attribute = Basal area

Ex. 6 Strata = Preferred Lifeform
      Attribute =Cover

Ex. 7 Strata = Preferred Lifeform
      Attribute = Cover

Ex. 8 Strata = ?
      Attribute = ?


                                         32 of 32

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:6
posted:11/7/2012
language:English
pages:32