Utility Water Usage Data Access and Analysis Using ArcView 3.0

Bruce Rindahl
Cliff Inbau

Utility Water Usage Data Access

and Analysis Using ArcView

Abstract

The City of Aurora's Utilities Department is utilizing a GIS to analyze water usage within the city service area for multiple projects. Esri's ArcView 2.1, and later ArcView 3.0, was chosen for accessing water usage information because of its power in SQL connectivity, Avenue script customization, spatial data analysis, file input/output, and graphical user interface. Using ArcView, the Utilities Department is able to access and analyze both current and historical customer water usage data by linking directly into an INFORMIX database. Applications include studies in water conservation, lawn irrigation return flows, water resources planning, and a water audit program. This paper will lay out the steps in the development and use of this tool including Avenue script customization and database design.

Study Area and Project Background

The City of Aurora is a community of approximately 247,000 located in the southeast Denver metro area, as shown in Figure 1. Aurora is the third largest city in the State of Colorado. The service area of the Utilities Department is about 135 square miles.

Figure 1 - Location Map of Aurora

Because the Colorado's Front Range population is growing, water planning is vital for cities to provide water to citizens. The City of Aurora Utilities Department is currently using GIS to assist in increasing and managing water supplies, as well as studying ways to reduce use of water through conservation.

The main project which will use GIS is the Lawn Irrigation Return Flow Project, a potential new source of water supply for the City of Aurora. Under Colorado Water Law, the City has the right to reuse water supplied to its customers, if the water is from another basin. Water from out-of-basin sources can be used and successively reused until extinction. Additional sources of water that can be reused include nontributary groundwater and consumptive use agricultural water. On an average year, the City of Aurora has the potential to reuse 93 percent of its water supply. A percentage of the reusable water delivered to customers is applied on lawns for irrigation purposes. Most of the water that is used for outdoor use is consumed through evapotranspiration by grass, trees, and shrubs. Additional losses occur from runoff and spray loss. The remaining amount percolates through the root zone and returns to the stream. By quantifying the amount of reusable deep percolation water reaching the stream, the City of Aurora can reuse the water for park and greenbelt irrigation, augmentation of wells, and for exchanges (diverting water upstream and replacing the diversion with a like amount from a downstream source). ArcView and its link to the Utility Billing database will assist the Department in determining water usage applied to lawns and irrigated areas.

In addition, the Utilities Department understands that conservation is also important for long range water planning. Since the early 1980's, the City of Aurora has utilized an aggressive water conservation plan. The GIS will assist the Department in analyzing usage, usage patterns, and targeting high water users for a voluntary water audit. The Audit Program is geared toward reducing service disconnects, increase water use efficiency, and for educational purposes.

In 1992, the Department first started using a GIS to analyze water usage within the City. However, this GIS and Utility Billing database setup was less than ideal. Due to software and hardware limitations, a direct link between the Utility Billing database (AS 400) and the GIS database was not possible. The process of acquiring the data was very labor intensive and time consuming. It involved using the results from a GIS address search and inputting them by hand into a AS400 terminal to query water usage information. These results then had to be analyzed statistically using spreadsheet software.

Not until early 1995, with the release of ArcView 2.0, did it became evident that linking the GIS to the Utility Billing database was possible. ArcView was ideal for this task because of its Structured Query Language (SQL) connectivity, Avenue script customization, spatial data analysis, file input and output, and graphical user interface. Knowing the benefits of accessing water usage data through ArcView, the Department proceeded with the development of the GIS and the usage data base.

Database Design

INFORMIX

The Utilities Billing Section uses AS400 as the billing database. As stated earlier, the AS400 is not suitable for use with ArcView or ArcInfo. Therefore, once a month the Utilities Water Resources Division loads billing information from the AS400 into an INFORMIX 7.0 database located in the Water Resources Division. The INFORMIX database contains water usage data and customer information for the years 1992 through the present.

Two INFORMIX tables are used for the Utility Billing database. The first table is the master reference table, called MASTER_REF. The master reference table contains customer number, GIS number (which can be related to address points in ArcView), meter types, service and mailing address. The second INFORMIX table used in this project is the water usage table, called WATER_USE. The WATER_USE table includes customer number, billing dates, water usage and sewer usage.

Currently there are over 2.78 million records in the WATER_USE table, which contains all water usage information for the years 1992 to 1996. Once a month this table is appended with current billing data from the AS 400 database. The MASTER_REF table is currently about 102,000 records in size. Because of its relatively small size this table is re-built monthly to reflect new billing accounts that come on line.

To assist in querying the water usage table, views were setup in INFORMIX of the master reference table. Views hard code query statements for user convince and faster database queries. For this project, six views were created from the master reference table based on the water schedule type (the type of account). These views are s_rate, i_rate, b_rate, a_rate, t_rate, and r_rate for single family, irrigation, commercial, apartments, townhomes, and raw water irrigation, respectively. In addition, to increase performance SQL statements are written to create a temporary INFORMIX table in memory called TEMP_USE.

Coverages and Libraries

The coverages use for this application include address points, streets, drainage basins, parcels, and the City limits. Address points attributes contain an item called GISTAG, which can be related to the item GIS_NO in the MASTER_REF table. In addition, this project utilizes ArcInfo libraries for the address points and parcel layers for improved query performance.

System Usage and Queries

Queries of water usage can be accomplished in two ways using this application. The first method is to select an area of interest or individual addresses. The second method is to query the water usage database directly based on certain criteria. Both methods have customized Avenue scripts that were developed for user friendly queries.

The first approach to accessing the water usage database is to select an area of interest or to select individual address points. This method is useful for determining water usage within a drainage basin or water use in parks and other large irrigators. An example of this type of query follows. The ArcView application has a view of the City of Aurora, containing streets and the City boundary. The first step is to select the Global icon to set some global variables for the application. This only has to be done once. The next step is to zoom into the area of interest. Large area queries are time and CPU intensive, so it is better to narrow down the query selection first. Next, the user must set the area of interest for the library themes (e.g. address points) using the area of interest icon. Once this is set and indexing is complete, the user can then select features of the active address theme. After the address points are selected, the Geo Select icon is pressed to start a menu driven SQL query builder. The first menu is the Rate Selection Menu, which allows the user to select the type of account. For example, there is a choice between all types, single family, irrigation, commercial, apartments, townhomes, and raw water irrigation. The next menu is the Time Selection Menu. This menu allows the user query any month, irrigation season, or winter months. The Year Selection Menu follows, allowing any year from 1992 to be selected or all years can be analyzed. Input from these menus builds a SQL statement for an INFORMIX query to the master reference and water usage tables. Once the query is completed, the user is prompted to link the newly created table with the attribute table of the address points. The resulting table can be analyzed for water usage.

The second approach to querying water usage is querying the INFORMIX water usage and master reference databases based on a selection criteria. This query is useful in determining usage information throughout the service area. To optimize table queries, this script does a quick paring down of the water usage table based on month, year and usage criteria selected by the investigator. Then the full SQL statement is compared to the pared down table to produce the final usage report. This query starts with the user selecting the Usage Select icon while the view window is active. A rate menu pops up for selecting the type of account (e.g. single family, irrigation). Next, the time selection menu asks for which month, followed by the year selection menu. Here the user can pick any year for analysis. Next, the meter size menu pops up, allowing the user to select a particular meter size or all meter sizes. The final menu is for the usage selection. In this menu, the user can input a value of usage (in 1000 gallons) as a criteria, i.e. usage greater than 5000 gallons. With the completion of the query string and search, a usage table is returned.

ArcView Customization and Performance

The use of Avenue and its capability has been very useful to the success of this water usage GIS project. Avenue allowed the Utilities Department to build SQL query statements through interactive, user friendly series of menus to access information on water billing information. Detailed discussions on the two main Avenue scripts are discussed below.

Geo Select Script

The first script, called Geo Select, builds a SQL query string of account type (rate), time frame (months), and year to create a usage table. The Geo Script is listed in the Appendix. As stated earlier, the key to this particular application is the ability to select address points geographically. This example will focus on analyzing water usage for single family residences in the Shop Creek Basin, a small drainage basin located in the southwest section of the City.

Once the address points are selected in the Shop Creek Basin, the user will initiate the script. The script starts by defining the view and active theme (address points). A variable, F, is set to the field " tag" in the active theme. The BitMap variable is set to the selected address points. Next, a connection is made to the INFORMIX tables and a temporary table named Scratch is created in memory. Each record from the BitMap (selected address points) is inserted into the temporary table. Dictionaries and lists are defined for rate and time. The remainder of the script builds the SQL statement through the use of menus. The first expression is created using the type of account or rate that the user selects, as shown in Figure 1. For example, if the user selects single family accounts (s_rate), the initial expression would look like this.

1) Select s_rate.gis_no, water_sch, add_units, water_use.* from s_rate, scratch, water_use where s_rate.gis_no = scratch.gis_no and water_use.cust_no = s_rate.cust_no

Figure 2 - Rate Selection Menu

The time and year menus will add to the previous SQL statement. In this case, the user selected October, 1994 from the input menus (Figures 2 and 3), the resulting SQL string would be as follows.

2) Select s_rate.gis_no, water_sch, add_units, water_use.* from s_rate, scratch, water_use where s_rate.gis_no = scratch.gis_no and water_use.cust_no = s_rate.cust_no and month(bill_date) = 10 and year(bill_date) = 1994

Figure 3 - Time Selection Menu

Figure 4 - Year Selection Menu

The SQL statement then returns a table of water usage information (Figure 4), and prompts the user to link the newly created table with the address point attribute table. Finally, the script terminates after disconnecting from INFORMIX. By disconnecting from INFORMIX the temporary table is cleared from memory.

Figure 5 - Result Table

Performance with the Geo Select script is good. In the above case where 1855 address points were selected in the Shop Creek Basin, the query to display the usage table took approximately 18 seconds. This is extremely good considering that the query is going through the 2.78 million records in the water usage table. When large areas of address points are selected, the performance goes down. In the case where all address points in the City are selected, the query took approximately 18 minutes (this does not include creation of the temporary Scratch table, which took considerable time). There are two reasons for this, first the time it takes to index and read records from the BitMap to the temporary table increases as the number of selected address points increase. The second reason is the actual query must read more records in both the temporary and water usage tables.

Usage Select Script

The Usage Select script is designed for database queries based not on geography, but rather on usage, rate, month and year criteria. Usage Select starts out by connecting to INFORMIX. Dictionaries and lists are defined for rate, time, and meter size. As a performance enhancement, an initial SQL string is developed to query the water usage table. Like the Geo Select script, the SQL statements are built by menu inputs from users. For example, if the user selected irrigation accounts, July, and 1992, all meter sizes, and usage greater than or equal to 1000, two strings would be created. The first SQL string (String 3) produces a temporary table in memory of water use, month, and year as selected by the user. In this example, a temporary table would be created with all usage greater than or equal to 1,000,000 gallons for July of 1992.

3) Select * from water_use where month(bill_date) = 7 and year(bill_date) = 1992 and water_use >= 1000 into temp temp_use

The second SQL string (String 4) is listed below.

4) Select i_rate.cuto_no, i_rate.gis_no, water_use, sewer_use, water_sch, bill_date from i_rate, temp_use where temp_use.cust_no = i_rate.cust_no

Here the string will return a table listing customer number, GIS number, water use, sewer use, type of account, and billing date, where there is a customer number match between the temporary and i_rate tables.

The Usage Select performance is excellent for specific month and year queries. In the example above a table was generated in 3 seconds showing the 22 irrigation accounts using over 1,000,000 gallons in July, 1992. When the usage query is lower the performance decreases slightly. For example, using a usage greater than or equal to 100,000 gallons the query took about 1 minute and 10 seconds. It should be noted that complex queries, such as multiple months of years will dramatically decrease performance. These complex queries should not be done with this current script.

Future Development and Directions

The ArcView water usage application has gone through many development changes over the past year. Through trial and error, and user feedback, the database design and the Avenue scripts are being modified. Future directions include further refinement of menus to allow greater flexibility for SQL string development. Examples include multiple year analysis, account type and meter size specific queries, and water usage audit queries. There will need to be modifications made to the Usage Select script. Currently, this script works great for specific month and year queries; however, if multiple months or years are used queries are very time consuming. Changes to this script will include different SQL statements for multiple months and years. Another change in the Usage Select will be to allow complex usage statements.

Modifications to the application for future analysis will include adding statistical and graphing tools. Other future development may include down loading real-time weather station data for evapotranspiration analysis, and integrating park coverages and databases into the application.

Conclusion

ArcView has proved to be extremely valuable as a way to link GIS information with an INFOMIX database. User friendly menu can be developed with Avenue scripts to allow custom SQL statements to be pass to INFORMIX. This allows end users, who may not know or have the time to learn SQL, the ability to access the database. Even more important, is the ability of an analyst to query the billing database based on geographic criteria. Before this application, selecting accounts and the associated usage from a geographic perspective was not possible. This tool greatly enhances the Utilities Department's ability to analyze water usage through the City of Aurora for various projects.

Acknowledgements

ArcView and ArcInfo were developed by Environmental Systems Research Institute, Inc. (Esri)

INFORMIX is a trademark of Informix Software, Inc.

AS400 is a trademark of IBM.

Appendix

Sample Avenue Script (Geo Select):


_theView = av.GetActiveDoc
theTheme = _theView.GetActiveThemes.Get(0)
theFTab = theTheme.GetFTab
F = theFTab.FindField("
tag")
if (F = Nil) then
  MsgBox.warning("Theme has NO GISTAG field, please select another theme", "OOPS!!!")
  return -1
end  
theBitMap = theFTab.GetSelection
if (theBitMap.Count < 1) then 
  MsgBox.warning("No Features selected in Address Points theme, please select 
  feature(s)","OOPS!")
  return -1
end

aSQLCon = SQLCon.Find("Informix Version 7")
if(aSQLCon.IsLogin.not) then
  aSQLCon.Login("billing@dilbert_tcp")
end
aSQLCon.ExecuteSQL("create temp table scratch (gis_no Integer) with no log")
expr = "insert into scratch values ("
for each r in theBitMap
  gis = theFTab.ReturnValue(F,r).SetFormat("dd")
  aSQLCon.ExecuteSQL(expr + gis.AsString + ")")
end

RateDict = Dictionary.Make(15)
RateDict.Add("All Types","master_ref")
RateDict.Add("Single Family","s_rate")
RateDict.Add("Irrigation","i_rate")
RateDict.Add("Commercial","b_rate")
RateDict.Add("Apartments","a_rate")
RateDict.Add("Townhomes","t_rate")
RateDict.Add("Raw Water Irrigation","p_rate")

TimeDict = Dictionary.Make(22)
TimeDict.Add("January","= 1")
TimeDict.Add("Febuary","= 2")
TimeDict.Add("March","= 3")
TimeDict.Add("April","= 4")
TimeDict.Add("May","= 5")
TimeDict.Add("June","= 6")
TimeDict.Add("July","= 7")
TimeDict.Add("August","= 8")
TimeDict.Add("September","= 9")
TimeDict.Add("October","= 10")
TimeDict.Add("November","= 11")
TimeDict.Add("December","= 12")
TimeDict.Add("Winter","in (1,2,12)")
TimeDict.Add("Irrigation Season","in (4,5,6,7,8,9,10)")

PickList = {"All Types","Single Family","Irrigation","Commercial","Apartments",
"Townhomes","Raw Water Irrigation"}
TimeList = {"January","Febuary","March","April","May","June","July","August",
"September","October","November","December","Winter","Irrigation Season"}
YearList = {"1992","1993","1994","1995","1996"}

theTemp = "/tmp/temp2.sql".AsFileName
LF = LineFile.Make(theTemp,#FILE_PERM_CLEARMODIFY)

RATE = MsgBox.ChoiceAsString(PickList,"Select Rate Type to Search","Rate Selection Menu") 
SQLT = RateDict.Get(RATE)
expr = "select " + SQLT + ".gis_no,water_sch,add_units,water_use.* from " + SQLT + 
",scratch,water_use where " + SQLT + ".gis_no = scratch.gis_no and water_use.cust_no = 
" + SQLT + ".cust_no"

Time = MsgBox.ChoiceAsString(TimeList,"Select Time Frame" + nl + "Choose Cancel 
for all Months","Time Selection Menu")
if (Time <> nil) then
   expr = expr + " and month(bill_date) " + TimeDict.Get(Time)
end

Year = MsgBox.ChoiceAsString(YearList,"Select Year Frame" + nl + "Choose Cancel 
for all Years","Year Selection Menu")
if (Year <> nil) then
   expr = expr + " and year(bill_date) = " + Year
end

LF.WriteElt(expr)
LF.Close

aVTab = VTab.MakeSQL(aSQLCon, expr)
  if (aVTab.GetNumRecords < 1) then
    MsgBox.warning ("No " + RATE +  " records!","Error")
    return -1
  end
aTable = Table.Make(aVTab)
aTable.GetWin.Open

answer = MsgBox.YesNo("Do you want to link the Table?","Link Table",false)
if (answer) then
  vtab2 = aTable.GetVtab
  field2 = vtab2.FindField("
_no") 
  theFTab.Link( F, vtab2, field2)
  vtab2.Link(field2, theFtab, F)
end 
aSQLCon.Logout

Sample Avenue Script (Usage Select):


aSQLCon = SQLCon.Find("Informix Version 7")
if(aSQLCon.IsLogin.not) then
  aSQLCon.Login("billing@dilbert_tcp")
end

RateDict = Dictionary.Make(15)
RateDict.Add("All Types","master_ref")
RateDict.Add("Single Family","s_rate")
RateDict.Add("Irrigation","i_rate")
RateDict.Add("Commercial","b_rate")
RateDict.Add("Apartments","a_rate")
RateDict.Add("Townhomes","t_rate")
RateDict.Add("Raw Water Irrigation","p_rate")

TimeDict = Dictionary.Make(22)
TimeDict.Add("January","= 1")
TimeDict.Add("Febuary","= 2")
TimeDict.Add("March","= 3")
TimeDict.Add("April","= 4")
TimeDict.Add("May","= 5")
TimeDict.Add("June","= 6")
TimeDict.Add("July","= 7")
TimeDict.Add("August","= 8")
TimeDict.Add("September","= 9")
TimeDict.Add("October","= 10")
TimeDict.Add("November","= 11")
TimeDict.Add("December","= 12")
TimeDict.Add("Winter","in (1,2,12)")
TimeDict.Add("Irrigation Season","in (4,5,6,7,8,9,10)")

MeterDict = Dictionary.Make(20)
MeterDict.Add("5/8 inch meter","A")
MeterDict.Add("3/4 inch meter","B")
MeterDict.Add("1 inch meter","C")
MeterDict.Add("1 1/4 inch meter","D")
MeterDict.Add("1 1/2 inch meter","E")
MeterDict.Add("2 inch meter","F")
MeterDict.Add("3 inch meter","G")
MeterDict.Add("4 inch meter","H")
MeterDict.Add("6 inch meter","I")
MeterDict.Add("8 inch meter","J")

PickList = {"All Types","Single Family","Irrigation","Commercial","Apartments",
"Townhomes","Raw Water Irrigation"}
TimeList = {"January","Febuary","March","April","May","June","July","August",
"September","October","November","December","Winter","Irrigation Season"}
YearList = {"1992","1993","1994","1995","1996"}
SizeList = {"5/8 inch meter","3/4 inch meter","1 inch meter","1 1/4 inch meter",
"1 1/2 inch meter","2 inch meter","3 inch meter","4 inch meter","6 inch meter",
"8 inch meter"}

theTemp = "/tmp/temp.sql".AsFileName

LF = LineFile.Make(theTemp,#FILE_PERM_CLEARMODIFY)

RATE = MsgBox.ChoiceAsString(PickList,"Select Rate Type to Search","Rate Selection Menu") 
SQLT = RateDict.Get(RATE)
expr = "select * from water_use "
where = "where "

Time = MsgBox.ChoiceAsString(TimeList,"Select Time Frame" + nl + "Choose Cancel for 
all Months","Time Selection Menu")
if (Time <> nil) then
   expr = expr + where + "month(bill_date) " + TimeDict.Get(Time)
   where = " and "
end

Year = MsgBox.ChoiceAsString(YearList,"Select Year Frame" + nl + "Choose Cancel for 
all Years","Year Selection Menu")
if (Year <> nil) then
   expr = expr + where + "year(bill_date) = " + Year
   where = " and "
end

Size = MsgBox.ChoiceAsString(SizeList,"Select Meter Size" + nl + "Choose Cancel for 
all Sizes","Meter Size Selection Menu")

Usage = MsgBox.Input("Select Usage Criteria","Usage Selection Menu","")
if (Usage <> nil) then
   expr = expr + where + "water_use >= " + Usage
   where = " and "
end
expr = expr + " into temp temp_use"

LF.WriteElt(expr)

if(aSQLCon.ExecuteSQL(expr)) then
end

expr = "select " + SQLT + ".cust_no," + SQLT + ".gis_no,water_use,sewer_use,
water_sch,bill_date from " + SQLT + ",temp_use where temp_use.cust_no = " + SQLT + 
".cust_no"
if (Size <> nil) then
   expr = expr + " and water_sch[2] = '" + MeterDict.Get(Size) + "'"
end

LF.WriteElt(expr)
LF.Close

aVTab = VTab.MakeSQL(aSQLCon, expr)
  if (aVTab.GetNumRecords < 1) then
    MsgBox.warning ("No " + RATE +  " records!","Error")
    return -1
  end
aTable = Table.Make(aVTab)
aTable.GetWin.Open

aSQLCon.Logout

Bruce Rindahl, Senior Water Resource Engineer
Cliff Inbau, Water Resource Engineer
City of Aurora, Utilities Department
1470 South Havana Street
Aurora, CO 80012
Phone: (303) 695-7370
Fax: (303) 695-7491
E-mail: cinbau@dilbert.ci.aurora.co.us