Home Software PostGIS: Spatially enabled Relational Database Sytem Analysis of SSURGO Data in PostGIS: An Overview Seasonally Wet Soils and Shrink-Swell Potential

Seasonally Wet Soils and Shrink-Swell Potential

Example 1: The location of seasonaly wet soils via two methods: hydricrating and USDA Soil Taxonomy interpretation.

Example 1 map

Example 2 map


-- optionally link polygons here...
-- compute the percent of each map unit that contains components that may be seasonally wet
SELECT mukey, wet_flag, sum(comppct_r) AS pct_mu
FROM
        (
        SELECT mukey, cokey, comppct_r,
        -- slightly less restrictive than hydricrating alone
        CASE WHEN ((taxclname ~~* '%aqu%') = 't') OR (hydricrating = 'Yes') then 1 ELSE 0 END AS wet_flag
        FROM component  
        -- subset to Yolo County
        WHERE areasymbol = 'ca113'
        ) AS yolo_wet_components
-- only keep map unit with some component that meets the criteria
WHERE wet_flag = 1
-- aggregate by map unit, wet_flag
GROUP BY yolo_wet_components.mukey, yolo_wet_components.wet_flag
ORDER BY mukey;
X

Example 1.1 Extract a list of wet components, and sum area based on component (series) name


 musym | mukey  |                             muname                             | wet_area_ac 
-------+--------+----------------------------------------------------------------+-------------
 Sc    | 459268 | Sacramento clay                                                |    35710.90
 Mf    | 459244 | Marvin silty clay loam                                         |    18877.18
 Sg    | 459272 | Sacramento soils, flooded                                      |    12273.51
 Cn    | 459219 | Clear Lake soils, flooded                                      |    11665.84
 Cc    | 459216 | Capay soils, flooded                                           |    11029.83
 Sv    | 459288 | Sycamore complex, drained                                      |     8410.41
 St    | 459286 | Sycamore silty clay loam, drained                              |     6900.77
 Ck    | 459218 | Clear Lake clay                                                |     6737.88
 Sa    | 459266 | Sacramento silty clay loam                                     |     6014.48
 Sp    | 459283 | Sycamore silt loam, drained                                    |     5537.18
 Sw    | 459289 | Sycamore complex, flooded                                      |     5041.96
 Wb    | 459301 | Willows clay                                                   |     4950.34
 Ss    | 459285 | Sycamore silty clay loam                                       |     4859.59
 Pb    | 459254 | Pescadero silty clay, saline-alkali                            |     4700.77
 So    | 459282 | Sycamore silt loam                                             |     3953.42
 Rh    | 459262 | Riverwash                                                      |     3698.88
 Pc    | 459255 | Pescadero soils, flooded                                       |     3589.01
 Tb    | 459292 | Tyndall very fine sandy loam                                   |     3299.03
 Ob    | 459252 | Omni silty clay                                                |     3174.44
[...]
X

Example 2: Classify soils according to shrink-swell capacity of the top 1 meter of soil, weighted by horizon thickness and component percent.


-- set a lower boundary for the query
\SET lwr_bdy 100
-- add a class label, based on NRCS guidelines
SELECT mapunit.musym, mapunit.muname, mapunit.muacres,
round(mu_wt_lep::numeric, 2) AS lep,
CASE WHEN mu_wt_lep < 3 THEN 'Low'
WHEN mu_wt_lep >= 3 AND mu_wt_lep < 6 THEN 'Moderate'
WHEN mu_wt_lep >= 6 AND mu_wt_lep < 9 THEN 'High'
WHEN mu_wt_lep >= 9 THEN 'Very High'
END AS lep_class
FROM
        (
        -- compute map unit lep, weighted by component percent, to set depth
        SELECT component.mukey,
        sum(component.comppct_r * co_wt_mean_lep) / sum(component.comppct_r) AS mu_wt_lep
        FROM
                (
                -- compute a horizon-thickness weighted mean lep to a set depth
                SELECT cokey, sum(thick * lep_r) / sum(thick) AS co_wt_mean_lep
                FROM
                        (
                        -- compute horizon thickness, but only to a set depth
                        SELECT cokey, hzdept_r, hzdepb_r, lep_r,
                        CASE WHEN hzdepb_r > :lwr_bdy THEN (:lwr_bdy - hzdept_r)
                        ELSE (hzdepb_r - hzdept_r) END AS thick
                        FROM chorizon
                        WHERE areasymbol = 'ca113'
                        AND lep_r IS NOT NULL
                        AND hzdept_r <= :lwr_bdy
                        ) AS hz_lep
                GROUP BY cokey
                ) AS co_lep
        JOIN component
        ON co_lep.cokey = component.cokey
        GROUP BY mukey
        ) AS mu_lep
JOIN mapunit
ON mu_lep.mukey = mapunit.mukey
ORDER BY muacres DESC, lep DESC;
X


 musym |                              muname                              | muacres | lep  | lep_class 
-------+------------------------------------------------------------------+---------+------+-----------
 Ya    | Yolo silt loam                                                   |   39698 | 2.52 | Low
 Sc    | Sacramento clay                                                  |   34886 | 7.50 | High
 Ca    | Capay silty clay                                                 |   33465 | 7.50 | High
 MrG2  | Millsholm rocky loam, 15 to 75 percent slopes, eroded            |   30118 | 1.50 | Low
 Rg    | Rincon silty clay loam                                           |   24580 | 6.36 | High
 BrA   | Brentwood silty clay loam, 0 to 2 percent slopes                 |   23045 | 7.50 | High
 CtD2  | Corning gravelly loam, 2 to 15 percent slopes, eroded            |   22080 | 5.34 | Moderate
 Mf    | Marvin silty clay loam                                           |   20970 | 6.60 | High
 DaF2  | Dibble clay loam, 30 to 50 percent slopes, eroded                |   18612 | 7.11 | High
 SmE2  | Sehorn-Balcom complex, 15 to 30 percent slopes, eroded           |   17794 | 6.17 | High
 TaA   | Tehama loam, 0 to 2 percent slopes                               |   16622 | 3.75 | Moderate
 BdF2  | Balcom-Dibble complex, 30 to 50 percent slopes, eroded           |   16405 | 5.73 | Moderate
 SmD   | Sehorn-Balcom complex, 2 to 15 percent slopes                    |   16117 | 6.50 | High
 BaF2  | Balcom silty clay loam, 30 to 50 percent slopes, eroded          |   12637 | 4.50 | Moderate
 Sg    | Sacramento soils, flooded                                        |   12258 | 6.27 | High
 Cn    | Clear Lake soils, flooded                                        |   11666 | 6.92 | High
 SmF2  | Sehorn-Balcom complex, 30 to 50 percent slopes, eroded           |   11226 | 6.33 | High
 Cc    | Capay soils, flooded                                             |   11030 | 7.50 | High
 Sv    | Sycamore complex, drained                                        |    9241 | 4.18 | Moderate
 Ms    | Myers clay                                                       |    8938 | 7.50 | High
 PfF2  | Positas gravelly loam, 30 to 50 percent slopes, eroded           |    7920 | 5.34 | Moderate
 St    | Sycamore silty clay loam, drained                                |    7839 | 4.50 | Moderate
 Ck    | Clear Lake clay                                                  |    6946 | 7.50 | High
 Ra    | Reiff very fine sandy loam                                       |    6847 | 1.50 | Low
[...]
X