After initial acquisition and use, borehole and water-well log data are too often hidden in state, town, or corporate file cabinets where they are typically not readily available and useful for future projects. Moreover, even when accessible, the data are often not optimally useable because, locations are not well defined, core logs are incomplete, or the method and nomenclature used to record the data were confusing or inaccurate. This results in considerable duplication of recollecting borehole data, and unnecessary added expense. Thus, the ability to store, retrieve and analyze subsurface geotechnical and geologic data is a key component of efficient planning and management of transportation systems.
To date, through the efforts of two URI Transportation Center funded projects (Geologic Transportation Maps For The 21st Century and A Web-Based Core library for Rhode Island) and a RIDOT project (Digital Databases to Create Geologic Maps) we have developed the capacity to store basic subsurface data elements in a unified digital geographic information system database that will be accessible over the internet. This database marks a significant advancement for the State of Rhode, permitting rapid identification of existing boring locations and retrieval of key subsurface data. The result of the two URI Transportation Center-funded projects (hereafter referred to asURITC1 & URITC2) were a CD-ROM and an interactive web site (at http://geo.uri.edu/borehole).
A limitation of this database, however, is the absence of a complete subsurface record (layer by layer visual description, blow counts, etc.) and associated layer-specific data (laboratory analyses and geologic / geotechnical interpretations) in a unified database that provides real-time digital access to the full subsurface record (for example, display of cross-section results). Commercially available software such as gINT? or ROCKWORKS® permits storage of boring data (and associated field and laboratory measurements) in a database; however these data are not part of a spatial relational database or geographic information system.
This need will be addressed through the development of a multi-dimensional relational database management system (compatible with gINT? format), which will permit storage and retrieval of the complete boring record and associated subsurface data. This relational database will use Access for data storage, ArcView for data access, and ROCKWORKS ® for manipulation of geologic data This will permit interactive querying and display of borehole lithology, retrieval of laboratory data, and access to all borehole related information. Once the database is established, RIDOT contractors will be able to submit their geotechnical data in electronic format. This will ensure continued population of the relational database with up-to-date subsurface information.
In URITC1 & URITC2 we chose a pilot area centered about the University of Rhode Island campus. The proposed work will focus on Providence Rhode Island, where several major transportation-related construction projects are underway or in the planning stages.
Summary of similarities and differences between our current and proposed work.
1. URITC 1
• Title: Using Digital Databases to Create Geologic Maps for the 21st Century
• Product: A CD-ROM that contained a compilation of important geologic, cartographic, environmental, and cultural data for the URI region. The self-contained CD contained simplified viewers and GIS capabilities.
• Strengths: This marked the first comprehensive attempt to develop a GIS-based catalog of borehole sites in Rhode Island. The resulting self-contained CD can be used easily by individuals with little or no training in GIS, databases, etc.
• Limitations: The database contains a relatively sparse set of data on subsurface relations (depth to bedrock, depth to water table, material at base of borehole).
• Intended audience: 1) state and local departments of transportation; 2) other state agencies dealing with environmental management issues; 3) town planners; 4) conservation and environmental groups.
2. URITC 2
• Title: A Web-based Core Library for Rhode Island
• Product: An interactive web site where individuals (with password access) can submit or download data on the subsurface geology along transportation corridors in the vicinity of URI. The web site may be accessed at http://geo.uri.edu/borehole
• Strengths: Although the target area is the same as that of URITC 1, the number of boreholes and well logs is greatly increased. The web site is user-friendly, and one can learn how to access the data and carry out simple queries relatively quickly. Additional fields were added to the borehole data template permitting identification of significant subsurface units (organic soils, high blow count zones). The database can be queried on-line and results can be viewed on screen or down-loaded into and Excel file.
• Limitations: The complete down-hole boring record (blow counts, visual description) is not stored in the database. A user must reference the borehole and retrieve the original log sheet to view the complete borehole record.
• Intended audience: Same as above, plus drilling and engineering firms.
3. URITC 4 (This project)
• Title: Harnessing the Power of Relational Databases for Management of Geotechnical and Geologic Data
• Product: An interactive, multi-dimensional, relational database that accommodates complex queries over multiple fields. The database will incorporate the complete boring record. The database can be queried in Access and spatial relations can be viewed in ArcInfo or other GIS programs.
• Unlike the database incorporated in URITC 2, this one will permit all of the sophisticated data manipulation that characterizes the largest, industrial-grade databases. Moreover, it will be populated with detailed information on boreholes in the Providence area.
• Limitations: its use requires familiarity with Access in order to query data. The database will not be accessible in an interactive mode via the web site. Direct web access through an Arc IMS interface is a goal for the next stage in our development of powerful, interactive, GIS-oriented methods for the management of subsurface data in the context of transportation systems.
ion available
Intermodal
The proposed work significantly extends our effort to establish a computer-based framework for the management and analysis of subsurface data as they pertain to the maintenance and planning of transportation networks in Rhode Island that will serve as a national model. It builds upon two previously funded RI Transportation Center projects. In those projects we developed a CD-ROM and an interactive, website-based borehole library that addressed two vexing and costly aspects of highway planning and maintenance: 1) the storage of borehole logs in an easily retrievable form; and 2) the inclusion of that information into a GIS-based framework that permits the efficient integration of subsurface geology with other data sets, such as those pertaining to environmental or land-use issues. These efforts culminated in the development of a website where one may access borehole logs from the Providence and URI regions, sample profiles of the subsurface geology, and RIGIS data. Additionally, it provides for on-line data entry direct from the driller or geotechnical contractor.
Recent presentations of these results at the 1) 42nd Annual Northeast States Geotechnical Engineers Conference; 2) The Rhode Island Transportation Forum; and 3) the Geological Society of America Annual Meeting have been enthusiastically received. Participants noted that our effort represents one of the most promising examples of digital analysis of subsurface data in a transportation context in the country. In short, we are poised to become national leaders in integrating GIS based-subsurface systems toward transportation issues. However, our current database does not provide access to the complete borehole record (e.g., downhole record of blow counts, sediment or rock composition, soil properties, ground-water chemistry, etc). Currently available technology will enable us to expand our, web-based core library into a multi-dimensional relational database, with the ability to store a complete subsurface record (layer-by-layer visual descriptions, blow counts, etc.) and associated data (laboratory analyses and geologic/geotechnical interpretations) in a unified database that provides real-time access and interactive capabilities. The sites will be georeferenced permitting spatial display and analysis of data in the database. This relational database will allow a much more complete and sophisticated analysis of subsurface data and will be open to accept and incorporate other datasets in the future.
The major tasks of this project center about the design and implementation of a multi-dimensional, relational database that will enable the efficient management of subsurface data as it pertains to the planning and maintenance of transportation systems. Much of the actual data used to populate the database will be drawn from our ongoing RIDOT project. Specific, chronologically arranged tasks are as follows:
1) Literature search: A literature search will be carried out, in order to identify other examples of Access-based relational databases that are linked to GIS systems, with emphasis placed upon those systems that address transportation issues. This task is discussed more fully under a subsequent section (i.e., Relation to Other Research or Projects).
2) Development of database structure: Based upon discussions with stakeholders and a literature search, we will design the basic framework for the database. The existing borehole database template will serve as the main data table that will link to associated data tables (layer descriptions, laboratory data, geologic interpretation, etc). In URITC 2 that template formed the basis for construction of the queries and forms seen on the web site.
3) Construction of interactive, relational database: This task entails development of the Access database to meet the needs of this project. It includes the design of necessary Access objects (i.e., tables, forms, queries, reports, etc.).
4) Modification and improvement of log template design: Starting with the Access-based templates developed in URITC2, we will prepare new templates that will allow access to all data in driller’s logs, and associated laboratory and interpretive data thus providing much more diverse and useful manipulation of data. We will provide additional tables in the template that summarize and group sediment types for easier interpretation and use of the logs. This template will be compatible with gINT? geotechnical software, which is widely used in the logging of boreholes and wells.
5) Population of database: The database will be populated with borehole and well data from the Providence area (see Figure 1). These data were collected as part of our current RIDOT-funded analysis of borehole data from Providence.
6) Linkage to web site: The web site developed in URITC2 will be linked to the relational database.
7) Generalization of database: Based upon discussions with state and national transportation officials, we will outline ways in which the relational database can be modified and applied to other areas and to the management of other types of transportation data.
8) Construction of geologic cross-sections: Key illustrative cross-sections showing Quaternary stratigraphy (gravel, sand, silt and clay), water table, depth to bedrock, and type of bedrock, will be developed. These demonstration diagrams will be useful in instructing individual users how to construct their own personally designed cross-sections and maps.
9) Meetings with private/public officials: This task will publicize our final product to the audience most interested in the utilization of subsurface information in the planning and maintenance of transportation systems. We will demonstrate the data contained in the interactive, relational database, and educate the public in methods to use and manipulate the data. Those interested in the database include, but are not limited to, the following: geologists and engineers at RIDOT, DEM, CRMC, engineering and environmental companies, and various Town engineering and planning departments; other scientists in the above agencies and companies; and environmental scientists in various non-governmental agencies including the Nature Conservancy, Audubon, Save The Bay, and local watershed groups. Moreover, preliminary discussions with the members of the National Transportation Board and transportation agencies in other states have indicated an enthusiasm for, and a need of, the kind of relational database we propose to develop.
The project will proceed according to the following schedule, and with the following milestones. The schedule is based upon an April 1 start date.
Apr. 1 - May 31, 2002
• Initiate meeting with partners and initiate design of relational database.
• Carry out literature search (Task 1).
June 1- September 30, 2002
• Develop and construct relational database (Initial version) (Tasks 2-3)
• Modify log template design (Task 4).
• Begin population of database (Task 5).
October 1 – December 31, 2002
• Complete Tasks 2-5
• Prepare illustrative cross-sections (Task 7).
• Present results of project at appropriate national professional meetings and venues (Task 8).
• Develop strategies for the application of the relational database, with GIS linkages, to other transportation programs (Task 8).
• SEMI-ANNUAL REPORT: Apr. 1 through Sept. 30; submitted OCTOBER 31, 2002.
January 1 - March 31, 2003
• Provide linkage between our URITC 2 web site and the relational database (Task 6).
• Prepare final draft report (January 31, 2003)
• Complete final report, with revision as indicated by reviewers
May 1, 2003
• SEMI-ANNUAL REPORT: Oct. 1, 2002 through Mar. 3, 2003; submitted MAY 1, 2003.
$139,485.00
At least one graduate student and two undergraduates will play a substantial role in the project. This will constitute a significant experiential learning opportunity for the students involved. As of today (July 11, 2002) we have involved one graduate student (Udayan Datta, a 2nd year graduate student in Computer Science) to develop the architecture for the relational database, and an undergraduate woman to work on data acquisition (who is funded through the “external match” component of the project). Both students represent underrepresented groups in the sciences.
We have initiated discussions with individuals who are familiar with applications of relational databases to transportation systems, including those we have met in the course of presenting the results of our transportation studies at national conferences. As an outgrowth of these discussions we have begun to explore ways in which our previously funded (i.e., the web site) and proposed work (the relational database) can be linked to ongoing transportation system research in other states, as well as data management in other areas such as water quality and health issues. Additionally, a literature search of the TRIS and RIP online libraries will be done, in order to identify existing projects that focus on the application of GIS and relational databases to the storage, retrieval and analysis of subsurface geotechnical data. Although the application of GIS is widespread, its application to the analysis of subsurface data was not evident in the search results. The Departments of Transportation in Virginia (VDOT) and Georgia (GaDOT) have programs designed to improve storage and access to borehole data. VDOT is evaluating a web-based borehole data entry and manipulation process. Data are entered remotely over the internet and the resulting file is processed with gINT? geotechnical software and posted on the internet in a read-only format. GaDOT contracted with Geosynthec Corp. to develop a program that permits the use of PDAs (personal data assistants e.g. palm pilot) for borehole data entry. These data are uploaded into a two-table relational database in Access and transferred to gINT? for plotting of geotechnical logs. This database is not, however, georeferenced to a uniform coordinate system.
The work proposed herein complements and builds upon our current transportation project, but does not duplicate or conflict with, any current or planned research. The importance and need for a GIS-based borehole library was an important finding stemming from our current transportation project. This work poignantly shows that borehole records and the valuable subsurface information contained therein are in a great state of disarray once the initial project is concluded. Our proposal will provide for extended usefulness of borehole data derived at substantial drilling costs.
This project will make use of information on Quaternary geology generated by the StateMap mapping program of the Rhode Island Geological Survey (funded by the US Geological Survey) and on the bedrock map of Rhode Island recently published by us.
A major product of our research will be the relational database borehole library, which will reside on an Internet site, which we will maintain. We will also prepare custom CD’s of the library, at cost, for those who require the information in that format. In addition, we intend to hold workshops that will promote awareness of the borehole library, and facilitate its application
The major direct benefit of this project will be to provide a pilot example of an interactive relational database, populated with borehole- and well-log data that can be widely accessed and easily manipulated. Our product will permit borehole data derived from highly costly drilling projects to be preserved in a user friendly data library for future applications. A significant benefit to the State of Rhode Island is the ability to view 3-dimensional spatial data derived from drilling projects in the State. This will mark a major technologic advance. Provisions in the design will permit updating of the database as additional areas are added to our pilot project. We will also develop a method to permit new data to be added to the database as it is collected from new drilling in the field. The pilot example also will be of benefit at the national level, where workers from other regions and states can learn how to use similar database management protocols in their regions.
Database, relational database, Access, boreholes, wells, geology, hydrology, core log, subsurface, geotechnical, land-use, transportation, environmental impact