Soil Property Scripts

1. Soil Property Scripts National Soil Survey Center

2. Preliminaries • Refresh your local database • Clear your selected set • Use the Pangaea query called: “Area/Lmap/Mapunit/MajorComp by AreaSym, AreaType” • Target “Legend” against the national database • Area type nat* • Area symbol Interps • Run it

3. Preliminaries • Use the same query against your local database • Target Area, Legend mapunit, correlation, and component • Areatype name nat* • Area symbol Interps • Run it

4. Preliminaries • Use the Pangaea query Component by COKEY the national database • Set the Cokey to 775215 • Target datamapunit • Run it • Against the local database, use same cokey against the component table • Run it • Select that component in the Component Table

5. Function of Soil Property Scripts Provide data for interpretations, a “high”, “low”, and “rv”, one value for each, thus a main feature of property scripts is some variety of aggregation Provide data for report scripts Act like a subroutine Normally aggregate columns, row aggregation is performed in the main SQL Property scripts are executed for one component at a time as the report cycles. This has ramifications for map unit aggregated reports.

6. Anatomy of a Simple Script Property Name: Must be unique per NASIS Site Description: Tells user what the script is meant to do. Data Type: Tells what kind of data is being retrieved Modality: Tells how many items Default Value: We try not to use any more Character or Numeric High and Low High, Low, and RV RV

7. Anatomy of a simple script You have seen “Base Table” with Paul. It becomes very evident what “Base Table” does when you realize there are no tables listed above component. All the coordination is done elsewhere. Exec sql acts just as in a report. Select is the same also This property will only retrieve the representative value of frost free days from the component table. Property scripts MUST produce at least an RV, by DEFINE or alias Push the green button when a component is highlighted and the property runs

8. Data Aggregation in the SQL This script: Makes this output: Note a value for each layer. These are arrays of data, which we will look at more closely later. Note aggregation of “none”

9. Data Aggregation in the SQL This script: Makes this output: Note a value for each layer. These are arrays of data, which we will look at more closely later. Note aggregation of “none”

10. Data Aggregation in the SQL This script: Makes this output: Since default aggregation is “unique”, any repeats are combined.

11. Data Aggregation in the SQL This script: Makes this output: Since default aggregation is “unique”, any repeats are combined.

12. Data Aggregation in the SQL This script: Makes this output: Other aggregation types are max, min, last, unique, sum, list, and a few others. Usually use “none” to preserve all the data for future use. Since default aggregation is “unique”, any repeats are combined.

13. Deriving data using other properties This script: Makes this output: Other aggregation types are max, min, last, unique, sum, list, and a few others. Usually use “none” to preserve all the data for future use. Since default aggregation is “unique”, any repeats are combined. This is a “called” property

14. Derive data Advantages Disadvantages Longer run time Might hide some unexpected conditions, for example might mismatch restriction kinds with the main property • Script is less cluttered • Reduces amount of typing • Helps control aggregation problems

15. Data Aggregation using Define, Array functions • ARRAYMIN • ARRAYMAX • ARRAYMEDIAN • ARRAYMODE • In the context of properties, these are used mainly to aggregate horizon table data to get one number for a component

16. Data Aggregation: DEFINE Array Functions This script: Makes this output:

17. Data Aggregation: Weighted Average This script: Makes this output: These arrays must be the same size!!

18. Data Aggregation: Lookup This script: Makes this output: “arraymax” finds the thickest layer, the “lookup” finds the pct_r that is associated with that layer

19. Data Aggregation below the Horizon table This script: Makes this output: “arraymax” finds the thickest layer, the “lookup” finds the pct_r that is associated with that layer If you try to aggregate this script in the SQL or by using an array function, you will get just one number for each column from the whole component, but what we want to do here is find a volumetric CEC. So we need to account for the rock fragments by layer. We would like to combine the rock fragment volumes by layer to adjust the CEC. The answer is REGROUP.

20. Data Aggregation below the Horizon table: REGROUP This script: Makes this output: REGROUP of fragments by layer using the SUM method of aggregation. Careful attention is needed to get the syntax correct using REGROUP. Other REGROUP options are AVERAGE, FIRST, LAST, MIN, MAX, and LIST Properties usually iterate on the base table component, so a device like regroup is needed to give an ability to aggregate at a deeper level.

22. Assignment WTD_AVG CLAY CONTENT 25-180cm OR ABOVE RESTRICTION Make the above script return the weighted average total sand content from 15 to 100cm.

23. Reporting Property Output • No mapunit aggregation • Map unit aggregation • INTERP - (NAT) Single Property Script Representative Values • UTIL - (NAT) MU Aggregated Property Data (Dom Comp) • INTERP – (NAT) Property Script Output, Numeric, MU Aggregated

24. That can’t be done?