Kansas Geological Survey, Open-file Report 2016-30
KGS Open File Report 2016-30
December 2016
This report explains how to use the macro-enabled Excel workbook HyDRA_Translate.xlsm. The workbook implements a few of the steps in the HyDRA (Hydrostratigraphic Drilling Record Assessment) processing sequence. The overall objective of the HyDRA process, which is an extension of the earlier PST+ project (Macfarlane and Schneider, 2007; Macfarlane, 2009), is to develop three-dimensional (3D) aquifer property models from water well drillers' logs. The basic approach is to convert the verbatim sediment (or lithology) descriptions in the logs into a 3D point dataset representing volumetric proportions of material in a reasonably small set of sediment classes or categories and then interpolate those proportions to a 3D grid (e.g., a flow model grid). Aquifer properties in each grid cell can then be computed based on the category proportions in that grid cell. A program implementing the full set of processing steps is under development but is not yet available. This workbook provides users an immediate means to perform the steps that are unique to the processing of drillers' logs. Specifically, HyDRA_Translate.xlsm implements the three steps involved in getting from a set of verbatim logs to a 3D point dataset that represents the category proportions in a sequence of regularly spaced intervals in the logged wells. These steps are referred to as standardization, categorization, and segmentation.
Standardization is the process of mapping the verbatim sediment description for each logged depth interval into one or more standardized lithology codes, along with a set of percentages associated with the standardized lithologies. (The term "sediment type" would be more accurate than "lithology" in many cases, but this documentation will continue to use "lithology.") For example, "lime green sand with streaks of fuchsia clay and a bit of baby blue gravel" might be represented as 70% snd (sand), 20% c (clay), and 10% g (gravel). This translation process uses a translation table that maps each unique sediment description into a standardized representation. Building the translation table is a labor-intensive and subjective process, since a standardized representation of each description has to be entered by hand. The degree of subjectivity involved in assigning each standardized representation varies with the degree of ambiguity of the description, which can range from quite clear to highly ambiguous.
Categorization is the process of converting the standardized lithology proportions in each logged interval into proportions of material in a smaller number of sediment categories, based on a table specifying the category into which each standardized lithology falls. KGS modeling projects have used a set of 71 standardized lithologies and mapped them into five to eight categories, with the number of categories and category assignments varying to meet the needs of each project. The workbook contains an example lithology/category worksheet listing these 71 lithology codes and two example categorizations, one using five categories and another using eight categories. The rationale behind the separation of standardization and categorization into separate steps is that the standardized representations of a set of verbatim descriptions should be useful for a number of different purposes, while the categorization of the standardized lithologies might change fairly often, to meet the needs of different projects or for modeling different properties within the same project (e.g., different categorizations for hydraulic conductivity and specific storage).
Segmentation is the process of dividing each well into a sequence of regular intervals (e.g., 10-foot intervals) and computing the category proportions within each of those regular intervals based on the category proportions in all the logged intervals that fall within or overlap each regular interval. The segmentation is based on elevation, rather than depth, and the process also adds geographic (X and Y) coordinates. The end result is a 3D point dataset of category proportions (with the Z coordinate of each point being the center elevation of the regular interval). To run this process, you need to provide coordinate information for the wells, including surface elevation and X and Y coordinates. However, you may provide all zeros for the coordinate values if no coordinate information is available. Supplying zeros for the surface elevation values leads to segmenting the wells based on negative depth.
HyDRA_Translate.xlsm does not include code for importing logs or extracting them from a database--you have to provide a worksheet with the logs--nor does it include code for the subsequent steps of interpolating the category proportions to a 3D grid or computing property values based on the proportions. Those steps will be included in the complete HyDRA processing system. Other important operations not included in the workbook are exploratory analysis and visualization of the logs. However, the workbook includes routines that assist with error identification and quality control and with management of the translation table.
Kansas Geological Survey, Geohydrology
Placed online Dec. 20, 2016
Comments to webadmin@kgs.ku.edu
The URL for this page is http://www.kgs.ku.edu/Hydro/Publications/2016/OFR16_30/index.html