The challenge

The CFO of a large retail chain suspects that the heating/cooling systems on her sites are sometimes running even when the sites are unoccupied - wasting energy and money and contributing to climate change. 

Across several sites most have a smart energy meter for the whole building which report energy used in the past half-hour, and several occupancy sensors within the building which give a True/False report on occupancy every 15 minutes.

The CFO wants a report showing energy wasted in unoccupied buildings over the past 7 days.

Joining the streams

Every IoT device has a unique ID which is reported with every reading that streams from that device. If our problem was entirely about energy (say we wanted to sum the energy from each meter over the past day) then all our calculations could be done within the stream from each meter.

But here we have a stream of energy readings from each energy meter and a stream of occupancy readings from each occupancy sensor. The only way we know which energy streams are related to which occupancy streams is that each stream has a “site” property.

In the picture above, and table below, we can see that Meter 1234 is reporting energy for site 1, and Occupancy Sensor 2222 is reporting occupancy also for site 1:

ID=1234, site=1, kW=3

ID=5678, site=2, kW=2

ID=2222, site=1, occupied=0

ID=4444,site=2, occupied=1

ID=1234, site=1, kW=1

ID=5678, site=2, kW=2

ID=2222, site=1, occupied=1

ID=4444,site=2, occupied=1

For simplicity above we’ve shown devices all reporting simultaneously (in reality they won’t) and the site property being sent repeatedly with every message (in reality it may just get set once when the device is first configured).

We need to re-organise the streams to collect the energy and occupancy readings for each site into a single stream, so that we can compare that site’s energy and occupancy side-by-side:

In Excel terms we might describe this as “building a pivot-table by site”. In SQL terms we might describe this as “joining by site”. 

In this example site happens to be a static property of each device, so in principle we could select devices according to their site and then merge the streams from those devices forever more without any further reference to site. However in other examples here we’ll see streams that need to be merged by properties which change dynamically, and the solution we present here will work even in that case. For example, if our devices were mobile then their site could change from time to time, and this site-based query will still work.

Now we can do a query with (GROUP BY 1h, site) to get the total energy consumption and average occupancy of each site, in each hour. E.g. below we see that only one site is consuming energy on a Saturday:

Calculating the final metric

Now we need to define a threshold above which the fractional occupancy must be in order for a building to be considered worth heating - then we can measure the energy consumed when occupancy is above and below that threshold. Having done that, we can then produce totals and ratios. 

This effectively adds two relational queries around our streaming query:

RELATIONAL: Work-out the percentages from...

RELATIONAL: energy-when-occupied & energy-when-not-occupied from...

STREAMING: the stream-merging query to calculate energy and occupancy

We discover that over the last week the Hitchin site is by far the biggest culprit, wasting 5% of its energy:

Parsimonious though the Scots are reputed to be, you may find it suspicious that the Edinburgh site is reporting zero inefficiency. On closer inspection we discovered that this is because there is no smart meter fitted at the Edinburgh site.

Conclusion

Combining streaming and relational queries can enable IoT data to deliver energy savings in buildings. Next, read about using it to deliver service assurance for a device estate.