After my extremely simple script last week, I was embarassed. Simply too simple, I thought. I had to dive in again and cobble together something more involved.
Here is the back story: One of the first things we did with ArcGIS at my office was to digitize borehole locations for approximately 25 years of “paper only” engineering files. Too many times had we started drilling a geotechnical project only to realize too late that, 10 years prior, another engineer had conducted a similar project nearby that could’ve provided useful information. Creating a GIS containing all boreholes ever drilled, referenced by project number, and maybe linked to borehole logs, made a lot of sense, and the effort seemed to be more about man hours (pulling files, looking up reports, etc.) than technological.
I quickly found that
- Recent files contained borehole coordinates and borehole logs while few older files did.
- Many older (>10 years) files, however contained reliable drawings/maps showing borehole locations, and borehole logs.
- Some older files contained only vague project descriptions, such as Hwy X @ X Creek, and borehole logs
- Few files contained only borehole logs with unclear/questionable site descriptions.
Moreover, I found that my colleagues in engineering had been using Google Earth (GE) to locate proposed boring locations for which clients provided only coordinates and no maps. That added up to several 100s of X,Y points of surveyed borehole data in GE. They had entered Lat/Long data into GE and were accumluating such data there.
Going forward, using Google Earth for entering the mountain of historical borehole data was, therefore, an obvious choice, while keeping track of the quality (‘survey data’, ‘taken from project map’, ‘eyeballed’, ‘estimated guess’) in a spreadsheet. Most queries of our GIS would be for site geology/soils; therefore, surveyed coordinate data wasn’t really a necessity. Plus, by using Google Earth, locating and “place-marking” of historic boreholes based on project maps and descriptions could be delegated to staff w/o access to or experience with ArcGIS .
Digitizing, i.e. “placing placemarks” labelled PROJECT#-BOREHOLE# in GE, took a few weeks. After that, I had a KML file with approximately 10,000 boreholes covering most of North Central Texas. To get this into ArcMAP, I used arc2earth (Cost at the time $199), which – with a few mouse clicks – created a geodatabase (MDB) that I could open/add in ArcMap. Linking to borehole logs, for which we converted all paper logs to PDF, remains an unfinished task to date. But we have, so far, gotten by with pulling the paper files of interest, once the projects have been identified using GIS.
As new projects were initiated and new borehole survey data came in, I was getting tired of having to enter this manually in GE, and didn’t really like the gobble-de-gook results when importing CSV files from Excel into GE, either. Luckily, I had begun learning Python and wrote this little script to convert simple coordinate data in 3-column, tabbed ASCII text files (Borehole#,Latitude, Longitude) to clean, uncluttered KML files that I could open in GE. This was plain Python, without using arcgisscripting.
Well, that wasn’t good enough anymore. So, given some downtime, I wrote a new script yesterday that reads the same kind of TXT file with X,Y borehole data and creates a shapefile, skipping the detour through arc2earth. I will put the code in a separate post, and discuss writing it there.