some NASIS user site IDs interpreted as scientific notation

[dylanbeaudette]

Try this one 056E916010:

library(soilDB)
x <- get_site_data_from_NASIS_db()

results:

'data.frame':	1 obs. of  38 variables:
 $ siteiid           : int 1353895
 $ peiid             : int 1245208
 $ site_id           : num Inf
 $ pedon_id          : num Inf
 $ obs_date          : POSIXct, format: "1991-06-27"
[...]

Notice site_id and pedon_id are interpreted as numbers in scientific notation that overflow limits of R's datatype.

I can't find any arguments to RODBC::sqlQuery() that might solve this problem.

[dylanbeaudette]

Worth trying: odbc package.

[brownag]

Note to self: investigate this in #149

[brownag]

With {DBI}+{odbc} and either ODBC Driver version 11 or 13:

# selected set contains only 056E916010

library(soilDB)
x <- get_site_data_from_NASIS_db()
x
#>   siteiid   peiid    site_id   pedon_id   obs_date utmzone utmeasting
#> 1 1353895 1245208 056E916010 056E916010 1991-06-27      NA         NA
#>   utmnorthing  x  y horizdatnm x_std y_std gpspositionalerror
#> 1          NA NA NA       <NA>    NA    NA                 NA
#>               describer          pedonpurpose pedontype pedlabsampnum
#> 1 C. Thomson & S. Jamsa soil survey inventory      <NA>          <NA>
#>   labdatadescflag tsectstopnum tsectinterval utransectid tsectkind tsectselmeth
#> 1           FALSE           NA            NA        <NA>      <NA>         <NA>
#>   elev_field slope_field aspect_field plantassocnm earthcovkind1 earthcovkind2
#> 1       1844          50          260         <NA>          <NA>          <NA>
#>   erocl bedrckdepth bedrckkind bedrckhardness hillslopeprof geomslopeseg
#> 1  <NA>          NA       <NA>           <NA>     backslope middle third
#>   shapeacross shapedown slopecomplex drainagecl flodfreqcl floddurcl
#> 1      linear    linear         <NA>       well       <NA>      <NA>
#>   flodmonthbeg pondfreqcl ponddurcl pondmonthbeg geomposhill geomposmntn
#> 1         <NA>       <NA>      <NA>         <NA>        <NA>        <NA>
#>   geompostrce geomposflats swaterdepth     slope_shape
#> 1        <NA>         <NA>          NA linear / linear

[brownag]

Can confirm that the conversion to {odbc} in #149 will close and resolve this issue.

Even with newer ODBC driver, {RODBC} converts this pedon to scientific notation:

# selected set contains only 056E916010

library(soilDB)
#> Warning: package 'soilDB' was built under R version 4.0.3
x <- get_site_data_from_NASIS_db()
#> Loading required namespace: RODBC
x
#>   siteiid   peiid site_id pedon_id   obs_date utmzone utmeasting utmnorthing  x
#> 1 1353895 1245208     Inf      Inf 1991-06-27      NA         NA          NA NA
#>    y horizdatnm x_std y_std gpspositionalerror             describer
#> 1 NA       <NA>    NA    NA                 NA C. Thomson & S. Jamsa
#>            pedonpurpose pedontype pedlabsampnum labdatadescflag tsectstopnum
#> 1 soil survey inventory      <NA>            NA               0           NA
#>   tsectinterval utransectid tsectkind tsectselmeth elev_field slope_field
#> 1            NA          NA      <NA>         <NA>       1844          50
#>   aspect_field plantassocnm earthcovkind1 earthcovkind2 erocl bedrckdepth
#> 1          260           NA          <NA>          <NA>  <NA>          NA
#>   bedrckkind bedrckhardness hillslopeprof geomslopeseg shapeacross shapedown
#> 1       <NA>           <NA>     backslope middle third      linear    linear
#>   slopecomplex drainagecl flodfreqcl floddurcl flodmonthbeg pondfreqcl
#> 1         <NA>       well       <NA>      <NA>         <NA>       <NA>
#>   ponddurcl pondmonthbeg geomposhill geomposmntn geomposflats swaterdepth
#> 1      <NA>         <NA>        <NA>        <NA>         <NA>          NA
#>       slope_shape
#> 1 linear / linear