Expert SQL Track — SDA Training

01

Temp Tables — Multi-Step Queries

The pattern used in 90% of production SSURGO scripts

Expert ▼

Temp tables break complex SSURGO queries into stages: first gather the map units (#map), then the components (#comp), then the horizons (#horizon) — each step building on the last. CTEs achieve the same result and work in SDA.

02

Soil Organic Carbon — The Full Formula

The equation behind SSURGO's carbon accounting

Expert ▼

SOC Formula: SOC (g/m²) = ((Hz_cm × (OM/1.724 × Db)) / 100) × ((100 − frag%) / 100) × compPct × 100

OM / 1.724 — converts organic matter % to organic carbon % (Van Bemmelen factor)
× Db — bulk density (g/cc) converts concentration to mass per volume
× Hz_cm — horizon thickness converts to depth-weighted stock
× (1 − frag/100) — rock fragment correction (soil only, not rocks)
× comppct_r × 100 — component weighting for map unit proportional stock

03

Interpretations — NCCPI & Fragile Soils

The cointerp table: how SSURGO rates soil suitability

Expert ▼

The cointerp table stores interpretation ratings for each component. Key fields: mrulename (the rule, e.g., "Fragile Soil Index"), ruledepth (0=top-level, 1=sub-rule), interphrc (rating class), interphr (numeric 0–1).

ruledepth: Use ruledepth = 0 for the overall top-level rating. Use ruledepth = 1 for crop-specific sub-rules (e.g., NCCPI for corn vs. soybeans separately).

04

Dominant Condition Method

Aggregating interpretations to the map unit level

Expert ▼

The dominant condition method assigns a single rating class to a map unit based on which class covers the most component percent. This is the standard SSURGO aggregation used in the Regen Ag Risk Map and fragile soil mapping.

Pattern: GROUP BY mukey + interphrc, SUM comppct_r, pick the highest sum with ROW_NUMBER().

05

Spatial Queries — Area of Interest

Constraining queries to a geographic polygon

Expert ▼

The SDA spatial endpoint accepts a WKT geometry to return only map units that intersect an area of interest. This enables field-level, county-level, and custom boundary queries without pre-filtering by areasymbol.

Spatial endpoint: Full AOI geometry queries use the SDA Spatial endpoint and Tabular/post services together. The tabular example below shows the standard approach using areasymbol as a proxy for geographic filtering.

For full spatial AOI workflows, see the Advanced Spatial Data Access PDF — the definitive guide to WKT geometry queries.

06

KSSL Lab SOC — The 4-Method Cascade

How DSPHub estimates soil organic carbon when measurements vary

Expert ▼

The Kellogg Soil Survey Laboratory (KSSL) doesn't always have the same measurements for every pedon. DSPHub solves this with a prioritized cascade: use the best available method, fall through to the next when data is missing.

4-Method Priority:

estimated_organic_carbon — Direct KSSL estimate (best)
total_carbon_ncs − (caco3_lt_2_mm × 0.12) — Subtract inorganic CaCO₃
total_carbon_ncs — Use total C when no CaCO₃ data
0.25 + organic_carbon_walkley_black × 0.86 — Walkley-Black correction

This cascade appears in DSPHub's Top Combined Estimated Organic Carbon.sql and is the same logic SHAPE uses to derive soil organic carbon from KSSL data.

-- KSSL Lab SOC -- 4-Method Cascade (Surface Horizon)
-- Change area_code = 'KS' to any two-letter state abbreviation.
-- Source: DSPHub / Top Combined Estimated Organic Carbon.sql
SELECT TOP 50
  lp.pedlabsampnum,
  CASE WHEN lcnn.corr_name IS NULL
       THEN lcnn.samp_name
       ELSE upper(substring(lcnn.corr_name,1,1))
          + lower(substring(lcnn.corr_name,2,120))
  END AS soil_name,
  la_st.area_name  AS state_name,
  la_ct.area_name  AS county_name,
  lcnn.latitude_decimal_degrees  AS lat,
  lcnn.longitude_decimal_degrees AS lon,
  -- 4-method SOC estimation cascade
  CASE
    WHEN lcp.estimated_organic_carbon IS NOT NULL
         THEN lcp.estimated_organic_carbon
    WHEN lcp.caco3_lt_2_mm IS NOT NULL
         THEN lcp.total_carbon_ncs - (lcp.caco3_lt_2_mm * 0.12)
    WHEN lcp.total_carbon_ncs IS NOT NULL
         THEN lcp.total_carbon_ncs
    WHEN lcp.organic_carbon_walkley_black IS NOT NULL
         THEN 0.25 + lcp.organic_carbon_walkley_black * 0.86
  END AS calc_soc_pct,
  CASE
    WHEN lcp.estimated_organic_carbon IS NOT NULL
         THEN 'Direct KSSL estimate'
    WHEN lcp.caco3_lt_2_mm IS NOT NULL
         THEN 'TotalC - CaCO3 x 0.12'
    WHEN lcp.total_carbon_ncs IS NOT NULL
         THEN 'Total C (no CaCO3 data)'
    WHEN lcp.organic_carbon_walkley_black IS NOT NULL
         THEN 'Walkley-Black: 0.25 + WB x 0.86'
  END AS method_used
FROM lab_combine_nasis_ncss lcnn
INNER JOIN lab_pedon lp
       ON lp.pedon_key = lcnn.pedon_key
INNER JOIN lab_layer ll
       ON ll.pedon_key = lcnn.pedon_key
      AND ll.hzn_top = (
        SELECT MIN(ll2.hzn_top)
        FROM lab_layer ll2
        WHERE ll2.pedon_key = lcnn.pedon_key
          AND (ll2.hzn_desgn IS NULL OR ll2.hzn_desgn NOT LIKE 'O%')
      )
INNER JOIN lab_chemical_properties lcp
       ON lcp.labsampnum = ll.labsampnum
INNER JOIN lab_area la_st
       ON la_st.area_key = lcnn.state_key
      AND la_st.area_type = 'state_admin_div'
INNER JOIN lab_area la_ct
       ON la_ct.area_key = lcnn.county_key
WHERE la_st.area_code = 'KS'
  AND (
    lcp.estimated_organic_carbon IS NOT NULL OR
    lcp.total_carbon_ncs IS NOT NULL OR
    lcp.organic_carbon_walkley_black IS NOT NULL
  )
ORDER BY lp.pedlabsampnum

Full DSPHub script with all KSSL characterization fields (texture, bulk density, CaCO₃, Walkley-Black, clay fractions): DSP_KSSL_SOC.sql ↗

07

SHAPE Scoring Groups — T1–T5 & S1–S5

Classifying soils so the right SHAPE curve applies

Expert ▼

SHAPE doesn't use one universal curve. Before scoring, every soil is classified into a texture group (T1–T5) based on surface texture and a suborder group (S1–S5) based on taxonomic suborder. These two axes select the right benchmark for that soil type.

Texture Groups: T1=Sandy · T2=Sandy Loam · T3=Silty · T4=Clay Loam · T5=Heavy Clay (>60%)
Suborder Groups: S1=Organic/Wet · S2=Aquic/Volcanic · S3=Mollisols/Spodosols · S4=Alfisols/Vertisols · S5=Arid/Erosional

Source: DSPHub / DSP_Point_SDA_intersect.sql ↗ — the full version uses spatial geometry to find the map unit at a point, then derives T and S groups.

-- SHAPE Texture Groups (T1-T5) and Soil Suborder Groups (S1-S5)
-- Source: DSPHub / DSP_Point_SDA_intersect.sql (jneme910/DSPHub)
-- Change areasymbol to any survey area.
SELECT TOP 30
  l.areasymbol,
  mu.musym,
  mu.muname,
  c.compname,
  c.comppct_r,
  c.taxsuborder,
  tg.texture        AS surface_texture,
  ch.claytotal_r    AS clay_pct,
  -- SHAPE Texture Group (T1-T5)
  CASE
    WHEN tg.texture IN ('S','LS')                           THEN 'T1 Sandy'
    WHEN tg.texture = 'SL' AND ch.claytotal_r <  8         THEN 'T1 Sandy'
    WHEN tg.texture = 'SL' AND ch.claytotal_r >= 8         THEN 'T2 Sandy Loam'
    WHEN tg.texture IN ('SCL','L')                          THEN 'T2 Sandy Loam'
    WHEN tg.texture IN ('SIL','SI')                         THEN 'T3 Silty'
    WHEN tg.texture IN ('SC','CL','SICL','SIC')             THEN 'T4 Clay Loam'
    WHEN tg.texture = 'C' AND ch.claytotal_r <  60         THEN 'T4 Clay Loam'
    WHEN tg.texture = 'C' AND ch.claytotal_r >= 60         THEN 'T5 Heavy Clay'
    ELSE 'Unclassified'
  END AS shape_texture_group,
  -- SHAPE Soil Suborder Group (S1-S5)
  CASE
    WHEN c.taxsuborder IN ('Fribists','Folists','Hemists','Histels','Saprists','Wassists')
         THEN 'S1 Organic'
    WHEN c.taxsuborder IN ('Aquands','Aquents','Aquepts','Aquods','Aquoxs','Cryods',
         'Humods','Orthels','Peroxs','Torrands','Tropepts','Turbels','Udands',
         'Udoxs','Ustands')
         THEN 'S2 Aquic/Volcanic'
    WHEN c.taxsuborder IN ('Albolls','Andepts','Aquolls','Aquults','Cryands','Cryepts',
         'Cryolls','Gelepts','Gelolls','Humults','Rendolls','Umbrepts','Ustoxs',
         'Vitrands','Wassents','Xerands')
         THEN 'S3 Mollisols/Spodosols'
    WHEN c.taxsuborder IN ('Aqualfs','Aquerts','Boralfs','Borolls','Cryalfs','Ochrepts',
         'Orthods','Orthoxs','Udalfs','Udepts','Uderts','Udolls','Usterts','Ustolls',
         'Xeralfs','Xerepts','Xerolls','Xerults')
         THEN 'S4 Alfisols/Vertisols'
    WHEN c.taxsuborder IN ('Arents','Argids','Calcids','Cambids','Cryerts','Cryids',
         'Durids','Fluvents','Gypsids','Orthents','Orthids','Psamments','Salids',
         'Torrerts','Torroxs','Udults','Ustalfs','Ustepts','Ustults')
         THEN 'S5 Arid/Erosional'
    ELSE 'Unclassified'
  END AS shape_suborder_group
FROM legend l
INNER JOIN mapunit   mu ON mu.lkey  = l.lkey
INNER JOIN component  c ON c.mukey  = mu.mukey AND c.majcompflag = 'Yes'
INNER JOIN chorizon  ch ON ch.cokey = c.cokey
INNER JOIN chtexturegrp tg ON tg.chkey = ch.chkey AND tg.rvindicator = 'Yes'
WHERE l.areasymbol = 'WI025'
  -- First mineral surface horizon: skip organic/duff layers (SPM, HPM, MPM)
  AND ch.hzdept_r = (
    SELECT MIN(ch2.hzdept_r)
    FROM chorizon ch2
    INNER JOIN chtexturegrp tg2 ON tg2.chkey = ch2.chkey
    WHERE ch2.cokey = c.cokey
      AND tg2.rvindicator = 'Yes'
      AND tg2.texture NOT IN ('SPM','HPM','MPM')
  )
ORDER BY mu.musym, c.comppct_r DESC

Expert SQLTrack

Temp Tables — Multi-Step Queries

Soil Organic Carbon — The Full Formula

Interpretations — NCCPI & Fragile Soils

Dominant Condition Method

Spatial Queries — Area of Interest

KSSL Lab SOC — The 4-Method Cascade

SHAPE Scoring Groups — T1–T5 & S1–S5

Take It to Code

Expert SQL
Track