Harnessing the Power of Relational Databases for Managing Subsurface Geotechnical and Geologic Data

Abstract

Knowledge of surface and subsurface geology as well as geotechnical properties is fundamental to the planning and development of transportation systems. Through dynamic linkage of readily available spatial geographic information system data and subsurface borehole data stored in a relational database, we have created a spatially referenced, digital catalog of borehole data for two pilot areas in Rhode Island. The borehole database is populated with data from Rhode Island Department of Transportation geotechnical reports and supplemental data from the U.S. Geological Survey groundwater site inventory system as well as local storm-water and sewer projects. Most of these data were previously stored in paper format, making historical or inter-project data comparisons very difficult, if not impossible. Consolidation of these data in a single relational database yields two primary benefits: Historical data are readily accessible for review and, therefore, can be easily incorporated into the planning stages of new projects, and sophisticated analysis of the region becomes possible with access to data from multiple projects with both spatial and temporal layers. Geologic data include bedrock geology, surface outcrops, unconsolidated materials, soil type, topographic and orthophotographic base maps, and location of boreholes and wells. Subsurface data include land-surface elevation, depth to water table, depth to bedrock, and presence or absence of fill, high and low blow-count zones, and organic sediment. The digital catalog is distributed on a CD-ROM that includes ArcView project files and an Access relational database. The borehole data are also accessible through the Internet (http://geo.uri. edu/borehole/index.asp), with public retrieval access for all users but data entry restricted to registered users only.