Month: July 2016

In the previous post I talked about using JFreeChart to create a plot of the last 8 hours.  I have never been satisfied with only 8 hours but had never spent the time to fix it.  To make matters worse I also wanted to have a website where I could see a list of the the flood events as well as plots of that data.  Well, this week I am going remedy this annoyance, and while I am at it, I am going to fix a bunch of things that I left hanging.

The Elkhorn Creek can do some crazy things.  Here is a plot that I made while I was working on these changes.  This was a spring flood that lasted 304 hours aka 12.6 days:

As there are more than 1M records in my database I knew that the Raspberry Pi didn’t have the horsepower to scan the data and create the charts in real time.  The first step was to create a table to hold statistics about the events.  I decided to store a timestamp for the start and end as well as the maximum depth.  Here is the table description:

The next step was to create a java program called “ProcessEvents.java” that would analyze the raw data in the creekdata table using the “known” events from the floodevents table.  The first thing that I did was create a helper class called StartEnd that hold one record from the floodevents table.  The lowest “id” in the floodevents table is 1 so the 0 default for id represents a record that is not yet in the database.

The program works by implementing the following algorithm.

1. Line 52: set a flag that you have not found an event
2. Line 53: scan the floodevent database
3. Line 58: If you didn’t find an event or there is an event ongoing (it has a start but no end)
4. Line 60: If there has never been an event then start scanning for a “start” the creekdatase from the start (all data)
5. Line 63: If there has been an event, then start scanning for a “start” the creekdata after the end of the current event
6. Line 66: If you don’t find a “start” then there are no more events (or you didnt find one at all) so stop scanning
7. Line 71: Now that you have a start, look for an “end”
8. Line 75: If you found a new event then save it in the database
9. Line 79: If you are in the middle of an event update the database

Once all of the events have been found and put into the floodevents database the last step is to look at all of the events and fix the “max” depth column [Line 84]

The “findStart” function returns a timestamp for the start of the next event.  If it doesn’t find one then it return null.  The function looks through all of the creek data starting from the beginning [line 149] or after a time [line 151].  My program has a little bit of hysteresis in that it “starts” a flood when it get to 2.0 feet and it “ends” a flood when it gets below 0.75 feet.  If there is a little bit of noise around the upper trip point or the lower trip point it won’t turn on/off.

The “findEnd” function returns a timestamp for the end of the current event.  If it doesn’t find one then it return null.

The “calcAndInsertMaxDepth” function:

• Sets up a query to scans all of the events in the floodevents database [line 253]
• Loop through all of the events [line 259]
• Foreach event find the maximum depth [line 265]
• Update the table with the current maximum depth [line 266]

The “getMaxDepthFunction” function uses a feature of mysql that allows you to perform a function during a query.  In this case it looks for all of the data between a start and end and calculates the “max(depth)”.  This query will return only one resultset.  I get the maximum depth value from the result set using the “rs.getFloat(“max(depth)”)”

The “depthUpdate” function takes in an “id” of one of the events and a calculated “maxDepth” then updates the “max” field for that id in the floodevents database.

All of this code is available on the iotexpert github site in the java program “ProcessEvents.java” which is in the “getCreek/src” directory.

In the next post I will talk about all of the modifications to the chart creation program that I made to support creating the flood events charts.

 

It is almost always better to see data in a plot than as raw values.  I knew that I wanted to be able to see the Elkhorn Creek Water Level.

The charting software that I am using has been done and re-done several times (including as I prepared this post).  I was committed to using Java and I wanted to find a toolkit for building charts.  After searching around on the internet a bit, I settled on JFreeChart which is an open source Java based plotting package.  I like to contribute to the good work of people so I bought the developers guide for $65 and it was well worth it.

I knew that creating a chart would take a bit of CPU and that the Raspberry Pi was a bit under powered as far as that goes.  This lead me to create a batch program which I run via crontab directly after I have collected the data (once per minute).  It appears to the web user that the chart is “fresh”, but really it is up to 1 minute old.

The “makeChart” program is broken up into the following parts

• Setup the global settings
• Read and process the command line arguments
• Read in x-y data from the MySql Database
• Make the chart

Setup the global settings

The first block of code (lines 2-16) just imports the different classes that I use in this program.  To make the org.jfree stuff work you need to have jcommon-1.0.16.jar and jfreechart-1.0.13.jar in your class path.  You can download them from the source forge project directory https://sourceforge.net/projects/jfreechart/files/

The second block of code (lines 20-26) setup global configuration variables which I declare as “final” so that I know that they are immutable.  It probably would have been better to make the filename be configurable with the command line arguments, but that is something for another day.  Publishing the MySql User and Password would be a problem except, that user can only access the MySql database from inside my network.

The last block of code (lines 32-40) just prints out the command like usage if they program is run with the “-help” argument.

Read and process the command line arguments

The next step is setting up the Start date/time and the number of hours.  Java has a cool class called “LocalDateTime” which I use to represent the starting time of the data.  There is an absolutely mind boggling number of issues when you deal with dates and times.  I was to far down the road, but if I had it to do over again I would have used Joda Time.

If there is only 1 argument then there are two possibilities

Lines 44-60: The user did 1 command line argument

• They typed in a number of hours.  On line 47 if the argv[0] string converts to an Integer with no Exception being thrown then I assume that is a number of hours from the current time
• They typed in a date in the form of “yyyy-MM-dd” or “yyyy-MM-dd hh:mm:ss” or “yyyy-MM-dd hh:mm”.  If they typed 1 argument and it isn’t an integer then I called the “convertStringDateTime” function to try to parse the string.

Lines 65-80: The users did 2 command line arguments

• The first argument has to be a date and I covert it to the LocalDateTime class using the same “convertStringDateTime” function (line 67)
• The second argument has to be an integer number of hours and I just use the “Integer(String)” to convert it to a number.

If any of these steps fail, then the program exits.

If there are 0 arguments (line 82-85) then set the date to the current date/time and the hours to the global constant runTimeDefault

The next block of code takes the command line argument and trys to turn it into a date/time.  To do this I use the DateTimeFormatter class.  Basically I try three different date/time formats to see if I can find one that works.  If there is nothing that works I throw an Exception.

Read in x-y data from the MySql Database

This function sets up the SQL statement to read the data into a JDBCXYDataset class (from jfree.org) .  The JDBCDataset class can be passed to the charting program to create the plot of the data.  The function is pretty simple, it just takes the input date/time and number of hours.  Then creates an end date/time and embeds that information into the SQL statement.  Finally it runs the MySql query and returns the data.

Create the chart

The last step is to call the JFreeChart class to build the chart which is done on line 88-96.  Once the chart is created, I setup the axis to have a range of 0.0 –> maxDepthChart (If the water is 23 feet deep then my floor is just getting covered )  (lines 98-101).  And finally create the PNG file (line 103).

 

In the last post I went through all of the setup work for the I2CDB program.  Now I will give you the main course.  My program is divided into five functions

• main: a simple function that loads the configuration file, attaches to the I2C bus, reads the data from the I2C slave and saves it into the database.
• getI2CBus: uses the Pi4J library to attach to the I2C Master and initialize the two gobal variables bus1 and bus2.
• readProperties: reads the “config.properties” file containing the global configuration (like the database name, userid and password) as well as the I2C registers, addresses and names.
• insertStringDatabase: run the MySql insert function.
• i2readVariables: read the data from the I2C, setup the insert string, and call the insert function.  This (long) function is where all of the action is at.

I am sure that more could be done to handle exceptions, mostly do a few retrys, but I decided to just print out the error message.

main

This function is called when the i2cdb program is run by the “runi2c” shell script.  It simply

• reads in the properties file
• attaches to the Raspberry Pi i2c master
• reads the i2c slave
• saves the data into the MySql database
• print out an error message if there is some exception

readProperties

This function reads the “config.properties” file into the global class “prop”.  The Properties class handles key/value pairs and knows how to read a file of “key=value” lines.  You can make whatever keys you want, and they have whatever value you want.  Later in the program I use the Integer(String) constructor to convert the String “123” into the Integer 123.

insertStringDatabase

This function handles the interface to MySql:

• 159 Takes an input sql command formatted into a string like “insert into creekdata.creekdata (created_at,depth,temperature) values (“2016-04-10 13:16:43.261″,1.568,21.199991);”
• 163-165 Makes a connection to the “dburl” that it gets from the config.properties file. The URL specifies, which protocol (jdbc), which port and IP address and what database.  e.g “dburl=jdbc:mysql://192.168.15.82/creekdata”
• 167-169: Executes the insert command

getI2CBus

The Broadcom 2835 ARM controller is the main chip that runs the Raspberry Pi.  It is essentially a cell phone MCU.  As with most MCUs it has fixed function serial communication blocks embedded into the chip.  In this case the RPi device driver enumerates the I2C blocks as “0” and “1”.  I have no idea what block 0 is attached to.  Block 1 is attached to the GPIO pins on the RPi.  The geti2C function makes a connection to the two controllers.  If you are unable to attach to either of the controllers then I throw an exception.  Hopefully this will future proof this code for updated version of the RPi.

i2readVariables

Finally the function that does all of the work.  Big picture, this function:

• iterates over all of the number of variables specified by “i2vars”
  • Figures out the I2C Bus, Address, Register Address and # of bytes
  • Reads the bytes into a “buffer”
  • Converts the buffer to the correct endian-ness
  • Converts the buffer to the correct type (uint8, int8, uint16, int16, float,double)
  • saves the name of the MySql column into the insertNames[] array
  • saves the value of the variable into the insertVals[] array
• builds up a string with the MySQL “insert ….” command
• run the insert

The start of the i2readVariables (I really should have called this i2cReadVariables) gets things going

• I noticed that the I2C reads are not perfectly reliable and it sometimes takes several reads to get a successful read.  I believe that the Broadcom chip does not like to have its I2C clock stretched.  I use the variables I2CReadSuccess and I2CRetryCount to keep track of failures and to retry.
• Lines 51-52 declare arrays to hold the names of the MySql Columns and the Values to insert.  I declare them as “Object” so that they can hold Integers or Floats.
• Lines 56-60 look at the properties class to figure out the I2C Bus, I2C Slave Address, I2C Register #, the type of data, and the endinaness
• Lines 63-68 set the nbytes variable to the right number of bytes to read.  One sin that I perform in this code is to embed the property name (uint8, …) into the code.  Even worse I duplicated the name a few places.
• Line 70: declare a buffer of bytes to hold the data read from the I2C Slave

The next block of code reads the I2C Slave.

• Lines 71-81 attaches to the device on the correct I2C bus.  If there a problem it throws an exception which terminates this function.  Perhaps it would have been better to have a retry scheme, but I didn’t.
• Lines 83-84: Setup the retry and success variables.  The retry scheme counts down from the hardcoded 20.  When I was running tests I noticed that it may fail 2-3-4 times.
• Line 86: Sets up a loop that runs until
  • You have had a successful read as indicated by I2CReadSuccess == true
  • You have retried 20 times as indicated by I2CRetryCount == 0
• Line 88: Reads the I2C Slave
• Lines 91-97: If there is a failed read as indicated by an Exception being thrown, then sleep for 200ms and try again.
• Lines 101-104: If you fall out of the loop and haven’t had a succesfull read then throw an exception and exit the read process

The next block of code uses a cool class called “ByteBuffer” to help convert the individual bytes read from the I2C Slave into Java variables of the right type.

• Line 106 takes the array of bytes and turns it into a ByteBuffer
• Lines 108-111 look at the endian-ness setup in the config.properties and then convert the ByteBuffer to the correct endianness.  Originally I did this design using a PSoC3 which was Big Endian… but it now uses a PSoC4 which is little endian.
• Lines 113-118 convert the ByteBuffer to a value of the right type as specified by the config.properties file.  One trick is that Java does not like unsigned so the bitwise “and” is used to convert the signed values to unsigned.
• Line 119 saves the MySql column name of the variable

The last block of code builds up the MySql String and then calls the insert function

• Lines 122 & 126-129: If the user has specified that he wants a timestamp, then create that timestamp and add it to the MySql Insert statement
• Line 124: setup the MySql Statement template
• Lines 130-133 iterate through the column names and add them to the MySql Statement
• Lines 135-139 if they have asked for a timestamp then add that value to the insert statement
• Lines 141-143 iterate through all of the values and add them to the insert statement
• Lines 144-147: finish the MySql Statement, print it out and run it.

I like this program because I can create a new template file without having to change the program.  Basically I created a generic interface that can bridge from an I2CSlave that collects data and then write that data into the database.

In the next posts Ill show you the user interface web pages.