Introduction
Our earlier research (URITC-1, URITC-2 and RIDOT1 projects) established a template and flat-file database that can be used to catalog diverse types of geotechnical, geologic, cartographic, environmental and cultural data in a GIS format. One of our current projects (URITC-4/RIDOT2) is in the final stages of converting that two-dimensional, flat database into a dynamic, interactive multi-dimensional relational database. The major advantage of this conversion is the ability to incorporate all of the geotechnical data present in a borehole log, including the down-hole data. The earlier version could illustrate only the single-value data fields, but not the multi-value data field. Not only is this relational database more robust, but, more importantly, it captures the three-dimensional nature of subsurface data.
However, at present, only the flat database is available to users through the web-based portal created during the URITC2 project. Internet users are therefore limited to simple queries for subsurface information (such as depth to bedrock), and cannot fully evaluate systematic changes that occur downhole for a given site or for clusters of sites. In addition, users cannot easily construct the kinds of two- and three-dimensional diagrams (cross sections, block diagrams, etc.) that would be of most use. We address these limitations by: 1) developing a web-based portal that will allow both tabular and map viewing of database queries; 2) adding to the capability of submitting more complex queries; 3) developing interactive cross-section capability; and 4) updating data in the database by coordinating incorporation of new drilling data through electronic submission of complete borehole records.
Thus, the goal of this project is to implement a more powerful statewide borehole database for Rhode Island. The project is the continuation of a multi-year project that will culminate in a more user-friendly web-based database. The final product will provide a useful and cost-effective system for the maintenance of subsurface data associated with transportation networks. By allowing easy access to prior geotechnical work (such as drilling, boring, and other data gathering), the project will result in significant savings through the elimination of unnecessary and repetitious exploratory drilling. Because the work is closely linked to our previous and current projects, we describe it in the following section.
Project History
Through the effort of three URI Transportation Center funded projects (URITC-1 Geologic Transportation Maps For The 21st Century; URITC-2 A Web-Based Core library for Rhode Island; URITC-4 Harnessing the Power of Relational Databases for Management of Geotechnical and Geological Data) and two RIDOT projects (RIDOT1 - Digital Databases to Create Geologic Maps ; RIDOT2 - Harnessing the Power of Relational Databases for Management of Geotechnical and Geological Data), we have developed the capacity to store basic subsurface data in a unified digital geographic information system database. This database marks a significant advancement for the State of Rhode Island, permitting rapid identification of existing boring locations and retrieval of key subsurface data. Currently, selected borehole data for the URI area is accessible at the http://geo.uri.edu/borehole. Data for the Providence area will be added upon completion of RIDOT1.
A limitation of the existing web-based 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). In URITC-4, we address this need through the development of a multi-dimensional relational database management system, which will permit storage and retrieval of the complete boring record and associated subsurface data. This relational database uses Access for data storage and ArcView for data display and analysis. It permits 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.
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.
Summary of similarities and differences between our proposed work and our other funded efforts.
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 where individuals (with password access) can submit or download data on the subsurface geology along transportation corridors in the vicinity of URI. The may be accessed at http://geo.uri.edu/borehole. Additionally, a template was developed for use by drillers, which guarantees that future borehole data will be collected in a manner that allows easy incorporation into the database and meaningful comparison to data from other sources.
• Strengths: Although the target area is the same as that of URITC 1, the number of boreholes and well logs is greatly increased. The 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. RIDOT1
• Title: A Digital –GIS Catalog of Borehole Data for the Providence Area
• Product: A flat-file database of borehole data that incorporates selected subsurface interval information (presence/absence of organic soil, low/high blow count and artificial fill horizons). The database will include a virtual library with scanned images of borehole logs and selected geologic cross-sections. The integrated database is presented as a geographic information system (GIS) with tabular data in Access, spatial data in ArcView shape files, and a virtual library accessible through the GIS.
• Strengths: This project expands upon the data template developed in URITC2 by incorporating more detailed subsurface and project information. Over 800 boreholes from the Providence area are included in the database. This project is in the final stages of report preparation with database implementation expected by mid-2003.
• Limitations: This database does not include a complete subsurface record for each borehole. Users requiring this detailed information for constructions of maps or cross-sections must refer to the original borehole logs.
4. URITC 4/RIDOT2
• 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; we refer to it as GEOInfoDB. The database will incorporate the complete boring record for a given site. The database can be queried in Access and spatial relations can be viewed in ArcInfo or other GIS programs. The template developed in previous work (TC2 & RIDOT 2) has been expanded and modified to include this additional information. A simplified version of the architecture of the relational database is shown in Figure 1.
• Strengths: Unlike the database incorporated into URITC 2, this one will permit all of the complex 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. Direct web access through an Arc IMS interface is a goal for the next stage in our development of powerful and interactive GIS-oriented methods for the management of subsurface data in the context of transportation systems. Figure 1 shows the Simplified version of the relational database (GEOInfoDB) that forms the basis of the portal.
5 URITC5/RIDOT3 (This Project)
• Title: An Interactive, Web-based Relational Database Portal for Subsurface Geotechnical Data
• Product: A web-based user interface providing a portal to GEOInfoDB and the capability to construct interactive geotechnical/geologic cross-sections using diverse transects of the GIS coverage. Data accessible through this portal will include all data cataloged during the previous database development phases in the Providence and University of Rhode Island pilot areas. The database will be applicable statewide and will accept new borehole data through an electronic submission protocol. Workshops will be given for the RI Department of Transportation and other members of the transportation community. The following sections describe the objectives and work plan for this phase of our project.
c) PROJECT OBJECTIVES
Our goals include the following:
1) Develop a web-based portal that will allow both tabular and map (ArcIMS) viewing of database queries. At present the available interface allows only tabular viewing of data. This portal will build upon the wed-site developed as part of TC2.
Bring GEOInfoDB (currently under development, through TC4 and RIDOT funding) online, and revise the current web-site (developed under TC2 funding) to reflect GEOIndoDB details.
2) Develop "on-the-fly" cross-section capability. Now that the database can store the complete subsurface lithologic record, we are proposing to couple cross-section capability with the database to allow users to select boreholes and view the subsurface stratigraphy in an interactive fashion.
3) Update data in the database by coordinating incorporation of new drilling data through web-based electronic submission of complete borehole records. This reflects an upgrade of from the data submission protocol developed under RIDOT1 and 2.
4) Coordinate with the MIS staff of the RIDOT the implementation and administration of the database and web portal.
5) Run an instructional workshop for the RI Department of Transportation and other interested organizations clarifying the use and applications of the web-site and database.
Highway
The 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. The resultant product will serve not only RI, but also serve as a national model. The project is a logical follow-up to three previously funded RI Transportation Center projects. In the first of 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 early 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, that project provided for on-line data entry direct from the driller or geotechnical contractor. However, this database did not provide access to the complete borehole record (e.g., the downhole record of blow counts, sediment or rock composition, soil properties, ground-water chemistry, etc).
The goal of the next phase of our work was to expand our 2-dimensional 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 resultant relational database allows a much more complete and sophisticated analysis of subsurface data. Moreover, it was designed to accept and incorporate new datasets as they are acquired in the future. The project takes this work to its logical conclusion. Here we request funds to tightly link the relational database to the website, in a manner that permits the easy and graphical manipulation of borehole data for Rhode Island. Among other tasks, the proposed work will allow one to generate simple cross sections “on the fly” for the Providence area, if one has access to software that has been designed and modified for this project. Specifically, the completion of the proposed work will place Rhode Island in the forefront of GIS- oriented analysis of subsurface data in the context of transportation planning, and will serve as a model for other transportation agencies throughout the country.
The two primary tasks of this project are the development of interactive cross-section capability and development of the web-based portal site for the relational database (GEOInfoDB). All work on the project will be carried out in close communication and collaboration with the RI Department of Transportation, the primary stakeholder in this project. The tasks are as follows:
1) Literature and web search: A literature search and assessment has begun, as part of the preparation of this proposal, and it was included in the pre-proposal we submitted in the autumn of 2002. The search will be continued, with emphasis placed on the identification and evaluation of other GIS-oriented s used by state and federal transportation agencies.
2) Project review meetings with RI DOT staff: Periodic meetings will be held with representatives of the Department of Transportation, including the MIS staff to discuss implementation and database administration issues. This will ensure that stakeholder comments are incorporated into the project throughout the design and implementation phases.
3) Development of an interactive, web-based portal for the relational database: With the benefit of advice from RI DOT and other stakeholders, plus the aforementioned search, we will develop a web portal for GEOInfoDB. Several issues need to be addressed: 1) The part of the current web-site that handles queries needs to be upgraded, so as to exploit the full power of the GEOInfoDB; 2) The front-end of the web-site also needs be modified, so as to be consistent with the relational database; 3) The site also will be expanded, so that both tabular and map (ArcIMS format) viewing of database queries are possible.
4) Development of interactive cross-section capability: In our previous work, we produced key illustrative cross-sections showing Quaternary stratigraphy (gravel, sand, silt and clay), water table, depth to bedrock, and type of bedrock information. Figure 2 gives an example of one such section, and Figure 3 shows the location of that section on a map of Providence. These demonstration diagrams are currently stored as static images in the database virtual library. At present, however, users cannot go to a map of borehole sites, such as Figure 3, click on boreholes of interest, and then generate a cross-section from them like the one shown in Figure 2. Figure 2 was generated through a multistage process, consisting of the following steps: 1) enter visual descriptions from each borehole along a cross section line into Rockworks 2002 Borehole Manager by Rockware® ; 2) assign a lithologic keyword to each subsurface interval; 3) Use Rockworks 2002 Borehole Manager to generate a draft version of the cross-section; 4) export the section into Corel Designer 9®, in order to create a visually more informative diagram. We propose to streamline this process, so that end-users can generate sections akin to Figure 2, using interpretations stored in the borehole relational database and the appropriate software. We will evaluate available programs potentially capable of generating cross-sections “on the fly”, such as upgraded versions of Rockworks 2002 Borehole Manager ®, Earthvision® by Dyanmic Graphics, or through the creation of scripts in ArcView. We will then develop a protocol and procedure for generating cross-sections and related diagrams that can be used by the transportation community.
5) Web-based data submission: As part of TC4/RIDOT2, we have modified the Access-based templates developed in URITC2, for the uniform acquisition of borehole data. We will develop web-based data submission capability to allow drillers and other State contractor to submit borehole data directly through the web portal.
6) Population of database: Data accessible through the web-portal will include all data cataloged to date from the URI and Providence pilot study areas (see Figure 1). The database structure is designed to accommodate data on a State-wide basis.
7) Generalization of database: Based upon discussions with state and national transportation officials, we will outline ways in which the relational database can be expanded and applied to other areas and to the management of other types of transportation data.
8) Workshops/Presentations: A workshop will be run at the end of the project, to train RIDOT staff and other stakeholders to use the web portal and associated relational database and cross-section functions. Those interested in the database include, but are not limited to, the following: geologists and engineers at RIDOT, DEM, CRMC, engineering and environmental companies, drilling and boring companies, and various Town engineering and planning departments. We also anticipate interest from other environmental scientists in non-governmental agencies such as the Nature Conservancy, Audubon Society, Save The Bay, and local watershed groups. Additional presentations will be made to RI DOT personnel, the URI Transportation Center, and several professional meetings.
The project will proceed according to the following schedule, and with the following milestones. The schedule is predicated upon a September 1 start date.
September 1 – October 31, 2003
• Carry out literature search (Task 1).
• Initiate meeting with partners and discuss design of portal (Task 2).
October. 1- January 31, 2004
Develop and construct the interactive, web-based relational database (Initial version) (Task 3)
October 1 – April 30, 2004
• Develop protocol for construction of geologic cross-sections “on the fly” (Task 4)
• Develop Protocol for web-based data submission (Task 5)
• 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 s 7 & 8).
• SEMI-ANNUAL REPORT: September 1 through February 28; submitted March 31, 2004.
March 1- July 30, 2004
• Finalize procedures for generation of cross-sections (Task 3).
• Offer workshop(s) to stakeholders (Task 8).
• Prepare final draft report (March 31, 2003)
August 1, 2004
• Complete final report, with revisions as indicated by reviewers
• SEMI-ANNUAL REPORT: March 1, 2003 through August 31, 2004; submitted September 15, 2004.
$137,465.00 ($137,465.00 Yearly)
Two graduate students will play a substantial role in the project. This will constitute a significant experiential learning opportunity for the students involved.
The project is the logical outgrowth of our previous and current URITC and RIDOT projects. Moreover, it is closely linked to Professor Veeger’s current Rhode Island Water Resources Center grant, which uses a GIS-oriented relational database to address issues of water quality in the state. Additionally, the project provides insights to, and benefits from, the RI State Geologist’s ongoing mapping of the Quarternary geology of the state.
A major product of our research will be the relational database borehole library (GEOInfoDB) that will reside on an Internet site, and which will be easily queried in a tabular and graphic (i.e., map-based) format. Moreover, access to GEOInfoDB will allow users to retrieve data needed to construct cross-sections or other interpretive diagrams. We will also prepare custom CD’s of the information on the , 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 an example of an interactive relational database, tightly linked to a user-friendly . It will be populated with borehole- and well-log data that can be widely accessed and easily manipulated. Although the included data will be only from the Providence and URI areas, the database and web-site are designed to be implemented on a state-wide basis. 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 subsurface 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.
Based upon our discussions with transportation planners on the national level, we understand that our program will represent a major advance in the use of GIS-oriented approaches to the analysis of subsurface data as it pertains to transportation issues. 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. In addition, the database can be linked to additional databases (e.g., water resources, mineral resources, etc.) so as to gather together seemingly disparate data that may be highly significant upon synthesis within GIS analysis.
Database, relational database, Access, boreholes, wells, geology, hydrology, core log, subsurface, geotechnical, land-use, transportation, environmental impact, internet