Mercurial > ~darius > hgwebdir.cgi > wh1080

comparison db/insertBoM.c @ 0:9dab44dcb331

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Initial commit of Greg's code from http://www.lemis.com/grog/tmp/wh1080.tar.gz
author Daniel O'Connor <darius@dons.net.au>
date Tue, 09 Feb 2010 13:44:25 +1030
parents
children
comparison
equal deleted inserted replaced
-1:000000000000 0:9dab44dcb331
1 /*
2 * Read in Australian Bureau of Meteorology comma-delimited files and convert
3 * them into INSERT commands for MySQL.
4 *
5 * $Id: insertBoM.c,v 1.4 2010/01/03 05:05:28 grog Exp $
6 */
7 #include <ctype.h>
8 #include <stdio.h>
9 #include <stdlib.h>
10 #include <string.h>
11 #include <sys/file.h>
12 #include <sys/stat.h>
13 #include <sys/types.h>
14 #include <unistd.h>
15
16 #define BUFSIZE 1024
17 char buf [BUFSIZE];
18 char sql_command [BUFSIZE];
19
20 /* We split up the input line into these fields, not all of which we use. */
21 char *field [50]; /* pointers to beginnings of fields */
22
23 /*
24 * We alias these structs to the input field above. Unfortunately, the data
25 * returned from Sheoaks is different from Ballarat and Melbourne airports:
26 * Sheoaks doesn't report a QNH pressure. I should really parse the input files
27 * to check the fields, but it's pretty messy.
28 */
29
30 struct ballarat_row
31 {
32 char *order; /* sort order (backwards!) */
33 char *station_id; /* station ID, apparently always numeric */
34 char *name; /* name of town, it would seem */
35 char *history; /* this looks like another station ID */
36 char *funnydate; /* date in useless form 0D/0H:0m[ap]m */
37 char *date; /* date as YYYYMMDDHHmmSS */
38 char *temp; /* temperature, °C */
39 char *apparent_temp; /* apparent temperature */
40 char *delta_t; /* wet bulb depression */
41 char *dewpoint; /* dew point */
42 char *wind_gust; /* wind gust speed */
43 char *gust_knots; /* same again in knots */
44 char *pressure_msl; /* pressure at mean sea level */
45 char *pressure_qnh; /* QNH pressure, whatever that may be */
46 char *rain; /* rain as text (can be "Tce") */
47 char *humidity; /* relative humdity */
48 char *wind_direction_text; /* text of direction */
49 char *wind_speed; /* speed in km/h */
50 char *wind_speed_knots; /* and in knots */
51 } *ballarat_readings = (struct ballarat_row *) field;
52
53 struct sheoaks_row
54 {
55 char *order; /* sort order (backwards!) */
56 char *station_id; /* station ID, apparently always numeric */
57 char *name; /* name of town, it would seem */
58 char *history; /* this looks like another station ID */
59 char *funnydate; /* date in useless form 0D/0H:0m[ap]m */
60 char *date; /* date as YYYYMMDDHHmmSS */
61 char *temp; /* temperature, °C */
62 char *apparent_temp; /* apparent temperature */
63 char *delta_t; /* wet bulb depression */
64 char *dewpoint; /* dew point */
65 char *wind_gust; /* wind gust speed */
66 char *gust_knots; /* same again in knots */
67 char *pressure_msl; /* pressure at mean sea level */
68 char *rain; /* rain as text (can be "Tce") */
69 char *humidity; /* relative humdity */
70 char *wind_direction_text; /* text of direction */
71 char *wind_speed; /* speed in km/h */
72 char *wind_speed_knots; /* and in knots */
73 } *sheoaks_readings = (struct sheoaks_row *) field;
74
75 void skipblanks (char **cp)
76 {
77 while (**cp == ' ')
78 (*cp)++;
79 }
80
81 /*
82 * Wind directions as text. BoM encloses them in "", so we do the same
83 */
84 struct wind_directions
85 {
86 char *text; /* name of direction */
87 float direction; /* and the direction it represents */
88 } wind_directions [] = {
89 {"\"N\"", 0},
90 {"\"North\"", 0},
91 {"\"NNE\"", 22.5},
92 {"\"NE\"", 45},
93 {"\"ENE\"", 67.5},
94 {"\"E\"", 90},
95 {"\"East\"", 90},
96 {"\"ESE\"", 112.5},
97 {"\"SE\"", 135},
98 {"\"SSE\"", 157.5},
99 {"\"S\"", 180},
100 {"\"South\"", 180},
101 {"\"SSW\"", 202.5},
102 {"\"SW\"", 225},
103 {"\"WSW\"", 247.5},
104 {"\"W\"", 270},
105 {"\"West\"", 270},
106 {"\"WNW\"", 292.5},
107 {"\"NW\"", 315},
108 {"\"NNW\"", 337.5}};
109
110 int wind_direction_count = sizeof (wind_directions) / sizeof (struct wind_directions);
111 float previous_wind;
112 float wind_direction (char *text)
113 {
114 int i;
115 if (! strcmp (text, "\"CALM\""))
116 return previous_wind;
117 for (i = 0; i < wind_direction_count; i++)
118 if (! strcmp (wind_directions [i].text, text))
119 return previous_wind = wind_directions [i].direction;
120 fprintf (stderr, "Unknown wind direction: %s\n", text);
121 return previous_wind; /* Sam Ting */
122 }
123
124 int main (int argc, char *argv [])
125 {
126 char *cp; /* pointers in buffers */
127 int fields; /* becomes number of fields in row */
128 char date_text [11]; /* YYYY-MM-DD */
129 char time_text [9]; /* HH-MM-SS */
130
131 if (argc > 1)
132 {
133 fprintf (stderr, "Usage: $0 < file\n");
134 exit (1);
135 }
136
137 while (1)
138 {
139 memset ((void *) buf, '\0', sizeof (buf)); /* don't trip over old stuff */
140 if (! fgets (buf, BUFSIZE - 1, stdin))
141 exit (0); /* empty file, just ignore */
142 if (isdigit (buf [0])) /* valid row */
143 {
144 fields = 0;
145 cp = buf;
146 while (1)
147 {
148 skipblanks (&cp); /* though there don't seem to be any */
149 field [fields++] = cp;
150 while (*cp && (*cp != ',') && (*cp != '\n') && (*cp != ','))
151 cp++;
152 if (! *cp)
153 break;
154 *cp++ = '\0'; /* delimit the string */
155 }
156 /*
157 * buf now contains "fields" strings, and field (and thus "ballarat_row"
158 * and "sheoaks_row") contain pointers to them.
159 *
160 * Fix a few fields.
161 */
162 strcpy (date_text, "YYYY-MM-DD");
163 strcpy (time_text, "HH:MM:SS");
164 /* The date info is surrounded with " */
165 memcpy (date_text, &ballarat_readings->date [1], 4); /* YYYY */
166 memcpy (&date_text [5], &ballarat_readings->date [5], 2); /* MM */
167 memcpy (&date_text [8], &ballarat_readings->date [7], 2); /* MM */
168 memcpy (time_text, &ballarat_readings->date [9], 2); /* HH */
169 memcpy (&time_text [3], &ballarat_readings->date [11], 2); /* MM */
170 memcpy (&time_text [6], &ballarat_readings->date [13], 2); /* MM */
171
172 if (strcmp (ballarat_readings->station_id, "94863")) /* Not Sheoaks */
173 {
174 if (! strcmp (ballarat_readings->pressure_qnh, "-9999.0")) /* this seems to be a null value */
175 ballarat_readings->pressure_qnh = "NULL";
176 sprintf (sql_command,
177 "REPLACE INTO remote_observations (station_id, date, time, outside_temp, apparent_temp,"
178 "delta_t, outside_dewpoint, wind_gust, pressure_msl, pressure_qnh, rain, outside_humidity, "
179 "wind_direction, wind_direction_text, wind_speed) "
180 "VALUES (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", %s, %s, "
181 "%s, \"%s\", %4.1f, %s, \"%s\");",
182 ballarat_readings->station_id,
183 date_text,
184 time_text,
185 ballarat_readings->temp,
186 ballarat_readings->apparent_temp,
187 ballarat_readings->delta_t,
188 ballarat_readings->dewpoint,
189 ballarat_readings->wind_gust,
190 ballarat_readings->pressure_msl,
191 ballarat_readings->pressure_qnh,
192 ballarat_readings->rain,
193 ballarat_readings->humidity,
194 wind_direction (ballarat_readings->wind_direction_text),
195 ballarat_readings->wind_direction_text,
196 ballarat_readings->wind_speed );
197 }
198 else /* currently only sheoaks */
199 {
200 if (! strcmp (sheoaks_readings->pressure_msl, "-9999.0")) /* this seems to be a null value */
201 sheoaks_readings->pressure_msl = NULL;
202
203 sprintf (sql_command,
204 "REPLACE INTO remote_observations (station_id, date, time, outside_temp, apparent_temp,"
205 "delta_t, outside_dewpoint, wind_gust, pressure_msl, rain, outside_humidity, "
206 "wind_direction, wind_direction_text, wind_speed) "
207 "VALUES (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", %s, "
208 "%s, \"%s\", %4.1f, %s, \"%s\");",
209 sheoaks_readings->station_id,
210 date_text,
211 time_text,
212 sheoaks_readings->temp,
213 sheoaks_readings->apparent_temp,
214 sheoaks_readings->delta_t,
215 sheoaks_readings->dewpoint,
216 sheoaks_readings->wind_gust,
217 sheoaks_readings->pressure_msl,
218 sheoaks_readings->rain,
219 sheoaks_readings->humidity,
220 wind_direction (sheoaks_readings->wind_direction_text),
221 sheoaks_readings->wind_direction_text,
222 sheoaks_readings->wind_speed );
223 }
224 puts (sql_command);
225 }
226 }
227 return 0;
228 }
229