1、 Access to Additional Content for PUBL 4731 (Click here to view the publication) This Page is not part of the original publication This page has been added by IHS as a convenience to the user in order to provide access to additional content as authorized by the Copyright holder of this document Clic
2、k the link(s) below to access the content and use normal procedures for downloading or opening the files. Files associated with PUBL 4731 Information contained in the above is the property of the Copyright holder and all Notice of Disclaimer however, the Institute makes no representation, warranty,
3、or guarantee in connection with this publication and hereby expressly disclaims any liability or responsibility for loss or damage resulting from its use or for the violation of any federal, state, or municipal regulation with which this publication may conflict. All rights reserved. No part of this
4、 work may be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without prior written permission from the publisher. Contact the Publisher, API Publishing Services, 1220 L Street, N.W., Washington, D.C. 20005. Copyrig
5、ht 2003 American Petroleum Institute iii ACKNOWLEDGMENTS API STAFF CONTACT Harley Hopkins, Regulatory Analysis and Scientific Affairs Department (RASA) MEMBERS OF THE API SOIL AND GROUNDWATER TECHNICAL TASK FORCE The API would like to acknowledge the following individuals for their contributions of
6、time and expertise during this study and in the preparation of this report: Curtis Stanley, Shell Global Solutions (US) Inc.; (Chairman) Mark Adamski, BP p.l.c Tim Buscheck, ChevronTexaco Corporation Tom Henson, Exxon Mobil Corporation Dan Irvin, ConocoPhillips Company Ravi Kolhatkar, BP p.l.c Victo
7、r Kremesec, BP p.l.c 2003 American Petroleum Institute - All Rights Reserved v PREFACE The Light Non-aqueous Phase Liquid (LNAPL) Parameters Database is a collection of information about samples that have had their capillary parameters determined, as well as other physical parameters measured. Capil
8、lary properties are critical in multiphase calculations, and those results have very high sensitivity to these properties. The primary purpose of this database is to provide information to users who are trying to characterize the movement and distribution of LNAPL within a site that has a limited se
9、t of direct observations of the capillary properties of the site. Other databases of related parameters have typically been derived from measurements in the agricultural or the petroleum extraction industries; neither being necessarily representative of near-surface environmental conditions. This da
10、tabase give the user the opportunity to understand the range of capillary characteristics observed at sites that are geologically similar, but where there are more direct and laboratory observations available. This document is broken up into 4 primary sections: Data Retrieval, Data Entry, Database D
11、esign, and Database Maintenance. Most users have interest in the first topic. The Data Retrieval section describes the types of data that are contained in the database and how a user can quickly retrieve subsets of the database that match various user input filters or selection criteria. This sectio
12、n includes several user scenarios with user inputs and database outputs conveyed through a series of sample screens and text. The Data Entry section describes how a user can add new data to the database using two primary data entry methods: 1) manual data entry through data entry forms, and 2) data
13、import from electronic sources. Both methods are described and examples are provided. API recommends that only individuals and organizations with a high level of technical and database expertise attempt to add data to the database. Appendices A-C are written for database developers. A detailed descr
14、iption of the database design is provided in the first appendix. This section describes the structure of the database and the relationships between its tables, queries, forms, reports, and macros. The database maintenance section describes areas within the database that will require periodic mainten
15、ance and how to perform that maintenance, data validation, removing data from the database, etc. 2003 American Petroleum Institute - All Rights Reserved vii TABLE OF CONTENTS page 1. Getting information out of the database: Data Retrieval 1 1.1. Database contents . 1 1.2. Getting Started 2 1.3. Retr
16、ieve samples for a given soil type 6 1.4. Retrieve samples within a range of fine (silt + clay) percentages. 7 1.5. Retrieve samples within a permeability range 8 1.6. Retrieve samples within a conductivity range 8 1.7. Retrieve samples with capillary parameters that match search criteria 8 1.8. Ret
17、rieve samples from a particular site. 9 1.9. Raw data retrieval. 10 1.9.1. Raw capillary data for a given sample 10 1.9.2. Raw grain size data for a given sample 11 2. Data Entry 13 2.1. Selecting data for entry into the database . 13 2.2. Manual data entry using data entry forms 13 2.2.1. Site Entr
18、y Form 14 2.2.2. Samples Entry Form . 15 2.2.3. Adding new standard values to pick-lists . 17 2.2.4. Capillary Data Entry. 19 2.2.5. Petrophysical data entry form. 21 2.2.6. Grain size data entry forms. 22 2.2.7. Fluid properties data entry forms 26 2.3. Importing data from other sources: Using the
19、Data Entry (Excel) Spreadsheet . 28 2.3.1. Getting Started 29 2.3.2. PTS Labs data structures 29 2.3.3. Reformatting data for import 31 2.3.4. Petrophysical (PphysData) data worksheet. 33 2.3.5. Raw capillary data (CapRaw) worksheet 34 2.3.6. Capillary fit parameters (CapFit) worksheet. 35 2.3.7. Gr
20、ain size weight percentages (GS-weight%) worksheet. 35 2.3.8. Grain size statistics (GS-stats) worksheet. 36 2.3.9. Gain size summary (GS-summ) worksheet 37 2.3.10. Raw grain size data (GS-sizes) worksheet 38 2.3.11. Site fluid viscosity data (Viscosity) worksheet. 38 2.3.12. Site fluid tensions dat
21、a (Tensions) worksheet 39 Appendices A Database Design and Maintenance. 41 A.1 Database Design Document Audience . 41 A.2 Design Overview 42 A.2.1 Data Type Basics 43 A.3 Database Tables 43 A.3.1 Sites Table 44 A.3.2 Samples Table. 45 A.3.3 Capillary Parameters Table. 46 A.3.4 Raw Capillary Data 46
22、A.3.5 Petrophysical Data Table 47 A.3.6 Fluid Properties Table 48 A.3.7 Fluid Tensions Table 48 A.3.8 Grain Size Summary Table. 49 A.3.9 Raw Grain Sizes Table . 51 viii 2003 American Petroleum Institute - All Rights Reserved TABLE OF CONTENTS (continued) page A.3.10 Weight Percentages Table 52 A.3.1
23、1 Grain Size Statistics Table 53 A.3.12 Ancillary Tables 54 A.4 Database Structure 56 B Data Quality Control 59 B.1 Data Sources. 59 B.2 Measurement Techniques and Standards 59 B.3 Data Validation. 60 B.3.1 Referential Integrity Checking 60 B.4 Database Backups. 60 C Database Maintenance 61 C.1 The
24、Query-by-Forms method .61 C.2 Adding Queries to the Database . 64 C.3 Adding Reports to the Database . 65 D Basic Equations 67 E References 73 List of Figures Figure 1: Basic Access screen with annotations. 2 Figure 2: Query Forms . 3 Figure 3: Query by Permeability Range screen (maximized) with ann
25、otations 3 Figure 4: Database Summary: All parameters, 1 page per sample . 4 Figure 5: Report: Samples and Stats. 5 Figure 6: Tabular Listing of Matching Samples. 6 Figure 7: Soil Type Selection Menu. 6 Figure 8: Query by Range of Fines Menu and Options 7 Figure 9: Querying by Capillary Parameters 8
26、 Figure 10: Query by site form 9 Figure 11: Raw capillary data report with van Genuchten parameter fit 10 Figure 12: Raw capillary data with Brooks-Corey fit. 11 Figure 13: Raw grain size data with grain size distribution plot. 12 Figure 14: Basic data entry form 14 Figure 15: Site data entry form.
27、15 Figure 16: Samples data entry form - last completed record 16 Figure 17: Samples data entry form - new blank record. 17 Figure 18: Update standard values data entry menu. 18 Figure 19: Updating standard values, a) Capillary test method, b) Fluid matrix 19 Figure 20: Capillary fit parameters data
28、entry form . 20 Figure 21: Raw capillary data entry form. 21 Figure 22: Petrophysical data entry form . 22 Figure 23: Grain Size Data form 23 Figure 24: Grain summary data entry form 24 Figure 25: Grain size statistics data entry form 25 Figure 26: Raw grain size data entry form . 26 Figure 27: Flui
29、d properties data entry form 27 Figure 28: Interfacial tensions data entry form. 28 Figure 29: Examples of PTS Labs data formats . 30 Figure 30: Excel data template workbook - PetPhys worksheet. 34 2003 American Petroleum Institute - All Rights Reserved ix TABLE OF CONTENTS (continued) page Figure 3
30、1: Excel data template workbook CapRaw worksheet . 34 Figure 32: Excel data template workbook - CapFit Worksheet 35 Figure 33: Excel data template workbook GS-weight% worksheet 36 Figure 34: Excel data template workbook GS-stats worksheet . 37 Figure 35: Excel data template workbook GS-summ workshee
31、t. 37 Figure 36: Excel data template workbook GS-sizes worksheet. 38 Figure 37: Excel data template workbook Viscosity worksheet 39 Figure 38: Excel workbook Viscosity worksheet with side-by-side PTS Lab formatted data. 40 Figure 39: Listing of all database tables. 44 Figure 40: Folk ternary diagram
32、s . 50 Figure 41: Database structure and relationships. 57 Figure 42: Design view of the Query_By_Permeability_Range form 62 Figure 43: Database Summary button property settings. 62 Figure 44: QBF_AllByPermRange report design view64 List of Tables Table 1: Common paste errors when copying data from
33、Excel worksheets to Access tables 32 Table 2: SITES 44 Table 3: Samples 45 Table 4: Cap_Summary . 46 Table 5: Cap_Rawdata. 47 Table 6: PetrophysicalData . 47 Table 7: Fluid_Properties. 48 Table 8: Fluid_Tensions. 49 Table 9: Grain_Summary. 49 Table 10: Folk soil description formation 50 Table 11: US
34、CS soil classification . 51 Table 12: Grain_Size 52 Table 13: GS_Weights . 53 Table 14: Grain_stats . 54 Light Non-Aqueous Phase Liquid (LNAPL) Parameters DatabaseVersion 2.0Users Guide 2003 American Petroleum Institute - All Rights Reserved 1 1 Getting information out of the database: Data Retrieva
35、l The most basic use of this database of LNAPL parameters involves retrieving a subset of data records that match a particular set of selection criteria. In database jargon, this process is called “querying the database”. Users specify what subset of information they would like to retrieve, and the
36、set of criteria by which matching records should be determined. However, before one can get started retrieving data, one must know a little bit about the types of data stored in the database. 1.1 Database contents The LNAPL Parameters Database is a collection of information about samples that have h
37、ad their capillary parameters determined, as well as other physical parameters measured. Capillary properties are critical in multiphase calculations, and those results have very high sensitivity to these properties. Capillary properties are sparsely documented in technical literature, and the docum
38、entation that exists is generally for other fields of study. Thus, they are not as applicable to environmental release conditions. There are significant property differences between the environmental samples in the subject database and those found in the historic literature. The purpose of this manu
39、al is not to discuss these differences, but rather document the construction and content of the API LNAPL parameters database. The data (samples) contained in this database come from a number of different sources and sites (refineries, fuel storage facilities, military bases, etc.); information now
40、in the public domain. The data that have been included in this database were not collected for the purpose of populating a database of useful parameters. The samples were collected to answer site-specific questions and to aid in the mitigation and remediation of site-specific problems. Given this di
41、verse sample background, it is not surprising that the range of tests performed on each sample and the testing procedures used during the tests differ in many cases. Nevertheless, this is currently the most complete set of laboratory measurements of samples whose properties have been analyzed for th
42、e purpose of understanding LNAPL remediation in near-surface aquifers. The database contains the following types of information: Sample capillary parameters and the raw data from which they were derived. This information is available for nearly all samples in the database. Petrophysical data includi
43、ng density (bulk/grain), porosity, permeability, conductivity, water and hydrocarbon saturations, etc. Grain size data. Grain size data are collected and presented in several ways. - Raw grain size distribution data (weight fraction vs. grain size) - Grain size at various percentages of the cumulati
44、ve sample weight (i.e. the grain size at the 10th, 50th, and 90thpercentiles). - Grain size distribution statistics (mean, median, standard deviation) - Fraction of the sample in various grain size classifications (% sand, % silt, etc.) 2 2003 American Petroleum Institute - All Rights Reserved API P
45、ublication 4731 Typically, if the full raw grain size distribution is available, then all of the summary parameters are also available. For some samples, only summary data is available, while others have no data available. Fluid properties (viscosity, density, interfacial tensions). Typically, fluid
46、 properties samples are not taken from the same samples where rock properties are measured. Fluid properties samples are provided as a form of site characterization, rather than for direct comparison with other samples within the site. In many cases, the process of measuring one set of sample proper
47、ties (grain size) makes the sample useless for measuring other properties (e.g., porosity). In general, the collection of measurements attributed to a single sample is obtained from slices of a single core not separated by more than 6 vertical inches from the slice where the sample capillarity was d
48、etermined. 1.2 Getting Started The first step is to open the database in Microsoft Access. Locate the database file LNAPL_db_2000.mdb and then double click the left mouse button on the file. After opening the database, your screen should look like the screen in Figure 1. Figure 1: Basic Access scree
49、n with annotations The database should open with the Forms object open and the Query Forms option highlighted. However, the database will open in the state that it was in when it was last closed. If the last user did not leave the database in normal state, then some set-up might be necessary prior to getting started. On the left hand side of the screen, under the heading Objects (see figure 1), select Forms (single or double left-mouse click). The screen should now look like the screen in figure 1. Double click the left mouse button to on Query F
