Data Collection

A countour map is a 2D representation of a 3D surface that is defined by a set of points having x, y, and z coordinates. The x and y coordinates correspond to longitude and latitude respectively and the z coordinate corresponds to the elevation of the potentiometric surface above mean sea level. Therefore, the data requirements of this project include well coordinates, well head elevation, and water level elevation.

I obtained the water data from the Texas Water Development Board web site. The data is located in the "Water Conditions and Data Download Area"

(click on the figures to obtain a larger view)

Next go to "Download TWDB Ground Water Data Base Files in ASCII Text Format" and you will see a disclaimer.

The disclaimer provides a description of the contents and limitations of the ground water data base. The file "Appg.txt" contains a description of the data fields that you will need to understand what is in each file because the fields are not labeled. Right click on "Appg.txt" to obtain a copy or download it from the disclaimer section.  Scroll to the end of the page to find the data download area link that will take you to the ftp area.

From the gwdata directory you can select the county folder of interest.

There are several files available, but the most important are aquifer.txt, wlevels.txt, and weldta.txt. Click on "aquifer.txt" to obtain a copy of the file. It contains a list of aquifer codes associated with the corresponding aquifer names that are needed to identify in which aquifer a well is screened. After selecting the county folder, copy the files: aquifer.txt, weldta.txt, and wlevels.txt.

"wlevels" contains the water depth entries and their corresponding collection dates; however, the well elevation is needed to convert the depth entries to water level elevation.  Here is an example wlevels file where I have added the field labels.

"weldta" contains the latitude and longitude coordinates of the well and the well elevation, but there are too many fields to show an example here. 

Excel is the most convenient tool to prepare the files for use in Arcview.  When opening a file, Excel first needs to know how the file is delimitted.  The TWDB files are delimitted with a "|" character. This character is located on the backslash button.  In order to select the "|" as the delimiter, click on other and type the character in the "other" box.

The "wlevels" and "weldta" files can now be processed, but first two obstacles must be dealt with.  First, the longitudes and latitudes are in a form that is difficult to convert to decimal degrees. For example, 100 degrees, 30 minutes, and 30 seconds would be represented by the number: 1003030. The second difficulty is that the data are split into two files: "wlevels" and "weldta".  These files must be merged so that the water depth field (in "wlevels") and the elevation field (in "weldta") can be added to determine the water elevation.  This also simplifies the querying process in Arcview.

After spending as much as and hour preparing one 100 line file for use in Arcview, I decided to write an Excel macro that would perform all of the tasks that were necessary.  This, in itself, took many hours, but the macro can now prepare an 8000-line file in approximately 30 minutes.   The macro labels the data fields, converts the coordinates to decimal degrees, calculates the water level elevation, and merges the "wlevel" and "weldta" files.  Click here if you would like to obtain a copy of my Excel workbook. 

I developed the following code to make the conversion to decimal degrees:

'evaluate 93 degrees
If (Cells(counter, "G") > 925959) And ((Cells(counter, "G") < 940000)) Then
  Cells(counter, 9).Select
  ActiveCell.FormulaR1C1 = "=(TRUNC((RC[-2]-930000)/100))/60"
  Cells(counter, "H") = "93"
  'calculate seconds
  For counter2 = 0 To 59
    If (((Cells(counter, "G") - ((930000) + (100 * counter2))) >= 0) And ((Cells(counter, "G") - ((930000) + (100 *            counter2))) < 60)) Then
      Cells(counter, "J") = (((Cells(counter, "G")) - ((930000) + (100 * counter2))) / 3600)
    End If
  Next counter2
  Cells(counter, "K") = -(Cells(counter, "H") + Cells(counter, "I") + Cells(counter, "J"))
End If

The macro runs through a series of loops to check for those latitudes and longitudes that encompass Texas.  When a match is found, it calculates the decimal minutes.  Then another loop is required to calculate the decimal seconds.  Finally, the degrees, decimal minutes, and decimal seconds are added.  Finally, another loop searches for a match in the well number field and then copies and pastes the water depth entry from the "wlevels" file to the "weldta" file.

(Note, I will use the following notation to indicate a command that is located in a menu: Menu/Command)

After you have opened the workbook, open the "wlevels" and "weldta".  Then you will have to separately move both files into the workbook in the edit menu: Edit/Move or Copy Sheet and select the workbook as the destination.  If you have given them different names, then you must rename the appropriate sheets as "wlevels" and "weldta" or the macro will not recognize them.  Then go to the Tools menu, then to the macros menu and finally click on macros.  There you will see two macros: "latlon" and "nodata".  Click on latlon to process the files as mentioned before.  The visual basic script is slow, therefore you should use the fastest computer available.  On a 200Mz machine, the macro processes approximately 300 lines per minute.  The macro "nodata" removes all of the entries with no water depth data, but this is optional.  When latlon has finished, make sure that the "weldta" sheet is active and save this sheet as a comma delimitted or *.CSV file with the command: File/Save As.  Before you open the file in Arcview, you should make a note of the aquifer code of the aquifer of interest, located in "aquifer.txt".

Potentiometric Map construction 

Back