Seasonally Wet Soils and Shrink-Swell Potential
Example 1 map
Example 2 map
Example 1: The location of seasonaly wet soils via two methods: hydricrating and USDA Soil Taxonomy interpretation.
-- 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;
-- 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;
Example 1.1 Extract a list of wet components, and sum area based on component (series) name
-- compute estimated, total acreage of seasonally wet map units in Yolo Co.
SELECT mapunit.musym, mapunit.mukey, mapunit.muname,
round(sum(ST_Area(wkb_geometry) * (yolo_mu_data.pct_mu::numeric/100.0) * 0.000247)::numeric, 2) AS wet_area_ac
FROM
mapunit_poly
JOIN
(
-- 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
) AS yolo_mu_data
ON mapunit_poly.mukey = yolo_mu_data.mukey
JOIN mapunit
ON mapunit.mukey = yolo_mu_data.mukey
-- aggregate by map unit
GROUP BY mapunit.mukey, mapunit.muname, mapunit.musym
ORDER BY wet_area_ac DESC;
SELECT mapunit.musym, mapunit.mukey, mapunit.muname,
round(sum(ST_Area(wkb_geometry) * (yolo_mu_data.pct_mu::numeric/100.0) * 0.000247)::numeric, 2) AS wet_area_ac
FROM
mapunit_poly
JOIN
(
-- 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
) AS yolo_mu_data
ON mapunit_poly.mukey = yolo_mu_data.mukey
JOIN mapunit
ON mapunit.mukey = yolo_mu_data.mukey
-- aggregate by map unit
GROUP BY mapunit.mukey, mapunit.muname, mapunit.musym
ORDER BY wet_area_ac DESC;
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 [...]
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;
\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;
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 [...]