An example of how to submit a query to BETYdb

Database Connection Parameters

dbparms <- list(driver = "PostgreSQL", 
                dbname = "<database name>", 
                user = "bety", 
                host = "<myserver.mydomain.edu>", 
                password = "<mysecretpassword>")

These are the default settings, used on the PEcAn VM and any installation running on the same computer / server that R is running on (called ‘localhost’)

library(RPostgreSQL)
## Loading required package: DBI
dbcon <- dbConnect(drv = "PostgreSQL", 
                   dbname = "bety", 
                   user = "bety", 
                   host = "localhost", 
                   password = "bety")

Query the first line of the traits table

result <- dbGetQuery(conn = dbcon, statement = "select * from traits limit 1")
print(result)
##      id site_id specie_id citation_id cultivar_id treatment_id date
## 1 22099     300      1019         227          NA         1352 <NA>
##   dateloc     time timeloc  mean  n statname stat notes
## 1       6 19:00:00       1 43.73 NA       SD 1.67      
##            created_at          updated_at variable_id user_id checked
## 1 2010-09-27 19:06:06 2012-05-29 23:36:54         159       3       0
##   access_level entity_id method_id date_year date_month date_day time_hour
## 1            3        NA        NA        NA         NA       NA        19
##   time_minute
## 1           0

A more complex query

Based on a user request (GitHub issue 159)

How can I find all SLA and Amax data from experiments with CO2 fumigation?: I am interested in finding out how many records and distinct sites have both elevated CO2 and control plot data of both SLA and Amax,

## The SQL Query
query159 <- "
SELECT DISTINCT
  result_type,
  traits_and_yields_view_private. ID,
    site_id,
    sitename,
    traits_and_yields_view_private.treatment_id,
    lat,
    lon,
    scientificname,
    author,
    citation_year,
    treatment,
    traits_and_yields_view_private. DATE,
    trait,
    mean,
    traits_and_yields_view_private.units,
    n,
    statname,
    stat,
    definition
FROM
    traits_and_yields_view_private
JOIN treatments ON traits_and_yields_view_private.treatment_id = treatments. ID
JOIN managements_treatments ON managements_treatments.treatment_id = treatments. ID
JOIN managements ON managements_treatments.management_id = managements. ID
WHERE
    site_id IN (
        SELECT DISTINCT
            site_id
        FROM
            traits_and_yields_view_private
        WHERE
            treatment_id IN (
                SELECT
                    treatment_id
                FROM
                    managements_treatments
                JOIN managements ON managements_treatments.management_id = managements. ID
                WHERE
                    managements.mgmttype = 'CO2_fumigation'
            )
    )
AND trait IN ('SLA', 'Amax')
ORDER BY
    trait,
    sitename;
"

result <- dbGetQuery(conn = dbcon, statement = query159)
print(result)
##   result_type    id site_id
## 1  traits     37373     669
## 2  traits     22096     300
## 3  traits     39466     300
## 4  traits     39468     300
## 5  traits     39467     669
## 6  traits     39469     669
##                                                            sitename
## 1 Oak Ridge National Laboratory Free-Air CO2 Enrichment (FACE) site
## 2                                                       Duke Forest
## 3                                                       Duke Forest
## 4                                                       Duke Forest
## 5 Oak Ridge National Laboratory Free-Air CO2 Enrichment (FACE) site
## 6 Oak Ridge National Laboratory Free-Air CO2 Enrichment (FACE) site
##   treatment_id   lat    lon          scientificname  author citation_year
## 1         1123 35.90 -84.33 Liquidambar styraciflua Sholtis          2002
## 2          735 35.97 -79.02             Pinus taeda   Drake          2008
## 3          735 35.97 -79.02 Liquidambar styraciflua Herrick          1999
## 4          736 35.97 -79.02 Liquidambar styraciflua Herrick          1999
## 5          735 35.90 -84.33 Liquidambar styraciflua Herrick          1999
## 6          736 35.90 -84.33 Liquidambar styraciflua Herrick          1999
##                        treatment                date trait  mean
## 1             elevated CO2 level 2000-08-01 05:00:00  Amax 29.90
## 2   normal atmospheric CO2 level 2010-09-27 00:00:00   SLA  8.71
## 3   normal atmospheric CO2 level 1997-08-29 05:00:00   SLA 12.93
## 4 elevated atmospheric CO2 level 1997-08-30 05:00:00   SLA  9.98
## 5   normal atmospheric CO2 level 1997-08-30 05:00:00   SLA 19.99
## 6 elevated atmospheric CO2 level 1997-08-30 05:00:00   SLA 18.02
##              units  n statname  stat                   definition
## 1 umol CO2 m-2 s-1 NA      MSE 3.200                  553-556 ppm
## 2          m2 kg-1 NA       SD 0.710      ambient level 360ul l-1
## 3          m2 kg-1  3       SE 1.796      ambient level 360ul l-1
## 4          m2 kg-1  3       SE 1.464 elevated CO2 level 560ul l-1
## 5          m2 kg-1  3       SE 2.623      ambient level 360ul l-1
## 6          m2 kg-1  3       SE 2.825 elevated CO2 level 560ul l-1