A global historical climate grid at half degree resolution for PostGIS

Download the data from the University of Delaware and place in directories called globtemp (for air temperature) and glob prec (precipitation)

wget http://climate.geog.udel.edu/~climate/html_pages/Global2011/Global2011T.tar.gz

mv Global2011T.tar.gz globtemp/Global2011T.tar.gz

cd globtemp

tar xvzf Global2011T.tar.gz

rm Glob*

wget http://climate.geog.udel.edu/~climate/html_pages/Global2011/Precip_revised_3.02/Global2011P.tar.gz

mkdir globprec

mv Global2011P.tar.gz globprec/Global2011P.tar.gz

cd globprec

tar xvzf Global2011P.tar.gz

rm Glob*

Create a grid

library(RODBC)
library(date)
library(reshape)
con <- odbcConnect("reddeam_full")
query <- "CREATE TABLE global.halfdeg(gid int,lon double precision,lat double precision);"
odbcQuery(con, query)

Set the grid coordinates

To do this we will just use one of the downloaded files.

setwd("/home/duncan/globtemp")
a <- dir()

a[1]
d <- read.table(a[1])
d <- data.frame(gid = 1:dim(d)[1], X = d[, 1], y = d[, 2])
write.table(d, file = "/home/duncan/temp.csv", row.names = F, sep = ",", col.names = FALSE)
fl <- "/home/duncan/temp.csv"
query <- paste("COPY global.halfdeg FROM '", fl, "' DELIMITERS ',' CSV;", sep = "")
query
odbcQuery(con, query)

## Now make a geometry column and index
query <- "SELECT AddGeometryColumn( 'global', 'halfdeg','geom', 4326, 'point', 2);update global.halfdeg set geom = ST_SetSRID(st_point(lon,lat),4326); CREATE INDEX ghf_deg_ind ON global.halfdeg USING GIST (geom);CREATE INDEX ghf_deg_gid ON global.halfdeg (gid);"
odbcQuery(con, query)

Expand the grid to form a rectangular graticule

query <- "create table global.halfdeggrat as select gid, st_expand(geom,0.25) geom from global.halfdeg;\ncreate index gid_ind on global.halfdeggrat(gid);"
odbcQuery(con, query)

Load the data

query <- "CREATE TABLE global.historical_airtemp( gid int,yrmon date,airtemp double precision)"
odbcQuery(con, query)

setwd("/home/duncan/globtemp")
for (i in 1:112) {
    d <- data.frame(file = gsub("air_temp.", "", a[i]), read.table(a[i]))
    d <- data.frame(gid = 1:dim(d)[1], d)
    names(d) <- c("gid", "year", "x", "y", as.character(1:12))

    dd <- melt(d, id = 1:4)
    dd$year <- as.numeric(as.character(dd$year))
    dd$dt <- as.character(sprintf("%i%02i01", dd$year, dd$var))
    dd$dt <- as.Date(dd$dt, format = "%Y%m%d")
    d <- data.frame(gid = d$gid, dt = dd$dt, val = dd$value)
    write.table(d, file = "/home/duncan/temp.csv", row.names = F, sep = ",", 
        col.names = FALSE)
    fl <- "/home/duncan/temp.csv"
    query <- paste("COPY global.historical_airtemp FROM '", fl, "' DELIMITERS ',' CSV;", 
        sep = "")
    query
    odbcQuery(con, query)
}

Precipitation data

query <- "CREATE TABLE global.historical_prec( gid int,yrmon date,prec double precision)"
odbcQuery(con, query)

setwd("/home/duncan/globprec")
a <- dir()
for (i in 1:112) {
    d <- data.frame(file = gsub("precip.", "", a[i]), read.table(a[i]))
    d <- data.frame(gid = 1:dim(d)[1], d)
    names(d) <- c("gid", "year", "x", "y", as.character(1:12))

    dd <- melt(d, id = 1:4)
    dd$year <- as.numeric(as.character(dd$year))
    dd$dt <- as.character(sprintf("%i%02i01", dd$year, dd$var))
    dd$dt <- as.Date(dd$dt, format = "%Y%m%d")
    d <- data.frame(gid = d$gid, dt = dd$dt, val = dd$value)
    write.table(d, file = "/home/duncan/temp.csv", row.names = F, sep = ",", 
        col.names = FALSE)
    fl <- "/home/duncan/temp.csv"
    query <- paste("COPY global.historical_prec FROM '", fl, "' DELIMITERS ',' CSV;", 
        sep = "")
    query
    odbcQuery(con, query)
}