Revision 86d73d7e
Added by Jim Regetz over 12 years ago
- ID 86d73d7ef27e5deed7eb6a94ad3125b974070601
- Parent 9b850015
| climate/extra/ghcn-to-psql.R | ||
|---|---|---|
| 8 | 8 |
# to be unpacked again if for some reason this script needs to be |
| 9 | 9 |
# re-run. |
| 10 | 10 |
# |
| 11 |
# The station data (names, locations) are now also imported as a |
|
| 12 |
# separate 'stations' table in the database. |
|
| 13 |
# |
|
| 11 | 14 |
# As currently written, the script assumes that the 'ghcn' database |
| 12 | 15 |
# already exists locally but has no tables, and that it can be accessed |
| 13 | 16 |
# via 'ident' authentication (i.e., as the user executing the script, |
| ... | ... | |
| 131 | 134 |
cat(file, "\n", file=logfile, append=TRUE) |
| 132 | 135 |
} |
| 133 | 136 |
|
| 137 |
# create ghcn stations table |
|
| 138 |
dbGetQuery(db, paste( |
|
| 139 |
"CREATE TABLE stations ( |
|
| 140 |
id char(11) primary key, |
|
| 141 |
latitude numeric, |
|
| 142 |
longitude numeric, |
|
| 143 |
elevation numeric, |
|
| 144 |
state char(2), |
|
| 145 |
name char(30), |
|
| 146 |
gsnflag char(3), |
|
| 147 |
hcnflag char(3), |
|
| 148 |
wmoid char(5) |
|
| 149 |
)")) |
|
| 150 |
|
|
| 151 |
# process and insert station data |
|
| 152 |
FMT.COLS <- strsplit(paste(c("A11", "F8", "F9", "F6", "A2", "A30", "A3",
|
|
| 153 |
"A3", "A5"), collapse=",X1,"), ",")[[1]] |
|
| 154 |
stations <- read.fortran(file.path(ghcndir, "ghcnd-stations.txt"), |
|
| 155 |
FMT.COLS, comment.char="", strip.white=TRUE, na="") |
|
| 156 |
dbWriteTable(db, "stations", stations, row.names=FALSE, append=TRUE) |
|
| 157 |
|
|
| 158 |
# add foreign key constraint to ghcn table |
|
| 159 |
dbGetQuery(db, |
|
| 160 |
"ALTER TABLE ghcn |
|
| 161 |
ADD FOREIGN KEY (station) |
|
| 162 |
REFERENCES stations (id)") |
|
| 163 |
|
|
| 134 | 164 |
dbDisconnect(db) |
| 135 | 165 |
|
| 136 | 166 |
# alternative approach: load some number (>1) of station data files into |
Also available in: Unified diff
added import of station data to ghcn postgres script