Sunday, July 13, 2014

HESA is Populating Data to MySQL Server

For the last week or so, I have been working on MySQL coding in python on my Home Environmental Sensor Array.  This weekend, I put the finishing touches on the python code in the SQL library.  I have standard functions to connect and disconnect from the server, create an INSERT statement, submit a query, and process the results.  This makes it relatively easy to add SQL functionality to a python program.

Next, I added code to the HESA python program so it writes status records to a MySQL database on my website every hour or so.  The current water sensor status is recorded (which is a bit strange since the status is either 0 or 1 -- there is either water in the basement or not).  Right now, the HESA basically stops running if water is detected.  In the future, it may keep running and keep recording various readings.  Then, this status may be more useful.  Also, it will record false positives and give me the opportunity to do some analysis.

The other thing that the HESA measures right now is the number of times that current was detected on the input pin, i.e., the pin was set HIGH.  The HESA looks for power on the input pin for about one second.  During that second, it takes 1,000 readings.  The pin must be set HIGH for 500 readings in a row for the HESA to determine that there is water in the basement.  There is a variable that keeps a running total of how many individual readings were HIGH in the past hour.  That number is written to the database.

Each individual measurement is written as a separate row in the database.  This way, I don't have to add a new column for every new thing I want to measure.  I could measure the temperature in every room in my house and record a new row for each room.  This is modeled after the SAP plant maintenance measuring point functionality.  Each device or room I want to take measurements for would have a record in a table.  Each thing I want to measure on each device or room would have a record in another table.  The measurements themselves that are recorded are related to one of the measuring points.  This should work out well.

Finally, I made a PHP web page to display the most recent results.  It reads the last 48 readings from the database and displays them in a table.  It's not really very impressive at this point (well, maybe it never would be).  In the future, I would like to add more sensors to the HESA to measure temperature, humidity, CO2, radon, etc.  Then, the readings might be a bit more interesting.

In other HESA news, I was getting a lot of false positives where it detected water when there was none.  I think that was because I was using an electrical cable from a lamp.  The ends of the cable were together but covered in insulation.  They should not have passed electricity between them but apparantly, they were.  Now, I separated the ends of the cable and attached them to opposite sides of a four inch PVC pipe.  I have not had any false positives since I made that change.