two approaches to retrieving canopy height + canopy cover data

all_s4_6_traits.sql

\copy (select raw_date, sitename, cultivar, trait, method, mean 
       from traits_and_yields_view_private 
       sitename like '%Season 4%' and sitename ~ 'Range (20|30)$') 
       to ~/season4traits.csv delimiter ',' csv header;
\copy (select raw_date, sitename, cultivar, trait, method, mean 
       from traits_and_yields_view_private 
       sitename like '%Season 6%' and sitename ~ 'Range (20|30)$') 
       to ~/season6traits.csv delimiter ',' csv header;

sample_data.R

#a <- list()
for(t in c('canopy_height', 'canopy_cover')){
  for(s in c('~Season 6', '~Season 4')){ 
    a[[paste0(t,s)]] <- traits::betydb_query(table     = "search", 
                                             trait     = t, 
                                             sitename  = s,
                                             limit     =  "none")
  }
}

for(t in c('canopy_height', 'canopy_cover')){
  for(s in c('~Season 6', '~Season 4')){
    a[[paste0(t,s)]] %>% 
      select(checked, sitename, commonname, cultivar, raw_date, trait, mean, units) %>% 
      readr::write_csv(path = file.path('~/terrarefsamples',
                                        paste0(t,gsub('~','',s),'.csv')))
  }
}

b <- bind_rows(a)

library(ggplot2)
library(dplyr)
library(lubridate)
c <- b %>% mutate(date = ymd_hms(raw_date))
ggplot(data = c) +
  geom_point(aes(date, mean, color = method_name)) +
  facet_wrap(~trait+year)

sample_data.sql

\copy (select raw_date, sitename, cultivar, trait, method, mean 
       from traits_and_yields_view_private 
       where trait = 'canopy_cover' and sitename like '%Season 4%') 
       to ~/s4cover.csv delimiter ',' csv header;                              
\copy (select raw_date, sitename, cultivar, trait, method, mean 
       from traits_and_yields_view_private 
       where trait = 'canopy_cover' and sitename like '%Season 6%') 
       to ~/s6cover.csv delimiter ',' csv header;
\copy (select raw_date, sitename, cultivar, trait, method, mean 
       from traits_and_yields_view_private 
       where trait = 'canopy_height' and sitename like '%Season 4%') 
       to ~/s4height.csv delimiter ',' csv header;
\copy (select raw_date, sitename, cultivar, trait, method, mean 
       from traits_and_yields_view_private 
       where trait = 'canopy_height' and sitename like '%Season 6%') 
       to ~/s6height.csv delimiter ',' csv header;

These are sql (fast) and R (accessible to public) methods for getting similar datasets from the terraref database.