ameriflux-surgo-grid-extract
Extract dominant-component soil profile variables for EcoSIM grid or template inputs from gSSURGO, with FAO HWSD v2.0 fallback when gSSURGO data are unavailable or incomplete. Use when deriving soil d
SKILL.md.agents/skills/ameriflux-surgo-grid-extract/SKILL.md
GitHubAmeriFlux gSSURGO/FAO Grid Extractor
Use When
- You need soil profile values for an AmeriFlux or EcoSIM site from a local gSSURGO geodatabase.
- You need FAO HWSD v2.0 fallback values when gSSURGO is outside coverage, unavailable, or has missing layer values.
- You need EcoSIM template variables such as
CDPTH,BKDSI,FC,WP,SCNV,SCNH,CSAND,CSILT,ROCK,PH,CEC, orCORGC. - You need depth-weighted interpolation from source horizons or HWSD2 layers to EcoSIM soil layers.
- You need to inspect or edit an existing EcoSIM grid NetCDF as an Excel workbook, then rebuild the NetCDF after edits.
Constraints
- NEVER use it extract climate data.
- Do not require atmospheric gas scalar variables in EcoSIM grid files. Treat
OXYEG(or typoOXYGE),Z2GEG,CO2EIG,CH4EG,Z2OEG, andZNH3EGas optional legacy/template variables, not derivation gaps.
Workflow
- Locate
gSSURGO_CONUS.gdbunderdata/first, then elsewhere in the repo if needed. - Identify the map unit (
MUKEY) by spatial query onMUPOLYGON. - Select the dominant component by
comppct_r. - Retrieve horizons and rock fragments from
chorizonandchfrags. - Convert units and interpolate vertically to the target template layers.
- If gSSURGO fails, generate the profile from FAO HWSD2 under
data/FAO_HWSD2. - If gSSURGO succeeds but some layer values are invalid/fill values, replace only those missing values from FAO HWSD2.
- Write the derived soil profile JSON under
result/<SITE_ID>/.
Overview
This document summarizes which variables in the provided template can be
extracted from the CONUS gSSURGO database (directory gSSURGO_CONUS.gdb) and
from FAO HWSD v2.0, along with methods for extraction and vertical
interpolation.
Extractable Variables from gSSURGO
The following template variables can be derived from standard gSSURGO tables:
Template Variable gSSURGO Source Notes
CDPTH chorizon.hzdepb_r / 100 Depth to bottom of horizon (m)
BKDSI chorizon.dbovendry_r Bulk density (g/cm3)
FC (wthirdbar_r / 100) / Field capacity, (1 - ROCK) volumetric per fine-earth volume
WP (wfifteenbar_r / 100) / Wilting point, (1 - ROCK) same basis
SCNV, SCNH ksat_r * 3.6 Convert um/s to mm/h
CSAND sandtotal_r * 10 kg/Mg
CSILT silttotal_r * 10 kg/Mg
ROCK sum(chfrags.fragvol_r) / 100 Volume fraction
PH ph1to1h2o_r Soil pH
CEC cec7_r Cation exchange capacity
CORGC om_r * 0.58 * 10 Soil organic carbon
Fallback Variables from FAO HWSD v2.0
The fallback reads HWSD2_RASTER/HWSD2.bil to identify HWSD2_SMU_ID, then
uses the HWSD2_LAYERS attribute table from HWSD2.mdb or an exported
HWSD2_LAYERS.csv/SQLite table. Use the dominant soil sequence (SEQUENCE=1)
unless a different sequence is explicitly requested.
Template Variable FAO HWSD2 Source Notes
CDPTH template CDPTH Target EcoSIM depths remain template-driven
BKDSI BULK, fallback REF_BULK g/cm3 equals Mg m-3
FC, WP SAND/SILT/CLAY + AWC Texture-class defaults, adjusted by plausible AWC when available
SCNV, SCNH SAND/SILT/CLAY Texture-class Ksat estimate
CSAND SAND * 10 kg/Mg
CSILT SILT * 10 kg/Mg
ROCK COARSE / 100 Volume fraction
PH PH_WATER Soil pH
CEC CEC_SOIL cmolc/kg
CORGC ORG_CARBON g/kg equals kg C/Mg soil
FAO HWSD2 negative miscellaneous/no-data codes (-1 through -9) must be treated
as invalid. The fallback should be recorded in the output JSON under
sources_by_variable and fallbacks.
Non-Extractable Variables
The following variables are not directly available from gSSURGO and require external datasets or modeling assumptions:
- Climate variables (e.g., ATCAG)
- Topography (e.g., ASPX, slope)
- Hydrologic boundary conditions
- Nutrient pools (NH4, NO3, PO4)
- Organic nitrogen/phosphorus pools
- Exchange coefficients (GKC*) are not measured by gSSURGO. If paired
exchange-solution chemistry is unavailable, invoke the
gapon-coefficient-estimatorecosystem-template fallback, match the closest ecosystem before climate and management refinements, and remap the selected values to the grid's layer-bottom depths. - Water table dynamics
For template-based GKC* values, keep the selector's provenance JSON with the
EcoSIM grid and include this reminder in the report or input metadata:
"Gapon coefficients are initialized from the closest ecosystem template, not
derived from site-specific paired exchange and solution chemistry. They are
starting values and are subject to calibration or tuning when needed."
The atmospheric gas scalar variables OXYEG/OXYGE, Z2GEG, CO2EIG,
CH4EG, Z2OEG, and ZNH3EG are not part of this soil-grid derivation
contract. If they are absent from a generated grid NetCDF, do not flag the file
as incomplete for this skill.
Water Retention Basis
gSSURGO wthirdbar_r and wfifteenbar_r are volumetric percentages of the
whole soil (gSSURGO mdstattabcols), not gravimetric, so they are never
multiplied by bulk density. Across 279,000 CONUS horizons, (wthirdbar_r - wfifteenbar_r)/100 correlates with awc_r at r = 0.83, against r = 0.14 after
multiplying by dbthirdbar_r. EcoSIM keeps soil water in the micropore matrix,
whose volume excludes rock (FracSoiAsMicP = (1-ROCK)(1-FHOL), readimod.F90,
Hour1Mod.F90), and compares FC with fine-earth porosity 1 - BKDSI/2.65
(SoilHydroParaMod.F90), so whole-soil values are divided by 1 - ROCK. About
0.08% of gSSURGO horizons report 1/3-bar water at or below 15-bar water; the
grid tool sets FC and WP to -1 for such layers so EcoSIM estimates them.
Vertical Interpolation
- Use overlap-weighted averaging for most variables.
- For soil organic matter (
CORGC), apply logarithmic interpolation. - Optionally extend deepest horizon or FAO layer downward with
--extend-last.
Script
A Python script (extract_gssurgo_profile.py) automates spatial lookup, horizon
extraction, variable conversion, vertical interpolation, and FAO HWSD2 fallback.
The companion bridge script
(scripts/grid_netcdf_excel_bridge.py) converts an EcoSIM grid NetCDF to an
editable .xlsx workbook and back. Use it for file-wide parameter inspection
or manual edits to grid parameters.
Example Usage
python .agents/skills/ameriflux-surgo-grid-extract/extract_gssurgo_profile.py \
--gdb /path/to/gSSURGO_CONUS.gdb \
--lon -121.85 \
--lat 39.0 \
--template template.nc \
--out result/<SITE_ID>/profile_<SITE_ID>.json \
--extend-last
The FAO fallback is enabled by default and looks under data/FAO_HWSD2. Use
--fao-hwsd2-dir /path/to/FAO_HWSD2 to override the location, or
--no-fao-fallback for a strict gSSURGO-only run.
Grid NetCDF Excel Bridge
Use the bundled bridge for grid NetCDF, Excel, and NetCDF round trips:
.venv-cmip6/bin/python .agents/skills/ameriflux-surgo-grid-extract/scripts/grid_netcdf_excel_bridge.py \
nc-to-xlsx input_grid.nc editable_grid.xlsx
.venv-cmip6/bin/python .agents/skills/ameriflux-surgo-grid-extract/scripts/grid_netcdf_excel_bridge.py \
xlsx-to-nc editable_grid.xlsx edited_grid.nc --format NETCDF3_CLASSIC
Workbook sheets:
dimensions: NetCDF dimension names, lengths, and unlimited flags.global_attrs: global NetCDF attributes.variables: variable names, dtypes, dimensions, and fill values.variable_attrs: per-variable metadata such aslong_name,units, andflags.values: flattened variable values with zero-based index tuples.
After editing, run a scratch round trip and compare dimensions, variables, attributes, and data values before using the rebuilt grid file in EcoSIM. Pay special attention to units and mass-balance-sensitive pools; the bridge edits values mechanically and does not make non-derivable parameters scientifically valid.
Notes
- Bulk density and water retention conversions assume standard pedotransfer interpretations.
- Ensure gSSURGO geodatabase includes required tables:
MUPOLYGON,component,chorizon,chfrags. - FAO HWSD2 provides seven layers (
D1-D7, 0-200 cm). When--extend-lastis used, the deepest FAO layer is extended downward to fill deeper EcoSIM template layers; this should be treated as a gap-fill assumption, not direct observation. - Reading
HWSD2.mdbrequires GDAL/pyogrio with MDB support. If that driver is unavailable, export the Access table asHWSD2_LAYERS.csvin the sameFAO_HWSD2directory.