Kfd Development

Environ Geol (2005) 47: 1072–1082 DOI 10.1007/s00254-005-1240-3

Y. Gao E. C. Alexander Jr R. G. Tipping

Received: 28 October 2003 Accepted: 12 January 2005 Published online: 9 April 2005 Ó Springer-Verlag 2005

Y. Gao (&) Department of Physics, Astronomy, and Geology, East Tennessee State University, Johnson City, TN, 37604, USA E-mail: [email protected] Tel.: +1-423-4395878 E. C. Alexander Jr (&) Department of Geology & Geophysics, University of Minnesota, Minneapolis, MN 55455, USA E-mail: [email protected] R. G. Tipping (&) Minnesota Geological Survey, 2642 University Ave., St. Paul, MN 55114, USA E-mail: [email protected]

ORIGINAL ARTICLE

Karst database development in Minnesota: design and data assembly

Abstract The Karst Feature Database (KFD) of Minnesota is a relational GIS-based Database Management System (DBMS). Previous karst feature datasets used inconsistent attributes to describe karst features in different areas of Minnesota. Existing metadata were modified and standardized to represent a comprehensive metadata for all the karst features in Minnesota. Microsoft Access 2000 and ArcView 3.2 were used to develop this working database. Existing county and sub-county karst feature datasets have been assembled into the KFD, which is capable of visualizing and analyzing the entire data set. By November 17 2002, 11,682 karst features were stored in the KFD of Minnesota. Data tables are stored in a Microsoft Access 2000 DBMS and

Introduction In the past decade, GIS and Database Management System (DBMS) have been widely used to develop Karst Feature Databases (KFDs) for spatial manipulation and resource management (Denizman 1997; Whitman and Gubbels 1999). Several countries have built national KFDs to enhance data accessibility and resource management (Cooper et al. 2001; Lei et al. 2001). In the US, many state agencies and karst scientists have significantly updated the geologic information and karst feature inventories in the past two decades (Gao et al. 2002; Whitman and Gubbels 1999). Several karst scientists are

linked to corresponding ArcView applications. The current KFD of Minnesota has been moved from a Windows NT server to a Windows 2000 Citrix server accessible to researchers and planners through networked interfaces. Keywords Database Management System (DBMS) Æ Karst Feature Database (KFD) Æ Relational database Æ Entity Æ Relationship Æ Minnesota

working with the US Geological Survey (USGS) to create a new karst map of the US (Veni 2002; White 2001). The new karst map will be created based on regional geologic information and karst feature distribution. The new karst map of the US will use GIS technology to combine regional karst information and therefore will be easily accessible to land-use planners and managers, educators as well as karst scientists. A preliminary version of such a map was published by the American Geological Institute (Veni et al. 2001). Although GIS-based KFDs have been established in many karst regions, the absence of comprehensive database models and standard metadata impeded the

1073

data compatibility and management. The karst lands of Minnesota present an ongoing challenge to environmental planners and researchers and have been the focus of a series of research projects and studies by researchers for approximately 30 years (Giammona 1973; Wopat 1974). Datasets in different counties and periods used different metadata to describe the karst features in Minnesota (Alexander and Maki 1988; Dalgleish and Alexander 1984; Witthuhn and Alexander 1995). This situation exists in many developed databases for other karst regions. One goal of this research was to design a comprehensive relational database for the karst features in Minnesota. The methodology used to design this DBMS is applicable to the development of other GISbased databases to analyze and manage geomorphic and hydrologic datasets at regional and local scales. Microsoft Access 2000 and ArcView 3.2 were used to develop the working database. Existing county and subcounty karst feature datasets have been assembled into a large GIS-based database capable of analyzing the entire data set. Data tables were stored in a Microsoft Access 2000 DBMS and linked to corresponding ArcView shape files. The current KFD of Minnesota was initially loaded onto a Windows NT server and then moved to a Windows 2000 Citrix server at Minnesota Geological Survey (MGS). The server is accessible to researchers and planners through networked interfaces. The development, implementation, and data analyses of the Minnesota KFD are described in detail in the first author’s Ph.D. dissertation (Gao 2002). This paper is the first in a series of two papers describing the Minnesota KFD (Gao et al. 2005).

Relational database design The relational model of data was introduced by Codd (1970). This model is based on a simple and uniform structure—relation. In the past 30 years, the relational model has become one of the most important and widely used database models. There are many well-established commercial relational DBMS packages in the market. Two of the most important concepts in a relational model are entities and relationships (Fleming and von Halle 1989). An entity is a real-world object or concept. In the KFD, different karst features such as sinkholes, springs, stream sinks, and water-tracing vectors are different entities. The relational model represents a database as a collection of relations (Elmasri and Navathe 1994). A relationship is an association among two or more entities. For example, the entity water-tracing vector has both tracer input and tracer output. The input is often associated with one or more sinkholes and stream sinks and the output is usually associated with one or more springs or wells. Each entity has specific properties, called attributes, which describe it. An entity type defines a set of entities

that have the same attributes. The attribute values that describe each entity are a major part of the data stored in the database. Each entity type in a relational model is equivalent to a relation. A relation or entity type corresponds to a table of values and each row in the table represents a collection of related data values. Other important terms in a relational database model are domain, key, primary key, and foreign key. In relational model terminology, a table is called a relation, a row in a table is called a tuple, and a column header in a table is called an attribute. The data type describing the types of values that can appear in each column is called a domain. A domain is a set of atomic values and a data type or format is specified for each domain. Atomic means that each value in the domain is indivisible (Elmasri and Navathe 1994). For example, the Universal Transverse Mercator North (UTMN) coordinate of a karst feature is a 7-digit numerical value. A relation consists of a set of tuples. In the relational model, no two tuples can have the same combination of values for all their attributes. In general, all attribute values are not needed to identify a particular tuple. For example, a RELATEID can be used to identify a particular karst feature in the karst index table. This minimal identifying attribute or set of attributes is defined as the key. A relation or entity type may have more than one key and each of the keys is called a candidate key. One of the candidate keys is usually designated as the primary key which is used to identify tuples in a relation. In other words, the primary key is used to identify records in a table. According to Fleming and von Halle (1989), a foreign key is an attribute or set of attributes that completes a relationship by identifying the associated entity. In other words, a foreign key refers to a foreign entity. For instance, the attribute RELATEID in a spring data table refers to a specific spring in the karst index table. Relational model constraints include domain constraints, key constraints, entity integrity, and referential integrity constraints (Elmasri and Navathe 1994). Domain constraint means that the value of an attribute must be an atomic value from the domain of that attribute. For example, the NAME of a karst feature must be a string that is less than or equal to 40 character long. A user who enters a 41-character-long string has violated the domain constraint for the NAME attribute of the karst feature. A key constraint specifies that no two tuples or records can have the same values for the key attributes in a relation or table. RELATEID is the primary key in the karst index table. If two karst features have the same RELATEID, it violates the key constraint. In the relational model, no primary key value can be null and this is defined as the entity integrity constraint. The referential integrity constraint is specified between two tables and is used to enforce the consistency between the records of the two related tables.

1074

According to Fleming and von Halle (1989), there are three steps to design a relational database: logical data modeling (LDM), translation of the logical data model to a relational database, and tuning of the relational database. Fleming and von Halle (1989) recommended a comprehensive LDM including 12 phases. It starts from identifying major entities and ends with analyzing for stability and growth. Elmasri and Navathe (1994) divided a medium or large database application system into eight phases, starting from system definition and ending with DBMS monitoring and maintenance. Fleming and von Halle’s (1989) and Elmasri and Navathe’s (1994) methods of building a DBMS were customized to develop a simpler DBMS that is usable by both karst scientists and the general public. Figure 1 shows the process to develop the KFD for Minnesota. This paper explains the first five phases of this process and a follow-up paper will discuss the last two phases.

Data and system requirements The process of identifying and analyzing the intended uses of a database is called ‘‘requirements collection and analysis’’ (Elmasri and Navathe 1994). This process is the most important procedure before the design of the physical data structure of a DBMS. Data and system requirements should be the first process of a DBMS life cycle. The karst feature DBMS used the following steps to collect information for data and system requirements: 1. Identify major pplication areas and user groups that will use the database. 2. Investigate existing documentation concerning the application of the database. 3. Study the current operating environment and planned use of the information. 4. Collect responses from potential database users. The results of data and system requirement are based on the information collected from existing documents and potential users (Table 1). ArcView GIS and Microsoft Access 2000 are the main software packages selected to construct a GIS-based DBMS for the karst features in Minnesota. Existing karst feature data and GIS data could be easily loaded or converted to Microsoft Access 2000 data tables and ArcView shape files. Most of the regular users of karst DBMS have access to these two software packages and many of the operations and analyses proposed in the conceptual model of the karst feature DBMS can be conducted using these two software packages. ArcView and Microsoft Access are also widely used in the karst communities in the US and other countries. These considerations make the Minnesota karst feature DBMS expandable and applicable to other karst areas.

Entity identification

Fig. 1 Relational database development process

Table 2 lists all the karst features recorded in existing documents or reported by local residents or state agencies in Minnesota. Features like caves and mines were not included in the KFD to protect the owner’s property, cave and mine features, and possible explorers to the caves and mines. Information about caves and mines was kept as private instead of public accessible data. Publicly accessible caves are identified as miscellaneous features. Karst windows were not included in the KFD due to their rarity in Minnesota. Dry valleys are usually included in bigger areas drained by subsurface runoff. Many karst researchers developed methods to delineate such areas to model groundwater flow in karst areas. Unlike most non-karst areas, topographic divides and hydrologic basins often do not coincide in karst terrains. Ray et al. (2000)

1075

Table 1 The scope of the karst feature database system, its users and applications

Users

System requirements Requirements for DBMS applications Additional requirements from users

Department of Geology and Geophysics, University of Minnesota Minnesota Geological Survey (MGS) Minnesota Department of Health (MDH) Minnesota Department of Natural Resources (MNDNR) Minnesota Pollution Control Agency (MPCA) Minnesota Department of Transportation (MNDOT) Other state agencies, resource managers, karst scientists, and the general public Microsoft Access 2000 ArcView GIS 3.2 Convertible to more powerful DBMS and GIS software packages such as ArcGIS and Oracle Easy to use interfaces in both Microsoft Access 2000 and ArcView GIS Reliable concurrency control and security Network and web accessible Standardize existing metadata for the karst features in Minnesota Maintain adaptability to new software and hardware Provide scientifically defensible criteria for making land-use decisions in the karst areas of Minnesota

compared tracer-delineated groundwater basins with Hydrologic Unit Code (HUC) watershed maps. This comparison demonstrated that 15–20% of the HUC topographic basins in karst regions fail to coincide with hydrologic basins in Kentucky’s karst watersheds. Alexander et al. (1995) defined springshed by using water-tracing tests and surface runoff in Fillmore County. A springshed contains surface area and subsurface volume that contribute water to a spring (Alexander et al. 1995). Green et al. (2002) delineated areas called karst units based on bedrock geology, depth to bedrock, geomorphology, topography, surface and subsurface hydrology, and the distribution of karst features in Mower County. The Minnesota KFD includes a feature called sub-drained area, which is an area drained by subsurface runoff. Sub-drained areas can be Table 2 Possible entities for the karst feature database in Minnesota Naturally occurring karst features

Artificial features

Other features

blind valley Cave Dry valley Karst window Losing stream Outcrop Seep Sinkhole Compound sinkhole Spring Stream sink/ stream sieve Quarry Mine Tile inlet Tile outlet Sub-drained area Water-tracing vector Springshed/ groundwater basin Karst unit

delineated based on topological water boundaries, bedrock geology, depth to bedrock, and the distributions of karst features such as sinkholes, springs, and stream sinks. The boundaries are further adjusted by watertracing tests. Sub-drained areas are usually larger than springsheds defined by Alexander et al. (1995) and smaller than karst units defined by Green et al. (2002). Agricultural drainage tiles are artificial but perform many of the functions of karst features. Tile inlets or surface tile inlets are pipes extending to the surface, installed to remove water from closed depressions and waterways on the land surface and move it via pipes (conduits) to points where water is returned to surface water flows. In Minnesota, tile inlets are often installed in filled sinkholes, sinking streams, or solution-enlarged joints and several can function as injection wells. Tile outlets are the ends of pipes (conduits) that return subsurface water to surface waterways. They are equivalent in function to springs. Tile outlets can easily be mistaken for springs. One of the reasons for having them in the database is to keep future workers from mistaking them for natural springs. Features such as tile inlets, tile outlets, sub-drained areas, and water-tracing vectors are artificial but important components to model groundwater flow in the karst areas of Minnesota. These items are integral parts or descriptions of the region’s karst hydrogeology and our user groups specifically requested that this information be accessible to all in the same database. Therefore, these features were included in the Minnesota KFD. There is no noticeable distinction between springs and seeps and they were not differentiated in most counties. In addition, stream sinks were used in most of the survey to represent losing streams. Therefore, springs and seeps were treated as one entity type and stream sinks and losing streams were treated as one entity type in the KFD.

1076

Table 3 Entities included in the karst feature database in Minnesota Naturally occurring karst features

Artificial features Other features

blind valley Outcrop Sinkhole Compound sinkhole Spring/seep Stream sink/ losing stream Quarry Tile inlet Tile outlet Sub-drained area Water-tracing vector Miscellaneous (any useful features other than the above features)

Table 3 lists both naturally occurring and artificial features included in the KFD. Appendix A in Gao (2002) contains definitions of all features in the karst feature DBMS. Features in Table 3 correspond to individual data tables in the KFD. In addition to the above karst features, a miscellaneous data table was constructed to store features not easily defined or not included in Table 3. An index table was created to include all common attributes shared by all karst features. A remarks table was used to store additional comments about a karst feature. An address table was used to store landowner’s contact information. All karst features share some common attributes but many features lack information about address and remarks. Separate index, remarks, and address tables can greatly reduce the size of the database and improve the performance of the data queries. Appendix B in Gao (2002) describes all the entities and data tables in the KFD of Minnesota. Entities in the KFD were divided into one supertype and many subtypes (Fleming and von Halle 1989). Karst feature index is the supertype, which includes common attributes of all karst features. The rest of the entity types are subtypes.

the ‘‘to’’ entity. Direction is often represented by an arrow between two entities. In the above example, compound sinkhole is the ‘‘from’’ entity and sinkhole is the ‘‘to’’ entity. The cardinality ratio in a relationship defines the expected number of related occurrences for each of the two entities. There are three different kinds of relationships based on cardinality ratio: One-to-One (1:1), One-to-Many (1:N), and Many-to-Many (M:N). The relationship between compound sinkholes and sinkholes are One-to-Many. For simplicity, a Many-toMany relationship is usually decomposed into two Oneto-Many relationships by adding a new entity to act as an intermediate entity between the two entities in the original Many-to-Many relationships. The KFD of Minnesota is constructed by a series of One-to-One and One-to-Many relationships. Figure 2 shows directions of all the One-to-One relationships in the karst feature DBMS. Figure 3 shows directions of all the One-toMany relationships in the karst feature DBMS. Overall structures of the relationships in this DBMS are illustrated in Fig. 4. Notice in Fig. 3 that any entity listed in Table 3 has a One-to-Many relationship with the address or remarks table. This means that any entity could potentially have many addresses or comments. The relationships between the karst feature index table and address or remarks

Determination of relationships and referential integrities When the entities of a DBMS are defined in a relational database, the next step is to construct relationships among these entities. A relationship is usually represented by action words. For example, a compound sinkhole contains two or more sinkholes within a single closed depression (‘‘Contains’’ is the name of this relationship). Two of the most important properties for a relationship are direction and cardinality ratio. Direction in a relationship describes that one entity of the two entities in a relationship is the ‘‘from’’ entity and the other one is

Fig. 2 One-to-One relationships in the Karst Feature Database of Minnesota

1077

Fig. 3 One-to-Many relationships in the Karst Feature Database of Minnesota

tables are propagated through specific karst feature entities as shown in Fig. 4. If One-to-Many relationships are constructed between karst feature index table and address or remarks tables, those two relationships would be redundant. These redundant relationships should be removed from the karst feature DBMS to ensure a clear and simple relational model.

Identification of attributes, domains, keys, and data integrity The datasets used in this research have been built over the past 20+ years by individual researchers and various agencies. Different attributes have been used to describe karst features in various karst areas of Minnesota. For example, the sources of the information used to identify and locate a karst feature were labeled ‘‘input source’’, ‘‘information source’’, and ‘‘location source’’ in different datasets. ‘‘Side’’ and ‘‘Shape’’ were used to describe the shape of a sinkhole. Datasets in different

counties used different codes to represent the age of a sinkhole. This made the metadata of karst features redundant and confusing. The most complete sinkhole database was constructed for Winona County by Dalgleish and Alexander (1984), and updated by Magdalene (1995) using a spreadsheet format. That inventory continued to be the model for subsequent work. Magdalene’s (1995) metadata of attributes and domains for sinkholes in Winona County were modified to be compatible with sinkholes in other counties. More attributes and domains were added and standardized to represent a comprehensive metadata for all the karst features in Minnesota. Redundant and confusing attributes were clarified during this process. Appendix C in Gao (2002) describes attributes and domains for all the entities included in the KFD of Minnesota. Code tables were used to simplify some complex and descriptive attributes. Codes are stored in separate lookup tables and are accessed by Structured Query Language (SQL) requests. Appendix D in Gao (2002) defines all the code tables used in the karst feature

1078

Fig. 4 Database Structure of the Karst Feature Database of Minnesota (Updated from Gao et al. 2002). (i.) is the main database structure. The top level karst feature index table stores basic and location information for each karst feature; The middle level tables, blind valley–misc., store specific information for different features; The bottom level tables, address and remarks, store owners’ address and additional comments of each karst

DBMS. Using codes in this database significantly reduces storage space and improves the performance of applications built on the DBMS. These codes are connected with meaningful descriptions through user interfaces or karst feature reports to be easily understandable to outside users. The attributes initially added into the KFD are primary and candidate keys. A candidate key is an attribute or a set of attributes that can uniquely identify the occurrence of an entity (Fleming and von Halle 1989). A candidate key that consists of more than one attribute is called a composite key. Table 4 shows all the primary and foreign keys to the entities in the KFD of Minnesota. Four tables use a composite key as the primary key. Spring or tile outlet can be measured multiple times and RelateID+Measurement date is used to uniquely identify each occurrence of such measurements. Remarks and address of a karst feature are updated on a regular basis and a feature can have multiple addresses and remarks. When a new comment is added to a specific karst feature, a new sequence number is assigned to the new comment. Therefore, the combination of RelateID and sequence number uniquely identifies every individual record in the remarks table. Contact information about the landowner of a karst feature is very

important for karst feature inventories. Karst workers need permission and help from the landowners to access their properties. Some landowners may also be able to provide valuable information such as the formation date of a sinkhole, contamination of a spring, locations of some karst features, and their concerns of some problems related to karst features in their properties. When ownership changes, the information of the previous owner would be kept in the database and the contact information of the current owner will be added in the address. RelateID+ the last update date is the primary Table 4 Primary and foreign keys of different entities in the karst feature database in Minnesota Entity

Primary Key

Karst feature index Blind valley Outcrop Sinkhole Compound sinkhole Spring/seep Stream sink/ losing stream Quarry Sub-drained area Tile inlet Tile outlet Water-tracing vector Remarks Address Miscellaneous feature

RelateID

Foreign Key

RelateID RelateID RelateID RelateID

RelateID RelateID RelateID RelateID

RelateID+Meas. Date RelateID

RelateID RelateID

RelateID RelateID RelateID RelateID+Meas. Date RelateID

RelateID RelateID RelateID RelateID RelateID

RelateID+seq. no. RelateID+Last Update RelateID

RelateID RelateID RelateID

1079

key to identify a unique individual record in the address data table. As can be seen in Table 4, RelateID is used as a foreign key for all the entities in the KFD. It is also a primary key or part of a primary key for those entities. Propagating primary keys as foreign keys makes it easy to understand and manipulate the relationships among entities in the KFD. After the physical data structure such as relationships, attributes, domains, and keys are defined in a DBMS, the next step is to specify a series of rules or constraints to ensure the consistency of data values among different relations. These data integrity rules include domain integrity, entity integrity, and referential integrity. Appendix C in Gao (2002) defines the domain constraints for the attributes of all data tables in the karst feature DBMS. Entity integrity was enforced to maintain the uniqueness of primary keys. Referential integrity constraint is a set of rules specified for a relationship to maintain consistency among the records of the two entities involved in the relationship. In a database with many relations, there are usually many referential integrity constraints (Elmasri and Navathe 1994). Rules for referential integrity in Microsoft Access 2000 are defined as follows:

features with different RELATEIDs could be identical. Some features with previously assigned RELATEID may not exist. Referential integrity constraints about changing and deleting primary keys were softened to clean up these errors. In Microsoft Access 2000, the restrictions against deleting or changing related records can be overridden to preserve referential integrity by setting the Cascade Update Related Fields and Cascade Delete Related Records check boxes in a relationship. When the Cascade Update Related Fields check box is set, changing a primary key value in the primary table automatically updates the matching value in all related records. When the Cascade Delete Related Records check box is set, deleting a record in the primary table deletes any related records in the related table. In the karst feature DBMS of Minnesota, all relationships were enforced with referential integrity and supplemented by cascading update and cascading delete options. In the karst feature DBMS, a Karst Feature Index Table is the primary table of the database, and has a series of One-to-Many and One-to-One cascading downward relationships to the other data tables as shown in Fig. 4. Two additional sets of One-to-Many relationships exist between compound sinkholes and sinkholes and between the water-tracing vector and its input and output features. To summarize, well-structured relationships among entities, standardizing the metadata of attributes, using code tables for repetitive descriptive attributes, and propagating RelateID as foreign keys make the KFD of Minnesota a consistent, stable, and less redundant DBMS.

– You cannot enter a value in the foreign key field of the related table that does not exist in the primary key of the primary table. However, you can enter a Null value in the foreign key, specifying that the records are unrelated. For example, you cannot have an address that is assigned to the owner of a sinkhole that does not exist, but you can have an address that is assigned to no one by entering a Null value in the RELATEID field. – You cannot delete a record from a primary table if matching records exist in a related table. For example, you cannot delete a karst feature index record from the ‘‘kfix’’ table if corresponding records in any specific karst feature table (e.g. sinkhole data table, ‘‘kfsh’’) exist. – You cannot change a primary key value in the primary table if that record has related records. For example, you cannot change the RELATEID of a karst feature index record in the ‘‘kfix’’ table if corresponding records in any specific karst feature table (e.g. spring data table, ‘‘kfsp’’) exist.

Data gathering and assembly

If referential integrity is enforced and users break one of the rules with related tables, Microsoft Access displays a message and does not allow the change. However, the last two rules are too conservative to be implemented in the KFD. As the karst feature data have been accumulated over more than 20 years, there are many errors associated with previously assigned unique numbers. These unique numbers were represented as RELATEID in the database. Different features might have been assigned to the same RELATEID and

1. The documentation concerning the application of the database, the operating environment, and the planned use of KFD were investigated. Initial DBMS structure and metadata guidelines were constructed in this stage to make the metadata compatible with mapped karst features. A conceptual data model including interactive modules was developed at this stage. 2. The KFD for Winona County was developed. In this stage, the most recent up-to-date sinkhole dataset

Existing county and sub-county karst feature datasets have been assembled into the karst feature DBMS. As some of the data were recorded more than twenty years ago, the attributes of most data were modified to match the new metadata as described in Gao’s (2002) Appendix C. The processing steps to develop the current KFD of Minnesota can be divided into the following seven phases:

1080

Table 5 Number of karst features sorted by county and feature types stored in the Karst Feature Database of Minnesota (Last update, November 17, 2002) County Chicago Dakota Dodge Fillmore Goodhue Hennepin Houston Mower Olmsted Pine Ramsey Wabasha Washington Winona Total

Sinkholes 31 62 6,253 347 2 63 302 915 260 197 22 661 9,115

Springs 1 63 28 866 154 27 71 92 530 14 45 129 300 2,320

Stream sinks

Tile outlets

Misc.

1 1 138 9 1 18 6

2

1

35

2 12 186

from Magdalene (1995) was modified and loaded into the database for this county. Outcrops in Winona County were digitized and transferred into the database. Sub-drained areas were delineated based on surface topography, watershed boundaries, karst feature distribution, and drainage patterns. 3. Entities, attributes, domains, keys, relationships, and referential integrities for the KFD were revised. A relatively complete metadata for all the karst features was created to include detailed entity types, attributes, domains, and keys for all the entities in the karst feature DBMS. Relationships and referential integrities among entities in the karst feature DBMS were established in Microsoft Access 2000 (Fig. 4). 4. Other archived karst feature datasets were loaded into the KFD and applications were built for the KFD. Archived karst feature datasets were modified to be compatible with the metadata of the karst feature DBMS and then loaded into the database. Magdalene’s (1995) sinkhole datasets were modified to replace the sinkhole data in Winona County in MGS’s archived karst feature dataset. Applications were Fig. 5 Number of karst features sorted by feature types in Minnesota (Last update, Nov. 17, 2002)

Tile inlets

20 1

1 38

22

Total 1 95 91 7,259 510 30 134 412 1,487 260 14 264 151 974 11,682

written in Visual Basic, ArcInfo AML, and ArcView Avenue programing languages to interact with the KFD. 5. Database consistency and security were verified and DBMS applications were tested. The karst feature DBMS was put on a Window NT server accessible to researchers in MGS and the Department of Geology and Geophysics at the University of Minnesota through networked interfaces. The applications of the karst feature DBMS were tested and modified to maintain its consistency and security. 6. User tests were conducted to verify attributes and locations of karst features in the database by using applications built for the database. Users in MGS and the Department of Geology and Geophysics at the University of Minnesota tested the applications of the karst feature DBMS and verified the locations and attributes of karst features in Winona and Olmsted Counties. Applications were revised based on issues and problems reported by the users. 7. Additional data were converted and loaded into the database and their overall consistency and security

1081

Fig. 6 Number of karst features sorted by counties in Minnesota (Last update, Nov. 17, 2002)

were verified. The conceptual data model, applications and metadata of the karst feature DBMS were further revised to be compatible with current and subsequent datasets. Web-accessible karst feature inventory points are generated and posted to the Minnesota Department of Natural Resources’ website. Table 5 lists all the karst features sorted by county and feature types stored in the KFD of Minnesota. There were 11,682 karst features stored in the KFD of Minnesota on November 17, 2002. Figures 5 and 6 show the number of karst features sorted by feature types and counties, respectively. Sinkholes and springs account for approximately 78% and 20% of all the karst features stored in the karst feature DBMS, respectively. The main data sources of the database are from County Atlas projects (Alexander et al. 2003; Alexander and Maki 1988; Dalgleish and Alexander 1984; Tipping et al. 2001; Witthuhn and Alexander 1995). The karst

mapping portions of County Atlas projects have focused on mapping sinkholes and nearby springs and other karst features. The sinkhole dataset is relatively more complete than the other karst feature types but a significant fraction of sinkholes have not been mapped in Minnesota. As can be seen in Fig. 6, more than 60% of all the karst features appear in Fillmore County. Fillmore, Olmsted, and Winona counties account for more than 80% of the currently mapped karst features and less than 20% of the karst features were located in the other 11 counties. Acknowledgements This research project was supported with funding and technical assistance from the Minnesota Department of Health (MDH) and conducted through the Minnesota Geological Survey (MGS). The karst feature locating and verification efforts of Scott Alexander, David Berner, Jeff Green, Lisa Holland, Sue Magdalene, Bev Shade, and many others are gratefully acknowledged.

References Alexander EC Jr, Maki GL (1988) Sinkholes and Sinkhole Probability. Geologic Atlas Olmsted County, Minnesota, County Atlas Series C-3, Plate 7 (1:100,000). Minnesota Geological Survey, University of Minnesota Alexander EC Jr, Green JA, Alexander SC, Spong RC (1995) Springsheds. Geologic Atlas Fillmore County, Minnesota, County Atlas Series C-8, Part B, Plate 9 (1:100,000). Minnesota Department of Natural Resources, Division of Waters Alexander EC Jr, Berner D, Gao Y, Green JA (2003) Sinkholes and Sinkhole Probability, and Springs and Seeps. Geologic Atlas of Goodhue County, Minnesota, County Atlas Series C-12, Part B, Plate 10 (1:100,000). Minnesota Department of Natural Resources, Division of Waters

Codd EF (1970) A relational model of data for large shared data banks. CACM 13(6):377–387 Cooper AH, Farrant AR, Adlam KAM, Walsby JC (2001) The development of a national geographic information system (GIS) for British karst geohazards and risk assessment. In: Beck BF, Herring JG (eds) Geotechnical and environmental applications of karst geology and hydrology, proceedings of the 8th multidisciplinary conference on sinkholes and the engineering and environmental impacts of karsts. Louisville, KY, A.A. Balkema, Lisse, 1–4 April 2001, pp 145–151 Dalgleish JD, Alexander EC Jr (1984) Sinkholes and sinkhole probability. Geologic Atlas Winona County, Minnesota, County Atlas Series C-2, Plate 5 (1:100,000). Minnesota Geological Survey, University of Minnesota

Denizman C (1997) Geographic information systems as a tool in karst geomorphology. In: Abstracts with ProgramsGeological Society of America 29(6):290 Elmasri R, Navathe SB (1994) Fundamentals of database systems. AddisonWesley, Reading, pp873 Fleming CC, von Halle B (1989) Handbook of relational database design. AddisonWesley, Reading, pp605 Gao Y (2002) Karst feature distribution in southeastern Minnesota: extending GIS-based database for spatial analysis and resource management. PhD Thesis, University of Minnesota Gao Y, Alexander EC Jr, Tipping RG (2002) The development of a karst feature database for southeastern minnesota. J Cave Karst Stud 64(1):51–57

1082

Gao Y, Alexander EC Jr, Barnes RJ (2005) Karst database implementation in Minnesota: analysis of sinkhole distribution. Environ Geol (in press) Giammona CP (1973) Fluorescent dye determination of groundwater movement and contamination in permeable rock strata. Int J Speleol 5(3–4):201–208 Green JA, Marken WJ, Alexander ECJ, Alexander SC (2002) Karst unit mapping using geographic information system technology, Mower County, Minnesota, USA. Environ Geol 42(5):457–461 Lei M, Jiang X, Li Y (2001) New advances of karst collapse research in China. In: Beck BF, Herring JG (eds) Geotechnical and environmental applications of karst geology and hydrology, proceedings of the 8th multidisciplinary conference on sinkholes and the engineering and environmental impacts of karsts. Louisville, KY, 1–4 April, A.A. Balkema, Lisse, pp 145–151

Magdalene SCC (1995) Sinkhole distribution in Winona County, Minnesota, revisited. MS Thesis, University of Minnesota Ray JA, Goodmann PT, Meiman J (2000) Inaccurate sub-division of hydrologic units in kentucky’s karst watersheds [abs.]: 45th annual midwest ground water conference. Columbus, OH, October 17–19, pp 35–36 Tipping RG, Green JA, Alexander EC Jr, (2001) Karst features. Geologic Atlas of Wabasha County, Minnesota, County Atlas Series C-14, Part A, Plate 5 (1:100,000): Minnesota Geological Survey, University of Minnesota Veni G (2002) Revising the karst map of the United States. J Cave Karst Stud 64(1):45–50 Veni G, DuChene H, Crawford NC, Groves CG, Huppert GN, Kastning EH, Olson R, Wheeler BJ (2001) Living with karst: a fragile foundation. American Geological Institute, Alexandria, pp65 White WB (2001) The karstmap project: progress and status [abs.]: national speleological society convention program guide. Great Saltpetre Cave Preserve, Kentucky, pp86

Whitman D, Gubbels T (1999) Applications of GIS Technology to the triggering phenomena of sinkholes in central Florida. In: Beck BF, Pettit AJ, Herring GJ (eds) Hydrogeology and engineering geology of sinkholes and karst, proceedings of the 7th multidisciplinary conference on sinkholes and the engineering and environmental impacts of karst. Harrisburg-Hershey, Penn., 10–14 April, A.A. Balkema, Rotterdam, pp 67–73 Witthuhn MK, Alexander EC Jr (1995) Sinkholes and Sinkhole Probability. Geologic Atlas Fillmore County, Minnesota, County Atlas Series C-8, Part B, Plate 8 (1:100,000): Minnesota Department of Natural Resources, Division of Waters Wopat MA (1974) The karst of southeastern Minnesota; and methods for statistical analysis of polymodal twodimensional orientation data. MS Thesis, University of Wisconsin-Madison