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.

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

 

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.

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

“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:

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.

Then assign “creek”s privileges

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”

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

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.

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

 

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

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.

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

Then I create a database for storing the creekdata.

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

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

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