Mast head image

Data Dump by MLRA for Ecological site development

This script retrieves the minimum and maximum value for many soil properties so an ecological site key can be developed.


Text highlighted in yellow in the example script can be changed for a particular MLRA. Examples can be found on the Tips page.

Tips Page

Copy the example script below and paste it into the query page on the Soil Data Access site.

Soil Data Access Query Page

Example Script

SELECT LEFT((l.areasymbol), 2) AS state,

lo.areasymbol, l.areaname, airtempa_l, airtempa_r, airtempa_h, ffd_l, ffd_r, ffd_h, map_l, map_r, map_h, mu.mukey,

mu.musym, mu.muname, mu.muacres, c.cokey, c.comppct_r, c.compname, localphase,

CASE WHEN nirrcapscl IS NULL THEN nirrcapcl ELSE nirrcapcl + nirrcapscl END AS capclass,

c.slope_r, hydgrp, taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp, taxpartsize,

(SELECT TOP 1 taxminalogy FROM cotaxfmmin WHERE cotaxfmmin.cokey=c.cokey) AS mineralogy,

taxtempregime, hydricrating,

(SELECT TOP 1 cocropyld.nonirryield_r FROM cocropyld WHERE c.cokey=cocropyld.cokey AND cropname LIKE 'Pasture') AS PastureYLD,

(SELECT TOP 1 coecoclass.ecoclassid FROM component LEFT OUTER JOIN coecoclass ON component.cokey =

coecoclass.cokey WHERE coecoclass.cokey = c.cokey AND coecoclass.ecoclassref LIKE 'Ecological Site Description

Database' ORDER BY ecoclassid) AS ecositeID,

(SELECT TOP 1 coecoclass.ecoclassname FROM component LEFT OUTER JOIN coecoclass ON component.cokey =

coecoclass.cokey WHERE coecoclass.cokey = c.cokey AND coecoclass.ecoclassref LIKE 'Ecological Site Description

Database' ORDER BY ecoclassid) AS ecositename,

constreeshrubgrp,

(SELECT TOP 1 coecoclass.ecoclassid FROM component INNER JOIN coecoclass ON component.cokey = coecoclass.cokey

AND ecoclasstypename LIKE 'Forage Suitability Groups' WHERE coecoclass.cokey = c.cokey ) AS foragesuitgroupid,

(SELECT TOP 1 coecoclass.ecoclassname FROM component INNER JOIN coecoclass ON component.cokey = coecoclass.cokey

AND ecoclasstypename LIKE 'Forage Suitability Groups' WHERE coecoclass.cokey = c.cokey ) AS foragesuitgroupname,

foragesuitgrpid, rsprod_l, rsprod_r, rsprod_h,

(SELECT TOP 1 cp.fprod_l FROM component LEFT OUTER JOIN coforprod cp ON component.cokey = cp.cokey WHERE

component.cokey = c.cokey ORDER BY cp.fprod_l DESC) AS ForestProd_l,

(SELECT TOP 1 cp.fprod_r FROM component LEFT OUTER JOIN coforprod cp ON component.cokey = cp.cokey WHERE

component.cokey = c.cokey ORDER BY cp.fprod_r DESC) AS ForestProd_r,

(SELECT TOP 1 cp.fprod_h FROM component LEFT OUTER JOIN coforprod cp ON component.cokey = cp.cokey WHERE

component.cokey = c.cokey ORDER BY cp.fprod_h DESC) AS ForestProd_h,

(SELECT CASE WHEN min(resdept_r) IS NULL THEN 999 ELSE CAST(MIN(resdept_r) AS VARCHAR) END FROM component

LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey WHERE component.cokey = c.cokey AND reskind IS NOT NULL) AS restrictiondepthr,

(SELECT TOP 1 reskind FROM component LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey

WHERE component.cokey = c.cokey AND reskind IS NOT NULL) AS restrkind,

(SELECT TOP 1 reshard FROM component LEFT OUTER JOIN corestrictions ON component.cokey = corestrictions.cokey

WHERE component.cokey = c.cokey AND reskind IS NOT NULL) AS restrhard,

(SELECT CASE WHEN MIN(soimoistdept_r) IS NULL THEN 999 ELSE

CAST(MIN(soimoistdept_r) AS VARCHAR) END FROM component LEFT OUTER JOIN

comonth LEFT OUTER JOIN cosoilmoist ON comonth.comonthkey =

cosoilmoist.comonthkey ON comonth.cokey = component.cokey WHERE

component.cokey = c.cokey AND soimoiststat = 'Wet' AND ((taxtempregime IN

('Cryic', 'Pergelic') AND comonth.month IN ('July', 'August')) OR

(taxtempregime IN ('Frigid', 'Mesic', 'Isofrigid') AND comonth.month IN

('May', 'June', 'July', 'August', 'September')) OR (taxtempregime IN

('Thermic', 'Hyperthermic') AND comonth.month IN ('April', 'May', 'June',

'July', 'August', 'September', 'October')) OR (taxtempregime IN ('Isothermic',

'Isohyperthermic', 'Isomesic') AND comonth.month IN ('March', 'April', 'May',

'June', 'July', 'August', 'September', 'October', 'November')))) AS mingswatertable_r,

(SELECT CASE WHEN MAX(soimoistdept_r) IS NULL THEN 999 ELSE

CAST(MAX(soimoistdept_r) AS VARCHAR) END FROM component LEFT OUTER JOIN

comonth LEFT OUTER JOIN cosoilmoist ON comonth.comonthkey =

cosoilmoist.comonthkey ON comonth.cokey = component.cokey WHERE

component.cokey = c.cokey AND soimoiststat = 'Wet'and ((taxtempregime IN

('Cryic', 'Pergelic') AND comonth.month IN ('July', 'August')) OR

(taxtempregime IN ('Frigid', 'Mesic', 'Isofrigid') AND comonth.month IN

('May', 'June', 'July', 'August', 'September')) OR (taxtempregime IN

('Thermic', 'Hyperthermic') AND comonth.month IN ('April', 'May', 'June',

'July', 'August', 'September', 'October')) OR (taxtempregime IN ('Isothermic',

'Isohyperthermic', 'Isomesic') AND comonth.month IN ('March', 'April', 'May',

'June', 'July', 'August', 'September', 'October', 'November')))) AS maxgswatertable_r,

(SELECT TOP 1 flodfreqcl FROM comonth, MetadataDomainMaster dm,

MetadataDomainDetail dd WHERE comonth.cokey = c.cokey AND flodfreqcl =

ChoiceLabel AND DomainName = 'flooding_frequency_class' AND dm.DomainID =

dd.DomainID ORDER BY choicesequence DESC) AS flodfreq,

(SELECT TOP 1 pondfreqcl FROM comonth, MetadataDomainMaster dm,

MetadataDomainDetail dd WHERE comonth.cokey = c.cokey AND pondfreqcl =

ChoiceLabel AND DomainName = 'ponding_frequency_class' AND dm.DomainID =

dd.DomainID ORDER BY choicesequence DESC) AS ponding_frequency,

(SELECT TOP 1 copmgrp.pmgroupname FROM copmgrp WHERE c.cokey = copmgrp.cokey

AND copmgrp.rvindicator='yes') AS parent_material,

(SELECT TOP 1 cogeomordesc.geomfname FROM cogeomordesc WHERE c.cokey =

cogeomordesc.cokey AND cogeomordesc.rvindicator='yes' AND

cogeomordesc.geomftname = 'Landform') AS landform, drainagecl, hzname, hzdept_r, hzdepb_r, texture AS

surface_texture,

CASE WHEN (100-sieveno10_r) = 0 THEN '' ELSE (100-sieveno10_r) END AS sur_hor_sieve_frags,

(SELECT SUM(cf.fragvol_r) AS fragvol FROM chfrags cf WHERE cf.chkey = ch.chkey ) AS surf_fragvol,

(SELECT SUM(sfragcov_r) FROM cosurffrags WHERE c.cokey = cosurffrags.cosurffragskey ) AS surf_cov_fragvol,

om_r AS surf_om_r,

(SELECT MIN(om_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100om,

(SELECT MAX(om_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100om,

(SELECT 1-(dbthirdbar_r/2.65) FROM chorizon WHERE chorizon.cokey=c.cokey AND hzdept_r = 0) AS surface_porosity,

dbthirdbar_r AS surf_dbthirdbar_r,

(SELECT MIN(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100dbthirdbar_r,

(SELECT MAX(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100dbthirdbar_r,

(SELECT MIN(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS min150dbthirdbar_r,

(SELECT MAX(dbthirdbar_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS max150dbthirdbar_r,

(SELECT MIN(awc_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100awc,

(SELECT MAX(awc_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100awc,

(SELECT MIN(awc_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS min150awc,

(SELECT MAX(awc_r) FROM chorizon WHERE hzdept_r < 151 AND chorizon.cokey=c.cokey) AS max150awc,

lep_r AS surf_lep_r,

(SELECT MIN(lep_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100lep,

(SELECT MAX(lep_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100lep,

(SELECT SUM(lep_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS sum100lep,

ksat_r AS surf_ksat_r,

(SELECT MIN(ksat_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100Ksat,

(SELECT MAX(ksat_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100Ksat,

(SELECT ph1to1h2o_r FROM chorizon WHERE hzdept_r = 0 AND c.cokey=chorizon.cokey ) AS surf_ph_r,

(SELECT MAX(ph1to1h2o_r) FROM chorizon WHERE hzdepb_r > = 23 AND hzdept_r <23 AND c.cokey=chorizon.cokey ) AS cm20_max_ph_r,

(SELECT MIN(ph1to1h2o_r) FROM chorizon WHERE hzdepb_r > = 23 AND hzdept_r <23 AND c.cokey=chorizon.cokey ) AS cm20_min_ph_r,

(SELECT ph01mcacl2_r FROM chorizon WHERE hzdept_r = 0 AND c.cokey=chorizon.cokey ) AS surf_CA_ph_r,

(SELECT MAX(ph01mcacl2_r) FROM chorizon WHERE hzdepb_r > = 23 AND hzdept_r <23 AND c.cokey=chorizon.cokey ) AS cm20_max_CA_ph_r,

(SELECT CASE WHEN SUM(((hzdepb_r-hzdept_r)*awc_r)) IS NULL THEN '0' ELSE CAST(sum(((hzdepb_r-hzdept_r)*awc_r)) AS VARCHAR)

END FROM chorizon WHERE chorizon.cokey = c.cokey) AS soil_profile_AWS,

(SELECT aws0150wta FROM muaggatt WHERE mu.mukey= muaggatt.mukey) AS mapunitAWS150, ec_r AS surf_ec_r,

(SELECT CASE WHEN SUM(ec_r) IS NULL THEN '0' ELSE CAST(SUM(ec_r) AS VARCHAR ) END FROM chorizon WHERE

chorizon.cokey = c.cokey) AS soil_sum_EC_r,

(SELECT MIN(ec_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100EC,

(SELECT MAX(ec_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100EC,

(SELECT MAX(ec_r) FROM chorizon WHERE chorizon.cokey=c.cokey) AS maxEC,

caco3_r AS surf_caco3_r,

(SELECT CASE WHEN SUM(caco3_r) IS NULL THEN '0' ELSE CAST(SUM(caco3_r) AS VARCHAR ) END FROM chorizon WHERE

chorizon.cokey = c.cokey) AS soil_sum_CaCO3_r,

sar_r AS surf_sar_r,

(SELECT MIN(sar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100sar,

(SELECT MAX(sar_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100sar,

gypsum_r AS surf_gypsum_r,

(SELECT MIN(gypsum_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS min100gypsum,

(SELECT MAX(gypsum_r) FROM chorizon WHERE hzdept_r < 101 AND chorizon.cokey=c.cokey) AS max100gypsum

FROM legend AS l

INNER JOIN laoverlap AS lo ON l.lkey=lo.lkey AND l.areasymbol <> 'US'

INNER JOIN muaoverlap AS mo ON mo.lareaovkey = lo.lareaovkey AND lo.areatypename='mlra' AND lo.areasymbol LIKE '74'

INNER JOIN mapunit AS mu ON mu.mukey = mo.mukey

INNER JOIN component AS c ON c.mukey = mu.mukey AND compkind = 'series'

INNER JOIN chorizon AS ch ON ch.cokey = c.cokey AND hzdept_r = 0

INNER JOIN chtexturegrp AS ct ON ch.chkey=ct.chkey AND ct.rvindicator = 'yes'

ORDER BY l.areasymbol, mu.musym

Example Table Output

The field headers were not changed from the original script.

Description of tables and columns can be found on this link: Table and Column Descriptions

state areasymbol areaname airtempa_l airtempa_r airtempa_h ffd_l ffd_r ffd_h map_l map_r map_h mukey musym muname muacres cokey comppct_r compname localphase capclass slope_r hydgrp taxclname taxorder taxsuborder taxgrtgroup taxsubgrp taxpartsize mineralogy taxtempregime hydricrating PastureYLD ecositeID ecositename constreeshrubgrp foragesuitgroupid foragesuitgroupname foragesuitgrpid rsprod_l rsprod_r rsprod_h ForestProd_l ForestProd_r ForestProd_h restrictiondepthr restrkind restrhard mingswatertable_r maxgswatertable_r flodfreq ponding_frequency parent_material landform drainagecl hzname hzdept_r hzdepb_r surface_texture sur_hor_sieve_frags surf_fragvol surf_cov_fragvol surf_om_r min100om max100om surface_porosity surf_dbthirdbar_r min100dbthirdbar_r max100dbthirdbar_r min150dbthirdbar_r max150dbthirdbar_r min100awc max100awc min150awc max150awc surf_lep_r min100lep max100lep sum100lep surf_ksat_r min100Ksat max100Ksat surf_ph_r cm20_max_ph_r cm20_min_ph_r surf_CA_ph_r cm20_max_CA_ph_r soil_profile_AWS mapunitAWS150 surf_ec_r soil_sum_EC_r min100EC max100EC maxEC surf_caco3_r soil_sum_CaCO3_r surf_sar_r min100sar max100sar surf_gypsum_r min100gypsum max100gypsum
KS 74 Butler County, Kansas airtempa_l 13 14 165 180 200 680 770 860 1382916 3491 Wells loam, 1 to 3 percent slopes 80 13560244 85 Wells 2e 2 B Fine-loamy, mixed, active, mesic Udic Argiustolls Mollisols Ustolls Argiustolls Udic Argiustolls fine-loamy mixed mesic No 2800 4000 5800 999 999 999 None None residuum weathered from sandstone and shale hillslopes Well drained Ap 0 25 L 2 1 3 0.5 3 0.464151 1.42 1.42 1.57 1.42 1.57 0.17 0.18 0.16 0.18 2.7 2.7 4.8 15.900000333786 9 9 9 6.1 6.1 6.1 5.4 5.4 33.39 24.61 0.2 0.8 0.1 0.2 0.3 0 0 0 0 0 0 0 0



Helping People Help the Land

USDA is an equal opportunity provider, employer, and lender.