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
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.
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.
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.
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. |
|
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. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
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. |
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 |
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. |
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. |
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) | |||||
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.