SDJR – Spatial analysis

1. SDJR – Spatial analysis Whityn Owen GIS Specialist – MO1 whityn.owen@or.usda.gov 503-414-3024 MO1 Guide

2. SDJR Data Package This presentation describes the data package delivered to Soil Survey Offices in MO1 Topics: • Raster data dictionary (model outputs) Slide 7 • Raster Classification Schema (cell values) Slide 16 • Tabulate Area Excel Macro Results Slide 18 • Working with Pivot Tables Slide 32

3. Vector Data • File Geodatabase Feature Dataset utm10n83 _a Seamless Soils _a_dis Seamless Soils Dissolved _aoi Used to create clip layer for raster _ssa Soil Survey Areas Data dump Excel table imported to Geodb Related to dissolved soils layer _a_dis Processing layers: _aoi projected to Albers …and buffered - this layer was used to clip raster

4. Data Processing Tools/Files .rmp files are “Remap” text files used to Classify Rasters Excel Macro ESRI 9.2 Toolbox contains data processing model

5. Raster Data • SDJR Model • Inputs – DEM and Soils • Outputs – Raster Surfaces

6. Model Inputs • Seamless Soils – Washington west of Cascades (Plus) • NED 10m DEM – Clipped to Extent of Soils (buffered)

7. Model Outputs: Surface Rasters - MLRA_SSO_1_2 - Root Directory - grids (folder contains Raster Outputs) Outputs are in ESRI GRID format Names all begin with “so1_2 _” and have a connotative suffix The following slides describe each dataset in the order shown at right

8. Raster Soils • Built on MUKEY (String) • Raster attribute table automatically adds a Value field (integer)

9. Raster Soils • May display based on Value field by default • Change display field in Properties >> Symbology tab Show: Unique Values Value field = MUKEY MUKEY 1 = NOTCOM Change to hollow or desired color

10. Raster Soils

11. Aspect • _asp - ‘Raw’ Aspect Calculation (float) • Flat areas assigned -1 • Other values are decimal degrees 0-360 • _asp_cl- Classified into 5 Increments (integer) • Flat areas assigned 0 • Other values are integers, {5, 10, 15, …360} • Change from “Stretched Color Ramp” to “Unique Values” in Properties >> Symbology tab

12. Aspect (classified)

13. Elevation (DEM) • _alb • floating point, XYZ meters • Source grid for Input DEM, Albers projection • _cls • integer, XYZ meters • Classified in 30m increments {0, 30, 60, 90, … 4410} • This is Raster used in the Tabulate Area analysis • _utm • floating point, XYZ meters • _alb grid projected to utm10n83 • Model Input DEM; source grid for all other grids except soils

14. Hillshade

15. Slope • _sl • ‘Raw’ slope calculation • Floating point, decimal percent slope • _sl_int • The _sl grid rounded to nearest whole % • Integer percent slope • _sl_cap • The _sl_int grid ‘capped’ at 100% slope {0, 1, 2, 3, …100} • Cells with value 100 are >= 100% slope • This is the grid used in the analysis *Cells with value 0 have no slope, i.e. flat areas, in all three slope grids

16. Classification Schema • Cell values represent upper limit of class • Aspect • Classified into 5° increments • Cell value 0 assigned to all -1 values in source grid (flat areas) • Cell value 5 assigned to values 0-5 in source grid • A cell with a value 25 represents aspects 20-25

17. Classification Schema • Elevation • Classified into 30m increments • Cell value 0 assigned to all values below sea level in source grid • Cell value 120 includes all elevations between 90-120m • Slope • Rounded, capped, integer slope percent • Cell value 0 assigned to values < .5% slope in source • Cell value 1 assigned to values > .5% and < 1.5% • Cell value 27.5 < 28 < 28.5 • Cell value 100 includes all values >= 100 in source grid

18. Tabulate Area Analysis • Spatial Analyst >> Zonal >> Tabulate Area • Input Zones – Raster Soils with MUKEYs • Based on the input zones, i.e. MUKEYs, it calculates the total number of cell-centers in classified analysis grid that intersect each zone • Output is a table that shows the total area of each class in the analysis grid per zone Uh…What?

19. ArcGIS Tabulate Area ‘Raw’ Output (.dbf) Each MUKEY is listed in the first Column Value_x columns headers represent Aspects from the classified Aspect grid, e.g. 0 = flat Data values represent total number of sq. meters in each Aspect for each MUKEY Note: Analysis is Map Unit wide, not per polygon. That is, each MUKEY is listed once

20. Tabulate Area • Interpreting results is difficult with many classes This Example: Slope %

21. Tabulate Area Summary - Excel Macro • TabulateAreaSummary_v1.xls • Included in data package under root directory • Includes ‘wizard’ • Limited functionality – for use with raw Tabulate Area results only • Written in 2009, it’s needs to be updated…

22. Tabulate Area Summary Result Output is Excel Workbook with 5 worksheets • Worksheets from Right to Left • First worksheet is named the same as the .xls file name, e.g. TA_aspect • Worksheet contains the ‘raw’ Tabulate Area data with user-definedheaders and formatted cells • “Value” has been changed to “Aspect” in headers • Cell values show 0 decimal places but retain original full values

23. Tabulate Area Summary • Worksheet – “acres” • Contains the same data as the first worksheet but cell values are acres, calculated from either sq. feet or meters

24. Tabulate Area Summary • Worksheet – “pivot_data” • Main function of macro • Converts two-dimensional array into three-column array required to create pivot table

25. Tabulate Area Summary • Worksheet “pivot_table” • Cross-tabulation table used to interpret data • Shows percent total area in each aspect (or slope, etc) by MUKEY • ** Group data dynamically • ** Linked to Pivot Chart which shows data distribution

26. Tabulate Area Summary – Pivot Table • Grouping Data Dynamically Select desired cells corresponding to the class value (0-2% slopes in this example) Tip: Use Ctrl to select non-adjacent cells (e.g. useful for bimodal distribution) Right-click >> Group Collapse the group to see the sum total % area for the group 30.27% of MU 74510 occurs within 0-2% slopes

27. Pivot Charts are linked to the pivot table

28. Tabulate Area Summary – Pivot Chart

29. Tabulate Area Summary – Pivot Chart

30. Tabulate Area Summary – Pivot Chart

31. Zonal Statistics ArcGIS Zonal Statistics Provides General Summary Statistics by MUKEY Example: Slope

32. Working with Pivot Tables The Excel script creates a pivot and chart using mostly default options Make the following additional changes Drag and drop the MUKEY cell to the cell above Total …after Before…

33. Working with Pivot Tables Click the filter icon next to “MUKEY” to select multiple MUKEYS for side-by-side comparison These MUs are all 0-3% slope Before… …after

34. Working with Pivot Tables Classifying slope groups: Click and drag to select the slopes of interest (0-3% in example) Tip: Use Ctrl to select non-adjacent cells (useful for bimodal distributions) R-click in highlighted area and select Group >> then click the minus sign to collapse These three map units have ~85% of their area in 0-3% slopes Before… …after

35. Working with Pivot Tables Important Notes: Always verify that the columns add up to 100% There are many ways to summarize the data in Excel pivot tables… Here, instead of each map unit showing over 80% of its area between 1-3%, this view is showing the percent contribution of each MUKEY toward the total area of ALL Three map units. That is, MUKEY 64250 contributes 77.12% of the area total area of these three map units combined; MU 64250 has more acres This info could be useful for some purposes, but is probably easier to just look at Acres for that information. *The “Acres” worksheet has Totals of both rows and columns and can be filtered See next for fix… This adds up to 100%

36. Working with Pivot Tables Right-click on “Sum of Acres” cell and choose “Value Field Settings” Select “% of Column Total”*from the dropdown * Different versions of Excel have slightly different options

37. Working with Pivot Tables • You can group Groups to create nested aggregations, collapse and expand to see each Example: Group 1 = 0-1% slope Group 2 = 2-3% slope Group 3 = 0-3% slope Example 2: Groups 1 and 2 have been collapsed to show their sums. Example 3: Group 3 has been collapsed to see the total of Groups1 and 2, i.e. the original grouping of 0-3%

38. Working with Pivot Tables • You can group columns, i.e. MUKEYs • This is helpful for seeing what the distribution of a new SDJR map unit would look like after combining Example: All three MUKEY headers are selected R-click and choose Group

39. Working with Pivot Tables If we combined the three MUKEYs from previous examples, 85% of the new map unit’s area would be between 0-3% slope Tip: Right-Click on any Group and choose “Ungroup…”

40. Working with Pivot Tables • Group all of the Zero values in the pivot table just to get them out of the way • In the previous example, nearly three MUKEYs are distributed almost entirely under 12% • Group 13-100% just to make the table easier to work with • When working on steeper slopes, ungroup 13-100% and group all of the lower values that have zero or near-zero values Note less than 2% of the area for these MUs occurs in slopes above 13%

41. Working with Pivot Charts The Pivot Charts are linked to the pivot tables Changes made in the pivot table are reflected in the Chart Here the Chart shows the 0-3% Group where each of the three MUs has ~85% total area in that group

42. Working with Pivot Tables This chart is much easier to read. It reflects the grouping of 13-100% slopes which are all near-zero values

43. Working with Pivot Charts Right-click on a chart background or use the Ribbon to change the chart type Example: Change from Column/Bar graph to line

44. Working with Pivot Charts Ungroup slope groups to visualize distribution with same line graph: This doesn’t work well with these map units because they are so tightly distributed between 0-3%

45. Working with Pivot Charts Line chart is helpful for visualization when MUs have broader distributions Note in this example MU 74142 is clearly mapped differently than the other two

46. Working with Pivot Chart Tip: Right-click on an Y-axis values, e.g. 100%, to change formatting This chart will always display up to 100% (fixed 1.0) Change Maximum to “Auto”and the Y-axis will vary based on the data

47. Working with Pivot Charts The Y-axis only goes up to 4.5% because of the wide distribution; the low percent areas in each individual slope integer class

48. The END Whityn Owen GIS Specialist – MO1 whityn.owen@or.usda.gov 503-414-3024