USGS visual identity mark and link to main Web site at http://www.usgs.gov/

Digital Mapping Techniques '02 -- Workshop Proceedings
U.S. Geological Survey Open-File Report 02-370

Developing a Working Database for Mapping and Modeling in Illinois

By Barbara Stiff

Illinois State Geological Survey
615 East Peabody Drive
Champaign, IL 61820
Telephone: (217) 244-2520
Fax: (217) 333-2830
e-mail: stiff@isgs.uiuc.edu

INTRODUCTION

The Illinois State Geological Survey (ISGS) has focused on development, production, and archiving of geologic maps for nearly a century. Central to development of geologic maps are the observations, measurements, analyses, and interpretations of research scientists working in the field. Historically, these field-acquired data and associated research materials were recorded, compiled, and maintained on paper by individual scientists. At the completion of the study, field maps and notebooks were archived in the ISGS library.

Currently, digital maps and models of geologic materials in both two and three dimensions are being developed by interdisciplinary teams of scientists. The transition to digital methods and team-based research requires new methods for recording and managing field-acquired and field-verified data (field data). Ideally these methods need to provide all team members with simultaneous desktop access to the most current data.

A working database of customized tables is being developed to accept field data, including site information, observations, and interpretations. Data interchange is via write-access forms that allow researchers to query, append, and edit the data housed in the working database. All ISGS staff members have read-only access to these data as they are developed. The data are maintained as part of the ISGS database system.

DIGITAL BACKGROUND

The transition to digital technology began in 1968 with tabular entry of well drillers' logs into a system called "3-card." A computer-based mapping system to help with the presentation of research data followed (Swann and others, 1970). These data were converted to a geographic information system (GIS) in the 1980's, and the tabular well data were subsequently extracted to form the core of the ISGS master well database (archival database). With advancements in digital technology, acquisition of geologic data, associated research, and presentation of research results have "gone digital" (Stiff, 1997; Krumm and others, 1997; Stiff and others, 1998; Luman and others, 1998; Stiff and Hansel, 1999; Abert and others, 2000; Hansel and others, 2001).

For each research study, ISGS field-acquired data, both old and new, provide detailed information for the maps and models. These data may include descriptions from study-specific drilling, geologic samples, and outcrops and exposures; aerial and terrestrial photographs; shallow seismic reflection and refraction profiles; wireline and natural gamma logs from both new and old boreholes; various other well data; field notes from ISGS library archives and previously published studies; and results of chemical and laboratory analyses. The data are compiled from various combinations of digital, analog, and hard-copy sources and converted to the digital format according to the needs of individual members of the research team. The resulting digital database is a diffuse aggregation of individually maintained word-processing, spreadsheet, and image files plus GIS coverages, shape and geodatabase files, and associated tabular data.

The ISGS maintains an extensive well-records library that contains both digital and paper records. The digital, tabular data reside in an ISGS archival well database that presently contains records from 488,347 sites, including water wells, oil and gas wells, and various test and engineering borings. It was designed primarily as a digital repository for well and boring records and includes location, driller descriptions, and well-completion information. All data entry and editing are performed by the Geologic Records Unit (GRU) staff to protect database integrity. Data in the archival database are entered as they appear on paper records submitted by drillers and/or site observers. Location information is initially recorded as Public Land Survey System (PLSS) section, township, range, and quarters.

A new, Web-based, working database is being developed to accept the input of field data. Its purpose is to facilitate retrieval, interpretation, and analysis for geologic mapping, and modeling. It is based on relational database management principles using existing Oracle and Arc/Info GIS software. Field scientists may edit, append, and delete the data in the working database. Geographic descriptions, attribute information, and source-reference data may be tailored to specific project needs, are adaptable to the particular methodologies of individual scientists, and are flexible to allow for future extensions. The working database will provide an interactive level of quality assurance/quality control (QA/QC) not currently available, and increase efficiency of data input and archiving. Data for each mapping project is monitored by a project-specific data specialist, who oversees timely input, maintenance, and accuracy. The field database also accepts legacy field data from previous research, publications, and reports. Archival well records that require change as the result of field-verification may be merged into the working database when necessary.

Data entry is adaptable to the needs of the researchers. Geologists may input study data directly into the working database tables via Web-enabled forms. These forms also permit database query and edit functions for existing records in either the working database or the archival database. Edits are written only to the working database. Alternatively, data may be entered into formatted spreadsheets that are then batch-loaded into the working database using Structured Query Language (SQL) Loader.

WORKING DATABASE TABLES AND INPUT FORMS

A specific "grammar" was developed for field data following the Digital Geologic Map Data Model, version 4.3 (Johnson and others, 1999). The data are defined as singular objects obtained by direct observation. The GIS objects are treated as point data. Each point has x/y coordinates and a unique identification code (SYS_ID). This identification code is used to establish the relationship to all associated tabular data including location information and geographic description (FIELD_NOTES_HEADER table) and geologic observations and interpretations (FIELD_DESCRIPTION and DESCRIPTION tables).

Input and data editing are accomplished by means of Web forms called managers. The ID manager is used to query the database for available data. The header manager is used to enter and update site information. The description manager is used to enter and edit field data in prose that preserves verbatim field notes and observations. The interpretation manager permits interpreted descriptions to be entered in the archival database. The interpretation manager uses a standardized format and geologic vocabulary that expedites transfer to modeling software but also accepts observations and comments that reflect the special preferences of individual scientists.

ID MANAGER

The ID manager lists the various identification codes that have been assigned to a particular site (Table 1). The API-Number, Field-ID and Mapper-ID are compound relational identifiers (Figure 1). The API-Number is the unique identifier assigned by the GRU to all well records that reside in the archival well-data tables. These data may only be entered and edited by GRU staff in order to protect the integrity of these data. The Field-ID and Mapper-ID are compounded of study, scientist, year, site type, and sequential number codes so that users may query by all or any part of these ID's depending upon their needs. The Site-ID records identifiers used by individual mappers on field maps, in field notebooks, and in publications. The Site-ID is particularly important for historic data. The Legacy-ID is used as a bibliographic reference and may include library identification codes. The layout of the ID manager is shown in Figure 2.

Table 1. Structure of the Field-ID table.

DB_ITEM_NAME     FORM     EXPLANATION
SYS_ID     N(12)     sequential, system-generated identifier
API_NUMBER     C(12)     assigned 12-character database identifier (e.g., 12097004500)
FIELD_ID     C(10)     study-specific id,field code,sequential numbers (e.g., IL29X00045)
MAPPER_ID     C(12)     study-specific id,field code,sequential numbers (e.g., JAL1953X0015)
SITE_ID     C(10)     mapper-specific identifier (e.g., EDM-1, JL-176, etc.)
SAMPLE_SET     C(5)     laboratory-assigned sample-id
LEGACY_ID     C(100)     may add several mapper-id's from the literature, archives, etc.

Oracle DB - API number
    Working DB - Field_ID
    Mapper_ID
state county co_number workover     study type number     initials year type number
xx xxx xxxxx xx     xxxx x xxxxx     xxx xxxx x xxxx
120970013400     WI02X0013     JAL1953X0013
  Figure 1. Components of the compound relational identifiers. The type codes within the Field_ID and Mapper_ID include: X, outcrop or section; E, engineering test; H, highway/bridge boring; I, ISGS test hole; A, hand auger; etc.

ID manager

Figure 2. The ID manager. To access data about a particular site, the field_ID, Mapper_ID, or API_Number is entered into the appropriate box and the select button is clicked. The database is queried and the remaining boxes are populated from both the archival and working databases. Additional ID's may be added, or existing data will be changed in the working data set. Archival data (e.g., API_Number and Sample_Set fields) are for informational purposes and may not be changed or added. Date and authorship are automatically recorded for additions and edits.

HEADER MANAGER

Header data include site and location information (Table 2). Many of these data are housed in the archival data tables. If the scientist has more accurate or more complete data, this table allows the scientist to make appropriate entries or edits. It also contains fields for site information requested by the field scientists. Figure 3 is a graphic representation of the Web-form layout of the header manager.

Table 2. Structure of the Field-Notes-Header table.

DB_ITEM_NAME     FORM     EXPLANATION
SYS_ID     N(12)     sequential, system-generated identifier
FIELD_LOC_VERBOSE     C(500)     verbose field/site description
FIELD_WHO     C(15)     initials of field scientists
FIELD_DATE     D     field date of the description (e.g., 01/05/2002 or JAN-05-2002 )
FIELD_SITE     C(30)     west side of county road, Illinois River bluff, etc.
FIELD_TYPE     C(20)     gravel pit, road cut, borrow pit, etc.
FIELD_CONDITION     C(30)     pit inactive, covered, wet and slippery, etc.
FIELD_NAME     C(30)     Cottonwood School Section, Rattlesnale Hollow Cut I, etc.
ELEV     N(5)     elevation
SEC_ELEV_SOURCE     C(3)     GPS, DEM, DLG, TOP(topo map), IDO(IDOT supplied), etc.
SECTION_ELEV_REF     C(1)     T (top) or B (bottom)
DATA_UNITS     C(1)     F (feet), M (meters), I (inches), C (centimeters)
SEC_LOC_SOURCE     C(3)     GPS, DRG, DOQ, DLG, TOP, TAX, PBK, PMT, DRL, LOG
UTMEASTING83     N(14,6)     meters (up to 6 decimal places)
UTMNORTHING83     N(14,6)     meters (up to 6 decimal places)
LATITUDE83     N(10,6)     decimal degrees (6 decimal places required)
LONGITUDE83     N(10,6)     decimal degrees (6 decimal places required)
LAMFEETX27     N(10,2)     feet (up to 2 decimal places)
LAMFEETY27     N(10,2)     feet (up to 2 decimal places)
SECTION     N(2)     section number
TOWN     N(2)     township number
TDIR     C(1)     N or S
RANGE     N(2)     range number
RDIR     C(1)     E or W
QUARTER     C(11)     up to 4 quarters (e.g., NE NW NW SE)
NSFOOT     N(5)     measurement in feet from reference corner
NSDIR     C(1)     direction of measurement
EWFOOT     N(5)     measurement in feet from reference corner
EWDIR     C(1)     direction of measurement
CORNER     C(2)     corner from which footages were measured
OWNER_NAME     C(30)     name of the property owner
COUNTY     C(3)     FIPS county code
QUAD     C(25)     7.5-minute quadrangle name
TOTAL_MEASURED     N(10,2)     total thickness of the section
ENTERED_BY     C(3)     initials of individual entering data
ENTERED_DATE     D     date of data entry (e.g., 01/05/2002 or JAN-05-2002)
SAMPLE_SETS     C(1)     samples taken, Y or N
CO_NAME     C(20)     county name
QUAD15     C(15)     15-minute quadrangle name
CITATION     C(35)     bibliographic references for site
FIELD_COMMENTS     C(300)     miscellaneous comments regarding field site

Header manager

Figure 3. The header manager. To access data about a site, the field_ID, Mapper_ID, or API_Number is entered into the appropriate box and the select button is clicked. The database is queried and the remaining boxes are populated from available data. Additional information may be added, or existing data may be changed. Date and authorship are automatically recorded for additions and edits.

DESCRIPTION MANAGER

The detailed observations made by the field scientist are critical to a field-notes archive. Although standardization helps with mapping- and modeling-software compatibility, it is essential that the ideas and perspective of the individual scientist be respected/recorded. This table contains fields relating to the particular needs of Quaternary geologists describing surficial materials (Table 3). The fields were developed by researching field notebooks and conferring with individual scientists. Figure 4 is a graphic representation of the Web-form layout of the description manager.

Table 3. Structure of the description fields form the Field-Descriptions table.

DB_ITEM_NAME     FORM     EXPLANATION
DESCRIPTION     C(2000)     verbose unit description
MATERIAL_VERBOSE     C(200)     verbose material description as recorded in field notes, etc.
TEXTURAL_VERBOSE     C(100)     verbose textural description as recorded in field notes, etc.
COLOR_VERBOSE     C(100)     verbose color description as recorded in field notes, etc.
CLAST_VEBOSE     C(100)     verbose clast description as recorded in field notes, etc.
LITHOLOGIC_VERBOSE     C(300)     verbose lithology as recorded in field notes, etc.
PEDOLOGIC_VERBOSE     C(300)     verbose soils description as recorded in field notes, etc.
GEOMORPHIC_VERBOSE     C(300)     verbose site characterization as recorded in field notes, etc.

Figure 4. The description manager. To access data about a site, click the select button, enter the field_ID, Mapper_ID, or API_Number and click the select button. The database is queried and the remaining boxes are populated from available data. Additional descriptive information may be added, or existing data may be changed. Text added to the general comments, material, textural characteristics, pedologic characteristics, color, clast, and morphology boxes wraps to accommodate the "prose" format of the data. Date and authorship are automatically recorded for additions and edits.

The description manager provides a form by which data entry staff may enter field notes verbatim, without the restriction of having to interpret what the scientist wrote. It also allows entry of historic descriptions from key stratigraphic sites that have been published in Field Guidebooks and other ISGS publications.

INTERPRETATION MANAGER

The classes and types of descriptive information used in the data-model structure (Johnson and others, 1999) do not include fields for the detailed information gathered by mappers of surficial materials in Illinois. Field notes and published descriptions by ISGS scientists were used to develop a preliminary set of descriptive criteria. These criteria were distributed to mappers for their comments and input. The current structure of the descriptions table (Table 4) contains fields that are diagnostic for the glacial sediments at the surface in Illinois. In addition to lithologic, soils, chemical, and engineering properties and geomorphic fields listed, several classification codes are included in the data table. The various classification codes embed data classifiers from other digital data sets (e.g., USDA soils classifications, AASHTO engineering codes, etc.) in the ISGS data set.

Table 4. Structure of the Descriptions table grouped by category.

DB_ITEM_NAME     FORM     EXPLANATION
SYS_ID     N(12)     sequential, system-generated identifier
WHO     C(3)     initials of describing individual
INTERP_DATE     D(11)     field date of the description ( e.g., 01/05/2002 or JAN-05-2002)
TOP     N(7,2)     top of the unit (up to 2 decimal places)
BOTTOM     N(7,2)     bottom of the unit (up to 2 decimal places)
Lithology
MATERIAL     C(20)     Dm, silt, sand, gravel, etc.
MATERIAL_COMMENT     C(100)     descriptive details relating to material
TEXTURE     C(50)     loam, silty clay loam, etc.
TEXTURE_COMMENT     C(100)     descriptive details relating to texture
FRAMEWORK     C(20)     matrix supported, etc.
FRAMEW_COMMENTS     C(30)     descriptive details relating to framework
COLOR_DESCRIPTIVE     C(30)     light yellowish brown, etc.
COLOR_MUNSELL     C(25)     5YR3/4, 5YR3/4-2.5YR5/6, etc.
COLOR_COMMENT     C(50)     descriptive details relating to color
COLOR_CONDITIONS     C(5)     wet, dry, etc.
Clasts and Inclusions
CLAST_TYPE     C(20)     limestone, exotic, granitic, slate, etc.
CLAST_SIZE     C(15)     pebble, cobble, 5 mm, etc.
CLAST_QUANTITY     C(15)     few, many, etc.
CLAST_ROUNDING     C(15)     angular, subangular, etc.
CLAST_DESCRIPTION     C(40)     striated, etc.
CLAST_COMMENTS     C(30)     descriptive details relating to CLASTS
Soils
ORGANICS     C(20)     numerous rootlets, etc.
BURROWS     C(20)     krotovina, etc.
COATINGS     C(20)     clay skins, iron stains, etc.
STRUCTURE     C(30)     blocky, etc.
STRUCT_COMMENTS     C(30)     descriptive details relating to structure
PEDOLOGIC_NAME     C(20)     Sangamon Soil, etc.
PEDOLOGIC_COMMENT     C(100)     descriptive details relating to PEDOLOGIC_NAME
SOIL_HORIZON     C(10)     A, B2, etc.
Chemical Properties
REACTIVITY     C(20)     calcareous, leached, etc.
REACT_COMMENTS     C(30)     descriptive details relating to reactivity (violent, slight, etc.)
REDOX_FEATURES     C(1)     Y or N
REDOX_COMMENTS     C(30)     descriptive details relating to redox
Engineering Properties
MOISTURE     C(10)     wet, moist, etc.
CONSISTENCY     C(15)     firm, sticky, plastic, etc.
PLASTICITY     C(10)     slight, very, etc.
ODOR     C(15)     petrochem, musty, etc.
FRACTURES     C(1)     Y or N
FRACT_COMMENTS     C(30)     descriptive details relating to fracture features
FRAC_VERT_INCLINE     N(3,2)     in degrees (up to 2 decimal places)
FRAC_HORIZ_INCLINE     N(3,2)     in degrees (up to 2 decimal places)
Stratigraphy
UNIT_NUMBER     N(9)     ISGS numeric code (used in generating digital maps)
UNIT_NAME     C(20)     Wadsworth, Lemont, etc
UNIT_COMMENTS     C(50)     descriptive details relating to the unit
Depositional
CONTACT     C(15)     clear, gradational, etc.
CONTACT_COMMENTS     C(30)     descriptive details relating to the contact
STRATIFICATION     C(1)     Y or N
STRAT_COMMENTS     C(30)     descriptive details relating to stratification
DEPOSITIONAL_ENVIRONS     C(30)     till, loess, lacustrine, debris flow, etc.
Classification
USCS_CLASS     C(5)     Unified Soil Classification System code (CL-ML, GM, etc.)
USDA_CLASS     C(8)     U.S. Department of Agriculture soils designation code (127C2, 8E2, etc.)
IDOT_CLASS     C(20)     Illinois Department of Transportation soil texture designation (loam, etc.)
AASHTO_CLASS     C(10)     engineering (highway) codes (A-7(5), A-7-5, A-7,6(15), etc.)
THICKNESS     N(7,2)     thickness of the unit (up to 2 decimal places)
FORM_CODE1     C(1)     primary material classification (see attached ESCODES)
FORM_CODE2     C(3)     descriptive classification (see attached ESCODES)
FORM_CODE3     C(2)     secondary descriptive classification (see attached ESCODES)

ALTERNATIVE DATA ENTRY

Previously, scientists kept digital records of field notes in word-processing software. They are being encouraged to enter data into spreadsheet software formatted for efficient import into the working database using SQL Loader when online forms are not available for data entry. Fields from the digital files as used to define and describe the columns in the Microsoft Excel spreadsheet.

CONCLUSION

Though designed parallel to the archival data sets, the field-data sets differ in several key areas. Because these data sets are "open," they have both benefits and risks. Benefits include: timely and direct data entry, centrality, multi-access, data-location reference maintained at discretion of project team (in UTM15NAD83, UTM16NAD83, LambertNAD27, or Latitude/Longitude), interactive QA/QC performed by the team involved with the data, reduction/simplification of load on data entry staff, and a fixed data format understood by everyone. The risk is the open access to the data. All authorized users will be able to make changes to the data. The Input forms permit ISGS scientists to enter data directly into the ISGS Oracle database from wherever they access the Internet.

ACKNOWLEDGMENTS

"Recommended guidelines for the development of the Illinois State Geological Survey digital geologic map database" (Illinois State Geological Survey, 2002) were modified from a Request For Proposals (ASPS 2002-1000-2669) issued by the Alaska Department of Geological and Geophysical Surveys (see Freeman, 2001). The Alaska document provided a framework for ISGS discussions regarding database development.

REFERENCES

Abert, C.C., Weibel, C.P., and Berg, R.C., 2000, Three dimensional geologic mapping of the Villa Grove quadrangle, Douglas County, Illinois, in Soller, D.R., ed., Digital Mapping Techniques '00--Workshop Proceedings: U.S. Geological Survey Open-File Report 00-325, p. 125-129, .

Freeman, L.K., 2001, A case study in database design: the Alaska Geologic Database, in Soller, D.R., ed., Digital Mapping Techniques '01--Workshop Proceedings: U.S. Geological Survey Open-File Report 01-223, p. 31-34, https://pubs.usgs.gov/of/2001/of01-223/freeman.html.

Hansel, A.K., Stiff, B.J., and Pugin, A., 2001, Creating a regional 3-D model of Quaternary deposits for mapping projects in northeastern Illinois: Geological Society of America Abstracts with Programs, v. 33, no. 6.

Johnson, B.R., Brodaric, B., Gaines, G.L., Hastings, J.T., and Wahl, R., 1999, Digital Geologic Map Data Model, version 4.3: AASG/USGS Geologic Map Data Model Working Group, http://geology.usgs.gov/dm/.

Krumm, R.J., Abert, C.C., Nelson, D.O., and Hester, J.C., 1997, Review of digital mapping techniques: the Illinois experience, in Soller, D.R., ed., Proceedings of a Workshop on Digital Mapping Techniques: Methods for Geologic Map Data Capture, Management and Publication: U.S. Geological Survey Open-File Report 97-269, p. 5-8, https://pubs.usgs.gov/openfile/of97-269/krumm.html.

Luman, D.E., Smith, L.R., and Stohr, C., 1998, Remote sensing inputs to a geologic mapping program for Illinois, in Soller, D.R., ed., Digital Mapping Techniques '98--Workshop Proceedings: U.S. Geological Survey Open-File Report 98-487, p. 19-25, https://pubs.usgs.gov/openfile/of98-487/luman.html.

Illinois State Geological Survey, 2002, Recommended guidelines for the development of the Illinois State Geological Survey digital geologic map database and development of guidelines for the ISGS digital geologic map database and recommended follow-on activities: Report of Working Group 2 of the ISGS Mapping Steering Committee.

Stiff, B.J., 1997, Use of raster imagery and vector data in support of a geologic mapping program, in Soller, D.R., ed., Proceedings of a Workshop on Digital Mapping Techniques: Methods for Geologic Map Data Capture, Management and Publication: U.S. Geological Survey Open-File Report 97-269, p. 23-26, https://pubs.usgs.gov/openfile/of97-269/stiff.html.

Stiff, B. J., Beaverson, S.K., and Krumm, R.J., 1998, Streamlining quadrangle map production for on-demand publication, in Soller, D.R., ed., Digital Mapping Techniques '98--Workshop Proceedings: U.S. Geological Survey Open-File Report 98-487, p. 53-56, https://pubs.usgs.gov/openfile/of98-487/stiff.html.

Stiff, B.J., and Hansel, A.K., 1999, Utility of digital geologic data in addressing outreach requests: Geological Society of America Abstracts with Programs, v. 31, no. 5.

Swann, D.H., DuMontelle,P.B., Mast, R.F., and VanDyke, L.H., 1970, ILLIMAP--a computer-based mapping system for Illinois: Illinois State Geological Survey Circular 451, 24 p.


RETURN TO Contents
National Cooperative Geologic Mapping Program | Geologic Division | Open-File Reports
U.S. Department of the Interior, U.S. Geological Survey
URL: https://pubsdata.usgs.gov/pubs/of/2002/of02-370/stiff.html
Maintained by David R. Soller
Last modified: 19:15:48 Wed 07 Dec 2016
Privacy statement | General disclaimer | Accessibility