Skip Maine state header navigation
A Publication Featuring The Information
Services Technology of Maine State Government
| Volume VII, Issue 2 | February 2004 |
|
|
By Robin Frost
In May of 2003 the Maine Drinking Water Program (MDWP) completed Source Water Assessments (SWA) for over 2200 public water supplies in the State of Maine and made them available to the public. This mass mailing to public water suppliers, towns, and other interested parties was the end result of three years of effort, which included developing a desktop GIS system, several Access database applications, and an Internet mapping site that is part of the community outreach portion of the Source Water Assessment Program.
The surface water assessments were done with the help of a private consultant, Drumlin Environmental LLC, who did on-site watershed reconnaissance. The groundwater assessments were done through an automated process by MDWP staff and interns, using GIS, Oracle and Access databases, and a MS Word mail merge. This process worked very well, but for the future, the MDWP needs to be able to turn out single assessments as new groundwater systems come on-line. We are currently redesigning our databases to create a process that will better support this new focus.
One of the drawbacks to our present system is that data is pulled from the MDWP main Oracle database tables, and a “snapshot” of the data is exported to an Access table. Our redesign will include linking the SWA Access application to the main tables directly, so that whenever an assessment is made, the data will be the most up-to-date that is available. Another improvement will be to create a series of macros in Access that will automatically run the queries that determine all of the risk rankings that are part of an assessment. Rankings include risk based on well type and site geology, existing risk of acute contamination, future risk of acute contamination, existing risk of chronic contamination, and future risk of chronic contamination.
Click map to view
To determine each of these risk factors, several tables from the main database must be queried, and in some cases queries are created to transform data into the form needed for another query. For instance, the data we need to determine if a system has experienced hits of specific contaminants does not exist in the main database in the form needed for an assessment. There may be a number code instead of the name of the contaminant. So a query is run to make a conversion, then the result of that query is used in a larger query to determine the risk factor.
The final result of our database redesign will be a one button process that will ask the user for a well identification number, run all of the needed queries through a series of Access macros, then print a final assessment based on the most up-to-date data available, in a pre-formatted Access report. This application will better serve the future purposes of the MDWP for assessing individual wells as they come on-line.
Robin Frost is the GIS Coordinator of the Maine Drinking Water Program. She may be reached by e-mailing robin.frost@maine.gov or by calling (207) 287-8411. For more information on the Drinking Water Program visit http://www.medwp.com/.
