Definitions

Flooding Frequency Class

Flooding is the temporary inundation of an area caused by overflowing streams, by runoff from adjacent slopes, or by tides. Water standing for short periods after rainfall or snowmelt is not considered flooding, and water standing in swamps and marshes is considered ponding rather than flooding. Frequency is expressed as none, very rare, rare, occasional, frequent, and very frequent.

  • “None” means that flooding is not probable. The chance of flooding is nearly 0 percent in any year. Flooding occurs less than once in 500 years.
  • “Very rare” means that flooding is very unlikely but possible under extremely unusual weather conditions. The chance of flooding is less than 1 percent in any year.
  • “Rare” means that flooding is unlikely but possible under unusual weather conditions. The chance of flooding is 1 to 5 percent in any year.
  • “Occasional” means that flooding occurs infrequently under normal weather conditions. The chance of flooding is 5 to 50 percent in any year.
  • “Frequent” means that flooding is likely to occur often under normal weather conditions. The chance of flooding is more than 50 percent in any year but is less than 50 percent in all months in any year.
  • “Very frequent” means that flooding is likely to occur very often under normal weather conditions. The chance of flooding is more than 50 percent in all months of any year.

Note: Very rare and rare are not used in CART.

Ponding Frequency Class

Ponding is standing water in a closed depression. The water is removed only by deep percolation, transpiration, or evaporation or by a combination of these processes. Ponding frequency classes are based on the number of times that ponding occurs over a given period. Frequency is expressed as none, rare, occasional, and frequent.

  • “None” means that ponding is not probable. The chance of ponding is nearly 0 percent in any year.
  • “Rare” means that ponding is unlikely but possible under unusual weather conditions. The chance of ponding is nearly 0 percent to 5 percent in any year.
  • “Occasional” means that ponding occurs, on the average, once or less in 2 years. The chance of ponding is 5 to 50 percent in any year.
  • “Frequent” means that ponding occurs, on the average, more than once in 2 years. The chance of ponding is more than 50 percent in any year.

Note: Rare is not used in CART.

Excess Water Resource Concern

Description

Surface water restricts land use and management goals.

Objective

Manage surface water more efficiently.

Analysis within CART

Each PLU is assessed regardless of land use. The assessment triggers a soil-data web-service to determine flood frequency rating of occasional, frequent, or very frequent. It also triggers a web service to evaluate if the PLU is within a 100-year flood plain according to FEMA maps. Either condition triggers an assessment of the resource concern and sets the threshold to 50. The planner may also identify the presence or absence of this resource concern based on site specific conditions and set the threshold to 50.

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

Flooding Frequency and Ponding Frequency

CREATE TABLE #pf
( aoiid INT, 
 landunit CHAR(20), 
 mukey INT, 
 mapunit_acres FLOAT, 
 cokey INT , 
 cname CHAR(60), 
 copct  INT, 
 majcompflag CHAR(3), 
 flodfreq CHAR(20), 
  pondfreq CHAR(20),   major_mu_pct_sum INT,
 mu_pct_sum INT);
INSERT INTO #pf
SELECT DISTINCT
aoiid, 
landunit, 
M44.mukey, 
 FORMAT ( mapunit_acres  , '#,###,##0.00') AS mapunit_acres , 
M44.cokey AS cokey, 
M44.compname AS cname, 
M44.comppct_r AS copct ,
M44.majcompflag AS majcompflag,
(SELECT TOP 1 flodfreqcl FROM comonth, MetadataDomainMaster AS  MD, MetadataDomainDetail AS DD WHERE comonth.cokey = M44.cokey and flodfreqcl = ChoiceLabel and DomainName = 'flooding_frequency_class' and 
MD.DomainID = DD.DomainID order by choicesequence desc) as flodfreq,
(SELECT TOP 1 pondfreqcl FROM  comonth, MetadataDomainMaster AS  MD, MetadataDomainDetail AS DD WHERE comonth.cokey = M44.cokey and pondfreqcl = ChoiceLabel and DomainName = 'ponding_frequency_class' and 
MD.DomainID = DD.DomainID order by choicesequence desc) as pondfreq,
major_mu_pct_sum ,mu_pct_sum
FROM #M4 AS M44 
INNER JOIN comonth AS CM ON M44.cokey = CM.cokey AND M44.majcompflag = 'Yes' 
AND CASE 
WHEN (flodfreqcl IN ('occasional', 'common', 'frequent', 'very frequent'))  THEN 1 
WHEN (pondfreqcl IN ('occasional', 'common', 'frequent'))  THEN 1
ELSE 2 END  = 1
GROUP BY aoiid, landunit, M44.mukey, mapunit_acres, major_mu_pct_sum,mu_pct_sum, M44.cokey,M44.compname , M44.majcompflag, M44.comppct_r, flodfreqcl, pondfreqcl
  • The assessment will trigger a soil data web service to determine flooding frequency rating of occasional, frequent, or very frequent.
  • The assessment will trigger a soil data web service to determine ponding frequency rating of occasional, common or frequent.
aoiid landunit mukey mapunit_acres cokey cname copct majcompflag flodfreq pondfreq major_mu_pct_sum mu_pct_sum
1 T9981 Fld3 2525754 12.64 16663602 Harriet 75 Yes Occasional Rare 75 100
1 T9981 Fld3 2525764 17.69 16663611 Regan 55 Yes Occasional Rare 55 100
2 T9981 Fld4 2525754 23.14 16663602 Harriet 75 Yes Occasional Rare 75 100 
CREATE TABLE #pf1
( aoiid INT, 
 landunit CHAR(20), 
  landunit_acres FLOAT,
 mukey INT, 
 mapunit_acres FLOAT, 
  cokey INT , 
 cname CHAR(60), 
 copct  INT, 
 majcompflag CHAR(3), 
 flodfreq CHAR(20), 
  pondfreq CHAR(20), 
 major_mu_pct_sum INT,  mu_pct_sum INT,
  adj_comp_pct FLOAT
      );
INSERT INTO #pf1
SELECT DISTINCT pf.aoiid, pf.landunit, landunit_acres, mukey, mapunit_acres, cokey, cname, copct, majcompflag, flodfreq, pondfreq , major_mu_pct_sum,  mu_pct_sum, (1.0 * copct / major_mu_pct_sum) AS adj_comp_pct
FROM #AoiAcres
LEFT OUTER JOIN #pf AS pf ON pf.aoiid=#AoiAcres.aoiid
GROUP BY  pf.aoiid, pf.landunit, landunit_acres, mukey, mapunit_acres, cokey, cname, copct, majcompflag, flodfreq, pondfreq ,  major_mu_pct_sum,  mu_pct_sum
aoiid landunit landunit_acres mukey mapunit_acres cokey cname copct majcompflag flodfreq pondfreq major_mu_pct_sum mu_pct_sum adj_comp_pct
1 T9981 Fld3 328.952 2525754 12.64 16663602 Harriet 75 Yes Occasional Rare 75 100 1
1 T9981 Fld3 328.952 2525764 17.69 16663611 Regan 55 Yes Occasional Rare 55 100 1
2 T9981 Fld4 318.722 2525754 23.14 16663602 Harriet 75 Yes Occasional Rare 75 100 1

Normalizing Component Percent’s and Calculating Components Acres From Intercepted Polygon

CREATE TABLE #pf2
    ( 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 #pf2
INSERT INTO #pf2
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), 2) AS co_acres
FROM #pf1;
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.64 16663602 Harriet 75 75 100 1 12.64
1 T9981 Fld3 328.952 2525764 17.69 16663611 Regan 55 55 100 1 17.69
2 T9981 Fld4 318.722 2525754 23.14 16663602 Harriet 75 75 100 1 23.14 
SELECT landunit, ROUND (landunit_acres,2) landunit_acres, ROUND (SUM (co_acres),2) AS ponding_flooding_acres, 
CASE WHEN ROUND (SUM (co_acres),2) IS NOT NULL THEN CONCAT ('Ponding or Flooding' , ':' , 1) 
WHEN ROUND (SUM (co_acres),2) = 0 THEN CONCAT ('Ponding or Flooding' , ':' , 0)
WHEN ROUND (SUM (co_acres),2) IS  NULL THEN CONCAT ('Ponding or Flooding', ':' , 'Not Rated') 
END AS rating_key,

'Ponding or Flooding' AS attributename 
FROM #pf2
GROUP BY landunit, landunit_acres
ORDER BY landunit;
landunit landunit_acres ponding_flooding_acres rating_key attributename
T9981 Fld3 328.95 30.33 Ponding or Flooding:1 Ponding or Flooding
T9981 Fld4 318.72 23.14 Ponding or Flooding:1 Ponding or Flooding

References

  1. U.S. Department of Agriculture, Natural Resources Conservation Service. National soil survey handbook, title 430-VI. http://www.nrcs.usda.gov/wps/portal/nrcs/detail/soils/ref/?cid=nrcs142p2_054242 (accessed July 2015).
  2. Soil Survey Staff. 2015. Soil Survey Geographic (SSURGO) Database. USDA Natural Resources Conservation Service. https://catalog.data.gov/dataset/soil-survey-geographic-ssurgo-database-for-various-soil-survey-areas-in-the-united-states- (accessed July 2015).