soil data access (SDA) services

[emiliom]

Starting point tutorial from Dylan: http://ncss-tech.github.io/AQP/soilDB/SDA-tutorial.html

• Very convenient SQL query test page for SDA queries: https://sdmdataaccess.sc.egov.usda.gov/Query.aspx
• SDA web services help and listing
• The service I'm most likely going to use (based on initial reading plus tests) are either https://SDMDataAccess.sc.egov.usda.gov/Tabular/SDMTabularService.asmx or https://SDMDataAccess.sc.egov.usda.gov/Tabular/post.rest, probably the latter (more convenient to issue a POST request, rather than SOAP)

SQL query statements, tests so far

These queries are working, and are VERY close to what I think we want!!

But make sure to look over the section on "SSURGO vs. STATSGO" and filtering, in the tutorial. Note that I added a DISTINCT qualifier, to collapse duplicate rows.

SELECT DISTINCT
  taxclname, 	taxorder, taxsuborder, taxgrtgroup, taxsubgrp
FROM mapunit 
INNER JOIN component ON component.mukey = mapunit.mukey
WHERE mapunit.mukey IN (
SELECT * from SDA_Get_Mukey_from_intersection_with_WktWgs84('point(-121.77100 37.368402)')
)

The query returns the following XML (when requesting xml response; JSON is also available?). But note the empty row.

<NewDataSet>
  <Table>
    <taxclname />
    <taxorder />
    <taxsuborder />
    <taxgrtgroup />
    <taxsubgrp />
  </Table>
  <Table>
    <taxclname>Fine, smectitic, thermic Aridic Haploxererts</taxclname>
    <taxorder>Vertisols</taxorder>
    <taxsuborder>Xererts</taxsuborder>
    <taxgrtgroup>Haploxererts</taxgrtgroup>
    <taxsubgrp>Aridic Haploxererts</taxsubgrp>
  </Table>
</NewDataSet>

This works, and approximates what I'm looking for.

SELECT 
  mapunit.mukey, mapunit.muname,
  compname, taxclname, 
  taxorder, taxsuborder, taxgrtgroup, taxsubgrp
FROM legend
INNER JOIN mapunit ON mapunit.lkey = legend.lkey
INNER JOIN component ON component.mukey = mapunit.mukey
WHERE mapunit.mukey IN (
SELECT * from SDA_Get_Mukey_from_intersection_with_WktWgs84('point(-121.77100 37.368402)')
)

Two relevant examples from Dylan's tutorial page

I used these to learn and build up the query I'm interested in.

"Get basic map unit and component data for a single survey area, Yolo County (CA113). There is no need to exclude STATSGO records because we are specifying a SSURGO areasymbol in the WHERE clause."

SELECT component.mukey, cokey, comppct_r, compname, taxclname, 
taxorder, taxsuborder, taxgrtgroup, taxsubgrp
FROM legend
INNER JOIN mapunit ON mapunit.lkey = legend.lkey
INNER JOIN component ON component.mukey = mapunit.mukey
WHERE legend.areasymbol = 'CA113'

"Get the map unit key and name at a single, manually-defined point (-121.77100 37.368402). Spatial queries using SDA helper functions automatically exclude STATSGO records."

SELECT mukey, muname
FROM mapunit
WHERE mukey IN (
SELECT * from SDA_Get_Mukey_from_intersection_with_WktWgs84('point(-121.77100 37.368402)')
)

[emiliom]

More general notes, for reference.

• Copying the notes about Dylan's resources, from the BiGCZ workshop, topic USDA-NRCS Soil Survey Data: sources, usage notes, and analysis (Dylan)
  • Slides: http://ncss-tech.github.io/AQP/presentations/2017-CZO-UCR-data-sources-aqp/presentation.html
  • Info: http://ncss-tech.github.io/AQP/
  • Repos: https://github.com/ncss-tech

[emiliom]

@dylanbeaudette I'm shifting over our exchanges to here, as I said I would.

For now, I have one question (and sorry if I'm being lazy ...): for this query (which works and that I'll be using for our initial needs; it's shown on the first post of this issue):

SELECT DISTINCT
  taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp
FROM mapunit 
INNER JOIN component ON component.mukey = mapunit.mukey
WHERE mapunit.mukey IN (
SELECT * from SDA_Get_Mukey_from_intersection_with_WktWgs84('point(-121.77100 37.368402)')
)

Does the SSURGO vs STATSGO issue/mix come into play? If it does, are there any reasons I'd ever want to get STATSGO results rather than SSURGO (again, for this query?)? FYI, that particular query returns two records, with one of them being blank.

More broadly, I see many possibilities for providing richer soil information and granular links to your agency's web applications, stemming directly from the lat-lon query. But that'll be for a later time.

[dylanbeaudette]

A couple of things. First off, I would suggest a slight modification of your query, so that all components along with some additional columns are returned:

SELECT co.mukey, cokey, compname, comppct_r, compkind, majcompflag,
taxclname, taxorder, taxsuborder, taxgrtgroup, taxsubgrp
FROM mapunit as MU
INNER JOIN component AS co ON co.mukey = mu.mukey
WHERE mu.mukey IN (
SELECT * from SDA_Get_Mukey_from_intersection_with_WktWgs84('point(-121.77100 37.368402)')
);

Results:

    mukey    cokey compname comppct_r compkind majcompflag                                    taxclname  taxorder taxsuborder
1 1882921 14459361   Diablo        85   Series         Yes Fine, smectitic, thermic Aridic Haploxererts Vertisols     Xererts
2 1882921 14459362   Sehorn         3   Series         No                                          <NA>      <NA>        <NA>
3 1882921 14459363      Alo         4   Series         No                                          <NA>      <NA>        <NA>
4 1882921 14459364    Haire         1   Series         No                                          <NA>      <NA>        <NA>
5 1882921 14459365   Raynor         1   Series         No                                          <NA>      <NA>        <NA>
6 1882921 14459366 Altamont         1   Series         No                                          <NA>      <NA>        <NA>
7 1882921 14459367    Linne         1   Series         No                                          <NA>      <NA>        <NA>
8 1882921 14459368  Cropley         4   Series         No  Fine, smectitic, thermic Aridic Haploxererts Vertisols     Xererts
   taxgrtgroup           taxsubgrp
1 Haploxererts Aridic Haploxererts
2         <NA>                <NA>
3         <NA>                <NA>
4         <NA>                <NA>
5         <NA>                <NA>
6         <NA>                <NA>
7         <NA>                <NA>
8 Haploxererts Aridic Haploxererts

Note that for any coordinate submitted this way there may be multiple possible soil components. You can filter out the most likely possibilities using the majcompflag column, or select based on a component percentage threshold. Alternatively, the "most limiting" component may be of interest. This is a common approach when dealing with potential wetland or riparian components that only occupy a small percentage of the entire map unit.

SSURGO data are implicitly selected when using SDA_Get_Mukey_from_intersection_with_WktWgs84.

There is always a 1:1 relationship between point queries and records in the mapunit table. There will typically be a 1:many relationship between records in the mapunit and component tables.

[emiliom]

Thanks!! Very helpful explanations; plus that expanded query seems really handy for both making a more explicit choice (based on majcompflag) and exploring the inclusion of component/series information, which we weren't planning to do yet.

[dylanbeaudette]

Some more suggestions:

• use the POST interface whenever possible, it is much more efficient and will eventually replace the SOAP interface
• use the JSON interface with headers
• think carefully about the methods used to flatten the 1:many relationship

[dylanbeaudette]

One more comment: it is likely that any given map unit will contain components with different subgroup level taxa.

[emiliom]

@dylanbeaudette Thanks for the additional input. Follow ups:

use the POST interface whenever possible, it is much more efficient and will eventually replace the SOAP interface

I'm already using POST, mainly for simplicity; but glad to hear there's also a performance advantage.

use the JSON interface with headers

What do you mean? I'm currently requesting format=JSON+COLUMNNAME, but that's in the parameters (just like the query parameter), not the headers per se. Are you referring to something else?

think carefully about the methods used to flatten the 1:many relationship
One more comment: it is likely that any given map unit will contain components with different subgroup level taxa.

Very helpful, thanks. I'm trying to make progress without having to fully immerse myself in understanding every aspect of these data, yet. Some of this I used to know back when I worked with diverse soils data (to some extent) to parameterize hydrological and ecosystem models; it's coming back to me, to some extent.

[emiliom]

@dylanbeaudette a question: I found a location (lat 32.4263, lon -110.7612) for which SDA did not return any soil taxonomy designation. This is in Arizona or New Mexico, in the Catalina-Jemez CZO.

Does SSURGO not have full CONUS coverage?? Or are there areas that have not been categorized?

[dylanbeaudette]

Correct, SSURGO isn't 100% complete.

[emiliom]

Correct, SSURGO isn't 100% complete.

Ah. I thought these soil maps were wall to wall, at least for CONUS. Darn.