Repair the Elkhorn Creek Water Level Sensor

Summary

As many of you have noticed, www.elkhorn-creek.org has been offline for quite a while (actually since December 22,2018).  I know that many of you have really missed knowing how much water is in the creek.  Given that it blew up in December, and I knew that I was going to have to crawl into the muddy creek to fix it, I had not gotten around to it.  But, now it is July and the water is nice, so I don’t have much of an excuse.  I have written extensively about my system, in fact, it was my first real IoT project.  I assumed that it would not be that hard to fix.  Boy was I wrong.

In this article I will:

  • Get the Sensor out of the Elkhorn Creek & Debug
  • Update the PSoC Project to use a Different GPIO
  • Blow up the CyPi Power Supply
  • Debug the Wrong Pressure Sensor
  • Turn the Raspberry Pi Back on and Retest

Get the Sensor out of the Creek and Debug

I felt like Stanley in the Congo heading down the path to the Elkhorn Creek.

But, when you get there, look how beautiful it is.

Here is a picture looking down at the sensor setup from the top of the bank (that is a 6 foot ladder, so that bank is something like 10 feet high)

When you slide down the bank onto the ladder, this is where you end up.  After I jumped off the ladder into the creek, I was in mud up to my knees.  I had assumed that the problem was that water had leaked around sensor NPT connection.  You can see the drain valve that I installed to drain water out of the pipe for just that case.  When I opened the valve, there was no water… like none. This made me start to wonder what was going on.  The sensor is installed in the end of that square clear out.

When I undid the sensor and brought it up into my lab, there was no leaking around the sensor.  My original theory that the sensor had gotten water onto the dry side was incorrect.

So, I plugged the sensor in on my bench to try to figure out what was going on.

The first measurement I took was across the 51.1 ohm sensor resistor.  Last time I checked, V=IR, so this means that it is drawing 133mA.  That is bad given that it is a 4-20mA current loop.

It is also bad to put 6.81V onto a PSoC pin.  When I measure the voltage at the pin it is 0.003 V.  Dead.  At this point, I suspect that whatever killed the sensor also killed the PSoC, but I don’t know.  I suppose that the sensor could have blown up (maybe an ESD event) and then when the voltage went to 6.81, it blew up the PSoC?  I suppose that I will never know.

In fact, when I connect the power supply directly to the A0/P2[0] analog input, I get 0.023A … which means that I also blew up the GPIO and it is now shorted to ground.  Well, actually it isn’t a short, it is more like 43 Ohm resistor.  Bad.

Here is the spec from the PSoC 4 data sheet.  2mA is a long long way from 2nA.

Unfortunately, this is the only CyPi board that I have.  Moreover, I don’t have another PSoC4 chip to fix it with (or at least at my house right now).  So, I decided that I will assume that the other pins are OK and I will use PSoC Creator to move to another pin (A2) that hopefully isn’t blown up.  To do this, I snip off the Arduino pin, then solder a jumper on the top from A0 to A2.

Update the PSoC Project to use a Different GPIO

Next, I have to fix the firmware to use A2 instead of A0.  This is AKA P2[2] instead of P2[0].  When I open up the workspace in PSoC Creator, it immediately starts complaining about components that are old.  Notice that all of the “dwr’s” which are opened have an asterisk indicating they have changed.  In order to fix this I need to update all of the components.

Starting with the boot loader project called “p4bootloader”, right click and select “Update Components…”

Notice that eight of the components in the project are old.  Select Next.

Then turn off the “create a workspace archive before updating option”.  My project is in Git so I don’t need to save it in case something bad happens.  Then click Finish.

And after a minute I can Build the project.

Next I update the main project which is called “p4arduino-creek”

Follow the same process as before:

In this case I forget to click the archive button, but I can cancel the archive.

Once the update is done, look at the schematic.  The first thing that I notice is that I called the pressure input “high side”.  I hate the name high side… so I am going to fix it to be called pressure

Double click the pin and change it to “pressure”

Because the boot loader hex changed, I need to update the reference in the bootloadable component.  Double click it and correct the path.

Notice that it has a new version of the compiler.

Then reassign the pressure pin to be P2[2] which is also known as A2

Then rebuild and notice that everything is OK.

Once I reprogram the board, I take it outside to reinstall the whole mess.  After I hook it up I start probing with the multimeter and immediately short out the power supply with one of the multi-meter probes.

Blow up the CyPi Power Supply

Which blows up the REG1117.  Here is an animated GIF where you can see that it turns on.. then immediately goes off.  This is more than a little bit annoying.

Fortunately, I have my original CyPi prototype.  So, I go back to the prototype CyPi – which means that I have to use two power supply connections.  One of the things that I fixed in the final CyPI was to have the ability to drive the Raspberry Pi with the 12V input (I have ordered a new REG 1117).

Debug the Wrong Pressure Sensor

When I install everything, an unbelievably frustrating thing happens. I put the probe on the sensor and I immediately measure 1.007V  which is 19.7 mA which is also known as 14.7346 PSI.  This is seriously bad.  I have been standing in mud up to my knees fixing the damn system and it is already broken again.  I should be measure 4mA*51.ohm = .202V.  But no.  This was a deeply frustrating moment because I assumed that something else is wrong with my system.

When I got back inside and tried to figure out what in the world was happening, I thought, maybe the pressure sensor is clogged or something?  This made me wonder what the air pressure in Kentucky at that moment was.  After a little bit of google I find that the air pressure is 30 inches of Hg… which turns out to be … guess… 14.7 PSI.  I knew immediately that I purchased the WRONG DAMN SENSOR.

Measurement Specialties makes the US381 in both Gauge and Absolute pressure.  In the Absolute case, it gives you the pressure with a reference to a vacuum.  In the gauge case it gives you a relative measurement.  The back side of this pressure sensor is exposed to the air, which lets it cancel out atmospheric pressure changes.  But I bought US381-000005-015PA instead of the correct US381-000005-015PG.


And, a few days later Mouser delivered me the correct sensor, and after and hour of mud and sweat I had things ready to try again.

Turn the Raspberry Pi Back on and Retest

After re-installing everything in the barn, I now get .202V across the 51.1 Ohm Sensor Resistor, which means that I am getting exactly 4mA.  That makes perfect sense as right now the pressure sensor is just exposed to the air. (meaning it is sticking out of the water)

And now www.elkhorn-creek.org is working again.  Good.

RS Components & the Elkhorn Creek

Summary

Earlier this year I hosted RS Components at my house in Kentucky.  Here are three videos that they recorded about the Elkhorn Creek Water Level System.  Whoever edited them knew what they were doing.  Thanks to RS Components.

Part 1

Part 2

Part 3

 

The Creek: Creek Server 1.2

In the previous post, I talked about two problems that I had discovered in my system.  I addressed first one,  a serious bug in the data collection, last time.  In this post Ill address the slow performance of the website.  A common way to solve a performance problem is with a cache.  Instead of running the web page charts in real time, I will create them automatically every couple of minutes, then serve up a cached version.   In some ways this kind of sucks.  If I had known that I was going to go this way then there would have been 0 need of Tomcat and the Java Server Pages.  Oh well.

I start this process by copying the CreekHistory java object into the “getCreek” part of my project.  I then modify the main batch Java program, called “CreekServer” to understand the CreekHistory object.

Screen Shot 2016-05-08 at 11.19.48 AM

Then I add a new function “createHtml” to the CreekHistory object.  This function creates the webpage with the current information on it.  Basically

  1. A table of the last three hours [line 221-235]
  2. Link to the current chart [line 237]
  3. Link to the floods charts [line 237/238]

Screen Shot 2016-05-08 at 11.26.31 AM

I then add the “Current” command to the “runi2c” batch program.

Screen Shot 2016-05-08 at 11.39.43 AM

This program is run every 2 minutes by the crontab on the Raspberry Pi.  I picked 2 minutes because that is a good bit longer than each job takes to run in total.   Notice that I check to see if the jobs are already running and quit the “runi2c” job if the chart creation process is already going on.

At this point I have (at least) several things which are ugly

  1. I don’t have a good back up system
  2. I duplicate the “CreekHistory” object in the JSP as well as the “getCreek”
  3. I duplicate the “readProperties” method into a bunch of different objects
  4. I have 5Vs (I think) on the I2C pins of the Raspberry Pi, which I don’t understand why this isn’t a problem
  5. I need to get the PSoC4 Bootloader Host going on the Raspberry Pi

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Web Pages with Java Server Pages (JSP)

The last piece in the Elkhorn Creek IOT puzzle are the webpages that let you view the creek data on the internet.  There are two pages, one with a table of the data and a chart and the other a dump CSV file of the database.  When it came time to work on this post, I realized that I had little memory of the evils deeds I had originally done to make the JSP pages work.  As I sorted through the code that was existing on the current production Raspberry Pi environment, all I could see was an undocumented mess.

Making dynamic web pages is ugly business.  As best I can tell, there is no clean way do this job (though I am planning on showing some ways to make it better in future posts).  A JSP is an interleaved mashup of HTML and Java.  To make a JSP, you create a file with sections of both languages separated by markers.   In the following example you can see an example that rolls two dice (generates a random number between 1-6).  The sections of code that are Java are marked with a “<%” and a “%>”.

<html>
<head><title>Roll the dice</title></head>
<body>
<%
int die1 = (int)(Math.random() * 6 + 0.5);
int die2 = (int)(Math.random() * 6 + 0.5);
%>
<h2> Die1 = <%= die1 %></h2>
<h2> Die2 = <%= die2 %></h2>

<a href=”<%= request.getRequestURI() %>”><h3>Try Again</h3></a>
</body>
</html>

When a web request is made, the Tomcat JSP Webserver takes the page (in JSP format)

  1. Converts it to Java (turns raw HTMLs into java commands e.g. “<html>” turns into “out.println(“<html>”);”
  2. Runs the compiler
  3. Executes the java program and pipes the output back to your web browser

To makes things go faster Tomcat will cache the output of 1+2 and only rerun it if something has changed.

Since I last worked on this system there is one thing that had greatly improved.  Specifically, this time I knew about Netbeans which is the Sun Microsystems IDE for editing Java.  Netbeans is cool for this project because:

  • It knows about Java (so it knows how to edit, highlight, search, refactor, syntax check etc)
  • It knows about JSP (so it knows how to edit…)
  • It knows how to edit Java Properties files
  • It has a JSP web server (GlassFish) integrated into the tool.  This allowed me to debug the web pages on my Mac (instead of on the RPi)
  • It knows how to make “war” files (the file format that you use to deploy a JSP web application)

The Netbeans project navigator lets you look at (and into) all of the files that are part of the JSP Web Application.  In the screenshot below you can see the organization of my project.  It has

  • A classes folder which will contain the compiled java classes.  It can also hold a “properties” file.  In this case I have a properties file called “config.properties” which contains the URL, User and Password for the mysql database
  • basic.jsp: The JSP file which displays the table and graph
  • index.html: a file which just loads the basic.jsp
  • CreekHistory.java: A java class to interact with the MySql Database.  This is essentially the “Model” of the MVC
  • A libraries folder with mysql-connector-java-5.1.6-bin.jar: the mysql library to connect to the database

Screen Shot 2016-05-03 at 7.13.26 AM

I like to use the Model View Controller (MVC) design paradigm of user interface programming.  In this methodology the model represents the data.  It knows nothing about user interfaces, how to create them, how to display them.  It is completely agnostic about GUI.  The view is a mechanism to display data. In this JSP web application, the view is just the raw html that is displayed on the web.  The last piece is the controller (aka the view controller).  It takes data out of the model and puts it into the view.  Here is a nice picture from Apples website (but if you google you will find lots of discussion about MVC programming)

model_view_controller_2x

The first thing that I created was Java class called CreekHistory.java.  This class has the responsibility to read the last 4 hours of depth and temperature data from the MySql database and put it into 15 minute buckets (using linear interpolation), and store the results into an array for easy retrieval by the controller.

In the first block of code (lines 29-31) I declare a few private variable that hold information out of the properties file about the database connection. The I declare a bunch of public variables which are the data interface to the model.

  • depth[] + temperature[] arrays of doubles that contain the depth and temperature in 15 minute buckets
  • depthDelta[] and temperatureDelta[] doubles that contain the change in depth and temperature since the start aka [0]
  • startTime a timestamp of the first datapoint (from the MySql database)

In the next clock of code (lines 43-46) I declare some constants (so that I don’t have magic numbers in my code).

On line 48 I declare a reference to a JspWriter.  This reference will allow me to print debugging information to the HTML output.  This is a clear violation of the MVC so I only use it for printing debugging information as it is a serious PITA to debug JSP code.

And finally the constructor which calls the function to read the properties file and then initialize the arrays.

Screen Shot 2016-05-04 at 6.25.48 AM

In the next section of code I have three helper functions.

The first function “interpolate” just performs a linear interpolation between two datapoints given known timestamps.

The “cToF” function just converts the a temperature from the bogus Centigrade units to useable Fahrenheit.

The last function, “readProperties” finds the config.properties file and reads the values into the global Properties class.  The only magic in this function occurs on lines 194-195 where it uses the ClassLoader object to search through the “classes” directory to find the file with the properties.

 

Screen Shot 2016-05-04 at 6.32.46 AM

The last function is “loadData” which has two sections.  The first section sets up and executes the MySql query to get the current Elkhorn Creek data.

Screen Shot 2016-05-04 at 6.40.44 AM

Once it has the data, it iterates through the data from the MySql database to find the datapoints that bracket the 15 minute buckets, then interpolates the results, then store it in the array.

Screen Shot 2016-05-04 at 6.51.08 AM

Finally the last part of the web server is the JSP page which outputs the actual HTML.

Lines 1-2 load two Java classes which I use on other sections of the page.  Specifically the Timestamp class which I use to do Time math, and the CreekHistory class which is the “model”

Lines 15-18 initialize the CreekHistory model object and tell it to load its data.  Notice on line 16 if I set the CreekHistory “out” public variable then it will put out debugging information.

Lines 24-37 just spit out an HTML table of the data from the CreekHistory model.

Screen Shot 2016-05-04 at 7.04.07 AM

That is all there is to it.  As with all of the code on this website you can get it from github.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Creek Server 1.1

The last few weeks I have been building up the java programs that form the “back office” part of my system.  This is composed of

  • i2cb.java: A program that reads I2C registers of the Creek Depth and Temperature and inserts them into the creekdata database.
  • MakeChart.java: A program to create a PNG graph of Elkhorn Creek depth using JFreeChart
  • ProcessEvents.java: A program that searches the creekdata for “flooding events” and then inserts them into the flood event database.

Things worked OK, but I had several problems that I wanted to fix

  1. The MakeChart program was not using global properties file so I had duplicated things like the database password
  2. I had implemented myself into a design flaw (I wanted to chain information from i2cdb –> MakeChart –> ProcessEvents –> MakeChart
  3. I really wanted a single Java program with options to run all of the back office

The bottom line is this week will be spent fixing the design flaws and cleaning up the code.

The first thing that I did was create a new Java program called “CreekServer” which hooks all of the programs together and provides a unified command line interface.  This program looks at the first command line argument and decides to either:

  • Lines 12-15: print the help text
  • Lines 17-23: run the “MakeChart” function
  • Lines 24-29: run the ProcessEvents function, then update/create all of the historical flood charts [line 27]
  • Lines 30-34: run the GetData function

Screen Shot 2016-04-17 at 11.09.49 AM

The next block of code is a kludgey work around for my design flaw.  Specifically, after you have create a table full of the flood events you would like to create charts for each event.  However, I didn’t want to duplicate the chart object into the ProcessEvents class, so I decided to hack it into the system by adding a public interface to the ProcessEvents object.

  • [line 52] I tell the ProcessEvents object to read the floodevents table
  • [line 57-75] I iterate through all of the flood events that are in the public array of events (bad design)
  • [line 58-60] create the appropriate filename for the plot
  • [line 63] if the file doesnt exist (it was previously created) or the flood is ongoing (it has no end date) then create a new chart by calling the MakeChart object

Screen Shot 2016-04-17 at 11.32.27 AM

After I got done with the main program, I went back and fixed the ProcessEvents program to read its properties from the “config.properties” file.

Screen Shot 2016-04-17 at 11.49.54 AM

Finally, I fixed the properties problem in the MakeChart program:

Screen Shot 2016-04-17 at 12.02.08 PM

All of this code is available on the iotexpert github.

In the next post I am finally going to move into the JSP and Servlet code that builds the main site.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

The Creek: Flood Event Web Page

In the previous post I talked about scanning through the Elkhorn Creek MySql database and finding flood events.  This is a very IO intensive task and as such is slower than crap.  It takes something like 20 seconds on the Raspberry Pi and as such is not very ideal for an interactive application.  So to make this better, I will create code that runs once per minute and then creates the “floods.html” with all a table of all of the floods and links to the charts.  It will also recreate the chart for any flood event that is in the database but doesn’t have a chart.

After the “ProcessEvents” run method has done its thing, there is a table in the database called “floodevents” that will have columns for the start,end and maxdepth of all of the floods.   To make things easy I would like a directory full of PNG files with the charts of the floods.  Each PNG file will contain one flood event and be named by the date of the flood.  To do this I create a function called “createCharts” that will:

  • [Line 71] Iterate through the list of events
  • [Lines ] Create a string that represents the filename
  • [lines] If that filename doesn’t exist (for some reason it was never created or it was deleted) or the flood isn’t over (and the chart needs to be redrawn) then:
  • [Lines 77-88] Create the chart of the flood

After this function has been run, there will be a directory full of charts.  I can then copy this directory to the Tomcat web apps directory

Screen Shot 2016-05-04 at 8.50.28 AM

As long as you know all of the floods, and you have created PNGs of the flood events you might as well also create and cache the HTML of the flood events.  This is simply done in the ProcessEvents object with the method “createHtml”.  This method take a filename  that will be the name of the HTML table file, then creates an HTML table, iterates through each of the events printing one row per flood event.

 

Screen Shot 2016-05-04 at 9.02.50 AM

As always you can get of all this code on the IOT Expert github site.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Flood Event Data Processor

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:

2015-03-04-18-35-40

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:

Screen Shot 2016-04-17 at 8.27.59 AM

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.

Screen Shot 2016-04-17 at 8.46.28 AM

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

Screen Shot 2016-04-17 at 8.49.00 AM

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]

Screen Shot 2016-04-17 at 9.02.47 AM

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.

Screen Shot 2016-04-17 at 9.29.35 AM

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

Screen Shot 2016-04-17 at 9.30.40 AM

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]

Screen Shot 2016-04-17 at 9.04.39 AM

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)”)”

Screen Shot 2016-04-17 at 9.05.59 AM

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.

Screen Shot 2016-04-17 at 9.07.06 AM

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.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Create the Chart with JFreeChart

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.

creekdepth

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.

Screen Shot 2016-04-15 at 12.21.46 PM

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

Screen Shot 2016-04-15 at 11.10.14 AM

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.

Screen Shot 2016-04-15 at 11.11.08 AM

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.

Screen Shot 2016-04-15 at 11.11.18 AM

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).

Screen Shot 2016-04-15 at 11.10.47 AM

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Install Tomcat

Apache Tomcat is a web server that allows you to create dynamic web pages using “Java Server Pages” (JSP).   Basically, you can embed Java into your HTML to create dynamic web pages.  Instead of writing Java that spits out HTML (CGI style) you write HTML with the Java embedded into it to create the dynamic part of your web page.  I use this to read the creek depth and temperature and put it into an HTML table, this will be the subject of a future post.

It is straight forward to install Tomcat onto your Raspberry Pi.  You use your old friend “apt-get install” to load Tomcat from somewhere out in the cloud.

  • sudo apt-get update
  • sudo apt-get install tomcat7  –fix-missing [wordpress turns double dash into endash… so use double -]
  • sudo apt-get install tomcat7-docs tomcat7-admin tomcat7-examples

Once you have started Tomcat you will be able to load up the default web page into your browser.  In my case it is “http://iotexpert:8080”.  The “8080” refers to the default port that Tomcat runs on.  If your install went OK, you should see the welcome screen:

Screen Shot 2016-04-03 at 10.29.35 AM

After you have it working, you will need to configure the Administration and Manager users.  To do this edit the “/etc/tomcat7/tomcat-users.xml” file and add a user.  I call my administration user “root” and gave it the excellent password “secret”.

Screen Shot 2016-04-03 at 11.35.34 AM

After you have made the changes you should restart Tomcat using “sudo service tomcat7 restart”.  Then when you click on the “manager web app” you will see this screen:

Screen Shot 2016-04-03 at 11.33.12 AM

And the Virtual Host Manager.

Screen Shot 2016-04-03 at 12.14.54 PM

At this point the Raspberry Pi has all of the required tools to make the Creek Server work.  In the next few posts Ill talk about the different programs that read the data, store it into the database and create the web pages.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Collect Data (Part 2)

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

Screen Shot 2016-04-10 at 9.02.35 AM

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.

Screen Shot 2016-04-10 at 9.10.21 AM

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

Screen Shot 2016-04-10 at 9.10.30 AM

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.

Screen Shot 2016-04-10 at 9.02.45 AM

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

Screen Shot 2016-04-10 at 10.12.10 AM

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

Screen Shot 2016-04-10 at 10.23.58 AM

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

Screen Shot 2016-04-10 at 10.31.47 AM

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.

Screen Shot 2016-04-10 at 10.37.34 AM

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.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

The Creek: Collect Data (Part 1)

In The Creek: Server Architecture I described a program called “runI2C” which is a shell script that is run once per minute by the crontab on the Raspberry PI (RPi).  This shell script does a “sudo” and runs the java program “i2cdb”.

Screen Shot 2016-04-09 at 1.02.25 PM

“i2cdb” is a Java program that uses the open source pi4j library to access the RPi I2C bus, reads the data out of the PSoC4 and then inserts it into the “creek data” database.  In order for all of this to work you first need to get the pi4j jar files.  To do that, on the Raspberry Pi, run “curl -s get.pi4j.com | sudo bash” which will install everything you need:

Screen Shot 2016-04-08 at 3.43.32 PM

Pi4J is a Java library that will give you complete access to the GPIOs on the Raspberry Pi, you can read and write the GPIOs as well as use the UART, SPI and I2C communication devices.  Once you install the libraries you will have 4 jar files in “/opt/pi4j/lib” as well as a bunch of examples in “/opt/pi4j/examples”.

Because I wanted to edit the files on my Mac, I copied the libraries into my “lib” directory in the project so that Netbeans would know about them.

The next library that you need is the mysql-connector which is a jar supplied by the MySql team.  You can get it from their website.  I put this jar file in my “lib” folder as well.

In order to access the database, there needs to be a user in the MySql database.  To do this, log in as root in the MySqlWorkbench tool and add a new user.  I call this user “creek” and give it a password of “creek”, then restrict it to only being able to connect from the RPi IP address.

Screen Shot 2016-04-09 at 1.27.39 PM

Then assign “creek”s privileges

Screen Shot 2016-04-09 at 1.31.30 PM

The next step is to create a MySql script to create the database.  Originally, I stored the ADC raw counts in the database because I wasn’t sure about calibration and I was interested in the raw data.  The problem with this method is that I had to encode the conversion from raw counts to depth on the server side.  I have come to believe that is a bad idea and that the acquisition hardware should be responsible for that conversion.

The easiest thing to do is to make a script to make the table.  I called it “table.sql” and I run it by doing “mysql -u creek -p < table.sql”

Screen Shot 2016-04-09 at 1.24.51 PM

This program has been written and re-written about a dozen times (including as I prepared this post) as I searched around for what was the “best” solution.  Originally it started as a hard coded program.  After some iteration I decided to create a configuration file.  That file, called “config.properties” is read in when the program starts.  It specifies:

  • The mysql url, user and password
  • Which database and table to store the data into
  • If it should store timestamps, and the name of the timestamp column
  • The number of “variables” to read
  • For each variable
    • The BUS # (0 or 1) on RPi (on my model only bus 1 works)
    • The I2C slave address
    • little or big endian
    • The name of the column to store the data in
    • What type of data (uint8, int8, uint16, int16, float, double)

Here is the file “config.properties.test”:

Screen Shot 2016-04-09 at 1.35.41 PM

I was going crazy trying to get all of the pieces to fit together (the endian-ness, the sign) and trying to sort out the fact that Java doesnt like unsigned.  So I created firmware for the PSoC that just had one of each test case.  The project is called “debugJavaPi” and is available on the iotexpert github site.  This program just creates an I2C buffer with one of each type of data, then initializes them with known data, then services I2C requests.

Screen Shot 2016-04-09 at 1.42.16 PM

In the next post I will explain in detail the I2CDB Java Program.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Install MySQL

As I described in the Creek Software Architecture, I am using MySql as the database to hold all of the creek temperature and depth information.  That means I need to get MySql going on the Raspberry Pi.  The process is well documented on the MySql installation website.  To do the installation you need to run:

  • sudo apt-get update
  • sudo apt-get install mysql-server
  • type password for root when it asks

After a bunch of gyrations and pages of information barf onto the screen, you will have MySql going on your Raspberry Pi.  You can verify that by running

  • sudo service mysql status

Screen Shot 2016-04-03 at 9.20.27 AM

It turns out that the command “service” has a number of useful options including:

  • sudo service mysql stop [which will turn off mysql]
  • sudo service mysql start [which will start it]
  • sudo service mysql restart [which will restart it]

As you guys might have noticed I like to run stuff on my Mac.  In order to get mysql to talk on the network you need to change the networking configuration.  To do this edit the file  “/etc/mysql/my.cnf”

  • sudo vi /etc/mysql/my.cnf

Then add a “#” to comment out this bind-address statement

  • #bind-address = 127.0.0.1

Then you can restart mysql with the updated options using:

  • sudo service mysql restart

Once that is working you can add the ability for the root user to access the mysql service remotely on the network.  To do this run the command line version of mysql

  • mysql -u root -p [it will ask you the mysql root password]
  • grant all privileges on *.* to ‘root’@’192.168.%.%’ with grant option;
  • quit

The “192.168.%.%” will restrict the access to this server/user to only the private IP addresses in my network.

Then you can startup MySqlWorkBench from your Mac (or whatever).  The first thing that I do in MySqlWorkbench is to create a connection to the RPi.  Remember in the previous post I added the IP address of the RPi to my “/etc/hosts” file and called it “iotexpertpi” this lets me refer to the RPi by name.

Screen Shot 2016-04-03 at 7.38.19 AM

After I make the connection and click on the “Users and Privileges” button things look like this:

Screen Shot 2016-04-03 at 7.41.51 AM

Then I create a database for storing the creekdata.

Screen Shot 2016-04-03 at 7.44.51 AM

And a user called “creek” who is allowed to connect only from the Raspberry Pi.

Screen Shot 2016-04-03 at 7.47.18 AM

And give that user insert/select privileges on the creekdata database:

Screen Shot 2016-04-03 at 7.49.46 AM

That is it for MySql.  In the next posts I will add Tomcat- a JSP Server- to the RPi.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

 

The Creek: Install the Raspberry PI Software

Years ago, when I did the first install of a Raspberry Pi (RPi), this whole thing was quite painful, however at this point the installation process is well documented.  In addition adafruit.com has a nice tutorial about making the I2C work here.

To start the process download the Raspbian image from it from here.

Screen Shot 2016-04-02 at 8.50.41 AM

Because I talked so much about security in this post, I thought that I had better check the cryptographic hash.  So, I run

  • openssl sha1 2016-03-18-raspbian-jessie.img

And I get:

SHA1(2016-03-18-raspbian-jessie.img)= 824f4daf805eb0ff49bc3fa515d97f447d382d37

which definitely didn’t match the signature, so now what? Is this a hack?  It turns out that my Mac “helps” you by unzipping the file.  The cryptographic hash of a file and a zip of that file are different.  If you download the zip and run the hash you will get:

SHA1(2016-03-18-raspbian-jessie.zip)= db41f2a8c6236c0ca9150fe4db2017c09e7871fb

OK.  They match.  Good.  The next step is to write the image to the flash card.  To do this

  • Insert the card (into your Mac)
  • See what disks are mounted
  • Unmount the volume (if you already had something on the card)
  • Write the image onto the flash card.  In the instructions on the Raspberry Pi site there is some confusion about if you should use /dev/rdiskx or /dev/diskx.  Thats simple, you should use the “r” version.  “r” stands for raw and is unbuffered and as such is much faster to write.  Even with the raw device it still takes a few minutes.  While it is writing you can press “ctrl-t” and it will give you a status (which I did twice during the write process)
  • Unmount the disk

Screen Shot 2016-04-02 at 9.20.38 AM

Now that you have a bootable flash disk your next step is to insert the card into your RPi, attach a keyboard, the network cable and a screen.  Then boot it.  After a few minutes you should see:

IMG_2768

The first thing that needs to be done is to change the password and expand the filesystem.  To do this run “sudo rapsi-config” then select

  • [Option 1] Expand Filesystem
  • [Option 2] Change User Password

I prefer to interact with the RPi using secure shell from my Mac.  To do this I need to know what IP address was assigned by my DHCP server.  So, start a terminal on the Pi and type:

  • ifconfig (find the ip address and the ethernet mac address)

Once I know the ethernet mac address I can configure my DHCP to always give this Raspberry Pi the same IP address.  You can read more about this in the “Creek Network” post.  For now I will assign the RPi the address 192.168.15.82 and I will add that address to the /etc/hosts file on my Mac so I can refer to it by the name “iotexpertrpi”

Now that all of the networking is setup, reboot.  As I have configured the IP address I can now log in using secure shell and start the configuration process.

  • [from the Mac] ssh pi@iotexpertpi (you will need to type the password which is “raspberry”)
  • [on the pi] mkdir .ssh
  • [on the pi] chmod 700 .ssh
  • [on the pi] exit
  • [from the Mac] cd ~/.ssh
  • [from the Mac] scp id_rsa.pub pi@iotexpertpi:.ssh/authorized_keys (you will need to type the password)

Then, to test SSH I will exit and log back in.  After the secure shell is going I need turn on the stuff in the configuration that enables I2C.

  • [on the pi] sudo apt-get install i2c-tools

In order for the I2C work you also need to enable the kernel drivers for I2C.  To do this run “[on the pi] sudo raspi-config”

Screen Shot 2016-04-02 at 9.53.12 AM

Screen Shot 2016-04-02 at 9.53.25 AM

Screen Shot 2016-04-02 at 9.53.39 AM

Screen Shot 2016-04-02 at 9.53.52 AM

After I2C is setup I will change the RPi to not start X-Windows.

Screen Shot 2016-04-02 at 9.56.10 AM

Screen Shot 2016-04-02 at 9.56.22 AM

After all of that, reboot the RPi and see if things are working.

  • [on the Mac] ssh pi@iotexpertpi
  • [on the pi] i2cdetect -y 1  (the 1 means bus 1.  On the Broadcom chip there are 2 IC Masters which they have labeled 0 and 1.  Apparently they did not connect Master 0 to anything)

This is good.  I can “search” all of the addresses.  Basically the RPi tries to write each address.  If it gets an “ack” then it will indicate that on the chart with a number.  In the picture below you can see that there are no I2C devices attached to the bus.

Screen Shot 2016-04-02 at 10.09.41 AM

Everything looks good.  Halt the RPi then attach the CYPI board and reboot.

IMG_2770

Now, when I log back in and run “i2cdetect -y 1” lookey there, the CYPI “ACKs” on address 08.

Screen Shot 2016-04-02 at 10.27.45 AM

If you remember in this post I talked about the I2C register structure of my Creek Firmware.  In the two bytes starting at location 2, is a 16-bit integer of the temperature times 100 (I just noticed the error in my comment, bad Alan).  Here is the register structure:

DataPacketFormat

In I2C tools, the command “i2cget” can read bytes or words from an I2C Slave.  Are things working?  Run “i2cget -y 1 8 2 b” and “i2cget -y 1 8 2 b” and I get 0x10 and 0x09 or 0x0910 (remember that the data is stored little endian) which is 2310 in decimal or 23.1 degrees C.  That makes sense.  What is a bit confusing is when I read a whole word by changing the “b” to “w” it gives it to me correctly because it ASSUMES little endian, so be careful if your slave device doesnt store thing little endian.

Screen Shot 2016-04-02 at 10.39.32 AM

Everything seems like it is working.  In the next posts I will discuss the installation of the MySql, Tomcat and the rest of the server software.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

The Creek: Server Software Architecture

In the previous Elkhorn Creek posts I discussed the overall system architecture, the design of the Creek Board, and the design of CYPI.  In this post I will focus on the software that is running on the Raspberry PI.  The software is divided into two main pieces.

  1. The Backend Server
  2. The Java Server Pages GUI

For this application, most of the software on the Raspberry Pi is written in Java.  In someways this was a strange choice, but I work with a group of guys who are Java programmers and build lots of web based Java applications, so Java it is.

Backend Server

Backend Server

The Backend Server performs the following jobs

  1. Collects data from the PSoC4 via an I2C Master that resides in the Raspberry Pi and stores it into a MySQL Database
  2. Once every 5 minutes creates a chart of the last 8 hours and stores it into a PNG which can be served by the Tomcat GUI
  3. Bootload new firmware into the PSoC4

The first two jobs are run by “Cron” which is a unix utility to trigger jobs automatically.  The cron table on the RPi looks like this:

# m h  dom mon dow   command

* * * * * /home/pi/getCreek/runi2c

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /home/pi/getCreek/makePNG

The BootloaderHost is triggered via the command line only after new firmware is sent to the RPi.

In future posts I will discuss

  1. The BootloaderHost and the BCM2835 library
  2. runI2C, the JDBC driver and the PI4J library
  3. makePNG including JFreeChart

Java Server Pages (JSP) GUI

Java Server Pages GUI

The user GUI for this system is a webpage that is served by the RPi.  I use the Tomcat Java Server Pages Server.  The JSP page “basic.jsp” creates the table of numbers and loads the graph.  The JSP page “excel.jsp” create a CSV dump of all of the data in the database.  The JSP programs are connected to MySQL a JDBC driver provided by Oracle.  In future posts I will discuss:

  1. The Tomcat installation and configuration
  2. The MySQL installation and configuration
  3. basic.jsp and excel.jsp

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster

The Creek: Testing the Bootloader

In the previous posts I described the main application firmware and the bootloader firmware.  In this post I will take you through the process of verifying that it all works.  In order to do this I need to:

  1. Test the bootloader (verify that I can bootload)
  2. Test the main application firmware (the subject of the next post)
    • Verify the pressure sensor
    • Verify the temperature sensor
    • Verify the I2C communication

Testing Bootloader

Cypress makes a cool programming tool called the MiniProg-3 which we also call CY8CKIT-002.  The MP3 is a multi purpose tool that can be use to:

  • Program and debug Cypress PSoC chips
  • Bridge USB <-> I2C (which I will use to test the firmware)
  • Bridge USB <-> JTAG
  • Bridge USB <-> ISSP

First, I will program the bootloader firmware using PSoC Creator connected via USB to a MP3.  In the photograph below you can see:

  • Mini Prog 3
  • The CYPI Bridge Board with the PSoC 4
  • The 10-pin ARM programming header (which is attached to the grey cable from the MP3)
  • The blue blinking LED.  (after I programmed the board the LED started blinking.  In this picture I caught it on)

Programming the bootloader

At this point it appears that the bootloader is programmed into PSoC4 as the LED is blinking.  In this case I only programmed the bootloader, so there is no application firmware to jump to, so the chip will just keep running the bootloader until the power goes off.

Next, I need to attach another MP3 acting in the role of a USB <–> I2C bridge.  The MP3 will emulate the Raspberry Pi which will be used in the production system, but for now it is easier to test without the added complexity of the RPi.  I am using one MP3 just as a power source for the CYPI board (I could have just plugged in a wall wart).  You can see that I have two wires, one for SDA and one for SCL connected to the correct RPi/CYPI pin.  The other side of the wires are sticking into the correct female connectors on the header of the MP3 (right next to each other at the bottom).

IMG_2728

Then I start the bootloader host which is a program that can read CYACD files (which is just a format of a hex file) and send it out over I2C (or UART) using the bootloading protocol.  First, I select the correct CYACD file from the directory where PSoC Creator put it.  Then I click the download button.

BootloaderHost-a

A few seconds after the boot loading is finished, the blinking blue led turns off and a blinking red led starts.  This indicates that the main application firmware is running.  To verify that the bootloader still works I press the reset switch on the board and the blue led starts blinking for 10 seconds before jumping back into the application firmware.  Good.  The bootloader and application firmware work together correctly.

In the next post I will show you how to use the Bridge Control Panel to verify that the firmware is working correctly.

Index Description
The Creek: IOT for the Elkhorn Creek Introduction
The Creek: Solution Architecture 1.0 Overall architecture
The Creek: Creek Board 1.1 Eagle layout of the board
The Creek: Creek Board 1.0 – RCCA A discussion of the errors in the 1.0 board
The Creek: CYPI, a Raspberry Pi to Arduino Bridge PSoC4 <--> Raspberry Pi Bridge Board
The Creek: PSoC4 Creator Schematic and Firmware Firmware to interface with the temperature and pressure sensors
The Creek: Testing the Firmware Using tools to verify that the PSoC 4 Firmware is working correctly
The Creek: Testing the Bootloader Make sure that you can load new firmware into the PSoC
The Creek: Software Architecture All of the Raspberry Pi software connections
The Creek: Install MySql Instruction to configure MySql
The Creek: Install Tomcat Instruction to configure Tomcat JSP Server
The Creek: Data Collection Java (Part 1) The Java program that reads the I2C and saves it in the database
The Creek: Data Collection Java (Part 2) The Java program that reads the I2C and saves it in the database
The Creek: Create the Chart with JFreeChart Using open source Java charting software to create plots of the Creek Depth
The Creek: Flood Event Data Processor A batch program to create analyze the database and create a table of flood events
The Creek: Flood Event Web Page A batch program to create the flood event web page
The Creek: Creek Server 1.1 Updates to all of the back off server programs to integrate charts
The Creek: JSP Web Page for www.elkhorn-creek.org The JSP program to make the table and display the website
The Creek: Raspberry Pi Clock Stretching Sorting out a bug in the system having to do with the Broadcomm Raspberry Pi Master not functioning well with clock stretching
The Creek: Creek Server 1.2 Caching the web pages to make them faster