Setup ODBC Connection to Local NASIS

2021-03-22

Introduction

This document describes how to setup an ODBC link between R and the local NASIS database.

Open the ODBC Data Source Administrator

  1. Go to the Start Menu and search for ‘ODBC’. Select “ODBC Data Sources (64-bit)” if you are using Windows 10. It should look like this:

  1. With the “User DSN” tab selected, create a new data source by clicking on the “Add…” button.

On Windows 10, you can view your computer name by opening the Control Panel and selecting “System” OR by searching “This PC” in the Start Menu, right-clicking it, and selecting “Properties” . You need the value found under “Computer name:” (highlighted below).

When you are done, you will have something similar to what was as entered on the form above:

When finished click “Next”.

  1. Under “Create a New Data Source to SQL Server”

  1. On the next screen, select “Nasis-local” as the default database. The connection you will be making is READONLY.

  1. Click “Next” and then “Finish”. If you would like, you can test the connection to make sure it works.

  2. Close the ODBC control panel by clicking ‘OK’

If you are doing this as part of a pre-course assignment, you do not need to do the following optional demonstration. However, it is a good way to verify everything is working correctly. You can return back to the assignment, but contact your mentor if you have any trouble.


Optional: Test Connection with soilDB and set up USDA-NRCS / NCSS soil packages

Install soil-themed packages and their dependencies using the commands below if you have not done so already.

# stable version from CRAN + dependencies
install.packages("aqp", dependencies = TRUE)
install.packages("soilDB", dependencies = TRUE)
install.packages("sharpshootR", dependencies = TRUE)

# database packages (replacement for RODBC)
install.packages(c("DBI","odbc","RSQLite"), dependencies = TRUE)

# latest version off GitHub
remotes::install_github("ncss-tech/aqp", dependencies = FALSE, upgrade = FALSE, build = FALSE)
remotes::install_github("ncss-tech/soilDB", dependencies = FALSE, upgrade = FALSE, build = FALSE)
remotes::install_github("ncss-tech/sharpshootR", dependencies = FALSE, upgrade = FALSE, build = FALSE)

After installing the above packages, load some pedons into your local database and selected set. Use a NASIS query such as POINT - Pedon/Site/NCSSlabdata by upedonid and taxonname in NSSC Pangaea.

Then, try loading them into your R session and making a “sketch” of the profiles.

# load required libraries
library(aqp)
library(soilDB)

# fetch all pedons from the selected set in local NASIS database
x <- fetchNASIS(from = 'pedons')

# make sketches of the first 10 pedons
par(mar = c(0,0,0,0))
plot(x[1:10, ], name = 'hzname', label = 'taxonname')

If you want to do more with SoilProfileCollections, profile sketches, and soil data wrangling check out some of the tutorials on the AQP website.

Details on loading NASIS data into R can be found in this tutorial.