Scottish Snow

• Scottish snow
• Databases
• Parallel processing
• Results

• Visible indicator
• Water resources
• Tourism
• Climate feedback

• Forecasting snow cover?
• Snowmelt frequency?
• Snowcover frequency?

• Collected by volunteers
• 1945 to 2007 (ish)
• 140 sites across Scotland
• Subjective assessment of snowline

• Requires tidy data
• Platform/software independant
• New language
• Offload heavy lifting

Preamble

install.packages("RSQLite")
library(RSQLite)

Connection

db = dbConnect(SQLite(), "~/Cloud/Michael/Uni_temp/SSGB/SSGB.sqlite")

You're now good to go!

# Available tables
dbListTables(db)

# Columns in a table
dbListFields(db, "SSGB_stations")

# Read all columns from obs table from Aviemore
head(dbGetQuery(db, "SELECT * FROM SSGB_obs WHERE Station='Aviemore'"))

head(dbGetQuery(db, "SELECT * FROM SSGB_obs WHERE Station='Aviemore' AND Snowline!='m'"))

df = dbGetQuery(db, "SELECT * FROM SSGB_obs WHERE Station='Aviemore' AND Snowline!='m'")

# lapply multiple stations
st = c("Aviemore", "Eskdalemuir", "Dalwhinnie")

dl = lapply(seq_along(st), function(i){
   dbGetQuery(db, paste0("SELECT HydroYear, Station, SnowlineElev, COUNT(SnowlineElev) AS Days FROM SSGB_obs WHERE Station='", st[i], "' GROUP BY HydroYear, Station, SnowlineElev"))
})

# Temp tables
dbSendQuery(db, "
   CREATE TEMP TABLE step AS
   SELECT * FROM SSGB_obs
   WHERE strftime('%m', Date) BETWEEN '11' AND '12'
      AND SnowlineElev !='m'
   OR strftime('%m', Date) BETWEEN '01' AND '04'
      AND SnowlineElev !='m'
")

# Create a new table
dbSendQuery(db,
      "CREATE TABLE fielddata
         (HydroYear INT,
         Date DATETIME,
         Station TEXT,
         Snowline TEXT,
         SnowlineElev TEXT,
         PRIMARY KEY(Date, Station))
")

# Write to it
dbWriteTable(conn=db, name="SSGB_obs", new.data, append=T, row.names=F)

https://bitbucket.org/mikerspencer/uk_climate

http://www.sqlite.org/lang_keywords.html

http://www.sqlite.org/docs.html

http://www.tutorialspoint.com/sqlite/

https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/

Parallel is available in R core

library(parallel)

mclapply()

# parameters
install.packages("TunePareto")
library(TunePareto)

params = list(
   a = 1:10,
   b = seq(0.1, 1, by=0.1))

# How many iterations?
prod(sapply(params, length))

# Get all parameter combinations
params = allCombinations(params)
params = lapply(params, function(i){
   do.call("cbind.data.frame", i)
})
params = do.call("rbind.data.frame", params)

# Single core
ptm = proc.time()
dl = lapply(1:nrow(params), function(i){
   rnorm(n=10, mean=params[i, 1], sd=params[i, 2])
})
proc.time() - ptm

# Multi core
ptm = proc.time()
dl = mclapply(1:nrow(params), mc.cores=2, function(i){
   rnorm(n=10, mean=params[i, 1], sd=params[i, 2])
})
proc.time() - ptm

Packages used:

• RSQLite https://cran.r-project.org/web/packages/RSQLite/index.html
  • see also: http://www.sqlite.org/
• parallel https://stat.ethz.ch/R-manual/R-devel/library/parallel/doc/parallel.pdf

Twitter: @mikerspencer

LinkedIn: https://www.linkedin.com/in/mikerspencer

Email: spencer.mike.r@gmail.com

Website: http://mikerspencer.com

Blog: http://scottishsnow.wordpress.com