Prairie Hills Soils Consulting
Interpreting soils information from the USDA Soils Database to meet customer's planning needs for urban and farming uses since 1980

Example SQLs

These are but two of an infinite number of SQLs that can be used to mine the Soil Data Mart.  My expertise is working with clients to identify their specific needs and then write the SQL needed to mine the data.  Feel free to try them in the parameter box above.  Contact me for your soil needs.

Generic steel corrosion SQL to extract soil properties and interpretations for pipeline companies
SELECT LEFT((l.areasymbol), 2) as state, areasymbol, areaname, musym, muname, m.mukey, slope_l, slope_r, slope_h, compname, comppct_r, corsteel, drainagecl, frostact, taxpartsize,
(select max(ec_r) from chorizon where c.cokey=chorizon.cokey) as MAX_salinity,
(select max(extracid_r) from chorizon where c.cokey=chorizon.cokey) as MAX_ex_acid,
(select min(ph1to1h2o_r) from chorizon where c.cokey=chorizon.cokey) as MIN_pH,
--this is the water table sub query looking for the miniumum depth during the year
(SELECT CASE when min(soimoistdept_r) is null then '>200' else cast(min(soimoistdept_r) as varchar) END
from component left outer join comonth left outer join cosoilmoist
on comonth.comonthkey = cosoilmoist.comonthkey
on component.cokey = comonth.cokey
where component.cokey = c.cokey and soimoiststat in ('Wet', 'Saturation') ) as minwatertable_r,
---by taking the same query and changing it from a minimum to a maximum you can identify the fluctuation of the water table in depth
(SELECT CASE when max(soimoistdept_r) is null then '>200' else cast(max(soimoistdept_r) as varchar) END
from component left outer join comonth left outer join cosoilmoist
on comonth.comonthkey = cosoilmoist.comonthkey
on component.cokey = comonth.cokey
where component.cokey = c.cokey and soimoiststat in ('Wet', 'Saturation')) as maxwatertable_r,
--then by taking a count, the number of months with a wet saturation is identified
(SELECT count(month)
from component left outer join comonth left outer join cosoilmoist
on comonth.comonthkey = cosoilmoist.comonthkey
on component.cokey = comonth.cokey
where component.cokey = c.cokey and soimoiststat in ('Wet', 'Saturation')) as watertablemonths,
--this subquery identifies the ponding frequency assigned to the soil
(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,
--identify the Parent Material
(SELECT TOP 1 copmgrp.pmgroupname FROM copmgrp WHERE c.cokey = copmgrp.cokey AND copmgrp.rvindicator='yes') as parent_material,
--identify the Geomorphology
(SELECT TOP 1 cogeomordesc.geomfname FROM cogeomordesc WHERE c.cokey = cogeomordesc.cokey AND cogeomordesc.rvindicator='yes' and cogeomordesc.geomftname = 'Landform') as landform,
--identify any restrictions in the soils and the ype of restriction next two subqueries
(SELECT CASE when min(resdept_r) is null then '>200' 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 restrictiondepth,
(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 restrictionkind, l.tabularversion, majcompflag, c.cokey
FROM legend l inner join mapunit m left outer join component c
ON m.mukey = c.mukey
ON l.lkey = m.lkey
where l.areasymbol like 'ND001' and majcompflag = 'yes'
ORDER BY areaname, musym, comppct_r DESC


Using an XY coordinate point to obtain horizon level data


SELECT CAST(DATEPART(month, tabularverest) as varchar(2)) + '/' + CAST(DATEPART(day, tabularverest) as varchar(2)) + '/' + CAST(DATEPART(year, tabularverest) as varchar(4)) AS Tabular_date, l.areasymbol, S.mukey, M.musym, M.muname, M.farmlndcl, 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, hzname, hzdept_r, hzdepb_r,  texture,  rvindicator,  om_r,  awc_l,  awc_r,  awc_h,  dbthirdbar_r,  wthirdbar_r,  wfifteenbar_r,  case when (select sum(cf.fragvol_r) as fragvol FROM chfrags cf WHERE cf.chkey = ch.chkey ) is null then '0'  else (select sum(cf.fragvol_r) as fragvol FROM chfrags cf WHERE cf.chkey = ch.chkey ) END as fragvol, CASE when ec_r >= 4 and ec_r < 8 then 'Mod Low EC' when ec_r >= 8 and ec_r < 12 then 'Mod EC' when ec_r >= 12 and ec_r < 16 then 'Mod High EC' when ec_r >= 16 then 'High EC' ELSE 'Low EC' END as ec, (1-(dbthirdbar_r/2.65)) as porosity 

FROM SDA_Get_Mukey_from_intersection_with_WktWgs84('point(-97.852 38.89)') as S, sacatalog sc,
satabularver st, legend as l, mapunit as m, component as c, chorizon as ch, chtexturegrp as cht                

WHERE m.mukey = S.mukey and l.lkey=m.lkey and sc.areasymbol=l.areasymbol and sc.areasymbol=st.areasymbol and m.mukey=c.mukey and c.cokey=ch.cokey and ch.chkey=cht.chkey AND c.cokey = (SELECT TOP 1 component.cokey FROM component WHERE component.mukey=m.mukey ORDER BY component.comppct_r DESC) and AND ch.hzdept_r=(SELECT TOP(1) hzdept_r FROM chorizon WHERE cokey=c.cokey AND (hzname NOT LIKE 'O' + '%%') and (hzname NOT LIKE 'L' + '%%') and (hzname NOT LIKE 'F' + '%%')
ORDER BY hzdept_r ASC) and cht.rvindicator = 'yes'



VIDEO GALLERY
  1. Soil Data Access video 1
    This video is an introduction to the Soil Data Access site
  2. Soil Data Access Basic Joins video 2
    This video begins the discussion on writing SQL and uses a basic sql. The discussion is on a single table query and a two table query
  3. Soil Data Access video 3
    This video discusses the inclusion of the Legend, Mapunit, Component, and Horizon tables and the columns and joins
  4. Soil Data Access video 4
    This video discusses the importance of understanding the difference between an INNER JOIN and an OUTER JOIN