Inherent Suitability for Aerobic Soil Organisms

Soil health is primarily influenced by human management, which is not captured in soil survey data at this time. This interpretation provides information on inherent soil properties that influence our ability to build healthy soils through management.

Soil is the habitat for a wide variety of organisms. They range from microscopic viruses, bacteria, archaea, fungi, and protozoa through micro- and meso-fauna, including nematodes, mites, and springtails, to macrofauna, such as earthworms, centipedes, and beetles. A healthy soil is a living system that supports an abundant and diverse biological community that aids crop production by providing key services and functions. These include:

  1. The decomposition of organic materials and their conversion into soil organic matter;
  2. Enhanced nutrient cycling;
  3. Improved soil structure and stability, which positively influence water flow, storage, and availability;
  4. Plant protection against disease, pests, and environmental stress; and,
  5. Detoxification of pollutants. Soil microbes are generally most abundant in the surface layer around plant roots. This area is termed the “rhizosphere”. Soils vary in their inherent ability to foster plant growth and thus also in their ability to support microbial populations. Although bacteria and archaea possess alternative metabolic strategies to survive under low- or no-oxygen content (i.e., anaerobic conditions), all other soil organisms require oxygen. Also, the majority of soil bacteria in agricultural soils function more efficiently in aerobic conditions; therefore, only aerobic organisms are considered.

Several site properties and soil properties make major contributions to the suitability for aerobic organisms. The properties chosen for this table include:

  • Soil temperature, because most biological processes increase, often double, with a 10 degree C increase in temperature;
  • water, inferred through the average total yearly precipitation, because plant productivity is linked to precipitation and soil microbes thrive in the rhizosphere;
  • soil organic matter content, because organic carbon is required by many soil organisms as an energy and carbon source;
  • soil pore space, which influences water and gas movement as well as physical space for organisms to occupy and the tortuosity of paths through which they may move;
  • soil water content, which is important, for example, because when too much water is present anaerobic processes begin and the population shifts to anaerobic bacteria and when too little is present organisms may die or go dormant; and
  • osmotic conditions and the presence of toxic materials or the absence of required elements.

The degree of favorability of each of these properties is rated for a soil. The degree of limitation of the least favorable attribute determines the overall rating.

The ratings are both verbal and numerical. Numerical ratings indicate the suitability of the individual soil properties. The ratings are shown in decimal fractions ranging from 0.01 to 1.00. They indicate gradations between the point at which a soil feature has the greatest favorability for organisms (1.00) and the point at which the soil feature becomes not favorable (0.00).

Rating class terms indicate the extent to which the soils are favorable considering all the soil features that are examined for this land use. “Very favorable” indicates that the soil has features that are very favorable for aerobic soil organisms. Healthy and thriving populations can be expected on properly managed agricultural systems on these soils. “Somewhat favorable” indicates that the soil has features that are moderately favorable for aerobic soil organisms. The soil can be made more favorable by careful management. Fair performance and moderate maintenance can be expected. “Very limited” indicates that the soil has one or more features that are unfavorable for aerobic soil organisms.

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 with different ratings may 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 Poorly suited Moderately suited Well suited Impact
pH 0-30cm Less than 4.0 Between 4.0 and 6.0 Between 6.0 and 7.0 Micronutrient availability
Mean annual air temperature (C) Less than 0 or Greater than 50 Between 0 and 15 or Between 24 and 50 Between 15 and 24 Amount of heat required for adapted species
Moisture relations Maximum of A-C
A. Mean annual precipitation minus annual potential evapotranspiration (mm) Less than -100 Between -100 and 100 Greater than 100 Moisture available for organisms
B. Water table depth during the growing season (cm) 0 or less Between 0 and 30 or between 50 and 100 Between 30 and 50 Moist but not saturated conditions
C. Root zone AWC (cm) Less than 3 3 to 30 Greater than 30 Retains moisture between rainfall events
Electrical Conductivity (dS/m) Less than 0.3 Between 0.3 and 0.75 or between 1.5 and 9.0 Between 0.75 and 1.5 Cations for mineral needs but not saline
Greater than 9.0
Aluminum saturation 0 to 30cm (percent) 100 Between 2 and 100 Less than 2 Aluminum toxicity
Organic matter content 0-30cm (percent) Less than 0.3 Between 0.3 and 3.0 Greater than 3.0 Energy source for soil organisms
Bulk density difference ratio maximum from 0 to 30cm Greater than 1.0 0 to 1.0 0 or less Metric of pore space and soil strength
Clay content weighted average from 0 to 30cm (percent) Less than 30 Between 30 and 40 Greater than 40 Surface area for microbes

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 map units 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 interpretation data yet.
  • Create a table containing necessary interpretation 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 interpretation.
  • Sets the minimum cutoff percent 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 interpretations.

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;
id rating_key attributename rating rating_num
20 Suitability for Aerobic Soil Organisms:1 Suitability for Aerobic Soil Organisms Not favorable 1
21 Suitability for Aerobic Soil Organisms:2 Suitability for Aerobic Soil Organisms Somewhat favorable 2
22 Suitability for Aerobic Soil Organisms:3 Suitability for Aerobic Soil Organisms Very favorable 3
23 Suitability for Aerobic Soil Organisms:4 Suitability for Aerobic Soil Organisms Not rated 4

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

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

Group of Insert Statements to Populate the Final Output Tables

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 Suitability for Aerobic Soil Organisms Not rated 4 Suitability for Aerobic Soil Organisms:4 0.01 0.03 328.95
1 T9981 Fld3 Suitability for Aerobic Soil Organisms Somewhat favorable 2 Suitability for Aerobic Soil Organisms:2 99.99 328.92 328.95
2 T9981 Fld4 Suitability for Aerobic Soil Organisms Not rated 4 Suitability for Aerobic Soil Organisms:4 1.21 3.86 318.72
2 T9981 Fld4 Suitability for Aerobic Soil Organisms Somewhat favorable 2 Suitability for Aerobic Soil Organisms:2 98.79 314.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 attr ibutename rati ng rating_num rating_key rating_pct rating_a cres landunit acres rolling pct rolling_acre s
T9981 Fld3 Suitabil ity for Aerobic Soil Organisms Somewhat favorable 2 Suit ability for Aero bic Soil Organisms:2 99.99 328.92 328.95 99.99 328.92
T9981 Fld3 Suitabil ity for Aerobic Soil Organisms Not rate d 4 Suit ability for Aero bic Soil Organisms:4 0.01 0.03 328.95 100 328.95
T9981 Fld4 Suitabil ity for Aerobic Soil Organisms Somewhat favorable 2 Suit ability for Aero bic Soil Organisms:2 98.79 314.86 318.72 98.79 314.86
T9981 Fld4 Suitabil ity for Aerobic Soil Organisms Not rate d 4 Suit ability for Aero bic Soil Organisms:4 1.21 3.86 318.72 100 318.72
  • 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 Suitability for Aerobic Soil Organisms Somewhat favorable Suitability for Aerobic Soil Organisms:2 99.99 328.92 328.95
2 T9981 Fld3 Suitability for Aerobic Soil Organisms Not rated Suitability for Aerobic Soil Organisms:4 100 328.95 328.95
1 T9981 Fld4 Suitability for Aerobic Soil Organisms Somewhat favorable Suitability for Aerobic Soil Organisms:2 98.79 314.86 318.72
2 T9981 Fld4 Suitability for Aerobic Soil Organisms Not rated Suitability for Aerobic Soil Organisms:4 100 318.72 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 Suitability for Aerobic Soil Organisms:2 ND001 2018-09-12 19:21:50 SD105 2018-09-12 23:49:29
T9981 Fld4 Suitability for Aerobic Soil Organisms:2 ND001 2018-09-12 19:21:50

References

  1. Blanco-Canqui, H., M.M. Mikha, D.R. Presley, and M.M. Claassen. 2011. Addition of cover crops enhances no-till potential for improving soil physical properties. Soil Sci. Soc. Am. J. 75:1471–1482. doi:10.2136/sssaj2010.0430.
  2. Carr, B. 2015. Winter cover crop species adapted to North-Central West Texas and Southwestern Oklahoma. USDA-NRCS James E. Bud Smith Plant Material Center. Knox City, TX. June 2015. 8p. ID# 12635.
  3. Duiker, S.W. 2014. Effects of soil compaction. Penn State Extension.
  4. Fernandez-Calvino, D., E. Baath. 2010. Growth response of the bacterial community to pH in soils differing in pH. FEMS Microbiol Ecol. (2010) 73:149–156.
  5. Fierer, N., and R.B. Jackson. 2006. The diversity and biogeography of soil bacterial communities. Proceedings of the National Academy of Sciences of the United States of America 103:626–631.
  6. Hooper D.U., D.E. Bignell, V.K. Brown, and L. Brussaard. 2000. Interactions between aboveground and belowground biodiversity in terrestrial ecosystems: Patterns, mechanisms, and feedbacks. Bioscience 50 (12):1049–1061.
  7. Kemper W.D., and E.J. Koch. 1966. Aggregate stability of soils from western United States and Canada. Technical Bulletin No. 1355. United States Department of Agriculture Agricultural Research Service. U S Government Printing Office Washington, DC. pp 52.
  8. Kieft, T.L., E.D. Soroker, and M. Firestone. 1987. Microbial biomass response to rapid increase in water potential when dry soil is wetted. Soil Biology and Biochemistry 19(2): I 19–I 26.
  9. Lauber, C.L., M. Hamady, R. Knight, and N. Fierer. 2009. Pyrosequencing-based assessment of soil pH as a predictor of soil bacterial community structure at the continental scale. Applied and Environmental Microbiology 75:5111–5120.
  10. Olson, Kenneth, S.E. Ebelhar, and James M. Lang. 2014. Long-term effects of cover crops on crop yields, soil organic carbon stocks and sequestration. Open Journal of Soil Science 4(8):9. doi:10.4236/ojss.2014.48030.
  11. Owojori, O.J., A.J. Reinecke, P. Voua-Otomo, and S.A. Reinecke. 2009. Comparative study of the effects of salinity on life-cycle parameters of four soil-dwelling species (Folsomia candida, Enchytraeus doerjesi, Eisenia fetida and Aporrectodea caliginosa). Pedobiologia 52 (6):351–360. https://doi.org/10.1016/j.pedobi.2008.12.002.
  12. Porazinska D.L., R.D. Bardgett, M.B. Blaauw, W.H. Hunt, A.N. Parsons, T.R. Seastedt, and D.H. Wall. 2003. Relationships at the aboveground-belowground interface: Plants, soil biota, and soil processes. Ecological Monographs 73 (3):377–395. doi:10.1890/0012-9615(2003)073[0377:RATAIP]2.0.CO;2.
  13. Schenk, H.R., and R.B. Jackson. June 2002. Rooting depths, lateral root spreads and below-ground/above-ground allometries of plants in water-limited ecosystems. Journal of Ecology 90(3):480–494.
  14. U.S. Department of Agriculture, Natural Resources Conservation Service. National soil survey handbook, title 430-VI.
  15. U.S. Department of Agriculture, Natural Resources Conservation Service. 2008. Soil Quality Indicators. USDA-NRCS Publication.
  16. U.S. Department of Agriculture, Natural Resources Conservation Service. 2014. Soil quality kit—Guide for educators. Bulk density, moisture, aeration. USDA-NRCS Publication.