Setup ODBC Connection to Local NASIS

Introduction

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

0. If you have the NASIS application open, please close it before creating ODBC data source connections.

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:

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

• Select the “ODBC Driver 13 for SQL Server” driver. If this driver is not available, use “SQL Server Native Client 11.0”

• Name the data source nasis_local. A data source description is optional.

• The NASIS SQL server name is in the format XXXXXX\NASISSQLEXPRESS

  • XXXXXX is your “computer name”

  • NASISSQLEXPRESS is the local NASIS SQL Express server.

• Your computer name is often on the asset tag sticker on the outside of your computer.

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).

• Your computer name will look something like: ARCCASON5000002 – where state, office and number will vary from computer to computer. Be very careful when typing it in! This is a common source of error in setting up ODBC connection.

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

• Server: ARCCASON5000002\NASISSQLEXPRESS

When finished click “Next”.

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

• Choose the THIRD option: “With SQL Server authentication using a login ID and password entered by the user”

• Enter Login ID and password. Don’t try and re-type these, just copy/paste

  • Login ID: NASISSQLRO
  • Windows 7 password: nasisRe@d0n1y
  • Windows 10 password: nasisRe@d0n1y365

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

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

6. 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.