Converting numbers stored as decimal to binary encoded decimal values in PostgreSQL, or how I fixed my smart water meter reader

Intro

In my previous post about reading my smart water meter in Home Assistant, I left you on a cliff hanger.  You don't really need to go back and read that post if you haven't already.  Just know that I'm reading my smart water meter using a USB SDR dongle and a tool called rtlamr, (and a wrapper for that tool called rtlamr2mqtt).  While I seemed to be able to read the meter, and the ID matched the one printed on the label, there was a drift between the two readings.

Also know that you really don't need to read this post.  Of everything I've written for this blog, this may be the most absurd self indulgent post yet.  I can guarantee you, that no one will find the contents of this post useful.

Figuring out the offset

My initial readings from the SDR were 157815, while the physical meter read 268770.  I had a suspicion that the SDR would have to be multiplied by 10, because the physical meter had an odometer style dial for all the digits down to the 10s digit.  The 1s digit was a permanent 0, and then there was a clock style dial, which I assumed was the 1s digit.

I just sort of assumed that the SDR version of this number would also have a resolution of the 10s digit, but I hoped it was better.  It was also possible it was much worse.  The water company bills me by the 1000s of gallon.  If I understood the meter, that would be the white dials.  It was entirely possible the SDR was only broadcasting that number.  I also wasn't sure what kind of time lag there would be between usage and the SDR reading changing.  I was getting readings pretty constantly, about once a minute, maybe more, but those readings didn't seem to change when I was actually using water.

After the first night I could see the SDR meter updating about once an hour.  Not ideal, if I was going to use this to help detect leaks, but still better than nothing.

I would wait until a time when we hadn't used much water, and then record the physical meter.  If the SDR didn't update for at least an hour before and after the time I read the physical meter, then I assumed that SDR reading corresponded to the physical reading I had.  My plan was to do that about once a day for a while and then try to find the offset.  I figured with just two pairs, I should be able to find the equation of the line through those points and that ought to be enough to convert between the two.  That is, as long as the SDR was increasing in a linear way.

After a day I had these two readings.  With both I verified that the SDR reading hadn't changed for a while around the time they were taken, both around 3pm.

6/2: 268815.4    157825
6/3: 268864.5    157830

Solving that line gave me this formula to convert the SDR reading to the physical reading: physical = 9.82 * SDR - 1281026.1.  Looking at the multiplier of 9.82 I realized that confirmed my suspicion that the SDR reading was for 10 gallons, and that the factor should just be 10, the only reason it wasn't was the error in the SDR value, since that was only accurate to 10 gallons.  I simply used 10x and found the offset and that gave me the slightly updated formula of: physical = 10 * SDR - 1309434.

And with that, I was pretty much done...

Discrepancies

6/3 was a Friday, so I didn't get a chance to get a good reading over the weekend, but come Monday I took another reading to confirm my formula worked.  On the afternoon of 6/6 I read the physical meter at 269214.8, and the SDR reading was 157985.  Plugging the SDR value into my formula gave: 10 * 157985 - 1309434 = 270416.  Which is 1201.2 gallons too high.

Now this was damn peculiar.  I wouldn't have been surprised to see some error, but 1200 gallons was way too much.  The fact that it was close to 1000 made me suspicious that perhaps I had read something wrong.  Luckily I had taken pictures of each physical meter reading.  I double and triple checked everything, but got the same results.

I went back and looked at the graph of SDR readings.  There was a clear jump at about 11:30am that day.  The previous change was at 3am and the SDR value at that time was 157849, then at 11:30 that changed to 157952.  Note that just looking at the last two digits the value went from 49 to 52.  Those are 10s of gallons so 30 gallons used.  That seemed plausible, albeit still a bit high.  I began to wonder if the first 4 digits of the SDR number were actually something else, basically two independent numbers just concatenated together.  I tried subtracting 1000 from the readings after the jump, and that helped, but they were still off by a lot.

After a week of this I began to lose hope that I'd be able to correct this issue.  I tried fitting any curve to these numbers, even though it really didn't make any sense why the meter would go up in a non-linear fashion.  I started to rationalize that at least the SDR did seem to increase when we used water, and stay the same when we didn't.  At the very least, I could do something with that.

Stumbling Block

You may remember from my first post, that my first stumbling block was reading the default scm meter type, instead of the r900 type I actually had.  It turns out this was also my second stumbling block.

I began to be more sure that A. I was not making any mistakes with these readings, and B. this was a common enough water meter that someone else must have also had this issue.  After a lot of searching, and just reading through every issue on the github project that mentioned the R900 meter, I finally found this issue.

That was it.  Turns out there was another r900 type, called r900bcd (binary coded decimal).  I had seen this type, but figured if I was able to read my meter using the r900 type that meant that was what I had.  I never thought that the r900bcd type would also be able to read my meter, but produce different outputs.

Sure enough I changed the type to r900bcd, relaunched the program, and the readings matched the physical meter exactly (up to the 10s digit).  This was both a huge relief and super annoying that I wasted so much time on what was essentially the same problem I already had.

Either way, I was happy to have accurate water usage data, to within 10 gallons and approximately an hour.  I could definitely use that to detect leaks while we were away or asleep.  But now I had a new problem.  I had already collected a week's worth of priceless data.  What was I to do with these data points, which through no fault of their own, had been encoded incorrectly?

Well, if you'd like a snack now is the time to get one, because this is the part where the blog post gets good.  If you're somewhat annoyed at having read a 1000 word essay, which was essentially a preamble to the actual purpose of this post, please remember that you were warned earlier.

Binary Coded Decimal

"What is Binary Coded Decimal (BCD)" you almost certainly do not ask yourself?  Well it's a system where instead of just converting a full number into binary, you instead take each individual decimal digit and convert it into binary.  4 binary digits let you express all the numbers from 0 to 9, so you can represent each decimal digit with 4 binary digits.  Why would we do that?  It does have some advantages when expressing numbers which could grow quite large, and we don't want to lose any precision.  Dates and times are a good example.  We could (and often do) convert a time into just the number of seconds since X, and then store that as a big binary number, but using BCD lets us encode each digit in a date exactly.

Converting decimal numbers into BCD

Now I have these values which seem all over the place, but are actually just numbers which were converted from binary to decimal, when they should have been converted from BCD into decimal.  No information has been lost, I simply need to figure out how to correct the encodings of these numbers.  Though, it took me a while to even wrap my head around what set of operations I wanted to perform.

After thinking it through for a while, and doing some trial and error with online calculators, I figured it out.  I had to convert my decimal values back into (normal) binary.  I had to then break that binary number up into groups of 4 digits each.  And then, convert each of those groups of 4 binary digits back into a decimal number.  Finally, I could just concat those decimal digits into my correct value.

From about a week I didn't have that much data to correct.  I could have very easily written a little script to iterate over each value and convert it for me.  I didn't need to do this in SQL, and in fact I wasn't even sure I could do this in SQL.  But I was pretty sure SQL could convert decimal to binary and back again, and that (plus a bunch of string manipulation) was really all I needed.

 

The Query

So without further ado, here is the monstrosity of a SQL query I wrote to convert values which had been incorrectly converted from BCD to decimal as if they were simple binary numbers.

I'll attempt to walk you through it, but if you don't know SQL you might as well just skip ahead.  I start with a subquery, as I like to do.  This subquery is just trying to convert the numbers from decimal back to binary.  This is the part doing work:

select right((state::int / 10)::bit(32)::varchar, 20) as binary_num

First it has to divide by 10, then convert to binary, then cast as a string and just take the last 20 digits (4 bits * 5 decimal digits = 20 bits in BCD).  Now that I have that BCD string, all I have to do is split it up into groups of 4 bits.  Each
SUBSTRING(binary_num, 1, 4)::bit(4)::int::varchar

Is grabbing one group of 4 bits, converting it from a string to a binary number, then to a decimal digit, and then back to a string.  And then I just concat those together, convert that back to an int so that I can multiply that by 10, and that is my corrected value.

With that I had a bunch of lines of the ids that had to change and the values they had to change to.  Now, despite having written that query, I can never remember how to write an insert or update that uses a subquery off the top of my head.  Instead I just copied the results of the select into Sublime, and used my favorite text editor feature of all time, multi-line cursor.  I just wrote an update statement for each line all at once and ran those few hundred statements together in a transaction.

 

Result

And with that, all was right.  I had my priceless data, and could admire my graphs to my heart's content.  I'll leave you with this graph of the original vs the corrected data for your viewing pleasure.

Reading my water meter from Home Assistant using a Software Defined Radio (SDR)

If you know me, you know that I must amass data.  To that end, I knew my water meter broadcast my water usage, and I had experimented with receiving that data in the past.  I also recently discovered this cool robot you can attach to any quarter turn water valve and remotely shut off your water.  I figured I could start reading and storing my water meter data, and use that to track usage, and also to attempt to detect leaks by looking for water usage while we were away or asleep.

 As is tradition, we have a long journey to get there.

 

Smart Meters

A smart meter is a meter in which the utility can read it without sending a person to walk up to it and read it with their eyes, or at least that's the definition I'm going to use here.  The newest smart meters are connected to the internet or form their own mesh network and the utility can get the readings on demand, whenever they want (called Advanced Metering Interface, or AMI).  There is also an older type of smart meter which simply broadcasts their readings every few minutes (called Automatic Meter Reading, or AMR).  The utility can then send someone around in a car to read all the meters while driving by.  They still need to send people out to all the neighborhoods, but they can cover way more ground than when they had to walk up to each meter to read it.

While the broadcast type of smart meter sounds worse, it's better for those of us who wish to hack into the signal and automate collecting data from their meters.  AMI meters are likely to be encrypted and the only way you can really access the data is if the utility provides a cloud based API to get it.  Many do, but I always prefer local solutions over cloud solutions.  AMR meters are (generally) unencrypted, and all we need to read them is something that will receive and understand the radio signals.

Software Defined Radio (SDR)

Software Defined Radio (SDR) is just what it sounds like.  It's a radio in which you can write software to determine how it functions and reads radio signals.  You get a hardware dongle and antenna and then can use different software to receive basically any radio signal.  The RTL-SDR is a very common open source one.  Everything you need is included in a kit for about $40.

Software

There is a ton of already written software out there for just about any purpose you could want to use a SDR for.  For reading smart meters the most common one is rtlamr.  The readme on that page is pretty good, but you run a separate utility called rtl_tcp in one console, then you run rtlamr in another console.  Then it'll begin dumping every smart meter packet it receives in your area.

Finding the right meter

If you can see your neighbors' houses then you'll be able to also read their meters when you fire up rtlamr.  Figuring out which meter was mine was my first stumbling block.  The ID of your meter should be printed either on the meter itself, or on a box that is wired to the meter.  I have three devices with serial numbers on them that are all connected together.  I copied down any serial numbers written on them and the current reading of my meter, and began looking through the data for a meter that matched either the serial numbers or reading.  There were at least a dozen meters I was reliably picking up, with a dozen more that I'd get occasional signals from.  I figured mine would be one of the strongest signals, and therefore one of the most frequent in the list.  The meters all seemed to broadcast every minute or so.

Still, none of the meters seemed to match mine.  This is what I was seeing (slightly anonymized):

You can see it lists Type 5 and 12.  This file says that type 5 is electrical, and type 12 is either gas or electrical.  That was odd, because I didn't think I had a smart electric meter, and thought I probably had a smart gas meter, but was just about certain I had a smart water meter.

This is one of the boxes that was attached to my water meter.  Googling it showed that a Neptune R900 was a very common water meter broadcaster.  I wondered if the ID it was broadcasting simply wasn't on the box, and if the reading it was broadcasting had some offset vs the one on my physical meter.  I had the candidate meters limited to a handful which I seemed to always receive the signals of, but none matched up.

Gameshark

Allow me to take you on a tangent.  This process reminded me a lot of my N64 Gameshark, which I often credit with getting me really interested in computers.  A Gameshark was a device a video game cartridge plugged into and then it, in turn, plugged into the console.  With the Gameshark between the game and the console it could access all the game's memory, reading and changing any values you wanted.  This let you cheat in games, but first you had to know what memory addresses to change, and how.  The memory addresses were always the same for a given game, but would be different for different games (or versions of the same game).  So if you wanted infinite lives in Mario 64, that was stored in some memory address, and once you knew which one it was, you (or anyone else who knew that address) could set that lives counter to whatever you want.  You could, of course, look these codes up online, but this was in an era of the internet when not literally every known piece of information was available within seconds.  It was often hard to find the codes you wanted, particularly for newer, or more obscure games, or if you wanted to do something else besides the common things like "infinite lives".

 

Earlier devices, like Game Genie, had a book with codes in it, but for new games you were out of luck.  The Gameshark provided a DIY way of getting these codes.  There was a button on the Gameshark, and when you pressed it, it froze the game and brought up a special menu.  This menu let you start a search for the right memory addresses that contained whatever it was you wanted to modify.  You then resumed the game and played for a bit.  Then you went back to that menu and told the Gameshark how the value you are searching for had changed.  You could say it went up, went down, stayed the same, or give it an exact value, or just that it changed in any way.  You repeated this process a few times, and eventually, from the millions of memory addresses only one would have changed in the exact pattern you specified.  Then you knew you had the right one, and you could change its value.

This process was super interesting to my 14 year old self.  The idea that literally everything about this game was just values stored in memory, and that I could modify any of them and that would modify the game I was playing was magic.

Finding the right meter, part 2

Anyway, with that section covered, I can now use this sentence: Like a real life Gameshark, I spent the night with my laptop next to my water meter and utility sink, using water to chage the water meter reading and waiting to see which broadcast meters matched my changes.  However, this only lead to more confusion as no meter seemed to match up with what I was doing.  This was complicated by not knowing what kind of lag there was between when water was used and when the new value would be broadcast, but I had pretty much ruled out any of the meters I could read being my water meter.

Upon researching this, I eventually stumbled upon some examples of other people reading R900 water meters with rtlamr.  They were all using the r900 type flag when launching rtlamr, rather than the default scm type I had been using.  For some reason I had assumed the default type would pick up all meters, and hadn't really paid attention to the other types.  Sure enough, changing the command to use the r900 type I got a totally different set of meters, with different types of data.  One of those meters had the exact ID printed on the yellow label on my Neptune R900 box, and so I was sure I had found the right meter.

Getting the data into Home Assistant

I knew I wanted to use MQTT as an intermediary to get the data into Home Assistant (HA).  When I first started dabbling with DIY sensors for HA I wrote a few custom components which would show up as a sensor directly in HA.  This proved to be a lot of work, particularly early on there were a lot of changes to the way these components had to work, and it was clear that making a custom component was intended for people who were planning on releasing their component for others to use.  I had no intentions of ever doing that, and having to support random people trying to use whatever hacked together, super specific to me, nonsense I had whipped up.

After a while, I just started writing data directly to the database.  Which I still do for a few things.  It works fine, although the one catch is I generally only put the data into the history table so it shows up in the history graphs.  I don't have the values available as a sensor that I can use to drive automations.  This is fine for most my purposes, because tracking the data is what I'm mostly after.

However, all of that is moot now, because I've discovered how easy MQTT is to use for this stuff.  MQTT is a protocol for sending messages on your local network, and is frequently used for DIY smart home devices and sensors.  You set up a server to run as a "broker" on your network, and then all your devices can publish to or subscribe to "topics".  So my DIY temperature sensors will publish to topics like: "home/bedroom/temperature" with a JSON payload there.  Home assistant then has a custom sensor you can use to read any topic and turn the values on it into a sensor.  If you get into MQTT I recommend MQTT Explorer as a GUI to help watch what data is being published to topics on your local MQTT network.

I almost began writing something to parse the text from this command line utility and send it to MQTT, but figured this would be the type of thing with a lot of glitches, and had the sense to search to see if someone else had already dealt with the headaches and edge cases.  Sure enough, I found a Python script called rtlamr2mqtt which did exactly what I wanted.  In fact, it even had an option to use the Home Assistant autodiscovery feature where, if you correctly format the MQTT topic, HA will just pick it up automatically.

The project uses Docker, but as I refuse to learn Docker, I just followed the steps in the Dockerfile manually and installed it as a regular ol' python script.

This was pretty much it.  There were some minor tweaks to the .yaml file that contained the configuration.  One cool thing I did was figure out I could multiple my readings by 10 by adding a 0 to the end of the format string, like this: "#######0".  This was nice because I was pretty sure my readings were for 10s of gallons.  The reading I was picking up didn't line up at all with what was on the physical meter, but I never really thought they would.  All that matters was if they moved by the same amount.

Over time though, I discovered the readings I was getting did not scale with what was on the physical meter at all.  They would for a short time, but then jump ahead, seemingly at random.  But, that odyssey will be in part 2.