--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/db/insertBoM.c	Tue Feb 09 13:44:25 2010 +1030
@@ -0,0 +1,229 @@
+/*
+ * Read in Australian Bureau of Meteorology comma-delimited files and convert
+ * them into INSERT commands for MySQL.
+ *
+ * $Id: insertBoM.c,v 1.4 2010/01/03 05:05:28 grog Exp $
+ */
+#include <ctype.h>
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <sys/file.h>
+#include <sys/stat.h>
+#include <sys/types.h>
+#include <unistd.h>
+
+#define BUFSIZE 1024
+char buf [BUFSIZE];
+char sql_command [BUFSIZE];
+
+/* We split up the input line into these fields, not all of which we use. */
+char *field [50];                               /* pointers to beginnings of fields */
+
+/*
+ * We alias these structs to the input field above.  Unfortunately, the data
+ * returned from Sheoaks is different from Ballarat and Melbourne airports:
+ * Sheoaks doesn't report a QNH pressure.  I should really parse the input files
+ * to check the fields, but it's pretty messy.
+ */
+
+struct ballarat_row
+{
+  char *order;                                  /* sort order (backwards!) */
+  char *station_id;                             /* station ID, apparently always numeric */
+  char *name;                                   /* name of town, it would seem */
+  char *history;                                /* this looks like another station ID */
+  char *funnydate;                              /* date in useless form 0D/0H:0m[ap]m */
+  char *date;                                   /* date as YYYYMMDDHHmmSS */
+  char *temp;                                   /* temperature, °C */
+  char *apparent_temp;                          /* apparent temperature */
+  char *delta_t;                                /* wet bulb depression */
+  char *dewpoint;                               /* dew point */
+  char *wind_gust;                              /* wind gust speed */
+  char *gust_knots;                             /* same again in knots */
+  char *pressure_msl;                           /* pressure at mean sea level */
+  char *pressure_qnh;                           /* QNH pressure, whatever that may be */
+  char *rain;                                   /* rain as text (can be "Tce") */
+  char *humidity;                               /* relative humdity */
+  char *wind_direction_text;                    /* text of direction */
+  char *wind_speed;                             /* speed in km/h */
+  char *wind_speed_knots;                       /* and in knots */
+} *ballarat_readings = (struct ballarat_row *) field;
+
+struct sheoaks_row
+{
+  char *order;                                  /* sort order (backwards!) */
+  char *station_id;                             /* station ID, apparently always numeric */
+  char *name;                                   /* name of town, it would seem */
+  char *history;                                /* this looks like another station ID */
+  char *funnydate;                              /* date in useless form 0D/0H:0m[ap]m */
+  char *date;                                   /* date as YYYYMMDDHHmmSS */
+  char *temp;                                   /* temperature, °C */
+  char *apparent_temp;                          /* apparent temperature */
+  char *delta_t;                                /* wet bulb depression */
+  char *dewpoint;                               /* dew point */
+  char *wind_gust;                              /* wind gust speed */
+  char *gust_knots;                             /* same again in knots */
+  char *pressure_msl;                           /* pressure at mean sea level */
+  char *rain;                                   /* rain as text (can be "Tce") */
+  char *humidity;                               /* relative humdity */
+  char *wind_direction_text;                    /* text of direction */
+  char *wind_speed;                             /* speed in km/h */
+  char *wind_speed_knots;                       /* and in knots */
+} *sheoaks_readings = (struct sheoaks_row *) field;
+
+void skipblanks (char **cp)
+{
+  while (**cp == ' ')
+    (*cp)++;
+}
+
+/*
+ * Wind directions as text.  BoM encloses them in "", so we do the same
+ */
+struct wind_directions
+{
+  char *text;                                   /* name of direction */
+  float direction;                              /* and the direction it represents */
+} wind_directions [] = {
+  {"\"N\"", 0},
+  {"\"North\"", 0},
+  {"\"NNE\"", 22.5},
+  {"\"NE\"", 45},
+  {"\"ENE\"", 67.5},
+  {"\"E\"", 90},
+  {"\"East\"", 90},
+  {"\"ESE\"", 112.5},
+  {"\"SE\"", 135},
+  {"\"SSE\"", 157.5},
+  {"\"S\"", 180},
+  {"\"South\"", 180},
+  {"\"SSW\"", 202.5},
+  {"\"SW\"", 225},
+  {"\"WSW\"", 247.5},
+  {"\"W\"", 270},
+  {"\"West\"", 270},
+  {"\"WNW\"", 292.5},
+  {"\"NW\"", 315},
+  {"\"NNW\"", 337.5}};
+
+int wind_direction_count = sizeof (wind_directions) / sizeof (struct wind_directions);
+float previous_wind;
+float wind_direction (char *text)
+{
+  int i;
+  if (! strcmp (text, "\"CALM\""))
+    return previous_wind;
+  for (i = 0; i < wind_direction_count; i++)
+    if (! strcmp (wind_directions [i].text, text))
+      return previous_wind = wind_directions [i].direction;
+  fprintf (stderr, "Unknown wind direction: %s\n", text);
+  return previous_wind;                         /* Sam Ting */
+}
+
+int main (int argc, char *argv [])
+{
+  char *cp;                                     /* pointers in buffers */
+  int fields;                                   /* becomes number of fields in row */
+  char date_text [11];                          /* YYYY-MM-DD */
+  char time_text [9];                           /* HH-MM-SS */
+
+  if (argc > 1)
+  {
+    fprintf (stderr, "Usage: $0 < file\n");
+    exit (1);
+  }
+
+  while (1)
+  {
+    memset ((void *) buf, '\0', sizeof (buf));  /* don't trip over old stuff */
+    if (! fgets (buf, BUFSIZE - 1, stdin))
+      exit (0);                                 /* empty file, just ignore  */
+    if (isdigit (buf [0]))                      /* valid row  */
+    {
+      fields = 0;
+      cp = buf;
+      while (1)
+      {
+        skipblanks (&cp);                       /* though there don't seem to be any */
+        field [fields++] = cp;
+        while (*cp && (*cp != ',') && (*cp != '\n') && (*cp != ','))
+          cp++;
+        if (! *cp)
+          break;
+        *cp++ = '\0';                           /* delimit the string */
+      }
+      /*
+       * buf now contains "fields" strings, and field (and thus "ballarat_row"
+       * and "sheoaks_row") contain pointers to them.
+       *
+       * Fix a few fields.
+       */
+      strcpy (date_text, "YYYY-MM-DD");
+      strcpy (time_text, "HH:MM:SS");
+      /* The date info is surrounded with " */
+      memcpy (date_text, &ballarat_readings->date [1], 4); /* YYYY */
+      memcpy (&date_text [5], &ballarat_readings->date [5], 2); /* MM */
+      memcpy (&date_text [8], &ballarat_readings->date [7], 2); /* MM */
+      memcpy (time_text, &ballarat_readings->date [9], 2); /* HH */
+      memcpy (&time_text [3], &ballarat_readings->date [11], 2); /* MM */
+      memcpy (&time_text [6], &ballarat_readings->date [13], 2); /* MM */
+
+      if (strcmp (ballarat_readings->station_id, "94863")) /* Not Sheoaks */
+      {
+        if (! strcmp (ballarat_readings->pressure_qnh, "-9999.0")) /* this seems to be a null value */
+          ballarat_readings->pressure_qnh = "NULL";
+        sprintf (sql_command,
+                 "REPLACE INTO remote_observations (station_id, date, time, outside_temp, apparent_temp,"
+                 "delta_t, outside_dewpoint, wind_gust, pressure_msl, pressure_qnh, rain, outside_humidity, "
+                 "wind_direction, wind_direction_text, wind_speed) "
+                 "VALUES (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", %s, %s, "
+                 "%s, \"%s\", %4.1f, %s, \"%s\");",
+                 ballarat_readings->station_id,
+                 date_text,
+                 time_text,
+                 ballarat_readings->temp,
+                 ballarat_readings->apparent_temp,
+                 ballarat_readings->delta_t,
+                 ballarat_readings->dewpoint,
+                 ballarat_readings->wind_gust,
+                 ballarat_readings->pressure_msl,
+                 ballarat_readings->pressure_qnh,
+                 ballarat_readings->rain,
+                 ballarat_readings->humidity,
+                 wind_direction (ballarat_readings->wind_direction_text),
+                 ballarat_readings->wind_direction_text,
+                 ballarat_readings->wind_speed );
+      }
+      else                                      /* currently only sheoaks */
+      {
+      if (! strcmp (sheoaks_readings->pressure_msl, "-9999.0")) /* this seems to be a null value */
+        sheoaks_readings->pressure_msl = NULL;
+
+        sprintf (sql_command,
+                 "REPLACE INTO remote_observations (station_id, date, time, outside_temp, apparent_temp,"
+                 "delta_t, outside_dewpoint, wind_gust, pressure_msl, rain, outside_humidity, "
+                 "wind_direction, wind_direction_text, wind_speed) "
+                 "VALUES (\"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", \"%s\", %s, "
+                 "%s, \"%s\", %4.1f, %s, \"%s\");",
+                 sheoaks_readings->station_id,
+                 date_text,
+                 time_text,
+                 sheoaks_readings->temp,
+                 sheoaks_readings->apparent_temp,
+                 sheoaks_readings->delta_t,
+                 sheoaks_readings->dewpoint,
+                 sheoaks_readings->wind_gust,
+                 sheoaks_readings->pressure_msl,
+                 sheoaks_readings->rain,
+                 sheoaks_readings->humidity,
+                 wind_direction (sheoaks_readings->wind_direction_text),
+                 sheoaks_readings->wind_direction_text,
+                 sheoaks_readings->wind_speed );
+      }
+      puts (sql_command);
+    }
+  }
+  return 0;
+}
+