Data Management Software
Managing the data from a stormwater program or utility’s billing program is a complex task, and more sophisticated software and databases are being developed to ensure accuracy of billing information and details of individual parcels. Various types of networks are also making data–both for administrative tasks like billing and for emergency management or flood response–available to personnel and area residents more quickly than ever. After the City and County of Denver, CO, started its stormwater utility in the late 1970s, downtown business owners in the early 1980s filed a class action lawsuit claiming it was unfair to implement the utility. The municipal body made some adjustments to its ordinance. Instrumental in its effort to bring equity and accuracy into mapping out impervious surfaces upon which to set up the billing rate was data management software.Many communities throughout the United States like Denver are becoming increasingly automated in their stormwater programs and reaping the benefits, which include deriving a greater body of information about their environments and accomplishing more with the same number of employees, or fewer. Jeff Blossom is the GIS photogrammetry administrator for the City and County of Denver, a Phase I community. Blossom’s tasks entail coordinating the area’s impervious surface mapping effort. His operation consists of a geographic information system (GIS) developer, a GIS analyst, and four technicians and investigators. Blossom is the GIS manager as well as the photogrammetry administrator.In Denver’s stormwater utility, landowners are billed per parcel according to an impervious surface ratio to determine their billing rate, “so if the percentage of impervious is low, then they get a lower billing rate, versus if it’s higher,” Blossom points out. The billing rate is applied toward the total impervious area on the property to determine the billing amount.A few years after Denver instituted its stormwater utility, the government derived impervious numbers from building square footages on assessment maps. In 1989, impervious surfaces were derived through 1 inch:660 foot scale aerial photography and digitized. That established a citywide comprehensive impervious surface map for the region. In 1993, the database was updated and the new data were used for billing. Aerial photos helped solve many of the customer disputes that began with the class action suit, Blossom says, adding that many calls that come from ratepayers are requests to verify that the government has accurately mapped their property’s impervious surfaces.“Before we started using the GIS, an investigator would go out in the field with a metric wheel or a measuring tape,” Blossom says, adding that impervious areas were mapped on graph paper, with the investigator producing the total amount for the landowner at that time.“Now when we get a customer’s request, we either send them a printout of their property with the total impervious [area] on it or give them the map,” he says.Having that information, the customer can verify through the high-resolution aerial photography and the impervious map that the billing rate is fair, Blossom notes.“That’s saved us a lot of time as far as justifying to customers what we do,” Blossom says. “The GIS and being able to get them a map really helps us be accountable for what we are doing. Customers see the equity in that and they accept it.”Blossom’s tool bag includes ESRI, ArcInfo, ArcEditor, ArcSDE, ArcObjects (programming language), and Quest Software Toad Server and Oracle Database version 10g. The stormwater bill is sent out yearly to customers with a flyer that explains the meaning of impervious surfaces, how they affect stormwater runoff, and how stormwater runoff needs to be controlled for public health to prevent flooding and the potential for West Nile virus and related consequences. And although customers understand that, what they often don’t understand are the unique characteristics of different properties, Blossom points out. For example, some properties are sunken so that all around the property, there’s a 3-foot drop that’s nearly vertical, with landowners claiming it contains runoff, Blossom says.Landowners who are not satisfied with their billing can request a hearing where board members decide what’s equitable. There are typically six hearings a year out of 160,000 accounts.The map is indisputable, Blossom points out. “We don’t officially use it for legal purposes,” he says. “However, it holds up in hearings and in court. When you look at the aerial photo and see what’s on there, it’s hard to dispute that document when you’re using 6-inch pixel resolution photography, which is what we use as our standard and has pretty high-level detail.” Denver’s data management system is set up so investigators and technicians can post data into a multiuser environment so that everyone involved can see any changes that are made. “From a data management side, it makes it really smooth,” Blossom says. “There’s no merging of datasets and not too much processing involved to get everyone’s edits into one master database.”As for the cost/benefit aspect of the GIS, Blossom notes that the number of investigations increased by 60% during the first year of its use, and his department has maintained that high level since. The City and County of Denver performs a field verification of each property map, something Blossom says he would not recommend for those implementing a stormwater utility for the first time. “In the City and County of Denver, we are committed to that and that’s the level of service we provide,” he adds.The field investigation involves visually inspecting a piece of property without trespassing, making sure that what is shown in the photo is still what is there.“If there are things like privacy fences or big trees that might obscure the view, in that case, we just go with what’s on the photo,” Blossom says. “But oftentimes, as we get to the end of our imagery collection cycle–when the images might be one and a half or two years old–there’ll be some changes we want to incorporate.”Blossom’s advice to other municipalities starting a stormwater utility is to make the process as accountable and equitable as possible; to that end, data management software is an asset. A graduating billing rate is a fair way to go, he says. “Someone with a house and a driveway pays a much lower rate than a business that has a big building and a big parking lot. The thinking is that someone with the greater percentage of impervious is going to contribute more [runoff] to the stormwater system,” he says.While Blossom doesn’t believe municipalities starting up a similar program should do field verification of data, contact with customers in the field if they ask specific requests is definitely required. “If you run the utility so that after the imagery is captured and delivered, it gets billed as soon as possible so the impervious map is as current as it possibly can be, then I think the need for field verifying is alleviated,” he says. “About half of the time spent by our investigators is verifying if what they see on the aerials is still correct.”Blossom says the goal of the City and County of Denver is all-encompassing, creating plenty of work for all employees. “Our mission is to provide as many customers as possible with as accurate and timely a stormwater bill as we possibly can,” he says. “When you look at the entire City and County of Denver and the fact we do field verifications, there’s unlimited amount of work for everyone,” Blossom says. “With the implementation of GIS and getting the investigators to use that on a daily basis, that has increased the number of customers for whom we can provide updated bills.“Doing something like mapping the entire city and then updating the billing based on that would be the best way to go; however, we are still obligated to field-verify these properties.”Blossom notes two limitations the City and County of Denver still face. “One is acquiring and using updated imagery, and the second is the limitations of the parcel map accuracy,” he says. “Those should be on the forefront of anyone implementing a stormwater utility.”The City and County of Denver is participating in an aerial acquisition project through the Denver Regional Council of Governments. “Participating members can pay a little bit extra and get the near-infrared band delivered if they want to,” Blossom says.Sanborn Co., which in the past six years has won several orthophoto and planimetric data contracts with the City and County of Denver, was awarded the DRCOG orthophoto acquisition project.“That’s really exciting to me; we’re paying the extra and getting the infrared delivered, because one thing I am trying to narrow down is how far can we take this automated feature extraction using the infrared, and is it going to be accurate enough for us to use in our billing methodology,” Blossom says.“The cost savings there is enormous versus the traditional method of stereo compilation or “˜heads-up’ digitizing each feature. To be able to automatically extract [features] is something that could really change the cost of implementing and maintaining a stormwater utility.”Blossom anticipates that late this year, he will perform impervious mapping using infrared data to try to ascertain exactly how much more revenue the City and County of Denver would be generating by using an automated method that would deliver updated impervious data quicker than more traditional methods.“Our mission as a municipality isn’t to generate revenue; it’s to provide customers with accurate and timely bills,” he says. “As the city grows with new construction and additions, the revenue is going to grow. It’s really more looking at how quickly can we capture that revenue by using new technologies.”He says part of his job is to figure out what combination of new technologies–be it digital imagery, infrared, satellite imagery, or a host of others–can produce a sufficiently accurate impervious map to meet Denver stormwater billing standards. His recommendation to those just starting a stormwater utility is to develop their own standard based on several factors, including various mapping scales and the cost of mapping methodologies.“We have to give everyone the quality of data that’s mapped off 6-inch pixel photography, and our billing unit is every 100 square feet, so they get charged at whatever billing rate the property has for every 100 square feet of impervious,” Blossom says. “That’s the minimum mapping unit we use, but a municipality can decide what they want the minimum mapping unit to be and how they want to go about doing it.”The bottom line is there’s a lot of flexibility in the process, Blossom says. “Ultimately what it comes down to is that establishing a stormwater utility through impervious surface mapping is very justifiable,” he says. “The municipality is accountable for it. The ratepayer can see a map of their property and imagine water falling and then running into the storm sewers, so it leads to greater acceptance than just an imposed flat rate or a flat tax.” As for the software learning curve, Blossom notes that the ESRI ArcEditor software is easy. The toolbars are customized. Blossom was able to do hands-on training with one new employee in just an hour, and, additionally, she read a small training manual and by the end of a week was as proficient at using the system as the other investigators.“It involves only six buttons you need to learn to start digitizing, because everything else is automated,” Blossom says.When a program user digitizes a polygon, for example, a window pops open with pull-down menus into which information is inserted regarding the type of impervious surface, the source of information collection, and other needed information. Once that particular piece of property is digitized, the user is prompted to build a report based on the information. The report includes a map with information on address, account number, date, aerial photo, impervious legend, disclaimer, and an explanation that buildings might appear to be leaning, but that this appearance is normal. The report is saved in the system and the investigator prints it out.Pittsburgh
Asset management is the role data management software plays in assisting Roy Rudolph, senior project manager for Metcalf & Eddy, a Massachusetts-based environmental engineering firm, in his consulting work with Pittsburgh, PA’s Water and Sewer Authority.For the past three years, Rudolph has been helping the authority establish a long-term plan for management of its combined sewer overflows (CSOs) as well as doing asset management of the city’s wastewater collection system. He’s helped develop field programs that use information to build hydraulic models and other computer models of Pittsburgh’s collection system. Flow meter studies have been used to calibrate the models. The models will eventually be used to evaluate alternatives for management of the CSOs.“It’s a precursor to the overall goal of what we have in asset management,” he says, adding that this step became even more necessary of late as a result of Pittsburgh’s Water and Sewer Authority and the City of Pittsburgh signing a consent order with the Pennsylvania Department of Environmental Protection requiring the municipality and the agency to press forward with issues relating to asset management as a result of the CSOs posing a significant threat to the area’s waterways.Rudolph’s firm is refining mapping as a component of the model building and data collection toward the overall goal of controlling discharges “to the extent they are no longer contributing factors to the water-quality standards,” Rudolph notes.The primary software being used in the asset management effort is Wallingford Software’s InfoNet, with InfoWorks being used for modeling. InfoNet is an asset and data management system for water networks, including water supply, water distribution, wastewater collection, sewers, and stormwater. InfoNet allows users to store, validate, review, clean, edit, and analyze water infrastructure data in one database; improve planning and operational decision-making; and increase the value and use of spatial and asset data. Together the programs address the whole water cycle, from supply and distribution, urban drainage, and wastewater management through river management modeling.The software has helped centralize data for Pittsburgh to increase efficiencies previously not realized, Rudolph says. “We are trying basically to redesign their work flow processes and how they conduct their business on a day-to-day basis,” he explains. “The time savings is immeasurable; rather than going to filing cabinets and pulling out plans, you have them in front of you in a way that’s centralized and easy to use.”As a result of the software, Pittsburgh’s water and sewer employees are able to expand their abilities to accomplish more tasks. “It’s taken a tremendous investment to get all the data and do everything they need to do, so it’s costing them a lot of money right now, but the efficiencies are going to be realized in the long run,” Rudolph says.The software is user-friendly with the learning curve relatively short, Rudolph notes. “You can look at the asset, its size, its depth, what the pipe material is, the diameter, and all of the physical characteristics. If there is other information from field surveys or other inspections, it’s available there, too.“It’s really clicking on the buttons. The tough part is learning how to integrate the data, standardizing information collected out in the field so you can bring it back in.”Baytown, TX
Baytown, TX, a Phase II community of about 65,000 residents, is initiating a stormwater program expected to go into full swing once the Texas general permit is passed. Data management software is helping town officials handle the requirements.“We do construction-site inspections, public education, public involvement, and good housekeeping without any ordinances,” says Rory Lang, stormwater coordinator. “At this point, everything is educational–even the inspections–but we are tracking everything we do. We do everything in the stormwater division that helps us comply with the Phase II permit.”To that end, Baytown uses the MS4 Permit Manager software from CBI Systems. The software helps organize and manage stormwater program implementation tasks, collects and manages program data, automatically reports program status, and automatically produces a draft of annual reports. “It’s been really great as far as the construction sites,” Lang says. “It helps us track the construction sites and also maintains the schedule of when inspections need to be done for the sites.” Lang says the software also helps organize photos and documents sent to site operators based on inspections.“In addition, it’s helping us keep track of the various things we do, such as the events we hold and the brochures we hand out,” she says.Previous to using the MS4 Permit Manager software, Baytown tracked its stormwater efforts using Excel spreadsheets. “This is far easier; it’s more manageable and gives us way more information and scheduling ability,” Lang says. Lang’s team never had formal training in using the MS4 Permit Manager software. “We just loaded it on our computers and started working with it,” she says. “There might be some questions on how to pull reports and specific information you may want, but it’s very intuitive,” she says of the software’s learning curve. Lang advises municipalities to seriously consider incorporating data management software into their stormwater programs. “Any kind of software you can get will help you manage all of this, especially for those municipalities that aren’t going to be able to have a large staff to help manage it, input it, file it, track it, and keep it for the life of the permit in case you get audited,” she says.“The software is definitely the way to go. Don’t try to do it with Excel spreadsheets or a simple database,” she adds. “Definitely get something that will help track everything you need to track, because it is easy to forget small pieces of what’s going on.”St. Johns River Management District
In Florida, helping northeast Florida municipal officials make decisions on flood control measures is one way the St. Johns River Management District employs data management software. The District is responsible for managing ground- and surface-water supplies throughout 18 counties in northeast and east-central Florida. Michael Daly is a GIS analyst with the St. Johns River Management District’s Division of Hydrologic Data Services. The duties of the division’s hydrologic data collection program include collecting, processing, managing, and distributing hydrologic and meteorological data used for consumptive use permitting, water shortage management, establishment of minimum flows and levels, water supply planning and management, environmental protection and restoration projects, and operation of the district’s flood control facilities.The division operates more than 1,200 monitoring stations and also processes data from about 300 additional sites collected by county and municipal agencies through mutual agreement or by the US Geological Survey under contract to the district. Each year, more than 12 million measurements are collected, verified, processed, and stored. Data collection is done concerning water levels, surface water, groundwater, and rainfall and provided to other groups within the water district.Daly says the software sequence starts with DataWise (by DEC Data Systems) that is used to transfer rain gauge data via FTP to OneRain Corp. OneRain provides NexRad data in text files that are uploaded to Oracle. From Oracle the data are accessed via ESRI’s ArcGIS and ArcIMS using a .NET/Link. The St. Johns River Management District has a network of rain gauges on a telemetry system that continuously records the rainfall and is recorded through a datawide system, which in turn is provided to OneRain. OneRain uses the data to adjust the radar rainfall to come up with estimates, which are sent back to the water district on an ongoing basis. The district has some in-house custom applications into which the data are fed, built on top of the ESRI Arc map platform. The engineering department, for example, uses the data by looking at the actual rain gauge amounts and the radar rainfall to determine if flood control measures need to be initiated. Daly says the water district has become much more automated in terms of data collection. “We now are trained to move more of our equipment onto telemetry where the data stream flows right into headquarters, and with that, there are fewer personnel in the field taking measures at the site,” he says. Field personnel now are spending more time doing maintenance and troubleshooting at the automated site. “For the same amount of men, we are collecting a lot more data because of the automation,” Daly says. Though he does not know precisely how much, Daly says the district derives a significant cost savings through the automation. “In the last five years we’ve lost about four people from retirement, and those positions have been eliminated without having to lay anyone off,” Daly says. “We’ve been collecting more data with fewer people.”The learning curve is fast, Daly notes. “Most of our staff is technically savvy, so we have continuous training on a regular basis as we get new versions of the software to keep them trained and up to date,” he says.Additionally, a large amount of data has been put onto the Internet for the public to view, Daly says. “If there is a storm, people can now go onto the Internet and look at the estimated rainfall in their neighborhood. If there’s a serious event like a hurricane, they have the ability to look at the water levels. If there are a few areas that are flood prone, they can look at the graphs and see what is going on.” Water level data are available almost in real time–updated every hour or so–enabling the communities and individuals to track serious rainfall events and take appropriate action.“That could become critical to [cities] if they need to make a decision of emergency management in terms of evacuating an area,” Daly says.The St. Johns River Management District also has developed a redundant system of telephone line and satellite data transmissions for critical flood control areas whereby if one system fails, the other can be accessed.
Asset management is the role data management software plays in assisting Roy Rudolph, senior project manager for Metcalf & Eddy, a Massachusetts-based environmental engineering firm, in his consulting work with Pittsburgh, PA’s Water and Sewer Authority.For the past three years, Rudolph has been helping the authority establish a long-term plan for management of its combined sewer overflows (CSOs) as well as doing asset management of the city’s wastewater collection system. He’s helped develop field programs that use information to build hydraulic models and other computer models of Pittsburgh’s collection system. Flow meter studies have been used to calibrate the models. The models will eventually be used to evaluate alternatives for management of the CSOs.“It’s a precursor to the overall goal of what we have in asset management,” he says, adding that this step became even more necessary of late as a result of Pittsburgh’s Water and Sewer Authority and the City of Pittsburgh signing a consent order with the Pennsylvania Department of Environmental Protection requiring the municipality and the agency to press forward with issues relating to asset management as a result of the CSOs posing a significant threat to the area’s waterways.Rudolph’s firm is refining mapping as a component of the model building and data collection toward the overall goal of controlling discharges “to the extent they are no longer contributing factors to the water-quality standards,” Rudolph notes.The primary software being used in the asset management effort is Wallingford Software’s InfoNet, with InfoWorks being used for modeling. InfoNet is an asset and data management system for water networks, including water supply, water distribution, wastewater collection, sewers, and stormwater. InfoNet allows users to store, validate, review, clean, edit, and analyze water infrastructure data in one database; improve planning and operational decision-making; and increase the value and use of spatial and asset data. Together the programs address the whole water cycle, from supply and distribution, urban drainage, and wastewater management through river management modeling.The software has helped centralize data for Pittsburgh to increase efficiencies previously not realized, Rudolph says. “We are trying basically to redesign their work flow processes and how they conduct their business on a day-to-day basis,” he explains. “The time savings is immeasurable; rather than going to filing cabinets and pulling out plans, you have them in front of you in a way that’s centralized and easy to use.”As a result of the software, Pittsburgh’s water and sewer employees are able to expand their abilities to accomplish more tasks. “It’s taken a tremendous investment to get all the data and do everything they need to do, so it’s costing them a lot of money right now, but the efficiencies are going to be realized in the long run,” Rudolph says.The software is user-friendly with the learning curve relatively short, Rudolph notes. “You can look at the asset, its size, its depth, what the pipe material is, the diameter, and all of the physical characteristics. If there is other information from field surveys or other inspections, it’s available there, too.“It’s really clicking on the buttons. The tough part is learning how to integrate the data, standardizing information collected out in the field so you can bring it back in.”Baytown, TX
Baytown, TX, a Phase II community of about 65,000 residents, is initiating a stormwater program expected to go into full swing once the Texas general permit is passed. Data management software is helping town officials handle the requirements.“We do construction-site inspections, public education, public involvement, and good housekeeping without any ordinances,” says Rory Lang, stormwater coordinator. “At this point, everything is educational–even the inspections–but we are tracking everything we do. We do everything in the stormwater division that helps us comply with the Phase II permit.”To that end, Baytown uses the MS4 Permit Manager software from CBI Systems. The software helps organize and manage stormwater program implementation tasks, collects and manages program data, automatically reports program status, and automatically produces a draft of annual reports. “It’s been really great as far as the construction sites,” Lang says. “It helps us track the construction sites and also maintains the schedule of when inspections need to be done for the sites.” Lang says the software also helps organize photos and documents sent to site operators based on inspections.“In addition, it’s helping us keep track of the various things we do, such as the events we hold and the brochures we hand out,” she says.Previous to using the MS4 Permit Manager software, Baytown tracked its stormwater efforts using Excel spreadsheets. “This is far easier; it’s more manageable and gives us way more information and scheduling ability,” Lang says. Lang’s team never had formal training in using the MS4 Permit Manager software. “We just loaded it on our computers and started working with it,” she says. “There might be some questions on how to pull reports and specific information you may want, but it’s very intuitive,” she says of the software’s learning curve. Lang advises municipalities to seriously consider incorporating data management software into their stormwater programs. “Any kind of software you can get will help you manage all of this, especially for those municipalities that aren’t going to be able to have a large staff to help manage it, input it, file it, track it, and keep it for the life of the permit in case you get audited,” she says.“The software is definitely the way to go. Don’t try to do it with Excel spreadsheets or a simple database,” she adds. “Definitely get something that will help track everything you need to track, because it is easy to forget small pieces of what’s going on.”St. Johns River Management District
In Florida, helping northeast Florida municipal officials make decisions on flood control measures is one way the St. Johns River Management District employs data management software. The District is responsible for managing ground- and surface-water supplies throughout 18 counties in northeast and east-central Florida. Michael Daly is a GIS analyst with the St. Johns River Management District’s Division of Hydrologic Data Services. The duties of the division’s hydrologic data collection program include collecting, processing, managing, and distributing hydrologic and meteorological data used for consumptive use permitting, water shortage management, establishment of minimum flows and levels, water supply planning and management, environmental protection and restoration projects, and operation of the district’s flood control facilities.The division operates more than 1,200 monitoring stations and also processes data from about 300 additional sites collected by county and municipal agencies through mutual agreement or by the US Geological Survey under contract to the district. Each year, more than 12 million measurements are collected, verified, processed, and stored. Data collection is done concerning water levels, surface water, groundwater, and rainfall and provided to other groups within the water district.Daly says the software sequence starts with DataWise (by DEC Data Systems) that is used to transfer rain gauge data via FTP to OneRain Corp. OneRain provides NexRad data in text files that are uploaded to Oracle. From Oracle the data are accessed via ESRI’s ArcGIS and ArcIMS using a .NET/Link. The St. Johns River Management District has a network of rain gauges on a telemetry system that continuously records the rainfall and is recorded through a datawide system, which in turn is provided to OneRain. OneRain uses the data to adjust the radar rainfall to come up with estimates, which are sent back to the water district on an ongoing basis. The district has some in-house custom applications into which the data are fed, built on top of the ESRI Arc map platform. The engineering department, for example, uses the data by looking at the actual rain gauge amounts and the radar rainfall to determine if flood control measures need to be initiated. Daly says the water district has become much more automated in terms of data collection. “We now are trained to move more of our equipment onto telemetry where the data stream flows right into headquarters, and with that, there are fewer personnel in the field taking measures at the site,” he says. Field personnel now are spending more time doing maintenance and troubleshooting at the automated site. “For the same amount of men, we are collecting a lot more data because of the automation,” Daly says. Though he does not know precisely how much, Daly says the district derives a significant cost savings through the automation. “In the last five years we’ve lost about four people from retirement, and those positions have been eliminated without having to lay anyone off,” Daly says. “We’ve been collecting more data with fewer people.”The learning curve is fast, Daly notes. “Most of our staff is technically savvy, so we have continuous training on a regular basis as we get new versions of the software to keep them trained and up to date,” he says.Additionally, a large amount of data has been put onto the Internet for the public to view, Daly says. “If there is a storm, people can now go onto the Internet and look at the estimated rainfall in their neighborhood. If there’s a serious event like a hurricane, they have the ability to look at the water levels. If there are a few areas that are flood prone, they can look at the graphs and see what is going on.” Water level data are available almost in real time–updated every hour or so–enabling the communities and individuals to track serious rainfall events and take appropriate action.“That could become critical to [cities] if they need to make a decision of emergency management in terms of evacuating an area,” Daly says.The St. Johns River Management District also has developed a redundant system of telephone line and satellite data transmissions for critical flood control areas whereby if one system fails, the other can be accessed.
Sign up for Stormwater Solutions Newsletters
Get the latest news and updates.