SDA_query: Soil Data Access Query

Description

Submit a query to the Soil Data Acccess (SDA) website in SQL, get the results as a dataframe.

Usage

SDA_query(q)
makeChunks(ids, size=100)
format_SQL_in_statement(x)

Arguments

a valid T-SQL query surrounded by double quotes

ids

vector of IDs for chunking, contents aren't used just length

size

target chunk size

character vector to be packed into an SQL `IN` statement

Value

A dataframe containing the results. NULL is retutned when queries result in 0 matches rows.

Details

The SDA website can be found at http://sdmdataaccess.nrcs.usda.gov and query examples can be found at http://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.

Examples

Run this code

# NOT RUN {
if(requireNamespace("curl") &
    curl::has_internet()) {
    
  ## get SSURGO export date for all soil survey areas in California
  # there is no need to filter STATSGO 
  # because we are filtering on SSURGO areasymbols
  q <- "SELECT areasymbol, saverest FROM sacatalog WHERE areasymbol LIKE 'CA%';"
  x <- SDA_query(q)
  head(x)


  ## 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\n
    INNER JOIN mapunit mu ON mu.lkey = legend.lkey\n
    INNER JOIN component co ON mu.mukey = co.mukey\n
    WHERE legend.areasymbol != 'US' AND compname = 'Amador';"
  
  res <- SDA_query(q)
  str(res)


  ## 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 \n
    component.mukey, cokey, comppct_r, compname, taxclname, \n
    taxorder, taxsuborder, taxgrtgroup, taxsubgrp \n
    FROM legend \n
    INNER JOIN mapunit ON mapunit.lkey = legend.lkey \n
    LEFT OUTER JOIN component ON component.mukey = mapunit.mukey \n
    WHERE legend.areasymbol = 'CA113' ;"
  
  res <- SDA_query(q)
  str(res)


  ## 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
  #
  # requires raster and rgeos packages because raster is suggested
  # and rgeos is additional
  if(require(raster) & require(rgeos)) {
    # text -> bbox -> WKT
    # xmin, xmax, ymin, ymax
    b <- c(-120.9, -120.8, 37.7, 37.8)
    p <- writeWKT(as(extent(b), 'SpatialPolygons'))
    q <- paste0("SELECT mukey, cokey, compname, comppct_r FROM component \n
      WHERE mukey IN (SELECT DISTINCT mukey FROM\n
      SDA_Get_Mukey_from_intersection_with_WktWgs84('"
      , p, "')) ORDER BY mukey, cokey, comppct_r DESC")
    
    x <- SDA_query(q)
    str(x)
  }
}
# }