Introduction

This document describes how to setup an ODBC link betweenR 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 sourcenasis_local. A data source description is optional.

• The NASIS SQL server name is in the formatXXXXXX\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 theControl Panel and selecting “System”OR by searching “This PC” in theStart 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 isREADONLY.

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 withsoilDB 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 + dependenciesinstall.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 GitHubremotes::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 asPOINT - Pedon/Site/NCSSlabdata by upedonid and taxonname inNSSC Pangaea.

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

# load required librarieslibrary(aqp)library(soilDB)# fetch all pedons from the selected set in local NASIS databasex<-fetchNASIS(from ='pedons')# make sketches of the first 10 pedonspar(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 outsome of the tutorials on the AQP website.

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