PSA Training Series github.com/precision-sustainable-ag

Database Querying with R — Brian W. Davis 2020-01-07

1. What is a DB? (video)
2. Getting started with R (video)
3. How to use a local DB (video)
4. How to use the PSA DB (video)

---

001 - What is a database?

1 What is a DB?

A database is a rectangular storage system, columns and rows of data, just like a spreadsheet. Unlike a spreadsheet, the way you read and write data in a DB is through code or the command line, not through a point-and-click interface like Google Sheets or Excel.

I’ll mostly be using “database” to mean a relational database management system, like PostgreSQL or SQLite, but there are other paradigms.

The code you use to interact with the DB is called “SQL” (Structured Query Language), but you don’t have to know SQL. In the code examples I’ll show, there will be a “vanilla SQL” version and a “tidy R” version.

1.1 Where is a DB?

A database consists of a server and one or more clients. “Server” and “clients” here just mean computer applications; the server can be a webserver living out in the cloud, or it can be your own laptop. The client can be a command line terminal, an R session, a SAS session, a webpage running JavaScript, etc.

I’ll demonstrate later on how to use a local DB, where the server and the client are both on your computer. Then I’ll discuss the PSA On-Farm database, which is hosted on Azure and you can access from anywhere with an internet connection.

1.2 What’s inside?

Inside a database there are one or more tables, which are analogous to individual tabs in a regular spreadsheet. What makes these tables different is that:

• Each column only contains one type of data (numbers, text, dates)
• The type of each column must be decided before you put any data in it
• There is nothing outside the “rectangle” (no plots, no notes in the margins, no comment boxes floating over top)

There are additional benefits to using a structured relational database:

• Each table has a “key” that connects an observation between tables
• Keys can map one row to another row, or many rows to one row, or vice versa
• Planning a structure (schema) helps you decide how to organize your data

Are there downsides to using a database?

• You have to learn how to use them, by reading this tutorial

1.3 Example

Let’s look at a toy database with two tables:

treatments

  plot_id crop    tillage
1     101 corn    no-till
2     102 corn strip-till
3     103  soy strip-till
4     104  soy    no-till
5     201 corn strip-till
6     202  soy    no-till
7     203 corn    no-till
8     204  soy strip-till

yields

  plot_id Mg_ha
1     101  10.0
2     102  12.0
3     103   4.5
4     104   4.5
5     201  11.0
6     202   3.0
7     203   9.5
8     204   5.0

If you were making a spreadsheet of yields, you would probably want all of this in one table, so you could associate each observation with the treatments. But I’m sure you’ve noticed that copy-pasting columns of treatments inevitably ends up with some errors that you have to go back and clean up. Then when you want to have another observation type, like cover crop biomass or ground cover or weed count, you have to do it all over again.

With a database, instead you record the minimally identifiable information, like plot_id and the observation. Then once all your data is recorded and stored and cleaned, you can join tables together for your analyses. Note that here we’re using plot_id as our key. That’s how we know which row from one table is connected to a row from another table.

This sort of structure is especially helpful where information from one table would need to be repeated many times. Imagine an experiment with 3 or 4 columns of treatment variables, and then a table of sensor readings every 15 minutes for a year (~35000 rows per plot!). You’d have a lot of duplicated information, and any mistakes (misspelling, miscoding treatments, rows out of order) would break the whole thing.

1.4 What do I do with it though?

In general, databases are for storage and organization, and you use other tools like R or SAS for analysis. Once you have the two tables, you join them either in the DB (via SQL) or locally (using merge or dplyr::full_join):

yield_data <- merge(treatments, yields)
yield_data

  plot_id crop    tillage Mg_ha
1     101 corn    no-till  10.0
2     102 corn strip-till  12.0
3     103  soy strip-till   4.5
4     104  soy    no-till   4.5
5     201 corn strip-till  11.0
6     202  soy    no-till   3.0
7     203 corn    no-till   9.5
8     204  soy strip-till   5.0

And then analysis is straightforward, using your normal tools.

model <- lm(Mg_ha ~ crop * tillage, data = yield_data)
anova(model)

Analysis of Variance Table

Response: Mg_ha
             Df Sum Sq Mean Sq  F value    Pr(>F)    
crop          1 81.281  81.281 173.4000 0.0001921 ***
tillage       1  3.781   3.781   8.0667 0.0468587 *  
crop:tillage  1  0.281   0.281   0.6000 0.4818174    
Residuals     4  1.875   0.469                       
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

aggregate(
  Mg_ha ~ crop*tillage, 
  data = yield_data, 
  FUN = mean
  )

  crop    tillage Mg_ha
1 corn    no-till  9.75
2  soy    no-till  3.75
3 corn strip-till 11.50
4  soy strip-till  4.75

Or if you prefer tidyverse-flavored R:

library(dplyr, warn.conflicts = FALSE)

yield_data %>% 
  group_by(crop, tillage) %>% 
  summarise_at(
    vars(Mg_ha), 
    list(~mean(.), ~sd(.))
    )

# A tibble: 4 x 4
# Groups:   crop [2]
  crop  tillage     mean    sd
  <chr> <chr>      <dbl> <dbl>
1 corn  no-till     9.75 0.354
2 corn  strip-till 11.5  0.707
3 soy   no-till     3.75 1.06 
4 soy   strip-till  4.75 0.354

---

precisionsustainableag.org

---

Icons: fontawesome.com, BY 4.0 | This work, BY-SA 4.0

---