# R script for batch parsing and loading GHCN daily station files

# (*.dly) into a SQLite database

#

# As written, the script is intended to create and populate the database

# from scratch, reporting an error if the script already exists. In

# principle though, the code that processes and loads a given *.dly file

# could be run on its own to load additional data into an already

# existing database file.

#

# Jim Regetz

# NCEAS

# Created on 09-May-2012

require(RSQLite)

db.path <- "ghcn_all.db"

# define bulk insert helper function (adapted from RSQLite

# documentation)

ghcn_bulk_insert <- function(db, sql, dat) {

    dbBeginTransaction(db)

    dbGetPreparedQuery(db, sql, bind.data = dat)

    dbCommit(db)

    dbGetQuery(db, "select count(*) from ghcn")[[1]]

}

# establish database connection

con <- dbDriver("SQLite")

if (file.exists(db.path)) {

    stop("database already exists at ", db.path)

}

db <- dbConnect(con, dbname=db.path)

# create main ghcn table

sql <- "

    CREATE TABLE ghcn (

        id text,

        year int,

        month int,

        element text,

        day int,

        value int,

        mflag text,

        qflag text,

        sflag text,

        srcrowid int)

"

dbGetQuery(db, sql)

# prepare sql insert statement

params.clist <- paste(rep("?", length(dbListFields(db, "ghcn"))),

    collapse=", ")

sql <- paste("insert into ghcn values (", params.clist, ")", sep="")

# process and insert daily data

dailies <- list.files(pattern="*.dly")

for (file in dailies) {

    x <- read.fortran(file,

        format=c("A11","I4","I2","A4",rep(c("F5","3A1"),times=31)))

    long <- reshape(x, direction="long",

        varying=matrix(5:ncol(x), nrow=4))

    ghcn_bulk_insert(db, sql, long)

}