Ryan Herbst

MySql XPL Logger

The MySql logger software, xPL_Mysql, has a single function. It listens on the xPL network for sensor.basic and log.basic xPL messages. When these messages are received an entry is added into a table in the mysql database. This software is implemented using the xPL4Linux framework.

The majority of the received xPL messages are converted into database entires without modification. There are a couple of cases where I found it neccessary to modify the entires before they where added to the database. These modifications are described below:

Count Rate Detection:

My rain gauge provides two values which are converted into xPL messages by the rxfcom gateway daemon. These two values are the total running measurement of rainfall as well as the current rainfall rate. Both of these values are labeled “count” by the rfxcom interface software with the total running count having units of “mm” and the rainfall rate having units of “mmph”. I determined that it would be best to store these two values as seperate entires in the mysql database. So I put a feature in my mysql database gateway software to detect a variable with the type field “count” containg “ph” in the units field. When this situation is detected the type field is adjusted to be “count_rate”.This feature can be turned on or off in the configuration file.

Average Wind Speed Detection:

My anemometer provides two value for the current wind speed. The instantaneous gust value and an average wind speed value. The rxfcom gateway daemon returns both of these values with the type field of “speed”. The two values are always broadcast back to back with  the gust value followed immediately by the average value. My mysql database gateway has the ability to detect this back to back transmission of values and will label the second values as “speed_average” if this feature is enabled.

Configuration:

 The following xPL configuration variables are supported and can be updated using DCM or another xPL configuration manager:

  • mysqlHost = Mysql database hostname
  • mysqlUser = Username for mysql database access.
  • mysqlPass = Password for mysql database access.
  • mysqlDatabase = Database to use.
  • detectSpeedAverage = Enable average wind speed detection. (true/false)
  • detectCountRate = Enable counter rate detection. (true/false)
  • logdevice = File to which debug messages should be written. “/var/log/home/iodevice.log”
  • iodevice = Device file which can be used to access the Insteon Power Link controller. “/dev/ttyUSB2″
  • cfgfile = File containing the XML device descriptions. “iodevice.xml”

Database Tables:

log_basic

This database table contains all of the log.basic xpl messages seen on the xpl network. My XPL gateways will generate a log.basic message anytime a device changes state and when the software status changes or an error is detected.

The table contains the following fields:

  • id – int: Serial ID of the table entry
  • time – datetime: Timestamp of the log.basic message.
  • type – varchar(24): Type field from the log.basic xpl message.
  • text – varchar(128): Text contents from the log.basic xpl message.
  • code – varchar(20): Event specific code from the log.basic xpl message.

sensor_basic

This database table contains all of the sensor.basic messages seen on the xpl network. This table is dominated by weather station information in my home automation system. Since this table contains every sensor.basic message seen on the xpl network it fills up with a large number of entries very quickly. A python script is run periodically to sparsify the data in this table into 15 minute blocks which are stored in the sensor_basic_15min table described later on this page. Once sparsified, entries in this table will be deleted after 30 days.

The sensor_basic table contains the following fields:

  • id – int: Serial ID of the table entry
  • time – datetime: Timestamp of the log.basic message.
  • type – varchar(50): Type field from the received sensor.basic xpl message. (temp, humidity, pressure, etc).
  • device – varchar(50): Device field from the received sensor.basix xpl message. (outdoor, rain, bedroom, indoor, etc).
  • current – float: Current value from sensor.basic message converted to a float value.
  • current_string – varchar(50): Current value from sensor.basic.
  • units – varchar(20): Units field from sensor.basic message. (mm, c, hpa, etc).
  • lowest – float: Lowest field from sensor.basic message converted to float (None of my sensors generate this field)
  • highest – float: Highest field from sensor.basic message converted to float (None of my sensors generate this field)
  • sparsed – tinyint(1): Flag which is set when this field has been sparsified. (See sensor_basic_15min table below)

sensor_basic_15min

This database table contains a compacted representation of the data contained in the sensor_basic database table. A python script (sparse_15min.py) periodically collects data in the sensor_basic table and converts 15 minutes of data into an entry in this table.The python script will automatically generate a table entry for each unique type-device combination found in the sensor_basic table.

Representing sensor data in 15 minute intervals allows multiple years of sensor data to be stored without bloating the mysql database. Currently my sensor_basic_15min table contains 5 years of sensor data and consumes ~150MB of disk space. By comparison 30 days of raw sensor_basic data consumes ~500MB of disk space.

The sensor_basic_15min table contains the following fields:

  • id – int: Serial ID of the table entry
  • time – datetime: Timestamp of the start of the 15 minute interval.
  • type – varchar(50): Type field from the sensor_basic table.
  • device – varchar(50): Device field from the sensor_basic table.
  • units – varchar(20): Units field from sensor_basic table.
  • lowest – float: Value of the lowest “lowest” field from sensor_basic table over the 15 minute interval.
  • highest – float: Value of the highest “highest” field from sensor_basic table over the 15 minute interval.
  • current_string – varchar(50): Value contained in the current_string field from the last sensor_basic record in the 15 minute interval.
  • first – float: “current” field from sensor_basic table at beginning of the 15 minute interval.
  • last – float: “current” field from sensor_basic table at the end of the 15 minute interval.
  • average – float: Average value of the “current” field over the 15 minute interval.
  • min – float: Minimum value of the “current” field in the 15 minute interval.
  • max – float: Maximum value of the “current” field in the 15 minute interval.
  • min_time – time: Timestamp of the sensor_basic record associated with the minimum “current” value.
  • max_time – time: Timestamp of the sensor_basic record associated with the maximum “current” value.
  • sparsed – tinyint(1): Flag which is set when this field has been sparsified. (See sensor_basic_day table below)

sensor_basic_day

This database table is similar to the sensor_basic_15min table except it contains information about a given sensor over a 24 hour period. The python script sparse_day.py will process all of the sensor_basic_15min records for a 24 hour period to fill an entry in this table. Although the sensor_basic_15min already contains all of the information found in this table, this table allows speedy access to the same data summarized over a 24 hour period.

The sensor_basic_day table contains the following fields:

  • id – int: Serial ID of the table entry
  • time – datetime: Timestamp of the start of the 24 hour interval.
  • type – varchar(50): Type field from the sensor_basic table.
  • device – varchar(50): Device field from the sensor_basic table.
  • units – varchar(20): Units field from sensor_basic table.
  • lowest – float: Value of the lowest “lowest” field from sensor_basic table over the  24 hour interval.
  • highest – float: Value of the highest “highest” field from sensor_basic table over the  24 hour interval.
  • current_string – varchar(50): Value contained in the current_string field from the last sensor_basic record in the  24 hour interval.
  • first – float: “current” field from sensor_basic table at beginning of the  24 hour interval.
  • last – float: “current” field from sensor_basic table at the end of the  24 hour interval.
  • average – float: Average value of the “current” field over the  24 hour interval.
  • min – float: Minimum value of the “current” field in the  24 hour interval.
  • max – float: Maximum value of the “current” field in the  24 hour interval.
  • min_time – time: Timestamp of the sensor_basic record associated with the minimum “current” value.
  • max_time – time: Timestamp of the sensor_basic record associated with the maximum “current” value.

Scripts:

  • sparse_15min.py – Convert sensor_basic records into sensor_basic_15min.
  • sparse_day.py – Convert sensor_basic_15min records into sensor_basic_day.

Email me if you have any questions or are interested in the software.

Amaroq Weather Station

Beresford Ave, Redwood City, CA
temp: 55.9 F (13.3 C) (13.3)
humidity: 73%
wind: Calm
pressure: 29.98" (1015.1 mb)
station: KCAREDWO4
hardware: WMR918
updated:June 29, 6:59 AM PDT
local forecast