Water table refers to a saturated zone in the soil and occurs during specified months. Estimates of the upper limit are based mainly on observations of the water table at selected sites and on evidence of a saturated zone, namely grayish colors (redoximorphic features) in the soil. A saturated zone that lasts for less than a month is not considered a water table. Water table is recorded as three separate values in the database. A low value and a high value indicate the range for the soil component. A “representative” value indicates the expected value for the component. Only the representative value is used for the CART calculation.

Seasonal High Water Table

Description

Groundwater or a perched water table causing saturated conditions near the surface degrades water resources or restricts capability of land to support its intended use.

Objective

Reduce seasonally high water table.

Analysis within CART

Each PLU regardless of land use will default to a “not assessed” status for seasonal high water table. The planner will identify this resource concern based on site specific conditions. If the planner identifies the resource concern it will trigger a soil data web service to determine if the water table is within 18 inches of the surface. If a high water table is identified a threshold of 50 will be set.

Script Breakdown

Insert identifier(s) string and WKT geometry for each area of interest (AOI) polygon

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);
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 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;

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

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;

Component level data and mapunit sum-of-comppct_r (major components only)

CREATE TABLE #M4
(   aoiid INT,
    landunit CHAR(20),
    mukey INT,
    mapunit_acres FLOAT,
    cokey INT,
    compname CHAR(60),
    comppct_r INT,
    majcompflag CHAR(3),
    mu_pct_sum INT, 
    major_mu_pct_sum INT, 
    drainagecl CHAR(254)
    );
---Populate component level data with cokey, comppct_r and mapunit sum-of-comppct_r
INSERT INTO #M4
SELECT M2.aoiid, M2.landunit, M2.mukey, mapunit_acres, CO.cokey, CO.compname, CO.comppct_r, CO.majcompflag, (SELECT SUM (CCO.comppct_r) 
FROM #M2 AS MM2
INNER JOIN component AS CCO ON CCO.mukey=MM2.mukey  AND M2.mukey=MM2.mukey AND majcompflag = 'Yes'  )  AS  major_mu_pct_sum,
SUM (CO.comppct_r) OVER(PARTITION BY M2.landunit, M2.mukey) AS mu_pct_sum, drainagecl
FROM #M2 AS M2
INNER JOIN component AS CO ON CO.mukey = M2.mukey
aoiid landunit mukey mapunit_acres cokey compname comppct_r majcompflag mu_pct_sum major_mu_pct_sum drainagecl
1 T9981 Fld3 354627 0.426 16464493 Slickspots 2 No 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464494 Daglum 25 Yes 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464495 Farnuf 65 Yes 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464496 Grail 3 No 90 100 Moderately well drained
1 T9981 Fld3 354627 0.426 16464497 Rhoades 3 No 90 100 Well drained
1 T9981 Fld3 354627 0.426 16464498 Tally 2 No 90 100 Well drained
1 T9981 Fld3 354648 0.287 16464607 Amor 25 Yes 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464608 Arnegard 4 No 85 100 Moderately well drained
1 T9981 Fld3 354648 0.287 16464609 Belfield 4 No 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464610 Heil 1 No 85 100 Poorly drained
1 T9981 Fld3 354648 0.287 16464611 Lantry 3 No 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464612 Reeder 60 Yes 85 100 Well drained
1 T9981 Fld3 354648 0.287 16464613 Vebar 3 No 85 100 Well drained
1 T9981 Fld3 2494708 1.729 16663928 Regent 5 No 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663929 Chama 5 No 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663930 Amor 49 Yes 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663931 Cabba 32 Yes 81 100 Well drained
1 T9981 Fld3 2494708 1.729 16663932 Shambo 9 No 81 100 Well drained
1 T9981 Fld3 2525720 56.699 16663899 Daglum 33 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525720 56.699 16663900 Savage 3 No 176 100 Well drained
1 T9981 Fld3 2525720 56.699 16663901 Barkof 2 No 176 100 Well drained
1 T9981 Fld3 2525720 56.699 16663902 Rhoades 2 No 176 100 Moderately well drained
1 T9981 Fld3 2525720 56.699 16663903 Rhoades 55 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525720 56.699 16663904 Belfield 5 No 176 100 Moderately well drained
1 T9981 Fld3 2525732 1.35 16663795 Lakota 4 No 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663796 Ekalaka 55 Yes 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663797 Yegen 17 Yes 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663798 Desart 14 No 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663799 Parshall 6 No 72 100 Well drained
1 T9981 Fld3 2525732 1.35 16663800 Rhoades 2 No 72 100 Moderately well drained
1 T9981 Fld3 2525732 1.35 16663801 Vebar 2 No 72 100 Well drained
1 T9981 Fld3 2525733 0.129 16663950 Beisigl 7 No 75 100 Somewhat excessively drained
1 T9981 Fld3 2525733 0.129 16663951 Vebar 50 Yes 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663952 Cohagen 25 Yes 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663953 Tally 14 No 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663954 Amor 2 No 75 100 Well drained
1 T9981 Fld3 2525733 0.129 16663955 Arnegard 2 No 75 100 Well drained
1 T9981 Fld3 2525739 28.479 16663915 Parshall 20 Yes 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663916 Tally 12 No 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663917 Vebar 58 Yes 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663918 Arnegard 8 No 78 100 Well drained
1 T9981 Fld3 2525739 28.479 16663919 Cohagen 2 No 78 100 Well drained
1 T9981 Fld3 2525745 4.983 16663920 Farnuf 12 No 150 100 Well drained
1 T9981 Fld3 2525745 4.983 16663921 Shambo 75 Yes 150 100 Well drained
1 T9981 Fld3 2525745 4.983 16663922 Arnegard 10 No 150 100 Well drained
1 T9981 Fld3 2525745 4.983 16663923 Amor 3 No 150 100 Well drained
1 T9981 Fld3 2525746 16.106 16663924 Arnegard 10 No 156 100 Well drained
1 T9981 Fld3 2525746 16.106 16663925 Farnuf 8 No 156 100 Well drained
1 T9981 Fld3 2525746 16.106 16663926 Amor 4 No 156 100 Well drained
1 T9981 Fld3 2525746 16.106 16663927 Shambo 78 Yes 156 100 Well drained
1 T9981 Fld3 2525754 12.638 16663598 Heil 3 No 150 100 Poorly drained
1 T9981 Fld3 2525754 12.638 16663599 Rhoades 4 No 150 100 Moderately well drained
1 T9981 Fld3 2525754 12.638 16663600 Daglum 2 No 150 100 Moderately well drained
1 T9981 Fld3 2525754 12.638 16663601 Vanda 5 No 150 100 Well drained
1 T9981 Fld3 2525754 12.638 16663602 Harriet 75 Yes 150 100 Poorly drained
1 T9981 Fld3 2525754 12.638 16663603 Regan 6 No 150 100 Poorly drained
1 T9981 Fld3 2525754 12.638 16663604 Glenross 5 No 150 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663605 Peta 2 No 55 100 Somewhat poorly drained
1 T9981 Fld3 2525764 17.691 16663606 Dimmick 6 No 55 100 Very poorly drained
1 T9981 Fld3 2525764 17.691 16663607 Arveson 12 No 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663608 Regan 10 No 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663609 Harriet 7 No 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663610 Straw 3 No 55 100 Well drained
1 T9981 Fld3 2525764 17.691 16663611 Regan 55 Yes 55 100 Poorly drained
1 T9981 Fld3 2525764 17.691 16663612 Marysland 5 No 55 100 Poorly drained
1 T9981 Fld3 2525766 0.032 16663539 Water 100 Yes 100 100 NULL
1 T9981 Fld3 2525769 181.356 16663985 Belfield 48 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525769 181.356 16663986 Grail 5 No 176 100 Moderately well drained
1 T9981 Fld3 2525769 181.356 16663987 Daglum 40 Yes 176 100 Moderately well drained
1 T9981 Fld3 2525769 181.356 16663988 Savage 5 No 176 100 Well drained
1 T9981 Fld3 2525769 181.356 16663989 Rhoades 2 No 176 100 Moderately well drained
1 T9981 Fld3 2755648 2.449 16663766 Reeder 58 Yes 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663767 Janesburg 20 Yes 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663768 Amor 10 No 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663769 Dogtooth 5 No 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663770 Regent 3 No 156 100 Well drained
1 T9981 Fld3 2755648 2.449 16663771 Belfield 2 No 156 100 Moderately well drained
1 T9981 Fld3 2755648 2.449 16663772 Barkof 2 No 156 100 Well drained
1 T9981 Fld3 2755654 4.599 16663846 Reeder 60 Yes 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663847 Amor 25 Yes 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663848 Belfield 4 No 85 100 Moderately well drained
1 T9981 Fld3 2755654 4.599 16663849 Regent 3 No 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663850 Vebar 3 No 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663851 Chama 3 No 85 100 Well drained
1 T9981 Fld3 2755654 4.599 16663852 Arnegard 2 No 85 100 Well drained
2 T9981 Fld4 2525720 8.623 16663899 Daglum 33 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525720 8.623 16663900 Savage 3 No 176 100 Well drained
2 T9981 Fld4 2525720 8.623 16663901 Barkof 2 No 176 100 Well drained
2 T9981 Fld4 2525720 8.623 16663902 Rhoades 2 No 176 100 Moderately well drained
2 T9981 Fld4 2525720 8.623 16663903 Rhoades 55 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525720 8.623 16663904 Belfield 5 No 176 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664017 Savage 30 Yes 85 100 Well drained
2 T9981 Fld4 2525724 0.458 16664018 Daglum 20 Yes 85 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664019 Grail 8 No 85 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664020 Regent 5 No 85 100 Well drained
2 T9981 Fld4 2525724 0.458 16664021 Rhoades 2 No 85 100 Moderately well drained
2 T9981 Fld4 2525724 0.458 16664022 Belfield 35 Yes 85 100 Moderately well drained
2 T9981 Fld4 2525730 31.514 16663990 Daglum 2 No 85 100 Moderately well drained
2 T9981 Fld4 2525730 31.514 16663991 Regent 68 Yes 85 100 Well drained
2 T9981 Fld4 2525730 31.514 16663992 Savage 17 Yes 85 100 Well drained
2 T9981 Fld4 2525730 31.514 16663993 Cabba 2 No 85 100 Well drained
2 T9981 Fld4 2525730 31.514 16663994 Grail 6 No 85 100 Moderately well drained
2 T9981 Fld4 2525730 31.514 16663995 Moreau 5 No 85 100 Well drained
2 T9981 Fld4 2525745 62.205 16663920 Farnuf 12 No 150 100 Well drained
2 T9981 Fld4 2525745 62.205 16663921 Shambo 75 Yes 150 100 Well drained
2 T9981 Fld4 2525745 62.205 16663922 Arnegard 10 No 150 100 Well drained
2 T9981 Fld4 2525745 62.205 16663923 Amor 3 No 150 100 Well drained
2 T9981 Fld4 2525746 63.55 16663924 Arnegard 10 No 156 100 Well drained
2 T9981 Fld4 2525746 63.55 16663925 Farnuf 8 No 156 100 Well drained
2 T9981 Fld4 2525746 63.55 16663926 Amor 4 No 156 100 Well drained
2 T9981 Fld4 2525746 63.55 16663927 Shambo 78 Yes 156 100 Well drained
2 T9981 Fld4 2525754 23.138 16663598 Heil 3 No 150 100 Poorly drained
2 T9981 Fld4 2525754 23.138 16663599 Rhoades 4 No 150 100 Moderately well drained
2 T9981 Fld4 2525754 23.138 16663600 Daglum 2 No 150 100 Moderately well drained
2 T9981 Fld4 2525754 23.138 16663601 Vanda 5 No 150 100 Well drained
2 T9981 Fld4 2525754 23.138 16663602 Harriet 75 Yes 150 100 Poorly drained
2 T9981 Fld4 2525754 23.138 16663603 Regan 6 No 150 100 Poorly drained
2 T9981 Fld4 2525754 23.138 16663604 Glenross 5 No 150 100 Poorly drained
2 T9981 Fld4 2525767 3.86 16663540 Water 100 Yes 100 100 NULL
2 T9981 Fld4 2525769 103.909 16663985 Belfield 48 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525769 103.909 16663986 Grail 5 No 176 100 Moderately well drained
2 T9981 Fld4 2525769 103.909 16663987 Daglum 40 Yes 176 100 Moderately well drained
2 T9981 Fld4 2525769 103.909 16663988 Savage 5 No 176 100 Well drained
2 T9981 Fld4 2525769 103.909 16663989 Rhoades 2 No 176 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663552 Regent 3 No 80 100 Well drained
2 T9981 Fld4 2755639 0.443 16663553 Lawther 2 No 80 100 Well drained
2 T9981 Fld4 2755639 0.443 16663554 Savage 62 Yes 80 100 Well drained
2 T9981 Fld4 2755639 0.443 16663555 Grail 18 Yes 80 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663556 Belfield 8 No 80 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663557 Daglum 2 No 80 100 Moderately well drained
2 T9981 Fld4 2755639 0.443 16663558 Farland 5 No 80 100 Well drained
2 T9981 Fld4 2755643 9.641 16663956 Telfer 3 No 88 100 Somewhat excessively drained
2 T9981 Fld4 2755643 9.641 16663957 Flasher 30 Yes 88 100 Somewhat excessively drained
2 T9981 Fld4 2755643 9.641 16663958 Vebar 40 Yes 88 100 Well drained
2 T9981 Fld4 2755643 9.641 16663959 Tally 18 Yes 88 100 Well drained
2 T9981 Fld4 2755643 9.641 16663960 Parshall 5 No 88 100 Well drained
2 T9981 Fld4 2755643 9.641 16663961 Amor 4 No 88 100 Well drained
2 T9981 Fld4 2755648 11.382 16663766 Reeder 58 Yes 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663767 Janesburg 20 Yes 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663768 Amor 10 No 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663769 Dogtooth 5 No 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663770 Regent 3 No 156 100 Well drained
2 T9981 Fld4 2755648 11.382 16663771 Belfield 2 No 156 100 Moderately well drained
2 T9981 Fld4 2755648 11.382 16663772 Barkof 2 No 156 100 Well drained

Flag any component with a water table depth less than 36 cm during the growing season

CREATE TABLE #wet
( aoiid INT, 
 landunit CHAR(20), 
 mukey INT, 
 mapunit_acres FLOAT, 
 cokey INT , 
 cname CHAR(60), 
 copct  INT, 
 majcompflag CHAR(3), 
 soimoistdept_l INT, 
 soimoistdept_r INT,
 soimoiststat CHAR(7), 
 MIN_soimoistdept_l  INT, 
 MIN_soimoistdept_r INT,
 major_mu_pct_sum INT , mu_pct_sum INT

      );
INSERT INTO #wet
SELECT 
aoiid, 
landunit, 
M44.mukey, 
mapunit_acres, 
M44.cokey AS cokey, 
M44.compname AS cname, 
M44.comppct_r AS copct ,
M44.majcompflag AS majcompflag, 
soimoistdept_l, 
soimoistdept_r,
soimoiststat, 
MIN (soimoistdept_l) over(partition by M44.cokey) AS  MIN_soimoistdept_l,
MIN (soimoistdept_r) over(partition by M44.cokey) AS  MIN_soimoistdept_r,
major_mu_pct_sum, mu_pct_sum
FROM (#M4 AS M44 INNER JOIN (comonth AS CM  INNER JOIN  cosoilmoist   AS COSM  ON COSM.comonthkey=CM.comonthkey AND soimoiststat = 'Wet' AND CASE WHEN soimoistdept_l < 46 THEN 1 WHEN soimoistdept_r < 46 THEN 1 ELSE 2 END = 1
) ON M44.cokey = CM.cokey AND M44.majcompflag = 'Yes' 
INNER JOIN component ON  M44.cokey=component.cokey 
AND (CASE WHEN soimoistdept_l IS NULL THEN soimoistdept_r ELSE soimoistdept_l END) = (SELECT MIN (CASE WHEN soimoistdept_l IS NULL THEN soimoistdept_r ELSE soimoistdept_l END) 
FROM comonth AS CM2  
INNER JOIN  cosoilmoist  AS COSM2  ON COSM2.comonthkey=CM2.comonthkey AND soimoiststat = 'Wet' AND CASE WHEN soimoistdept_l < 46 THEN 1 WHEN soimoistdept_r < 46 THEN 1 ELSE 2 END = 1  AND  CM2.cokey=M44.cokey
))
WHERE CASE 
WHEN (taxorder = 'gelisols'  AND taxtempcl IN ('hypergelic', 'pergelic', 'subgelic') AND CM.month IN ('jul', 'aug')) THEN 1 
WHEN (taxtempregime IN ('cryic', 'pergelic', 'isofrigid') AND CM.month  IN ('jul', 'aug')) THEN 1 
WHEN (taxtempregime IN ('frigid') AND CM.month IN ('may', 'jun', 'jul', 'aug', 'sep')) THEN 1
WHEN (taxtempregime IN ('mesic') AND CM.month IN ( 'apr','may', 'jun', 'jul', 'aug', 'sep', 'oct')) THEN 1
WHEN (taxtempregime IN ('thermic', 'hyperthermic') and CM.month IN ('mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct'))THEN 1
WHEN (taxtempregime IN ('isothermic', 'isohyperthermic', 'isomesic') AND CM.month IN ('feb', 'mar', 'apr', 'may', 'jun', 'jul', 'aug', 'sep', 'oct', 'nov')) THEN 1
WHEN (CM.month IN ('jun', 'jul')) THEN 1
ELSE 2 END  = 1
  • Major Components
  • Soil Moisture Status equals Wet and water table less then 46 cm (AND)
  • Taxonomic Order is ‘gelisols’ AND Taxonomic Temperature Regime is either hypergelic, pergelic, subgelic’ and occurs in July or August (OR)
  • Taxonomic Temperature Regime is either cryic, pergelic, isofrigid and occurs in either July or August (OR)
  • Taxonomic Temperature Regime is frigid and occurs in either May to August (OR)
  • Taxonomic Temperature Regime Mesic and water table is present from April to October (OR)
  • Taxonomic Temperature Regime is either thermic or hyperthermic and water table present from May to October (OR)
  • Taxonomic Temperature Regime is either isothermic,isohyperthermic, isomesic and water table is present from February to November (OR)
  • Else-if conditions are met water table occurs in June or July.
aoiid landunit mukey mapunit_acres cokey cname copct majcompflag soimoistdept_l soimoistdept_r soimoiststat MIN_soimoistdept_l MIN_soimoistdept_r major_mu_pct_sum mu_pct_sum
2 T9981 Fld4 2525754 23.138 16663602 Harriet 75 Yes 0 23 Wet 0 23 75 100
1 T9981 Fld3 2525754 12.638 16663602 Harriet 75 Yes 0 23 Wet 0 23 55 100
1 T9981 Fld3 2525764 17.691 16663611 Regan 55 Yes 0 23 Wet 0 23 75 100 
CREATE TABLE #wet1
( aoiid INT, 
 landunit CHAR(20), 
 landunit_acres FLOAT,
 mukey INT, 
 mapunit_acres FLOAT, 
 cokey INT , 
 cname CHAR(60), 
 copct  INT, 
 majcompflag CHAR(3), 
 MIN_soimoistdept_l  INT, 
 MIN_soimoistdept_r INT,
 major_mu_pct_sum INT,  mu_pct_sum INT,
  adj_comp_pct FLOAT
      );

Normalizing component percent

INSERT INTO #wet1
SELECT DISTINCT #AoiAcres.aoiid, #AoiAcres.landunit, landunit_acres, mukey, mapunit_acres, cokey, cname, copct, majcompflag,  MIN_soimoistdept_l, MIN_soimoistdept_r, major_mu_pct_sum, mu_pct_sum,(1.0 * copct / major_mu_pct_sum) AS adj_comp_pct
FROM #AoiAcres
LEFT OUTER JOIN #wet AS wet ON wet.aoiid=#AoiAcres.aoiid
GROUP BY  #AoiAcres.aoiid,  #AoiAcres.landunit, landunit_acres, mukey, mapunit_acres, cokey, cname, copct, majcompflag, MIN_soimoistdept_r, MIN_soimoistdept_l, major_mu_pct_sum, mu_pct_sum
  • Adjust the component percent for the major components to sum up to 1 for the map unit.
aoiid landunit landunit_acres mukey mapunit_acres cokey cname copct majcompflag MIN_soimoistdept_l MIN_soimoistdept_r major_mu_pct_sum mu_pct_sum adj_comp_pct
1 T9981 Fld3 328.952 2525754 12.638 16663602 Harriet 75 Yes 0 23 75 100 1
1 T9981 Fld3 328.952 2525764 17.691 16663611 Regan 55 Yes 0 23 55 100 1
2 T9981 Fld4 318.722 2525754 23.138 16663602 Harriet 75 Yes 0 23 75 100 1

Calculate component acres

CREATE TABLE #wet2
    ( aoiid INT,
    landunit CHAR(20),
    landunit_acres FLOAT, 
    mukey INT,
    mapunit_acres FLOAT,
    cokey INT,
    cname CHAR(60),
    copct INT,
    major_MU_pct_sum INT,MU_pct_sum INT,
    adj_comp_pct FLOAT,
    co_acres FLOAT
    );
TRUNCATE TABLE #wet2
INSERT INTO #wet2
SELECT  aoiid, landunit, landunit_acres, mukey, mapunit_acres, cokey, cname, copct, major_MU_pct_sum, MU_pct_sum, adj_comp_pct, ROUND ( (adj_comp_pct * mapunit_acres), 4) AS co_acres
FROM #wet1;
aoiid landunit landunit_acres mukey mapunit_acres cokey cname copct major_MU_pct_sum MU_pct_sum adj_comp_pct co_acres
1 T9981 Fld3 328.952 2525754 12.638 16663602 Harriet 75 75 100 1 12.638
1 T9981 Fld3 328.952 2525764 17.691 16663611 Regan 55 55 100 1 17.691
2 T9981 Fld4 318.722 2525754 23.138 16663602 Harriet 75 75 100 1 23.138

Water Table by Land Unit

SELECT landunit, ROUND (landunit_acres,2) landunit_acres, ROUND (SUM (co_acres),2) AS water_table_acres, 
CASE WHEN ROUND (SUM (co_acres),2) IS NOT NULL THEN CONCAT ('Water Table' , ':' , 1) 
WHEN ROUND (SUM (co_acres),2) = 0 THEN CONCAT ('Water Table' , ':' , 0)
WHEN ROUND (SUM (co_acres),2) IS  NULL THEN CONCAT ('Water Table', ':' , 'Not Rated') 
END AS rating_key,
'Water Table' AS attributename 
FROM #wet2
GROUP BY landunit, landunit_acres
ORDER BY landunit;

*Joins the water table acres to the landunit.

landunit landunit_acres water_table_acres rating_key attributename
T9981 Fld3 328.95 30.33 Water Table:1 Water Table
T9981 Fld4 318.72 23.14 Water Table:1 Water Table