USGS OFR 02-370:

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

Alaska Division of Geological & Geophysical Surveys Geologic Database Development -- Logical Model

By Larry Freeman,¹ Kathryn Engle,² and Carrie Browne¹

¹Alaska Division of Geological & Geophysical Surveys
794 University Avenue, Suite 200
Fairbanks, AK 99709
Telephone: (907) 451-5027
Fax: (907) 451-5050
e-mail: Larry_Freeman@dnr.state.ak.us, Carrie_Browne@dnr.state.ak.us

²GeoNorth, LLC
1000 O'Malley Road, Suite 200
Anchorage, AK 99515
Telephone: (907) 677-1500
Fax: (907) 677-1502
e-mail: kengle@geonorth.com

INTRODUCTION

The Database Project at the Alaska Division of Geological & Geophysical Surveys (DGGS) was conceived in the late 1990s to stop the loss of critical geologic data that are used in the compilation of geologic maps and to modernize the way that DGGS delivers geologic data to the public. To assist in this task, in late 2000 the DGGS hired a contractor to help with the initial database design and implementation. A logical database model is now complete, and development of the physical model and subsequent building and testing of the initial database will occur this summer (2002).

PROJECT OBJECTIVE

Work began on the DGGS database project in late 2000. The objective was to create a new, comprehensive database to meet the data storage needs of the Survey, provide the basis for delivering geologic information to the public, and provide a stable data structure to interact with databases at other agencies such as the National Geologic Map Database, http://ncgmp.usgs.gov/ngmdbproject/home.html, Interagency Minerals Coordinating Group (IMCG), http://imcg.wr.usgs.gov/, and the Alaska State Geo-Spatial Data Clearinghouse, http://www.asgdc.state.ak.us/ (Freeman, 2001).

The database project faces such challenges as serving the needs of multiple users, functioning as part of multiple distributed data networks, anticipating future needs of the database and DGGS, and allowing for flexibility in the design of the database to adapt to changing technology. These challenges will be met using a fully normalized relational database model, developed in a spatial-data-capable, relational database management system. The logical model presented in our poster will be the basis for the development of the DGGS geologic database.

METHODOLOGY

During the past year, DGGS contracted with GeoNorth, LLC, of Anchorage, to design a relational database and implement it in Oracle 8i and ArcSDE on a UNIX platform. GeoNorth has developed the following models toward the completion of their contract with DGGS:

A business-process model -- a graphic model showing the flow of business and scientific work at DGGS from conception of a project through publication of the project's data, delivery of publications, and archiving of all project data. This model identifies and describes data entities and positions responsible for the work and the business (science) rules that define the relationships between the entities.
A conceptual database model -- a model that identifies the data entities, their attributes, and the relationships between entities.
A logical database model -- a comprehensive model of data entities, attributes including their logical data types, and the relationships between the entities. GeoNorth used Computer Associates ERwin database modeling software to construct the model.

GeoNorth involved all members of the DGGS staff in an iterative and collaborative effort during the design period for the business-process and logical models. This is a critical factor in database design for two reasons: First, the geologists at DGGS are considered experts in their field and are most familiar with their data and how they will be using the data available to them; and second, involving the staff encouraged participation and interest in the database project. The logical model is based on data entities identified in the business-process and conceptual models. It has taken nearly seven months to design the models.

GeoNorth's next step is to convert the logical model to a physical model. Database (structured query language data definition language) code will be generated from the physical database model and a prototype database will be built. We expect that much of this design work and code generation will be performed using ERwin. An initial internal release of the database on the DGGS network is anticipated by summer 2002.

DGGS staff will test the database structure, using command-line interface (Oracle SQL+), ArcGIS 8, Microsoft Access, Map Info, and other third-party products. After the initial testing is complete and DGGS has accepted the database from GeoNorth, we will start building applications to facilitate frequently repeated data entry functions and queries and to allow for public access to the data via the DGGS Web site and through a publicly accessible workstation that will be available at DGGS's office.

THE LOGICAL MODEL

In the logical database model, http://wwwdggs.dnr.state.ak.us/Logmod_0205_web/dggs_logmod_020519.htm, DGGS data are broken into multiple main categories connected by conceptual and logical relationships that represent the way that data are collected, edited, and analyzed by the specific work groups. Although the logical model appears complex, encompassing more than 200 entities (tables), many of these entities are subtypes, data validation lists, and descendants of six primary entities (Figure 1). The relationships between the primary entities are a distillation of the business rules identified in the business-process model and serve to link the main data categories, which are described below.

Figure 1. Selected primary entities of the DGGS logical database model. The many-to-many relationship between field data and geologic-map features is defined manually by a geologist and by geographic data manipulation. Because of the diversity and inconsistency in the way that geologic-map features are defined at DGGS, we were not able to logically resolve this relationship. Information Engineering (IE) notation (Halpin, 2000) is used in this diagram and in Figure 2; verbs describing the relationships are read from bottom to top.

Field station data include descriptive, measured, and instrumental data that are collected in the field to support geologic mapping, resource investigations, and hazard evaluations. At DGGS, all field data are identified and located by a field station. Each field station has a point location and may have an associated geometry, such as a polygon that surrounds an outcrop or geothermal occurrence or the surface trace of a borehole.

Sample analyses consist of instrumental analyses and descriptions of geologic samples. Sample analysis data include summary and secondary analysis information as well as original laboratory data. A large number of the entities in the DGGS logical model are subtypes and descendants of a sample analysis. A sample analysis is identified by the sample number and the analysis batch (who, what, and where).

Publication information is recorded for DGGS publications and for external publications that are cited as sources in the DGGS data. Information for DGGS publications includes information about distribution, electronic files used to make the publications, and the archive location of those publications. A geospatial data set is a set of spatially referenced, interrelated features. They are constrained by a geographic domain and exist as a separately addressable file that can be linked to the relational database that contains the map's descriptive information. All geologic map objects in the DGGS database will be derived from a geospatial data set. Some data sets are distributed by DGGS and others are distributed from other sources and used in the DGGS database. Regardless, metadata for the source data set can be queried from the database and displayed in a format compatible with FGDC-compliant metadata. Metadata for each data set will be required before it is loaded into the database.

A geologic map feature in the DGGS data model (Entity Data Spatial Classification, Figure 2) is defined by geometry and classification attributes. Geologic map features have topologic and geologic relationships that are internally consistent within a geospatial data set, but are related to geologic map features in other geospatial data sets only by their classification attributes. In other words, we are intending to preserve "geologic map edge faults" until they can be resolved. Classification attributes of geologic map features include feature type, composition, geologic age, or map unit (lithostratigraphic) name, proper name (e.g., Denali Fault), and derivative classification themes. Classification attributes are related to cartographic symbols to provide visualization of the features using multiple symbol sets. Although this model deviates from the North American Geologic Map Data Model (Johnson, and others, 1999), it gives us flexibility to create views of geologic map data in multiple configurations. The remaining entities in the database include thematic information such as mineral occurrence information and reference tables for data validation and indexing. The reference tables will contain the standard nomenclature, classification, and keywords that DGGS uses to conduct geologic work.

Figure 2. Entity-relationship diagram for geologic-map features in the DGGS logical database model.

FUTURE WORK

The DGGS database logical model will be converted to a physical model and then a database prototype during summer 2002. The database will be implemented in Oracle version 8.1.7, and ArcSDE 8.2. DGGS then will begin loading and manipulating data through SQL scripts, procedure language, and Oracle SQL+ command-line interface, and viewing the data through ODBC (open database connectivity) client software and ArcGIS. This will allow us to discover any flaws in the design and implementation before we start to build custom delivery applications.

In the next year, DGGS will begin using the database to compile geologic data collected during the 2003 field season. Other DGGS projects with their own data sets will begin connecting to the DGGS database. The database project staff will continue loading data from multiple data sources, and will begin creating custom applications using the database to produce output files for Web pages, publications, and distribution to external databases.

ACKNOWLEDGMENT

The DGGS database project is funded by a U.S. Geological Survey grant through the interagency Minerals Data and Information at Risk in Alaska (MDIRA) program.

REFERENCES

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.

Halpin, T., 2000, Entity relationship modeling from an ORM perspective: Part 3: Journal of Conceptual Modeling, Issue 13, http://inconcept.com/jcm/April2000/halpin.html.

Johnson, B.R., Brodaric, B., Raines, G.L., Hastings, J.T., and Wahl, R., 1999, Digital geologic map model; Version 4.3: Association of American State Geologists/U.S. Geological Survey draft document, 69 p., http://geology.usgs.gov/dm/model/Model43a.pdf.

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/freeman.html
Maintained by David R. Soller
Last modified: 19:15:37 Wed 07 Dec 2016
Privacy statement | General disclaimer | Accessibility

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