Database Development for Water Conservation
Database development might not be the first thing that comes to mind when we think of water conservation, but it can actually be a big part of what makes a program successful. After all, data plays a critical role in everything from discovery to outreach to measuring outcomes. And pairing that data with technology is an extremely powerful way to accomplish conservation goals.
You can have the most attractive site or app in the world to help draw people to your cause, but ultimately, the strength of your portal, and thereby your program’s effectiveness, depends on having a well designed and properly supported back end. And that comes from good database development—the kind that goes way beyond spreadsheets and homegrown programs.
Radian has developed databases and accompanying apps for several water conservation projects, allowing our clients to organize and store their data in a way that makes it easier than ever to access, analyze, and use.
The Center for Water-Efficient Landscaping (CWEL) at Utah State University Extension rolled out their Slow the Flow Water Check program in 1999 to help Utah residents conserve irrigation water in a state that is the second-largest consumer of it (per person) in the country. Since about two-thirds of Utah’s potable water ends up on lawns and landscaping via inefficient systems, the CWEL had an opportunity to save huge amounts of water through outreach and education.
The CWEL worked with Utah’s Division of Water Resources and various water conservation districts and municipalities to offer free landscape consultations to homeowners. Over the course of an hour or so, a CWEL evaluator walks through the landscape, conducts tests, and collects data to determine the watering system’s efficiency.
This is where database development comes in. When the Water Check Program started, it was a manual, paper-based process that eventually moved to a Microsoft Access database. But as the size and scope of the program evolved, the CWEL needed a more secure and convenient system for storing the large amounts of data the program collects.
Radian developed a MySQL database, migrated data from an Access database, and created an iPad app that lets the in-field evaluators enter data with automatic upload to the new database and creation of a user-friendly homeowner report. With field tolerances and an intuitive interface, the app simplifies data input and reduces manual errors.
Program administrators can also access data via a web interface where they can run a variety of queries, create reports, and export CSV files.
In combination with Radian’s app and database development expertise, the CWEL has been able to make a real impact on water use in Utah. The average Water Check participant reduces water consumption by 30% for up to four years, and the program has helped save 175,427,000 gallons of water since its inception.
Salt Lake City Department of Public Utilities Intranet Web Portal
To enhance their water conservation efforts, the Salt Lake City Department of Public Utilities (SLCDPU) collects as much data as possible on water use within their district. When they needed to gain a deeper understanding of CII (Commercial, Industrial, and Institutional), or non-residential, water use in particular, Radian launched a web portal and database development project to accommodate and manipulate a huge amount of very detailed data.
The SLCDPU had an existing Microsoft SQL database that housed all of its water use data in addition to other department information. To avoid disrupting billing and administrative activities, Radian built an additional database layer and integrated it with the original.
The new database features better categorization and lots of new attributes, giving the SLCDPU extremely detailed insight into the water use of different types of properties in a variety of industries. For example, they can collect and analyze data including hierarchical NAICS codes, the number of beds in a hotel, the number of seats in a restaurant, building square footage, date of construction, and much more. That way, the department can compare apples to apples when creating conservation programs and conducting outreach activities. And the more information users put in, the more they can get out of the database.
The database is also connected to an intranet web portal for advanced web querying and reporting.
Landscape Efficiency Optimizer
In another database development project to aid water conservation, Radian created the Landscape Efficiency Optimizer (LEO) for municipalities and utilities to offer landscape contractors as an auditing tool. Utilities can conserve water, and landscapers can help their clients make the changes they need to increase efficiency. Administrators can also view aggregated data from all contractors on a system-wide level across multiple utilities.
LEO is a mobile app with a MySQL database behind it. The contractor enters basic information on a client’s landscaping design (e.g., zones, season length, run time, area) and LEO calculates the recommended amount of water and compares it to the current actual amount of water.
LEO also creates a report for the client, which can include custom utility and/or contractor logos.
Integrating database development with water conservation projects can exponentially broaden programs’ potential in a variety of ways. It means flexibility in data collection and analysis, the ability to evolve with the scope of efforts, and flat-out ease of access and organization. And when it comes to something as important as preserving a precious resource, we can’t let messy data stand in the way.