Soils are rated based on their susceptibility to compaction from the operation of the ground-based equipment that is used for planting, harvesting, and site preparation activities when soils are moist. Soil compaction is the process in which soil particles are pressed together more closely than in the original state. Typically, the soil must be moist to be compacted because the mineral grains must slide together. Compaction mostly reduces the abundance of large pores in the soil by damaging the structure of the soil. This produces several effects that are unwanted in agricultural soils because large pores are most effective at transmitting water and air through the soil. Compaction also increases the soil strength and thereby limits root penetration and growth. The ability of soil to hold water is adversely affected by compaction because the large pores hold water. The degree of compaction of a soil is measured by its bulk density, which is the mass per unit volume and generally is expressed in grams per cubic centimeter.

Compacted soils are less favorable for plant growth because of high soil bulk density and hardness, reduced pore space, and poor aeration and drainage. Root penetration and growth are decreased in compacted soils because the hardness or strength of these soils prevents the expansion of roots. Supplies of air, water, and nutrients that roots need are also less favorable where compaction decreases soil porosity and drainage.

Interpretation ratings are based on soil properties in the upper 12 inches of the profile. Factors considered are soil texture, soil organic matter content, soil structure, rock fragment content, and the existing bulk density. Each of these affects compaction. Organic matter in the soil provides resistance to compaction and resilience to the effects over time. Soil that has strong structure has discrete aggregates, which add strength. It is the aggregates that are deformed or destroyed by compactive forces, thus strong soil structure lowers the susceptibility to compaction. Similarly, rock fragments in the soil can bridge and provide a framework to resist compaction. Finally, if a soil is already fairly dense then causing further compaction is more difficult.

Definitions of the Ratings

Low.—The potential for compaction is insignificant. This soil is able to support standard equipment with minimal compaction. The soil is moisture insensitive, exhibiting only small changes in density with changing moisture content.

Medium.—The potential for compaction is significant. The growth rate of seedlings may be reduced following compaction. After the initial compaction (i.e., the first equipment pass), this soil is able to support standard equipment with only minimal increases in soil density. The soil is intermediate between moisture insensitive and moisture sensitive.

High.—The potential for compaction is significant. The growth rate of seedlings is reduced following compaction. After initial compaction, this soil is still able to support standard equipment, but the soil continues to compact with each subsequent pass. The soil is moisture sensitive, exhibiting large changes in density with changing moisture content.

The map unit components listed for each map unit in the accompanying “Summary by Map Unit” table in Web Soil Survey or the “Aggregation Report” in Soil Data Viewer are determined by the aggregation method chosen. An aggregated rating class is shown for each map unit. The components listed for each map unit are only those that have the same rating class as listed for the map unit. The percent composition of each component in a particular map unit is presented to help the user better understand the extent of each map unit that has the specified rating.

Other components that have different ratings can be present in each map unit. The ratings for all components, regardless of the map unit aggregated rating, can be viewed by generating the equivalent report from the Soil Reports tab in Web Soil Survey or from the Soil Data Mart site. Onsite investigation may be needed to validate these interpretations and to confirm the identity of the soil on a given site.

Criteria Table

Site or soil attribute Low Moderate High Impact
Soil Texture loamy sand, loamy fine sand, loamy coarse sand, sand, fine sand, coarse sand silty clay, clay, sandy clay, sandy clay loam, sandy loams with less than 15 percent clay loam, silt, silt loam, silty clay loam, very fine sandy loam, sandy loams with 15 percent or more clay
Strong soil structure Ratings are reduced by one class, such as from “high” to “moderate” for strong soil structure grade.
Rock fragment content 35 to 60 percent Ratings are reduced by one class.
Rock fragment content greater than 60 percent Ratings are reduced by two classes.

Soil Script Breakdown

Create AoI Table

CREATE TABLE #AoiTable 
    ( aoiid INT IDENTITY (1,1),
    landunit CHAR(20),
    aoigeom GEOMETRY);
  • Create AOI table with polygon geometry. Coordinate system must be WGS1984 (EPSG 4326).
SELECT @aoiGeom = GEOMETRY::STGeomFromText('MULTIPOLYGON (((-102.12335160658608 45.959173206572416, -102.13402890980223 45.959218442561564, -102.13386921506947 45.944643788188387, -102.12327175652177 45.944703605814198, -102.12335160658608 45.959173206572416)))', 4326);   
SELECT @aoiGeomFixed = @aoiGeom.MakeValid().STUnion(@aoiGeom.STStartPoint());  
INSERT INTO #AoiTable ( landunit, aoigeom )  
VALUES ('T9981 Fld3', @aoiGeomFixed); 
SELECT @aoiGeom = GEOMETRY::STGeomFromText('MULTIPOLYGON (((-102.1130336443976 45.959162795100383, -102.12335160658608 45.959173206572416, -102.12327175652177 45.944703605814198, -102.1128892282776 45.944710506326032, -102.1130336443976 45.959162795100383)))', 4326);   
SELECT @aoiGeomFixed = @aoiGeom.MakeValid().STUnion(@aoiGeom.STStartPoint());  
INSERT INTO #AoiTable ( landunit, aoigeom )  
VALUES ('T9981 Fld4', @aoiGeomFixed);

Create summary acres for each landunit

CREATE TABLE #AoiAcres
    ( aoiid INT,
    landunit CHAR(20),
    landunit_acres FLOAT
    );
INSERT INTO #AoiAcres (aoiid, landunit, landunit_acres )
SELECT  aoiid, landunit,
SUM( ROUND( ( ( GEOGRAPHY::STGeomFromWKB(aoigeom.STAsBinary(), 4326 ).STArea() ) / 4046.8564224 ), 3 ) ) AS landunit_acres
FROM #AoiTable
GROUP BY aoiid, landunit;
aoiid landunit landunit_acres
1 T9981 Fld3 328.952
2 T9981 Fld4 318.722

Populate intersected soil polygon table with geometry

-- Create intersected soil polygon table with geometry
CREATE TABLE #AoiSoils 
    ( polyid INT IDENTITY (1,1),
    aoiid INT,
    landunit CHAR(20),
    mukey INT,
    soilgeom GEOMETRY
    );
INSERT INTO #AoiSoils (aoiid, landunit, mukey, soilgeom)
SELECT A.aoiid, A.landunit, M.mukey, M.mupolygongeo.STIntersection(A.aoigeom ) AS soilgeom
FROM mupolygon M, #AoiTable A
WHERE mupolygongeo.STIntersects(A.aoigeom) = 1;

Populate soil geometry with landunit attribute

-- Soil geometry with landunits
CREATE TABLE #AoiSoils2 
    ( aoiid INT,
    polyid INT,
    landunit CHAR(20),
    mukey INT,
    poly_acres FLOAT,
    soilgeog GEOGRAPHY
    );
-- Populate Soil geometry with landunit attribute
INSERT INTO #AoiSoils2   
SELECT aoiid, polyid, landunit,  mukey, ROUND((( GEOGRAPHY::STGeomFromWKB(soilgeom.STAsBinary(), 4326 ).STArea() ) / 4046.8564224 ), 3 ) AS poly_acres, GEOGRAPHY::STGeomFromWKB(soilgeom.STAsBinary(), 4326 ) AS soilgeog 
FROM #AoiSoils;

Create Table to Store Survey Area Datestamps (sacatalog.saverest)

CREATE TABLE #DateStamps
(landunit CHAR(20),
datestamp VARCHAR(32));
landunit datestamp
T9981 Fld3 ND001 2018-09-12 19:21:50
T9981 Fld3 SD105 2018-09-12 23:49:29
T9981 Fld4 ND001 2018-09-12 19:21:50 
INSERT INTO #DateStamps
SELECT DISTINCT AM.landunit, ([SC].[areasymbol] + ' ' + CONVERT(VARCHAR(32),[SC].[saverest],120) ) AS datestamp
FROM #M4 AM
INNER JOIN mapunit Mu ON AM.mukey = Mu.mukey
INNER JOIN legend LG ON Mu.lkey = LG.lkey
INNER JOIN sacatalog SC ON Lg.areasymbol = SC.areasymbol;
  • Get survey area dates for all soil mapunits involved

Create Table to Store Landunit Metadata (survey area and saverest) Which Comes From #DateStamps

CREATE TABLE #LandunitMetadata
(landunit CHAR(20),
soils_metadata VARCHAR(150)
);
INSERT INTO #LandunitMetadata
SELECT DISTINCT
landunit,
STUFF((SELECT ' | ' + CAST([datestamp] AS VARCHAR(30))
FROM #DateStamps dt2
WHERE dt1.landunit = dt2.landunit
FOR XML PATH ('') ), 1, 2, '') AS soils_metadata
FROM #DateStamps dt1;
  • Populate landunit soils-metadata
landunit soils_metadata
T9981 Fld3 ND001 2018-09-12 19:21:50 SD105 2018-09-12 23:49:29
T9981 Fld4 ND001 2018-09-12 19:21:50

Populate #SDV with Interp Metadata

CREATE TABLE #SDV
(attributekey BIGINT,
attributename CHAR(60),
attributetablename CHAR(30),
attributecolumnname CHAR(30),
attributelogicaldatatype CHAR(20),
attributefieldsize SMALLINT,
attributeprecision TINYINT,
attributedescription NVARCHAR(MAX),
attributeuom NVARCHAR(60),
attributeuomabbrev NVARCHAR(30),
attributetype CHAR(20),
nasisrulename CHAR(60),
ruledesign NVARCHAR(60),
notratedphrase CHAR(15),
mapunitlevelattribflag TINYINT,
complevelattribflag TINYINT,
cmonthlevelattribflag TINYINT,
horzlevelattribflag TINYINT,
tiebreakdomainname CHAR(40),
tiebreakruleoptionflag TINYINT,
tiebreaklowlabel CHAR(20),
tiebreakhighlabel CHAR(20),
tiebreakrule SMALLINT,
resultcolumnname CHAR(10),
sqlwhereclause CHAR(255),
primaryconcolname CHAR(30),
pcclogicaldatatype CHAR(20),
primaryconstraintlabel CHAR(30),
secondaryconcolname CHAR(30),
scclogicaldatatype CHAR(20),
secondaryconstraintlabel CHAR(30),
dqmodeoptionflag TINYINT,
depthqualifiermode CHAR(20),
layerdepthtotop FLOAT,
layerdepthtobottom FLOAT,
layerdepthuom CHAR(20),
monthrangeoptionflag TINYINT,
beginningmonth CHAR(9),
endingmonth CHAR(9),
horzaggmeth CHAR(30),
interpnullsaszerooptionflag TINYINT,
interpnullsaszeroflag TINYINT,
nullratingreplacementvalue CHAR(254),
basicmodeflag TINYINT,
maplegendkey SMALLINT,
maplegendclasses TINYINT,
maplegendxml XML,
nasissiteid BIGINT,
wlupdated DATETIME,
algorithmname CHAR(50),
componentpercentcutoff TINYINT,
readytodistribute TINYINT,
effectivelogicaldatatype CHAR(20),
rulekey CHAR(30)
);
INSERT INTO #SDV (attributename, nasisrulename, rulekey, ruledesign, notratedphrase, resultcolumnname, maplegendxml, attributedescription)
SELECT sdv.attributename, sdv.nasisrulename, md.rulekey, md.ruledesign, sdv.notratedphrase, sdv.resultcolumnname, sdv.maplegendxml, sdv.attributedescription
FROM sdvattribute sdv
LEFT OUTER JOIN distinterpmd md ON sdv.nasisrulename = md.rulename
WHERE sdv.attributename IN ('Agricultural Organic Soil Subsidence', 'Soil Susceptibility to Compaction', 'Organic Matter Depletion', 'Surface Salt Concentration', 'Hydric Rating by Map Unit', 'Suitability for Aerobic Soil Organisms', 'Ponding Frequency Class','Flooding Frequency Class',
'Available Water Storage','Depth to Water Table', 'Drainage Class', 'Farmland Classification')
GROUP BY md.rulekey, sdv.attributename, sdv.nasisrulename, sdv.resultcolumnname, md.ruledesign, sdv.notratedphrase, sdv.maplegendxml, sdv.attributedescription;
  • Begin populating static tables. These are for the base soils data and metadata. No interp data yet.
  • Create a table containing necessary interp data.
  • Please note that if we instead get ruledesign from sdvattribute, those values change to integer as in 1:limitation, 2:suitability.

Populate soil map unit acres, aggregated by mukey (merges polygons together)

-- Soil map unit acres, aggregated by mukey (merges polygons together)
CREATE TABLE #M2
    ( aoiid INT,
    landunit CHAR(20),
    mukey INT,
    mapunit_acres FLOAT
    );
INSERT INTO #M2
SELECT DISTINCT M1.aoiid, M1.landunit, M1.mukey,
ROUND (SUM (M1.poly_acres) OVER(PARTITION BY M1.landunit, M1.mukey), 3) AS mapunit_acres
FROM #AoiSoils2 AS M1
GROUP BY M1.aoiid, M1.landunit, M1.mukey, M1.poly_acres;
aoiid landunit mukey mapunit_acres
1 T9981 Fld3 354627 0.426
1 T9981 Fld3 354648 0.287
1 T9981 Fld3 2494708 1.729
1 T9981 Fld3 2525720 56.699
1 T9981 Fld3 2525732 1.35
1 T9981 Fld3 2525733 0.129
1 T9981 Fld3 2525739 28.479
1 T9981 Fld3 2525745 4.983
1 T9981 Fld3 2525746 16.106
1 T9981 Fld3 2525754 12.638
1 T9981 Fld3 2525764 17.691
1 T9981 Fld3 2525766 0.032
1 T9981 Fld3 2525769 181.356
1 T9981 Fld3 2755648 2.449
1 T9981 Fld3 2755654 4.599
2 T9981 Fld4 2525720 8.623
2 T9981 Fld4 2525724 0.458
2 T9981 Fld4 2525730 31.514
2 T9981 Fld4 2525745 62.205
2 T9981 Fld4 2525746 63.55
2 T9981 Fld4 2525754 23.138
2 T9981 Fld4 2525767 3.86
2 T9981 Fld4 2525769 103.909
2 T9981 Fld4 2755639 0.443
2 T9981 Fld4 2755643 9.641
2 T9981 Fld4 2755648 11.382
aoiid landunit aoigeom
1 T9981 Fld3 POLYGON ((-102.13386921506947 45.944643788188387, -102.12327175652177 45.9447036058142, -102.12335160658608 45.959173206572416, -102.13402890980223 45.959218442561564, -102.13386921506947 45.944643788188387))
2 T9981 Fld4 POLYGON ((-102.12327175652177 45.9447036058142, -102.1128892282776 45.944710506326032, -102.1130336443976 45.959162795100383, -102.12335160658608 45.959173206572416, -102.12327175652177 45.9447036058142))

Create Table to Store Survey Area Datestamps (sacatalog.saverest)

CREATE TABLE #DateStamps
(landunit CHAR(20),
datestamp VARCHAR(32));
SELECT @attributeName = 'Suitability for Aerobic Soil Organisms';
SELECT @minPct = 10;
SELECT @minAcres = 10;
  • Defines the soil interpretion
  • Sets the minimun cutoff pecent for a resource concern at 10 percent or 10 acres for a given landunit

XML Parsing

SELECT @rating1 = (SELECT maplegendxml FROM #SDV WHERE attributename = @attributeName).value('(/Map_Legend/Legend_Elements/Labels/@value)[1]', 'VARCHAR(100)');
SELECT @rating2 = (SELECT maplegendxml FROM #SDV WHERE attributename = @attributeName).value('(/Map_Legend/Legend_Elements/Labels/@value)[2]', 'VARCHAR(100)');
SELECT @rating3 = (SELECT maplegendxml FROM #SDV WHERE attributename = @attributeName).value('(/Map_Legend/Legend_Elements/Labels/@value)[3]', 'VARCHAR(100)');
SELECT @rating4 = (SELECT maplegendxml FROM #SDV WHERE attributename = @attributeName).value('(/Map_Legend/Legend_Elements/Labels/@value)[4]', 'VARCHAR(100)');
SELECT @rating5 = (SELECT maplegendxml FROM #SDV WHERE attributename = @attributeName).value('(/Map_Legend/Legend_Elements/Labels/@value)[5]', 'VARCHAR(100)');
SELECT @rating6 = (SELECT maplegendxml FROM #SDV WHERE attributename = @attributeName).value('(/Map_Legend/Legend_Elements/Labels/@value)[6]', 'VARCHAR(100)');
  • Get ordered set of interphrc values from sdvattribute.maplegendxml. This is assumed to begin with the ‘worst’ rating. Need to double-check this for all interps.

Set Interp Rulekey and Ruledesign as a Variable To Be Used in Cointerp Query

SELECT @ruleKey = (SELECT rulekey FROM #SDV WHERE attributename = @attributeName);
SELECT @ruleDesign = (SELECT ruledesign FROM #SDV WHERE attributename = @attributeName)
SELECT @notRatedPhrase = (SELECT notratedphrase FROM #SDV WHERE attributename = @attributeName);

Add Not Rated Phrase to @rating Variables

IF @notRatedPhrase IS NOT NULL
  IF @rating1 IS NULL (SELECT @rating1 = @notRatedPhrase)
  ELSE 
    IF @rating2 IS NULL (SELECT @rating2 = @notRatedPhrase)
    ELSE
      IF @rating3 IS NULL (SELECT @rating3 = @notRatedPhrase)
      ELSE
        IF @rating4 IS NULL (SELECT @rating4 = @notRatedPhrase)
        ELSE 
          IF @rating5 IS NULL (SELECT @rating5 = @notRatedPhrase)
          ELSE
            IF @rating6 IS NULL (SELECT @rating6 = @notRatedPhrase)

Append the Rating Classes for This Interp to the #RatingClasses Table

INSERT INTO #RatingClasses (attributename, ruledesign, rating1, rating2, rating3, rating4, rating5, rating6)
SELECT @attributeName AS attributename, @ruleDesign AS ruledesign, @rating1 AS rating1, @rating2 AS rating2, @rating3 AS rating3, @rating4 AS rating4, @rating5 AS rating5, @rating6 AS rating6;

attributename|ruledesign|rating1|rating2|rating3|rating4|rating5|rating6 Soil Susceptibility to Compaction|limitation |High |Medium |Low |Not rated |NULL|NULL

Populate the #RatingDomain Table with a Unique Rating_key for This Interp

SELECT @ratingKey = RTRIM(@attributeName) + ':1'
IF NOT @rating1 IS NULL INSERT INTO #RatingDomain VALUES( @ratingKey, @attributename, @rating1, 1)
 
SELECT @ratingKey = RTRIM(@attributeName) + ':2'
IF NOT @rating2 IS NULL INSERT INTO #RatingDomain VALUES( @ratingKey, @attributename, @rating2, 2)
 
SELECT @ratingKey = RTRIM(@attributeName) + ':3'
IF NOT @rating3 IS NULL INSERT INTO #RatingDomain VALUES( @ratingKey, @attributename, @rating3, 3)
 
SELECT @ratingKey = RTRIM(@attributeName) + ':4'
IF NOT @rating4 IS NULL INSERT INTO #RatingDomain VALUES( @ratingKey, @attributename, @rating4, 4)
 
SELECT @ratingKey = RTRIM(@attributeName) + ':5'
IF NOT @rating5 IS NULL INSERT INTO #RatingDomain VALUES( @ratingKey, @attributename, @rating5, 5)
 
SELECT @ratingKey = RTRIM(@attributeName) + ':6'
IF NOT @rating6 IS NULL INSERT INTO #RatingDomain VALUES( @ratingKey, @attributename, @rating6, 6)

id|rating_key|attributename|rating|rating_num 5|Soil Susceptibility to Compaction:1 |Soil Susceptibility to Compaction |High |1 6|Soil Susceptibility to Compaction:2 |Soil Susceptibility to Compaction |Medium |2 7|Soil Susceptibility to Compaction:3 |Soil Susceptibility to Compaction |Low |3 8|Soil Susceptibility to Compaction:4 |Soil Susceptibility to Compaction |Not rated |4

Populate Component Level Ratings Using the Currently Set Soil Interpretation

TRUNCATE TABLE #M5
INSERT INTO #M5
SELECT M4.aoiid, M4.landunit, M4.mukey, mapunit_acres, M4.cokey, M4.compname, M4.comppct_r, TP.interphrc AS rating, SUM (M4.comppct_r) OVER(PARTITION BY M4.landunit, M4.mukey) AS mu_pct_sum
FROM #M4 AS M4
LEFT OUTER JOIN cointerp AS TP ON M4.cokey = TP.cokey AND rulekey = @ruleKey
WHERE M4.majcompflag = 'yes';

aoiid|landunit|mukey|mapunit_acres|cokey|compname|comppct_r|rating|mu_pct_sum 1|T9981 Fld3 |354627|0.426|16464494|Daglum|25|Medium|90 1|T9981 Fld3 |354627|0.426|16464495|Farnuf|65|Medium|90 1|T9981 Fld3 |354648|0.287|16464607|Amor |25|Medium|85 1|T9981 Fld3 |354648|0.287|16464612|Reeder|60|Medium|85 1|T9981 Fld3 |2494708|1.729|16663930|Amor |49|Medium|81 1|T9981 Fld3 |2494708|1.729|16663931|Cabba |32|Medium|81 1|T9981 Fld3 |2525720|56.699|16663899|Daglum|33|Medium|88 1|T9981 Fld3 |2525720|56.699|16663903|Rhoades |55|Medium|88 1|T9981 Fld3 |2525732|1.35|16663796|Ekalaka |55|Medium|72 1|T9981 Fld3 |2525732|1.35|16663797|Yegen |17|Medium|72 1|T9981 Fld3 |2525733|0.129|16663951|Vebar |50|Medium|75 1|T9981 Fld3 |2525733|0.129|16663952|Cohagen |25|Medium|75 1|T9981 Fld3 |2525739|28.479|16663915|Parshall |20|Medium|78 1|T9981 Fld3 |2525739|28.479|16663917|Vebar |58|Medium|78 1|T9981 Fld3 |2525745|4.983|16663921|Shambo|75|Medium|75 1|T9981 Fld3 |2525746|16.106|16663927|Shambo|78|Medium|78 1|T9981 Fld3 |2525754|12.638|16663602|Harriet |75|Medium|75 1|T9981 Fld3 |2525764|17.691|16663611|Regan |55|Medium|55 1|T9981 Fld3 |2525766|0.032|16663539|Water |100|Not rated |100 1|T9981 Fld3 |2525769|181.356|16663985|Belfield |48|Medium|88 1|T9981 Fld3 |2525769|181.356|16663987|Daglum|40|Medium|88 1|T9981 Fld3 |2755648|2.449|16663766|Reeder|58|Medium|78 1|T9981 Fld3 |2755648|2.449|16663767|Janesburg |20|Medium|78 1|T9981 Fld3 |2755654|4.599|16663846|Reeder|60|Medium|85 1|T9981 Fld3 |2755654|4.599|16663847|Amor |25|Medium|85 2|T9981 Fld4 |2525720|8.623|16663899|Daglum|33|Medium|88 2|T9981 Fld4 |2525720|8.623|16663903|Rhoades |55|Medium|88 2|T9981 Fld4 |2525724|0.458|16664017|Savage|30|Medium|85 2|T9981 Fld4 |2525724|0.458|16664018|Daglum|20|Medium|85 2|T9981 Fld4 |2525724|0.458|16664022|Belfield |35|Medium|85 2|T9981 Fld4 |2525730|31.514|16663991|Regent|68|Medium|85 2|T9981 Fld4 |2525730|31.514|16663992|Savage|17|Medium|85 2|T9981 Fld4 |2525745|62.205|16663921|Shambo|75|Medium|75 2|T9981 Fld4 |2525746|63.55|16663927|Shambo|78|Medium|78 2|T9981 Fld4 |2525754|23.138|16663602|Harriet |75|Medium|75 2|T9981 Fld4 |2525767|3.86|16663540|Water |100|Not rated |100 2|T9981 Fld4 |2525769|103.909|16663985|Belfield |48|Medium|88 2|T9981 Fld4 |2525769|103.909|16663987|Daglum|40|Medium|88 2|T9981 Fld4 |2755639|0.443|16663554|Savage|62|Medium|80 2|T9981 Fld4 |2755639|0.443|16663555|Grail |18|Medium|80 2|T9981 Fld4 |2755643|9.641|16663957|Flasher |30|Low |88 2|T9981 Fld4 |2755643|9.641|16663958|Vebar |40|Medium|88 2|T9981 Fld4 |2755643|9.641|16663959|Tally |18|Medium|88 2|T9981 Fld4 |2755648|11.382|16663766|Reeder|58|Medium|78 2|T9981 Fld4 |2755648|11.382|16663767|Janesburg |20|Medium|78

Populate Component Level Ratings with Adjusted Component Percent to Account for the Un-used Minor Components

TRUNCATE TABLE #M6
INSERT INTO #M6
SELECT aoiid, landunit, mukey, mapunit_acres, cokey, compname, comppct_r, rating, mu_pct_sum, (1.0 * comppct_r / mu_pct_sum) AS adj_comp_pct
FROM #M5;

aoiid|landunit|mukey|mapunit_acres|cokey|compname|comppct_r|rating|mu_pct_sum|adj_comp_pct 1|T9981 Fld3 |354627|0.426|16464494|Daglum |25|Medium |90|0.277777778 1|T9981 Fld3 |354627|0.426|16464495|Farnuf |65|Medium |90|0.722222222 1|T9981 Fld3 |354648|0.287|16464607|Amor |25|Medium |85|0.294117647 1|T9981 Fld3 |354648|0.287|16464612|Reeder |60|Medium |85|0.705882353 1|T9981 Fld3 |2494708|1.729|16663930|Amor |49|Medium |81|0.604938272 1|T9981 Fld3 |2494708|1.729|16663931|Cabba |32|Medium |81|0.395061728 1|T9981 Fld3 |2525720|56.699|16663899|Daglum |33|Medium |88|0.375 1|T9981 Fld3 |2525720|56.699|16663903|Rhoades |55|Medium |88|0.625 1|T9981 Fld3 |2525732|1.35|16663796|Ekalaka |55|Medium |72|0.763888889 1|T9981 Fld3 |2525732|1.35|16663797|Yegen |17|Medium |72|0.236111111 1|T9981 Fld3 |2525733|0.129|16663951|Vebar |50|Medium |75|0.666666667 1|T9981 Fld3 |2525733|0.129|16663952|Cohagen |25|Medium |75|0.333333333 1|T9981 Fld3 |2525739|28.479|16663915|Parshall |20|Medium |78|0.256410256 1|T9981 Fld3 |2525739|28.479|16663917|Vebar |58|Medium |78|0.743589744 1|T9981 Fld3 |2525745|4.983|16663921|Shambo |75|Medium |75|1 1|T9981 Fld3 |2525746|16.106|16663927|Shambo |78|Medium |78|1 1|T9981 Fld3 |2525754|12.638|16663602|Harriet |75|Medium |75|1 1|T9981 Fld3 |2525764|17.691|16663611|Regan |55|Medium |55|1 1|T9981 Fld3 |2525766|0.032|16663539|Water |100|Not rated |100|1 1|T9981 Fld3 |2525769|181.356|16663985|Belfield |48|Medium |88|0.545454545 1|T9981 Fld3 |2525769|181.356|16663987|Daglum |40|Medium |88|0.454545455 1|T9981 Fld3 |2755648|2.449|16663766|Reeder |58|Medium |78|0.743589744 1|T9981 Fld3 |2755648|2.449|16663767|Janesburg |20|Medium |78|0.256410256 1|T9981 Fld3 |2755654|4.599|16663846|Reeder |60|Medium |85|0.705882353 1|T9981 Fld3 |2755654|4.599|16663847|Amor |25|Medium |85|0.294117647 2|T9981 Fld4 |2525720|8.623|16663899|Daglum |33|Medium |88|0.375 2|T9981 Fld4 |2525720|8.623|16663903|Rhoades |55|Medium |88|0.625 2|T9981 Fld4 |2525724|0.458|16664017|Savage |30|Medium |85|0.352941176 2|T9981 Fld4 |2525724|0.458|16664018|Daglum |20|Medium |85|0.235294118 2|T9981 Fld4 |2525724|0.458|16664022|Belfield |35|Medium |85|0.411764706 2|T9981 Fld4 |2525730|31.514|16663991|Regent |68|Medium |85|0.8 2|T9981 Fld4 |2525730|31.514|16663992|Savage |17|Medium |85|0.2 2|T9981 Fld4 |2525745|62.205|16663921|Shambo |75|Medium |75|1 2|T9981 Fld4 |2525746|63.55|16663927|Shambo |78|Medium |78|1 2|T9981 Fld4 |2525754|23.138|16663602|Harriet |75|Medium |75|1 2|T9981 Fld4 |2525767|3.86|16663540|Water |100|Not rated |100|1 2|T9981 Fld4 |2525769|103.909|16663985|Belfield |48|Medium |88|0.545454545 2|T9981 Fld4 |2525769|103.909|16663987|Daglum |40|Medium |88|0.454545455 2|T9981 Fld4 |2755639|0.443|16663554|Savage |62|Medium |80|0.775 2|T9981 Fld4 |2755639|0.443|16663555|Grail |18|Medium |80|0.225 2|T9981 Fld4 |2755643|9.641|16663957|Flasher |30|Low |88|0.340909091 2|T9981 Fld4 |2755643|9.641|16663958|Vebar |40|Medium |88|0.454545455 2|T9981 Fld4 |2755643|9.641|16663959|Tally |18|Medium |88|0.204545455 2|T9981 Fld4 |2755648|11.382|16663766|Reeder |58|Medium |78|0.743589744 2|T9981 Fld4 |2755648|11.382|16663767|Janesburg |20|Medium |78|0.256410256

Populate Component Acres by Multiplying Map Unit Acres with Adjusted Component Percent

TRUNCATE TABLE #M8
INSERT INTO #M8
SELECT  aoiid, landunit, mukey, mapunit_acres, cokey, compname, comppct_r, rating, MU_pct_sum, adj_comp_pct, ROUND ( (adj_comp_pct * mapunit_acres), 4) AS co_acres
FROM #M6;

aoiid|landunit|mukey|mapunit_acres|cokey|compname|comppct_r|rating|MU_pct_sum|adj_comp_pct|co_acres 1|T9981 Fld3 |354627|0.426|16464494|Daglum |25|Medium |90|0.277777778|0.1183 1|T9981 Fld3 |354627|0.426|16464495|Farnuf |65|Medium |90|0.722222222|0.3077 1|T9981 Fld3 |354648|0.287|16464607|Amor|25|Medium |85|0.294117647|0.0844 1|T9981 Fld3 |354648|0.287|16464612|Reeder |60|Medium |85|0.705882353|0.2026 1|T9981 Fld3 |2494708|1.729|16663930|Amor|49|Medium |81|0.604938272|1.0459 1|T9981 Fld3 |2494708|1.729|16663931|Cabba |32|Medium |81|0.395061728|0.6831 1|T9981 Fld3 |2525720|56.699|16663899|Daglum |33|Medium |88|0.375|21.2621 1|T9981 Fld3 |2525720|56.699|16663903|Rhoades |55|Medium |88|0.625|35.4369 1|T9981 Fld3 |2525732|1.35|16663796|Ekalaka |55|Medium |72|0.763888889|1.0312 1|T9981 Fld3 |2525732|1.35|16663797|Yegen |17|Medium |72|0.236111111|0.3187 1|T9981 Fld3 |2525733|0.129|16663951|Vebar |50|Medium |75|0.666666667|0.086 1|T9981 Fld3 |2525733|0.129|16663952|Cohagen |25|Medium |75|0.333333333|0.043 1|T9981 Fld3 |2525739|28.479|16663915|Parshall |20|Medium |78|0.256410256|7.3023 1|T9981 Fld3 |2525739|28.479|16663917|Vebar |58|Medium |78|0.743589744|21.1767 1|T9981 Fld3 |2525745|4.983|16663921|Shambo |75|Medium |75|1|4.983 1|T9981 Fld3 |2525746|16.106|16663927|Shambo |78|Medium |78|1|16.106 1|T9981 Fld3 |2525754|12.638|16663602|Harriet |75|Medium |75|1|12.638 1|T9981 Fld3 |2525764|17.691|16663611|Regan |55|Medium |55|1|17.691 1|T9981 Fld3 |2525766|0.032|16663539|Water |100|Not rated |100|1|0.032 1|T9981 Fld3 |2525769|181.356|16663985|Belfield |48|Medium |88|0.545454545|98.9215 1|T9981 Fld3 |2525769|181.356|16663987|Daglum |40|Medium |88|0.454545455|82.4345 1|T9981 Fld3 |2755648|2.449|16663766|Reeder |58|Medium |78|0.743589744|1.8211 1|T9981 Fld3 |2755648|2.449|16663767|Janesburg |20|Medium |78|0.256410256|0.6279 1|T9981 Fld3 |2755654|4.599|16663846|Reeder |60|Medium |85|0.705882353|3.2464 1|T9981 Fld3 |2755654|4.599|16663847|Amor|25|Medium |85|0.294117647|1.3526 2|T9981 Fld4 |2525720|8.623|16663899|Daglum |33|Medium |88|0.375|3.2336 2|T9981 Fld4 |2525720|8.623|16663903|Rhoades |55|Medium |88|0.625|5.3894 2|T9981 Fld4 |2525724|0.458|16664017|Savage |30|Medium |85|0.352941176|0.1616 2|T9981 Fld4 |2525724|0.458|16664018|Daglum |20|Medium |85|0.235294118|0.1078 2|T9981 Fld4 |2525724|0.458|16664022|Belfield |35|Medium |85|0.411764706|0.1886 2|T9981 Fld4 |2525730|31.514|16663991|Regent |68|Medium |85|0.8|25.2112 2|T9981 Fld4 |2525730|31.514|16663992|Savage |17|Medium |85|0.2|6.3028 2|T9981 Fld4 |2525745|62.205|16663921|Shambo |75|Medium |75|1|62.205 2|T9981 Fld4 |2525746|63.55|16663927|Shambo |78|Medium |78|1|63.55 2|T9981 Fld4 |2525754|23.138|16663602|Harriet |75|Medium |75|1|23.138 2|T9981 Fld4 |2525767|3.86|16663540|Water |100|Not rated |100|1|3.86 2|T9981 Fld4 |2525769|103.909|16663985|Belfield |48|Medium |88|0.545454545|56.6776 2|T9981 Fld4 |2525769|103.909|16663987|Daglum |40|Medium |88|0.454545455|47.2314 2|T9981 Fld4 |2755639|0.443|16663554|Savage |62|Medium |80|0.775|0.3433 2|T9981 Fld4 |2755639|0.443|16663555|Grail |18|Medium |80|0.225|0.0997 2|T9981 Fld4 |2755643|9.641|16663957|Flasher |30|Low |88|0.340909091|3.2867 2|T9981 Fld4 |2755643|9.641|16663958|Vebar |40|Medium |88|0.454545455|4.3823 2|T9981 Fld4 |2755643|9.641|16663959|Tally |18|Medium |88|0.204545455|1.972 2|T9981 Fld4 |2755648|11.382|16663766|Reeder |58|Medium |78|0.743589744|8.4635 2|T9981 Fld4 |2755648|11.382|16663767|Janesburg |20|Medium |78|0.256410256|2.9185

Aggregate the Classes and Sum up the Component Acres by Landunit (Tract and Field number)

TRUNCATE TABLE #M10
INSERT INTO #M10
SELECT landunit, rating, SUM (co_acres) AS rating_acres
FROM #M8
GROUP BY landunit, rating
ORDER BY landunit, rating_acres DESC;

landunit|rating|rating_acres T9981 Fld4 |Low |3.2867 T9981 Fld3 |Medium |328.9209 T9981 Fld4 |Medium |311.5763 T9981 Fld3 |Not rated |0.032 T9981 Fld4 |Not rated |3.86

Group of Insert Statements to Populate the Final Output Rables

INSERT INTO #LandunitRatingsDetailed1 (aoiid, landunit, attributename, rating, rating_key, rating_num, rating_pct, rating_acres, landunit_acres)
SELECT aoiid, M10.landunit, @attributeName AS attributename, M10.rating, RD.rating_key, RD.rating_num,
ROUND ((rating_acres/ landunit_acres) * 100.0, 2) AS rating_pct, 
ROUND (rating_acres,2) AS rating_acres,
ROUND ( landunit_acres, 2) AS landunit_acres
FROM #M10 M10
LEFT OUTER JOIN #AoiAcres ON #AoiAcres.landunit = M10.landunit
INNER JOIN #RatingDomain RD ON M10.rating = RD.rating
WHERE RD.attributename = @attributeName
GROUP BY aoiid, M10.landunit, M10.rating, rating_key, rating_acres, landunit_acres, rating_num
ORDER BY landunit, attributename, rating_num DESC;

aoiid|landunit|attributename|rating|rating_num|rating_key|rating_pct|rating_acres|landunit_acres 1|T9981 Fld3 |Soil Susceptibility to Compaction |Medium |2|Soil Susceptibility to Compaction:2 |99.99|328.92|328.95 1|T9981 Fld3 |Soil Susceptibility to Compaction |Not rated |4|Soil Susceptibility to Compaction:4 |0.01|0.03|328.95 2|T9981 Fld4 |Soil Susceptibility to Compaction |Low |3|Soil Susceptibility to Compaction:3 |1.03|3.29|318.72 2|T9981 Fld4 |Soil Susceptibility to Compaction |Medium |2|Soil Susceptibility to Compaction:2 |97.76|311.58|318.72 2|T9981 Fld4 |Soil Susceptibility to Compaction |Not rated |4|Soil Susceptibility to Compaction:4 |1.21|3.86|318.72

  • Detailed Landunit Ratings1: rating acres and rating percent by area for each soil-landunit polygon.
  • These will be summarized to a single set of interpretation ratings for each landunit. Currently there are 5 interpretations.

Determine Dominant Critical

INSERT INTO #LandunitRatingsDetailed2 (landunit, attributename, rating, rating_num, rating_key, rating_pct, rating_acres, landunit_acres, rolling_pct, rolling_acres)
SELECT landunit, attributename, rating, rating_num, rating_key, rating_pct, rating_acres, landunit_acres,
  rolling_pct = SUM(rating_pct) OVER
  (
    PARTITION BY landunit
    ORDER BY rating_key ROWS UNBOUNDED PRECEDING
  ),
  rolling_acres = SUM(rating_acres) OVER
  (
    PARTITION BY landunit
    ORDER BY rating_key ROWS UNBOUNDED PRECEDING
  )
  FROM #LandunitRatingsDetailed1
  WHERE attributename = @attributeName
  ORDER BY landunit, attributename;

landunit|attributename|rating|rating_num|rating_key|rating_pct|rating_acres|landunit_acres|rolling_pct|rolling_acres T9981 Fld3 |Soil Susceptibility to Compaction |Medium |2|Soil Susceptibility to Compaction:2 |99.99|328.92|328.95|99.99|328.92 T9981 Fld3 |Soil Susceptibility to Compaction |Not rated |4|Soil Susceptibility to Compaction:4 |0.01|0.03|328.95|100|328.95 T9981 Fld4 |Soil Susceptibility to Compaction |Medium |2|Soil Susceptibility to Compaction:2 |97.76|311.58|318.72|97.76|311.58 T9981 Fld4 |Soil Susceptibility to Compaction |Low |3|Soil Susceptibility to Compaction:3 |1.03|3.29|318.72|98.79|314.87 T9981 Fld4 |Soil Susceptibility to Compaction |Not rated |4|Soil Susceptibility to Compaction:4 |1.21|3.86|318.72|100|318.73

  • LandunitRatingsDetailed2 is populated with all information plus rolling_pct and rolling_acres which are using in the landunit summary rating.
  • Detailed Landunit Ratings2 table columns: landunit, attributename, rating, rating_key, rating_num, rating_pct, rating_acres, landunit_acres, rolling_pct, rolling_acres.

Landunit Ratings CART

INSERT INTO #LandunitRatingsCART (id, landunit, attributename, rating, rating_key, rolling_pct, rolling_acres, landunit_acres)
SELECT ROW_NUMBER() OVER(PARTITION BY landunit ORDER BY rating_key ASC) AS "id",
landunit, attributename, rating, rating_key, rolling_pct, rolling_acres, landunit_acres
FROM #LandunitRatingsDetailed2
WHERE attributename = @attributeName AND (rolling_pct >= @minPct OR rolling_acres >= @minAcres)
  • Identifies the single, most limiting rating (per landunit) that comprises at least 10% by area or 10 acres.
  • This record will have an id value of 1.

id|landunit|attributename|rating|rating_key|rolling_pct|rolling_acres|landunit_acres 1|T9981 Fld3 |Soil Susceptibility to Compaction |Medium |Soil Susceptibility to Compaction:2 |99.99|328.92|328.95 2|T9981 Fld3 |Soil Susceptibility to Compaction |Not rated |Soil Susceptibility to Compaction:4 |100|328.95|328.95 1|T9981 Fld4 |Soil Susceptibility to Compaction |Medium |Soil Susceptibility to Compaction:2 |97.76|311.58|318.72 2|T9981 Fld4 |Soil Susceptibility to Compaction |Low |Soil Susceptibility to Compaction:3 |98.79|314.87|318.72 3|T9981 Fld4 |Soil Susceptibility to Compaction |Not rated |Soil Susceptibility to Compaction:4 |100|318.73|318.72

Final CART Soil Interpretation Ratings for Each Landunit

INSERT INTO #LandunitRatingsCART2 (landunit, attributename, rating, rating_key, rolling_pct, rolling_acres, landunit_acres, soils_metadata)
SELECT LC.landunit, LC.attributename, LC.rating, LC.rating_key, rolling_pct, rolling_acres, landunit_acres, MD.soils_metadata
FROM #LandunitRatingsCART LC
INNER JOIN #RatingDomain RD ON LC.attributename = RD.attributename AND LC.rating = RD.rating
INNER JOIN #LandunitMetadata MD ON LC.landunit = MD.landunit
WHERE LC.id = 1
ORDER BY landunit, rating_key;
  • The LandunitRatingsCART table will have all data, but the record for the overall landunit rating will have an id = 1.

landunit|rating_key|soils_metadata T9981 Fld3 |Soil Susceptibility to Compaction:2 | ND001 2018-09-12 19:21:50 SD105 2018-09-12 23:49:29 T9981 Fld4 |Soil Susceptibility to Compaction:2 | ND001 2018-09-12 19:21:50

References

  1. Adams, P.W. 1981. Compaction of forest soils. Oregon State University Extension Publication PNW 217.
  2. Adams, P.W. 1998. Soil compaction on woodland properties. Oregon State University Extension Publication EC 1109.
  3. Boyer, Don. 1997. Guidelines for soil resource protection and restoration for timber harvest and post-harvest activities. U.S Forest Service, Pacific Northwest Region, Watershed Management.
  4. Geist, J.M., J.W. Hazard, and K.W. Seidel. 1989. Assessing physical conditions of some Pacific Northwest volcanic ash soils after forest harvest. Soil Science Society of America Journal 53:946–950.
  5. Froehlich, Henry A., and David H. McNab. 1983. Minimizing soil compaction in Pacific Northwest forests. Proceedings of Sixth North American Forest Soils Conference, University of Tennessee.
  6. Page-Dumrose, Deborah S. 1993. Susceptibility of volcanic ash influenced soils in Northern Idaho to mechanical compaction. U.S. Forest Service Intermountain Research Station. Research Note INT–409.