William Kinzie
Published © 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 hours2,491
Water Meter Reader

Things used in this project

Hardware components

SparkFun Triple Axis Accelerometer Breakout - ADXL335
SparkFun Triple Axis Accelerometer Breakout - ADXL335
×1
Particle Photon I²C 2-Channel SPDT 1-Amp Signal Relay
ControlEverything.com Particle Photon I²C 2-Channel SPDT 1-Amp Signal Relay
×1
3.3 V Step Down Voltage Regulator
×1
NXP PCA9600 Differential I2C Long Cable Extender with Boost Convertor
Required to extend I2C signal for 60 meters
×1
NXP PCA9600 Differential I2C Long Cable Extender with Buck Convertor and RJ45 Adaptor
×1
Adafruit USB Micro-B Breakout Board
×1
Electop Micro USB Female to 2 Micro USB Male Splitter Cable
×1
YCS Basics Category 5 Ethernet cable, Black, 60 Feet
×1
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)
×1
PiTech 2.5A Raspberry Pi 3 Power Supply -6 Foot Extra Long Universal USB Adapter Charger for Raspberry Pi 1, 2 & 3
×1
CableGuard CG-1000XL Coax Demarcation Enclosure
×1

Software apps and online services

Google Sheets
Google Sheets
AppSheet
Provides development for iPhone application

Hand tools and fabrication machines

Drill press
Soldering iron (generic)
Soldering iron (generic)
Screwdrivers (various sizes - Phillips head and straight blade)

Story

Read more

Schematics

Wiring Diagram

Show components and wiring between components as well as jumper settings on extender boards

Wiring Diagram (Meter Side)

Shows components and wiring for meter side of data gathering.

Code

Google JavaScript Cod

JavaScript
Google's interval timer triggers the collection of the meter cycle counts and stores it on the database worksheet and the calculation worksheet every minute. Based on the interval (minute, hour, day, month, etc.) the data is rolled up one line to update the charts.
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 + ".");
        }
}

Particle Code

C/C++
Collects readings from HMC5883L and counts the number of cycles in a minute. Saves the data to a variable called result for collection by Google Sheets. Also publishes the count to the Particle Dashboard for monitoring.
#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);
}

Credits

William Kinzie

William Kinzie

1 project • 2 followers

Comments