Published July 28, 2016 © GPL3+

Water Meter Reader

Reads water meter with remote link (to meter 60 feet from house), sends info to Google Sheets and summarizes for presentation on iPhone.

IntermediateFull instructions provided8 hours3,700

Things used in this project

Hardware components

SparkFun Triple Axis Accelerometer Breakout - ADXL335

ControlEverything.com Particle Photon I²C 2-Channel SPDT 1-Amp Signal Relay

3.3 V Step Down Voltage Regulator

NXP PCA9600 Differential I2C Long Cable Extender with Boost Convertor

Required to extend I2C signal for 60 meters

NXP PCA9600 Differential I2C Long Cable Extender with Buck Convertor and RJ45 Adaptor

Adafruit USB Micro-B Breakout Board

Electop Micro USB Female to 2 Micro USB Male Splitter Cable

YCS Basics Category 5 Ethernet cable, Black, 60 Feet

uxcell® Waterproof Plastic Electronic Project Box Enclosure Case Total Size: 160 x 45 x 55mm/6.3 x 1.8" x 2.2"(L*W*H)

PiTech 2.5A Raspberry Pi 3 Power Supply -6 Foot Extra Long Universal USB Adapter Charger for Raspberry Pi 1, 2 & 3

CableGuard CG-1000XL Coax Demarcation Enclosure

Software apps and online services

Google Sheets

AppSheet

Provides development for iPhone application

Hand tools and fabrication machines

Drill press

Soldering iron (generic)

Screwdrivers (various sizes - Phillips head and straight blade)

Story

Background:

With the California drought and a relatively high water bill, I decided that monitoring my water usage would be a worthwhile endeavor. I was able to buy an application that connects to my PG&E Smart Meter to report real time usage on my iPhone so I was looking for something similar for my water meter. The only thing I could find would cost about $750 and would only monitor my landscape water utilization. I then discovered Christopher Cope's work with the Particle Proton and the HMC5883L magnetometer and began investigating further. Mr. Cope suggested InfluxDB as a database and Grafana as the presentation program. Since I know nothing about that, I began looking for alternatives. I discovered Gustavo Gonnet's project on saving data on Google sheets and I knew I had an answer I could deal with. So far I've had to learn a little C++ for the Proton and Javascript for the Google sheets and an addon to Google sheets called AppSheets but I'm pretty familiar with Excel so constructing the sheet was not a problem and the rest just takes a little time and effort.

The Hardware:

I have a Sensus SR-II water meter. Per the meters datasheet, "Water flows through the meter’s strainer and into the measuring chamber where it drives the piston. The hydrodynamically balanced piston oscillates around a central hub, guided by the division plate. A drive magnet transmits the motion of the piston to a driven magnet located within the hermetically sealed register. The driven magnet is connected to the register gear train. It reduces the piston oscillations into volume totalization units displayed on the register dial face." In other words, for this process to work, you need a meter that has a magnet driving the meter. This process counts the number of oscillations and converts it to gallons (or liters ). Christopher Case did the preliminary work in helping to understand how the HMC5883L works with the water meter and connection to the Proton. Additionally, Sparkfun has documentation on the HMC5883L and their breakout board which was very helpful. Getting the HMC5883L connected to the Proton and changing the code to report activity (passing a magnetic over it) on my Particle dashboard was pretty straight forward. Unfortunately, my water meter is encased in a cement enclosure about 60 feet from the house which was far enough away that the WIFI reception on the Proton was nonexistent. The meter was also next to the street and in the open which eliminated some options. Since the HMC5883L communicates using I2C which is a very short range protocol, I needed an alternative. I found a pair of devices by Sandbox Electronics that converts the I2C protocol to a bidirectional protocol using an NXP PCA9600 at 12 volts for the extended distance. Sandbox provided good documentation and answered all of my questions via email. The Proton is located next to the house near a 120 volt power source and within range of my wifi and the HMC5883L is attached to the water meter. The bidirectional traffic and power for the HMC5883L is over a CAT6 cable buried underground. A 120 volt power supply provides 5 volts to the Proton using a USB mini connector, the Sandbox devices also need 5 volts and and two step down voltage regulator supplies 3.2 volts for the I2C signal and the HMC5883L. All of the electronics are enclosed in water resistant enclosures. I decided on the cable since the HMC5883L is sampling constantly and the Proton is always on so maintaining a battery would be an issue.

Proton and 3.2 voltage regulator on breadboard., I2C boost extender and power supply.

I2C buck extender. HMC5883L connected to cable exiting at the bottom of the box.

The Software:

Particle Proton - The C++ code came primarily from Christopher Cope with the Google interface from Gustova Gonnet. Refer to their project descriptions for additional instructions. I did some slight modifications. The code is attached.

Google sheet - I use separate sheets for: 1) the database (timestamp and gallons every minute), 2) calculations, and 3) a separate sheet to list the data used in each chart (year, month, day, hour, minute). The AppSheet graphs work better with stand alone sheets. At one minute intervals the system collects 1,440 records a day. I will have to manually move the history to another location but that should not be for 6 months or possibly more. The calculations sum minutes into 5 minute intervals, minutes into hours, hours into days, days into months and months into years for display in the charts. At the appropriate time, the summary data is rolled up one row by a Javascript function. Google sheet works well for me but this approach is not a scalable product.

1 / 2 • Calculation Sheet - All computations are done on this page.

1 / 2 • Database Sheet - The data passed as a sting is currently stored as is

1 / 2 • Minute Sheet - Values aor the graphs are separate from calculations.

Google Javascript - Google provides an interval timer which will trigger a function call. I used a one minute interval and, initially, the function did the roll one cell at a time in a loop but I hit my daily quota for processing time with Google. I modified the function to copy and paste a range instead and have been under quota since then. However, Google's quota could still be an issue. I may have to drop to 5 minute intervals. The interval timer also triggers a call to the Proton to collect a variable (cycle count) which drives the whole thing. See the attached code. Note: In the fetch command, replace “__YOUR_PARTICLE_ DEVICE_ID__” and “__YOUR_PARTICLE_ACCESS_TOKEN__” with your actual device ID and access token.

Appsheet - Appsheet is an addon to Google Sheets. It pushes your spreadsheet data to your smartphone for display, graphing, editing, etc. I focused on the graphs exclusively. The advantage of Appsheet is development work is free and you can push it out to your smartphone to see how it works. I did six different versions of the Google Sheet and Appsheet while I learned how they worked together. As with any application, there is a learning curve. It seemed to work best for graphs when the calculations are done on a separate worksheet and results for each graph are displayed on separate worksheets with a formula reference to the calculations.

Finished Product

Code

var sheet = SpreadsheetApp.getActiveSpreadsheet();

function collectDataGallons() {
  sheet.setActiveSheet(sheet.getSheets()[5]);
 
  // fetches the number of cycles per minute which is converted to gallons from Particle Proton
 var response = UrlFetchApp.fetch("https://api.spark.io/v1/devices/__YOUR_PARTICLE_DEVICE_ID__/result?access_token=__YOUR_PARTICLE_ACCESS_TOKEN__");
  try {
    var response = JSON.parse(response.getContentText()); // parse the JSON the Core API created
    var result = unescape(response.result); // you'll need to unescape before your parse as JSON
    try {
      var intCycles = JSON.parse(result);    // parse the JSON you created
      var dteTimeStamp = new Date();   // time stamps are always good when taking readings
      sheet.appendRow([dteTimeStamp, intCycles]);   // append the date and data to the sheet
      sheet.setActiveSheet(sheet.getSheets()[6]);
      sheet.getRange('R3').setValue(dteTimeStamp);
      sheet.getRange('R5').setValue(intCycles)
      } catch(e)
    {
      Logger.log("Unable to do second parse");
    }
  } catch(e)
  {
    Logger.log("Unable to return JSON");
  }
 
}

function getData(){
   
        collectDataGallons();   // fetch data from Proton
        var rollPeriod = sheet.getRange("Q34").getValue(); // key to when data is rolled forward
        sheet.getRange("R8").setValue(rollPeriod);
         //   Roll the values depending on end of hour, day, month
        switch (rollPeriod) {
          case "Year":
            var range = sheet.getRange("B4:O33");
            // Shifts everything in the source range up one row
            range.copyValuesToRange(832021091, 2, 15, 3, 32);
            break;
          case "Month":
             var range = sheet.getRange("E4:O33");
            // Shifts everything in the source range up one row
            range.copyValuesToRange(832021091, 5, 15, 3, 32);
            break;
          case "Day":
             var range = sheet.getRange("H4:O33");
            // Shifts everything in the source range up one row
            range.copyValuesToRange(832021091, 8, 15, 3, 32);
            break;
          case "Hour":
             var range = sheet.getRange("K4:O33");
            // Shifts everything in the source range up one row
            range.copyValuesToRange(832021091, 11, 15, 3, 32);      
            var interval5Minute = sheet.getRange("O35").getValue();
            sheet.getRange("L32").setValue(interval5Minute);
            break;
          case "Minute":
             var range = sheet.getRange("N4:O33");
            // Shifts everything in the source range up one row
            range.copyValuesToRange(832021091, 14, 15, 3, 32);
            break;
         
          default:
          //  console.log("Switch selection not valid: " + rollPeriod + ".");
        }
}

#define hmc5883l_address 0x1E
#define publish_delay 60000

STARTUP(WiFi.selectAntenna(ANT_EXTERNAL));

unsigned long last_publish = 0;
unsigned long now = 0;
unsigned int crossings = 0;

int new_val = 0;
int old_val = 0;

boolean changed = false;

char resultstr[64];

int16_t x;
int16_t z;
int16_t y;


void setup() {
    // expose your char buffer to the Cloud API
    Particle.variable("result", resultstr, STRING); 
    // set up the HMC5883L
    Wire.begin();
    Wire.beginTransmission(hmc5883l_address);
    Wire.write(0x00); // Select Configuration Register A
    Wire.write(0x38); // 2 Averaged Samples at 75Hz
    Wire.endTransmission();

    Wire.beginTransmission(hmc5883l_address);
    Wire.write(0x01); // Select Configuration Register B
    Wire.write(0x20); // Set Default Gain
    Wire.endTransmission();

    Wire.beginTransmission(hmc5883l_address);
    Wire.write(0x02); // Select Mode Register
    Wire.write(0x00); // Continuous Measurement Mode
    Wire.endTransmission();
}

void loop() {
   
    now = millis();
    Wire.beginTransmission(hmc5883l_address);
    Wire.write(0x03); // Select register 3, X MSB Register
    Wire.endTransmission();
    
    Wire.requestFrom(hmc5883l_address, 6); delay(4);
    if(Wire.available() >= 6) {
        Wire.read(); Wire.read(); Wire.read(); Wire.read(); 
       
        y = Wire.read() << 8;  // Y MSB
        y |= Wire.read();      // Y LSB
    }

    old_val = new_val;
    new_val = map(y, 0, 300, -150, 150);
 
    changed = (old_val < 0 && new_val > 0) || (old_val > 0 && new_val < 0);
    
    if(changed) {
        crossings ++;
    }

    if((now - last_publish) >= publish_delay)  {
       // format your data as JSON, don't forget to escape the double quotes
       sprintf(resultstr, "{\"crossings\" :%d}", crossings);
      
       // publish to Particle dashboard
       String stringY =  String((crossings), DEC);  
       Particle.publish("Readings ", stringY, 60, PRIVATE);
       crossings = 0;
       last_publish = now;
    }   
    
    
    delay(10);
}