# 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

DLY.COLS <- c("character", "integer", "integer", "character",

    rep(c("numeric", "character", "character", "character"), times=31))

loadAsCSV <- function(dly) {

    awk <- paste(

        "awk -v FIELDWIDTHS='",

        paste(c(11, 4, 2, 4, rep(c(5,1,1,1), times=31)), collapse=" "),

        "' -v OFS=',' '{ $1=$1 \"\"; print }'", sep="")

    tr <- "tr -d ' '"

    system(paste(awk, dly, "|", tr), intern=TRUE)

    read.csv(textConnection(system(paste(awk, dly, "|", tr),

        intern=TRUE)), header=FALSE, colClasses=DLY.COLS)

}

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

for (file in dailies) {

    x <- loadAsCSV(file)

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

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

    ghcn_bulk_insert(db, sql, long)

}