Dashboard Stories

Observing Garden Irrigation water usage with SQL

This dashboard utilizes the SQL plugin in order to display a variety of data that helps me with the irrigation of my garden.

Nathan Foreman

Principal Software Engineer, SquaredUp

Dashboard Preview

Create your dashboard

Why I built this dashboard

I built this dashboard to assist my father and I with the irrigation of our garden in Denver, Colorado. My father really enjoys working on the garden and is therefore keen to know about potential leaks and what the costs moving forward will be. I created the dashboard to assist with this, as it gives me access to all the “behind-the-scenes” data I need. This dashboard displays a series of data on system availability, water usage and weather conditions.

The garden in Denver, being watched over by Duncan the Dog!

Challenge

Denver is a semi-arid desert, with water being both an economically and environmentally precious resource, so usage needs to be monitored. Along with that, leaks as well as freezes and drops in temperature can be very detrimental.

I was already using some sensors and an Arduino device I had built to track usage through an Azure Logic App and into a SQL database. However, I had no way of displaying this data, and waiting until the next water bill to see if there had been a leak or any other kind of overuse was obviously not ideal!

Solution

I created this dashboard using SquaredUp and the SQL plugin. It displays the data gathered by the Arduino device, allowing me to quickly visualize water usage across any timeframe I choose. This means that I can spot irregularities in usage, which could indicate a leak.

It also allows me to monitor potential risks such as temperature and humidity changes, and freeze warnings. All of these could contribute to or cause leaks, so being made aware of them as early as possible is crucial.

Being able to view the data in this way is a huge improvement, as it allows me to discover leaks within only a few days.

Another benefit of using this dashboard is that it gives me the power of long-range monitoring. I am now based in the UK, so being able to remotely monitor the garden gives me an added level of comfort.

While I enjoy working within the “nuts and bolts” of a project, visualization has always been a challenge for me, so this dashboard was a perfect fit.

Create a SquaredUp account – free forever

Dashboard walk-through

In the top-left there is a tile displaying gallons of water used. This can be set to any specific timeframe and provides a very quick and easy snapshot of our water usage.

The tile at the top shows water as a rate of flow. This is the tile that best assists with identifying leaks, as a period of excessive water use would be very clearly displayed in the line graph.

To create this tile, I wrote the following custom SQL metrics query:

SELECT readingUnit [label], reading [value], postedDateTime [timestamp] FROM [dbo].[wifiReadingsV1] where postedDateTime between '{{timeframe.start}}' and '{{timeframe.end}}' and readingUnit = 'gallon'

The query pulls water usage from my selected database and displays it dynamically within the timeframe that I select in SquaredUp. I can easily adjust the timeframe in the top right of my dashboard.

What I like about this is that I can set the timeframes to whatever I want, giving me access to any of the data I need.

This tile effectively operates as the “heartbeat” of the dashboard. I created a simple C++ program which is used in the Arduino device to check the sensors and post to the Azure Logic App if the value has changed. Ultimately it just gives me peace of mind that the system is running!

The right-hand tile shows historical temperature and humidity data, allowing me to make predictions on future temperatures and humidity levels, minimizing potential risk of freezing or water overuse.

This tile shows the current temperature within the garden in a gauge visualization. I have enabled monitoring and selected the appropriate thresholds, so that I have an "at-a-glance" view of the current environment. If the temperature drops below 6° the gauge turns amber, and below 3° it turns red.

This tile provides a reading of the current humidity displayed in a gauge visualization within SquaredUp like the Freeze Warning tile. This information is most useful in the Spring when new seedlings are planted and need the most optimal growing environment. Denver can be very dry so this tile would inform us as to whether we would need to add a humidifier to the garden.

In the future

In its current state the dashboard is perfectly functional and does everything my dad and I need. I gain all the necessary knowledge to warn my dad about leaks and make projections about future costs, while also being able to monitor remotely.

Some potential ways to expand the dashboard would be to set alerts based on average gallon usage, for example if twice as much water was used compared to last month. This would be easily created with SQL Analytics.

Another larger idea would be to utilize the OpenWeatherMap plugin, to use temperature and weather forecast data to control whether the garden is watered or not.

This is one of the great advantages of using SquaredUp and its plugins, as even though I’m only working with simple data, once you surface it there’s a lot you can do with it.

Create your free dashboard

This Garden Irrigation dashboard is not available out of the box, but you can easily build something similar yourself using the SQL plugin or the WebAPI plugin.

Create your dashboard – free forever

Simply create a free account to get started, or check out this video to see how easy it is to use our Dashboard Designer: