An Interface for Retrieval and Analysis of Hydrologic and Water Quality Data

Background

The Mission of the District is mandated through legislation and is to monitor and protect the natural resources in South Florida. The District responsibilities include managing flood control, providing water supply, preventing salt water intrusion, encouraging responsible agricultural and urban development, and preserving fish and wildlife habitat. Additionally, the District is responsible for protecting the Everglades and acts as local sponsor for federally funded and authorized projects. The District currently employs more than 1,600 employees to accomplish this mission.

District's mission is accomplished through the combined efforts of planning and research, operations and maintenance, community and government relations, land management, regulation, and construction. These groups collect and maintain substantial amount of data that is ultimately used in support of carrying out the Agency's Mission. For example, decision support and environmental modeling activities rely on availability of water quality and quantity data for the regions in question.

District has selected ArcInfo Geographic Information System and the Oracle relational database management system in 1989 as the software tools for display, analysis, and maintenance of the data collected by different groups. District's system is comprised of a networked, distributed GIS with more than 58 floating ArcInfo and ArcView licenses.

Based on the findings of an internal study conducted in 1993-1994 regarding GIS Graphical User Interface (GUI) for Oracle Databases, similarities between various District efforts were identified, and the goal of a unified interface for all District projects was agreed upon. The unified interface design included access to corporate databases, models and applications, and project areas. The corporate database component of the interface included the development of an interface to Hydrometeorologic, Water Quality, Permit, and Operations database. This paper describes the development of the Hydrometeorologic and the Water Quality databases which were included in the initial development phase.

System Requirements

• Step 1 - Select an area of interest using a number of different selection mechanisms such as by county, basin, or water quality project area.
• Step 2 - Select the parameters of interest from the target database (Hydrometeorological or water quality).
• Step 3 - Dynamically construct a coverage that has the stations which have data matching the selected characteristics in Step 2 for the area selected in Step 1.
• Step 4 - Allow for selection of a subset of the displayed stations using a cursor or a table lookup.
• Step 5 - Return the parameters selected in Step 2 for the selected stations in Step 4.
• Step 6 - Allow for graphical display and formatting of the resulting data based on user's choice.

Step 1 was a typical GIS operation for selecting a rectangular region (or polygon) based on one or more themes. In some instances, there may not be any specific coverage associated with the area but one may need to be developed dynamically. In case of water projects, the bounding rectangle that has all of the sampling points was to be developed dynamically based on the coordinate of the sampling stations.

Step 2 required development of specific GUI forms containing text fields, scrolled regions, and choice buttons to display the list of parameters that can be selected by the user.

Step 3 required processing a Structured Query Language (SQL) statement to retrieve the coordinates of the stations that matched the criteria. The retrieval was done against the summary tables in the database which contained statistical data such as period of record for any given station. The coordinate data was then used to dynamically construct a theme for display and selection purposes.

Step 4 was a typical GIS operation that included selection of one or more points from the displayed coverage using the cursor or from the associated point attribute table.

Step 5 required processing of an SQL statement to retrieve the actual time series data for the parameters that were selected in step 2. It is important to note the same SQL statement has to be processed for each selected station. For example, the pH had to be returned for 300 stations that were selected in Step 4.

Step 6 required formatting and display of the resulting data in a time series graphics package or for import into other systems such as Microsoft Excel and Hydrologic Engineering Center (HEC) DSS program.

Embedded in the above steps was the necessary requirement for maintaining multiple connections to several oracle databases while processing the data. Given the requirements above, a choice had to be made between ArcInfo or ArcView software to develop the user interface.

ArcInfo vs. ArcView

• ArcView's generic connection to Oracle contained a number of shortcomings which could potentially impact the application performance. One of these short comings included the need to remove any permanent connections from ArcView in order to eliminate the refresh operation triggered by entering and existing ArcView.

• Another database related limitation had to do with the fact that ArcView did not support cursors which resulted in compilation and execution of the same SQL statement many times while retrieving the same data for many stations.

• ArcView did not provide for an easy to use mechanism for constructing custom forms and dialog boxes. This was a key requirement for being able to implement Step 2 of the System Requirements.

• ArcView did not provide an easy mechanism to generate time series graphics. The generic graphics in ArcView follows a business model which does not support date and time on the x-axis.

System Design

By turning ArcView into an RPC Server, each tool could be invoked from within ArcView and could become an RPC Client in order to communicate back to ArcView. RPC turned out to be an excellent mechanism for controlling the tools that were needed to address the ArcView limitations.

The tools needed for addressing the ArcView limitations included a Hydrometeorologic and a Water Quality GUI RPC client, an Oracle Interface (OI) tool for efficiently processing queries, and a Formatter tools for processing the output data resulting from the query. The GUI RPC clients were implemented using native OpenLook widgets on SunOS. These tools allowed for display and selection of the query parameters by the user. The options selected by the user were then written into a data file for lookup by the OI tool.

OI was developed to provide cursor processing of an SQL select statement based on a parametric substitution of the user selected options. OI provided progress status (i.e., number of records retrieved) back to ArcView using the RPC mechanism. OI generated a standard output which could further be formatted using the Formatter tool.

The Formatter included support for generating output files for TSPLOT and XMGR-ACE/gr time series plotting packages, HEC DSS, and comma separated values. The Formatter was developed using a plug and play architecture to allow for adding support for future formats.

The time series plotting was done using several customized versions of public domain time series plotting software, i.e., TSPLOT and XMGR-ACE/gr. The key to enabling the linkage to these packages was the ability of the Formatter to format the retrieved data accordingly.

Figure 1 shows the data flow between the different components of the systems. This figure does not included the GUI servers as they are considered to be part of the ArcView interface.

Conclusions

Author Information

Joe Chapa
South Florida Water Management District
3301 Gun Club Road
West Palm Beach, FL 33406
407.687.6391
407.687.6442
joe.chapa@sfwmd.gov

Michael Inada, PE
GIS Consultant
Diba Consulting Software Engineers
26980 Crown Valley Parkway
Mission Viejo, CA 92691
714.347.7581
714.347.7565
mci@dcse.com

Nancy Lin
South Florida Water Management District
3301 Gun Club Road
West Palm Beach, FL 33406
407.687.6391
407.687.6442
nancy.lin@sfwmd.gov

Tim Scharff
South Florida Water Management District
3301 Gun Club Road
West Palm Beach, FL 33406
407.687.6391
407.687.6442
tim.scharff@sfwmd.gov