--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/local-compare.c	Tue Feb 09 13:44:25 2010 +1030
@@ -0,0 +1,390 @@
+/*
+ * Create text files with a table comparing local temperature and barometric
+ * pressurs to remote observations.
+ *
+ * Format:
+ *
+ * Date     Difference    # legible date
+ *
+ * Greg Lehey, 17 December 2009
+ *
+ * FIXME.  This needs rethinking.  In particular, startup parameters are a mess.
+ * Read on, brave programmer.
+ *
+ * $Id: local-compare.c,v 1.5 2010/02/07 03:38:26 grog Exp $
+ */
+
+#include "wh1080.h"
+#include <syslog.h>
+
+#define STAMPSIZE 32                            /* size of time stamp to print out */
+
+char *stations [] = {"94852",                   /* Ballarat airport */
+                     "94863",                   /* Sheoaks */
+                     "94866"};                  /* Melbourne airport */
+
+const int station_count = sizeof (stations) / sizeof (char *);
+
+/* MySQL stuff */
+MYSQL *mysql;
+MYSQL_RES *mysql_result_BoM;
+MYSQL_RES *mysql_result_local;
+MYSQL_ROW mysql_row_BoM;
+MYSQL_ROW mysql_row_local;
+char mysql_querytext [1024];                    /* build up queries here */
+
+/* Command line options */
+int debug;                                      /* -d */
+int verbose;                                    /* -v */
+
+/*
+ * Temperatures for calculation:  two pairs of BoM time and temperature,
+ * and one time and temperature between them for local.
+ */
+time_t interval_starttime;
+float interval_starttemp;
+time_t interval_endtime;
+float interval_endtemp;
+time_t local_time;
+float local_temp;
+float interval_temp;                            /* interpolated BoM temperature */
+/* barometric pressure stuff */
+float interval_startpressure;
+float interval_endpressure;
+float interval_pressure;                        /* interpolated BoM pressure */
+float local_pressure;
+
+/* XXX get time zone in here */
+const int my2k = 946731600;                     /* difference between UNIX and GNU epochs  */
+char *startdate_text;                           /* start date, YYYY-MM-DD */
+char *enddate_text;                             /* and end date (default: 24 hours later) */
+/* Same times in struct tm format */
+struct tm startdate;
+struct tm enddate;
+time_t endtime_t;
+
+void usage (char *myname)
+{
+  fprintf (stderr,
+           "Usage: %s YYYY-MM-DD YYYY-MM-DD [-d] [-v] [station-id] [user] [passwd] [host] [db]\n",
+           myname);
+  exit (1);
+}
+
+/*
+ * Make files with comparative data for temperatures.
+ *
+ * The remote observations are relatively infrequent, while the local
+ * observations are frequent.  We interpolate the "remote" values linearly to
+ * estimate the temperature at the time of a local observation, but it doesn't
+ * make sense to do that for all local observations.  Instead, we just take the
+ * one immediately after the remote observation, then skip to the next remote
+ * observation.
+ */
+void mktempcompare (char *stationid)
+{
+  char filename [PATH_MAX];
+  FILE *myfile;                                 /* output file */
+  int finishing = 0;                            /* set to exit loop */
+
+  /* Create a file name for this comparison */
+  strcpy (filename, config.comparefile);
+  strcat (filename, "-");
+  strcat (filename, stationid);
+  myfile = fopen (filename, "w");
+  if (! myfile)
+  {
+    syslog (LOG_ERR | LOG_DAEMON,
+            "Can't open %s: %s (%d)\n",
+            filename,
+            strerror (errno),
+            errno );
+    exit (1);
+  }
+  chmod (filename, 0666);                       /* let anybody access it */
+  sprintf (mysql_querytext,
+           "SELECT unix_timestamp(timestamp(date, time)), outside_temp\n"
+           "FROM remote_observations\n"
+           "WHERE station_id = '%s'\n"
+           "  AND date >= '%s'\n"
+           "  AND date <= '%s'\n"
+           "ORDER BY date, time;\n",
+           stationid,
+           startdate_text,
+           enddate_text );
+  if (domyquery (mysql_querytext, &mysql_result_BoM))
+    exit (1);                                   /* failed and reported */
+  if (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM)) /* got something */
+  {
+    interval_starttime = atol (mysql_row_BoM [0]);
+    interval_starttemp = atof (mysql_row_BoM [1]);
+    /* Next row for first interval */
+    if (! (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM))) /* got something ?*/
+      exit (1);                                 /* no */
+    interval_endtime = atol (mysql_row_BoM [0]);
+    interval_endtemp = atof (mysql_row_BoM [1]);
+
+    /*
+     * Now we have an initial interval.  Get the first local record after start
+     * of interval.
+     */
+    sprintf (mysql_querytext,
+             "SELECT unix_timestamp(timestamp(date, time)), outside_temp\n"
+             "FROM %s\n"
+             "WHERE station_id = '%s'\n"
+             "  AND date >= '%s'\n"
+             "  AND date <= '%s'\n"
+             "ORDER BY date, time;\n",
+             config.db_table,
+             config.station_id,
+             startdate_text,
+             enddate_text );
+    if (domyquery (mysql_querytext, &mysql_result_local))
+      exit (1);                                 /* failed and reported */
+    while (mysql_row_local = mysql_fetch_row (mysql_result_local)) /* got something */
+    {
+      local_time = atol (mysql_row_local [0]);
+      local_temp = atof (mysql_row_local [1]);
+      if (local_time > interval_starttime)      /* we're in business */
+      {
+        while (local_time > interval_endtime)   /* beyond current BoM readings */
+        {
+          interval_starttime = interval_endtime;
+          interval_starttemp = interval_endtemp;
+          /* Next row for first interval */
+          if (! (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM))) /* finished? */
+          {
+            fclose (myfile);
+            return;
+          }
+          interval_endtime = atol (mysql_row_BoM [0]);
+          interval_endtemp = atof (mysql_row_BoM [1]);
+        }
+
+        /*
+         * Now our local reading is between start and end times.  Interpolate
+         * to get BoM temperature at this time.
+         */
+        interval_temp = interval_starttemp
+          + (interval_endtemp - interval_starttemp)
+          * (local_time - interval_starttime) / (interval_endtime - interval_starttime);
+        fprintf (myfile,
+                 "%d %d %2.1f %2.1f %2.1f # %s",
+                 local_time,
+                 local_time - my2k,
+                 local_temp,
+                 interval_temp,
+                 interval_temp - local_temp,
+                 ctime (&local_time) );
+
+        if (finishing)
+        {
+          fclose (myfile);
+          return;
+        }
+        /* Next remote row */
+        interval_starttime = interval_endtime;
+        interval_starttemp = interval_endtemp;
+        if (! (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM))) /* finished? */
+          finishing = 1;
+        else
+        {
+          interval_endtime = atol (mysql_row_BoM [0]);
+          interval_endtemp = atof (mysql_row_BoM [1]);
+        }
+      }
+    }
+  }
+}
+
+/*
+ * Make files with comparative data for barometric pressures.
+ */
+void mkpresscompare (char *stationid)
+{
+  char filename [PATH_MAX];
+  FILE *myfile;                                 /* output file */
+  int finishing = 0;                            /* set to exit loop */
+
+  /* Create a file name for this comparison */
+  strcpy (filename, config.comparefile);
+  strcat (filename, "-pressure-");
+  strcat (filename, stationid);
+  myfile = fopen (filename, "w");
+  if (! myfile)
+  {
+    syslog (LOG_ERR | LOG_DAEMON,
+            "Can't open %s: %s (%d)\n",
+            filename,
+            strerror (errno),
+            errno );
+    exit (1);
+  }
+  chmod (filename, 0666);                       /* let anybody access it */
+  sprintf (mysql_querytext,
+           "SELECT unix_timestamp(timestamp(date, time)), pressure_msl\n"
+           "FROM remote_observations\n"
+           "WHERE station_id = '%s'\n"
+           "  AND date >= '%s'\n"
+           "  AND date <= '%s'\n"
+           "  AND pressure_msl IS NOT NULL\n"
+           "ORDER BY date, time;\n",
+           stationid,
+           startdate_text,
+           enddate_text );
+  if (domyquery (mysql_querytext, &mysql_result_BoM))
+    exit (1);                                   /* failed and reported */
+  if (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM)) /* got something */
+  {
+    interval_starttime = atol (mysql_row_BoM [0]);
+    interval_startpressure = atof (mysql_row_BoM [1]);
+
+    /*
+     * Next row for first interval.
+     *
+     * The BoM doesn't issue this information very frequently, so we'll accept a
+     * single reading if we have to.  In this case, we just pretend that the
+     * pressure is constant.
+     */
+    if (! (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM))) /* got something ?*/
+    {
+      finishing = 1;                            /* no, just do one more result */
+      interval_endtime = interval_starttime + 100;
+      interval_endpressure = interval_startpressure;
+    }
+    else
+    {
+      interval_endtime = atol (mysql_row_BoM [0]);
+      interval_endpressure = atof (mysql_row_BoM [1]);
+    }
+
+    /*
+     * Now we have an initial interval.  Get the first local record after start
+     * of interval.
+     */
+    sprintf (mysql_querytext,
+             "SELECT unix_timestamp(timestamp(date, time)), pressure_msl\n"
+             "FROM %s\n"
+             "WHERE station_id = '%s'\n"
+             "  AND date >= '%s'\n"
+             "  AND date <= '%s'\n"
+             "  AND pressure_msl IS NOT NULL\n"
+             "ORDER BY date, time;\n",
+             config.db_table,
+             config.station_id,
+             startdate_text,
+             enddate_text );
+    if (domyquery (mysql_querytext, &mysql_result_local))
+      exit (1);                                 /* failed and reported */
+    while (mysql_row_local = mysql_fetch_row (mysql_result_local)) /* got something */
+    {
+      local_time = atol (mysql_row_local [0]);
+      local_pressure = atof (mysql_row_local [1]);
+      if (local_time > interval_starttime)      /* we're in business */
+      {
+        /*
+         * Unlike with temperatures, we only look at the first pressure reading
+         * newer than the BoM reading.  We have no way of knowing if the BoM has
+         * similar changes to what we have locally.
+         */
+        while (local_time > interval_endtime)   /* beyond current BoM readings */
+        {
+          interval_starttime = interval_endtime;
+          interval_startpressure = interval_endpressure;
+          /* Next row for first interval */
+          if (! (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM))) /* finished? */
+          {
+            finishing = 1;
+            interval_endtime += 1000;           /* to avoid division by 0 */
+          }
+          else
+          {
+            interval_endtime = atol (mysql_row_BoM [0]);
+            interval_endpressure = atof (mysql_row_BoM [1]);
+          }
+        }
+
+        /*
+         * Now our local reading is between start and end times.  Interpolate
+         * to get BoM pressure at this time.
+         */
+        interval_pressure = interval_startpressure
+          + (interval_endpressure - interval_startpressure)
+          * (local_time - interval_starttime) / (interval_endtime - interval_starttime);
+        fprintf (myfile,
+                 "%d %d %2.1f %2.1f %2.1f # %s",
+                 local_time,
+                 local_time - my2k,
+                 local_pressure,
+                 interval_pressure,
+                 interval_pressure - local_pressure,
+                 ctime (&local_time) );
+        local_time = interval_endtime + 1;      /* force the next record */
+
+        if (finishing)
+        {
+          fclose (myfile);
+          return;
+        }
+        /* Next remote row */
+        interval_starttime = interval_endtime;
+        interval_starttemp = interval_endtemp;
+        if (! (mysql_row_BoM = mysql_fetch_row (mysql_result_BoM))) /* finished? */
+          finishing = 1;
+        else
+        {
+          interval_endtime = atol (mysql_row_BoM [0]);
+          interval_endtemp = atof (mysql_row_BoM [1]);
+        }
+      }
+    }
+  }
+  fclose (myfile);
+}
+
+int main (int argc, char *argv [])
+{
+  int station;
+  /*
+   * This is tacky.  We want to use the standard parameter sequence in
+   * read_config (), but that doesn't include dates.  So we require the sequence
+   * in usage ().  And that means that we need to find a way to specify the end
+   * day.  FIXME.
+   */
+
+  if (argc < 3)                                 /* we need at least start date */
+    usage (argv [0]);
+  startdate_text = argv [1];                    /* start and */
+  enddate_text = argv [1];                      /* end date */
+  read_config (argc - 2, &argv [2]);            /* this implies that we know argv [0] won't be used */
+
+  mysql = mysql_init (mysql);
+  if (! mysql)
+  {
+    syslog (LOG_ERR | LOG_DAEMON, "Can't initialize MySQL: insufficient memory\n");
+    exit (1);
+  }
+  if (! mysql_real_connect (mysql,              /* DB state */
+                            config.db_host,     /* database host */
+                            config.db_user,
+                            config.db_passwd,
+                            config.db,
+                            0,
+                            NULL,
+                            0 ))
+  {
+    syslog (LOG_ERR | LOG_DAEMON,
+            "Can't connect to databaase: %s (%d)\n",
+            mysql_error (mysql),
+            mysql_errno (mysql) );
+    exit (1);
+  }
+
+  /* Generate the files */
+  for (station = 0; station < station_count; station++)
+  {
+    mktempcompare (stations [station]);
+    mkpresscompare (stations [station]);
+  }
+  exit (0);
+}