Submit a query to the Soil Data Access (SDA) REST/JSON web-service and return the results as a data.frame. There is a 100,000 record limit and 32Mb JSON serializer limit, per query. Queries should contain a WHERE statement or JOIN condition to limit the number of rows affected / returned. Consider wrapping calls to SDA_query in a function that can iterate over logical chunks (e.g. areasymbol, mukey, cokey, etc.). The function makeChunks can help with such iteration.

SDA_query(q)

Arguments

q

A valid T-SQL query surrounded by double quotes

Value

a data.frame result (NULL if empty, try-error on error)

Details

The SDA website can be found at https://sdmdataaccess.nrcs.usda.gov and query examples can be found at https://sdmdataaccess.nrcs.usda.gov/QueryHelp.aspx. A library of query examples can be found at https://nasis.sc.egov.usda.gov/NasisReportsWebSite/limsreport.aspx?report_name=SDA-SQL_Library_Home.

SSURGO (detailed soil survey) and STATSGO (generalized soil survey) data are stored together within SDA. This means that queries that don't specify an area symbol may result in a mixture of SSURGO and STATSGO records. See the examples below and the SDA Tutorial for details.

Note

This function requires the httr, jsonlite, and xml2 packages

See also

Author

D.E. Beaudette

Examples

# \donttest{
  ## get SSURGO export date for all soil survey areas in California
  # there is no need to filter STATSGO
  # because we are filtering on SSURGO area symbols
  q <- "SELECT areasymbol, saverest FROM sacatalog WHERE areasymbol LIKE 'CA%';"
  x <- SDA_query(q)
#> single result set, returning a data.frame
  head(x)
#>   areasymbol              saverest
#> 1      CA011 8/30/2022 10:52:02 PM
#> 2      CA013  8/31/2022 3:46:49 PM
#> 3      CA021   9/1/2022 4:04:38 AM
#> 4      CA031 8/31/2022 10:00:07 PM
#> 5      CA033   9/1/2022 4:06:38 AM
#> 6      CA041  9/13/2022 9:48:32 PM


  ## get SSURGO component data associated with the
  ## Amador series / major component only
  # this query must explicitly filter out STATSGO data
  q <- "SELECT cokey, compname, comppct_r FROM legend
    INNER JOIN mapunit mu ON mu.lkey = legend.lkey
    INNER JOIN component co ON mu.mukey = co.mukey
    WHERE legend.areasymbol != 'US' AND compname = 'Amador';"

  res <- SDA_query(q)
#> single result set, returning a data.frame
  str(res)
#> 'data.frame':	54 obs. of  3 variables:
#>  $ cokey    : int  22732753 23459437 22485048 22521674 22523040 23467336 23471183 22557939 22732652 22733290 ...
#>  $ compname : chr  "Amador" "Amador" "Amador" "Amador" ...
#>  $ comppct_r: int  25 10 3 3 76 10 85 5 7 45 ...
#>  - attr(*, "SDA_id")= chr "Table"

  ## get component-level data for a specific soil survey area (Yolo county, CA)
  # there is no need to filter STATSGO because the query contains
  # an implicit selection of SSURGO data by areasymbol
  q <- "SELECT
    component.mukey, cokey, comppct_r, compname, taxclname,
    taxorder, taxsuborder, taxgrtgroup, taxsubgrp
    FROM legend
    INNER JOIN mapunit ON mapunit.lkey = legend.lkey
    LEFT OUTER JOIN component ON component.mukey = mapunit.mukey
    WHERE legend.areasymbol = 'CA113' ;"

  res <- SDA_query(q)
#> single result set, returning a data.frame
  str(res)
#> 'data.frame':	608 obs. of  9 variables:
#>  $ mukey      : int  459154 459204 459205 459208 459208 459208 459208 459209 459209 459209 ...
#>  $ cokey      : int  22734238 22734512 22734700 22734523 22734524 22734525 22734526 22734243 22734244 22734245 ...
#>  $ comppct_r  : int  100 100 100 5 5 85 5 2 5 5 ...
#>  $ compname   : chr  "Water" "Gravel pits" "Water" "Sehorn" ...
#>  $ taxclname  : chr  NA NA NA NA ...
#>  $ taxorder   : chr  NA NA NA NA ...
#>  $ taxsuborder: chr  NA NA NA NA ...
#>  $ taxgrtgroup: chr  NA NA NA NA ...
#>  $ taxsubgrp  : chr  NA NA NA NA ...
#>  - attr(*, "SDA_id")= chr "Table"

  ## get tabular data based on result from spatial query
  # there is no need to filter STATSGO because
  # SDA_Get_Mukey_from_intersection_with_WktWgs84() implies SSURGO
  p <- wk::as_wkt(wk::rct(-120.9, 37.7, -120.8, 37.8))
  q <- paste0("SELECT mukey, cokey, compname, comppct_r FROM component
      WHERE mukey IN (SELECT DISTINCT mukey FROM
      SDA_Get_Mukey_from_intersection_with_WktWgs84('", p,
       "')) ORDER BY mukey, cokey, comppct_r DESC")

   x <- SDA_query(q)
#> single result set, returning a data.frame
   str(x)
#> 'data.frame':	337 obs. of  4 variables:
#>  $ mukey    : int  462527 462527 462527 462554 462554 462554 462555 462555 462555 462558 ...
#>  $ cokey    : int  23471173 23471174 23471175 23471263 23471264 23471265 23471266 23471267 23471268 23471272 ...
#>  $ compname : chr  "Madera" "Alamo" "San Joaquin" "Corning" ...
#>  $ comppct_r: int  10 85 5 85 5 10 85 10 5 85 ...
#>  - attr(*, "SDA_id")= chr "Table"
# }