EnviroInsite training workshop - Database fundamentals

50 %
50 %
Information about EnviroInsite training workshop - Database fundamentals
Technology

Published on March 5, 2014

Author: BruceJacobs3

Source: slideshare.net

Description

Presentation from EnviroInsite training workshop covering data flow and database schema used by EnviroInsite

Understanding Data Flow EnviroInsite Training Workshop HydroAnalysis, Inc.

EnviroInsite Database Wells EI Data Well Id, Coordinates, surface elevation, top casing elevation, class Observations Well Id Well Id, Screen Id, Date, Constituent, Media, Fraction, Value, Formatted Value Screens Well Id Screen Id Well Id, Screen Id, top/bottom elevations Constituents Constituent, Media, Fraction, Units, Standard Name, Standard Data Flow Constituent Media Fraction

Wells Table Field Content / Comments Type Well Well id Text Easting Consistent coordinate system, can be left blank Numeric Northing Consistent coordinate system Numeric Surface Elevation Screen depth relative to this elevation Numeric Top of Casing Can be left blank Numeric Well Bottom Elevation Elevation of bottom of hole Numeric EnviroInsite Training

Wells Table Field Content / Comments Type Class User-specified category used for querying data Text Azimuth Slope direction clockwise from due north Numeric Incline Well angle relative to vertical Numeric (Optional Fields) Used in full-page boring logs Text EnviroInsite Training

Screens Table Field Content / Comments Type Well Well id – must be in Wells table Text Screen Unique id for each well Text Top Elevation Numeric Bottom Elevation Numeric (Optional Fields) Used in full-page boring logs EnviroInsite Training Text

Wells – Screens – Observations (one to many) Wells Screens Observations 1/1/2011, TCE, 1.0 D 1/1/2011, PCE, 1.0 1/1/2013, TCE, 1.0 MW-1 S EnviroInsite Training 1/1/2011, TCE, 1.0

Constituents – Observations (one to many) Constituents Observations MW-1, D, 1/1/2001, 1 Mercury, water, dissolved MW-2, D, 1/1/2001, 1 MW-1, S, 1/1/2001, 1 Constituents Observations MW-1, D, 1/1/2001, 1 PCE, water, - MW-2, D, 1/1/2001, 1 MW-1, S, 1/1/2001, 1 EnviroInsite Training

In Database Well Screens Northing Surface Elevation MW-1 23456 7890 75 MW-2 12345 6789 75 Well Wells Easting Screen Top Elevation Bottom Elevation MW-1 S 50 45 MW-1 D 0 -10 Well MW-1 S Mercury Water D 2 MW-1 Observations Screen Constituent Media Fraction Value D Mercury Water D 1 Constituent Constituents Media Fraction Units Mercury Water D mg/L PCE water - µg/L EnviroInsite Training … … …

What about data not at a well? All measured data occurs at a “Well” and a “Screen”including • Samples from a well • Soil samples • Air measurements • Surface water measurements EnviroInsite Training

What about data not at a well? Well A Location Screen Vertical Horizon EnviroInsite Training

What about data not at a well? There is an Exception!!! Data you don‟t want to load in the database • Modeled data • Gridded data • Any data where location not important EnviroInsite Training

You Have Choices Elevation Based Depth Based Table Field Table Field Wells Well Bottom Elevation Wells Well Bottom Depth Screens Top Elevation Screens Top Depth Bottom Elevation Convert fields by: Bottom Depth In Excel -> Changing header cell text In Access -> Changing field name EnviroInsite Training

You Have Choices User-Specified Coordinates Lat - Long Table Field Table Field Wells Easting Wells Longitude Wells Northing Wells Latitude Convert fields by: In Excel -> Changing header cell text In Access -> Changing field name EnviroInsite Training

If you choose lat – long coordinates Enter longitude as degrees east (in USA longitude < 0) Transformation of coordinates prior to plotting to either (you choose which) • State Plane (feet) • Universal Transverse Mercator (meter) EnviroInsite Training

What is Formatted Value in Observations table? • Control over number of digits / display of data flags … • Populating Formatted Value field is optional • Used by: • Data Tables • Posted Values 1,000,000.00 100.00 0.01 Without Formatted Value 1,000,000 100.0 0.01u With Formatted Value EnviroInsite Training

What about non-detects? Observations . Flag • Populate with ND or < to indicate non-detect Observations . Formatted Value • Populate with text string as you want values to appear (e.g. <0.1u or ND) Some plots need a numeric value (ie. Time Charts, pie charts, radial diagrams) • Put value of your choice in Observations . Value • Could be 0 or some fraction of detection limit EnviroInsite Training

EnviroInsite Database Wells EI Data Well Id, Coordinates, surface elevation, top casing elevation, class Well Id Well Id Stratigraphy Well Id, top/bottom elevations Data Flow Borings Well Id, top/bottom elevation

Borings and Stratigraphy Borings Detailed description of soil or geologic profile as would be reported in a boring log Plotted in 3d, on sections, strip logs, or boring logs No correlation of units between Borings Stratigraphy Geologist or hydrogeologist interpretation of stratigraphic units that would typically straddle multiple Borings records Plotted in 3d, on sections, plus fence diagrams and geologic models Correlation of units between Stratrigraphy locations is implied EnviroInsite Training

Borings Table Field Content / Comments Type Well Well id – must be in Wells table Text Strata Text Top Elevation Numeric Bottom Elevation Numeric (Optional Fields) Used in full-page boring logs EnviroInsite Training Text

Stratigraphy Table Field Content / Comments Type Well Well id – must be in Wells table Text Strata Must be unique for each location Text Top Elevation Numeric Bottom Elevation Numeric EnviroInsite Training

You Have Choices Elevation Based Depth Based Table Field Table Field Stratigraphy Top Elevation Stratigraphy Top Depth Bottom Depth Bottom Elevation Borings Screens Top Elevation Bottom Depth Bottom Elevation Convert fields by: Top Depth In Excel -> Changing header cell text In Access -> Changing field name EnviroInsite Training

Be Careful When Loading Stratigraphy Data • Don‟t repeat units NO! OK! B1 B1 Sand Sand Clay Clay 1 Sand Deep Sand Clay Clay 2 EnviroInsite Training

Hints for Entering Stratigraphy Data? • Don‟t repeat units • Be consistent NO! OK! B1 B2 B1 Sand Sand B2 Sand Clay 1 Clay 1 Sand Clay 1 Deep Sand Clay 2 Clay 1 Deep Sand Clay 2 Deep Sand Deep Sand Clay 2 Clay 2 EnviroInsite Training

Hints for Entering Stratigraphy Data? • Don‟t repeat units • Be consistent • May omit layers (inferred zero thickness) OK! B1 B2 Sand Sand Clay 1 Deep Sand Deep Sand Clay 2 Clay 2 EnviroInsite Training

EnviroInsite Database Wells EI Data Well Id, Coordinates, surface elevation, top casing elevation, class Well Id Well Id Well Construction Well Id, material, top/bottom depth, diameter, notes Fill Well Id, material, top/bottom depth, diameter We‟ll come back to this in a later lesson Data Flow

How do I get started? 1. Let EnviroInsite do it for you • File -> Create Database • Elevation and North-East based (for now) 2. Copy empty files from DocumentsMy EnviroInsite Work • Input.xls • input.xlsx • input.mdb EnviroInsite Training

How do I get started? 3. Borrow from DocumentsMy EnviroInsite WorkTutorial • Existing data can be modified and extra rows deleted • In Excel – be careful to delete rows (not just cells) • May be particularly useful for boring logs 4. Export from 3rd party database such as EQuIS or EnviroData EnviroInsite Training

Loading Data… Sometimes it can be hard… EnviroInsite Training

Populating Data Files w/o Tears 1. Populate all linking fields EI Data 2. Populate filtering fields (e.g. Screen.Group, Well.Class, Screen.ID) 3. Double-check match of linking fields 4. Assume case-sensitivity of linking fields Full Description of Database in EI Tutorial Data Flow

Populating Data Files w/o Tears 1. Check consistency of elevation field values EI Data 2. Check consistency of depth field values 3. If you don‟t know what to do and it‟s not a numeric field, enter a single hyphen 4. If all else fails – support@enviroinsite.com Full Description of Database in EI Tutorial Data Flow

Populating MS Excel Data Files w/o Tears 1. No non-numeric data in numeric fields EI Data 2. Preface numbers in text fields with single apostrophe ( „8) Full Description of Database in EI Tutorial Data Flow

What‟s Wrong? Wells Table Well Easting Northing MW-1 300 200 MW-2 100 500 Class Monitoring well Screens Table Well mw-1 MW-2 Screen Top Elevation Bottom Elevation d 200 300 500 300 EnviroInsite Training

What‟s Wrong? Constituents Table Constituent Media Units Standard Cadmium Water ug/L 100 Cadmium Soil mg/kg None Observation Table Well Screen Date Constituent mw-1 d 20-Mar-1999 Cadmium MW-2 s Cadmium EnviroInsite Training Media Value W 300 Soil 300

Saving Documents • File -> Save preserves complete session • Document extension - vizx or xml • Automated Backup • Complete file path of images / database • File -> Package zips all files into single file – to simplify file exchange EnviroInsite Training

Data Flow Hands-on Exercise

Add a comment

Related presentations

Related pages

Database Training and Fundamentals – Microsoft Virtual ...

Access the full library of high quality Microsoft training courses for the ... Learn database fundamentals with this free course designed to help beginners ...
Read more

Oracle Database 11g: SQL Fundamentals II | SQL and PL/SQL ...

This Oracle Database 11g: SQL Fundamentals II Self-Study Course teaches you how to control privileges at the ... WW Oracle training; Why choose Oracle ...
Read more

Oracle Database 11g: SQL Fundamentals I | SQL and PL/SQL ...

This Oracle Database 11g: SQL Fundamentals course teaches you about the fundamentals of SQL using Oracle Database 11g database ... Training Formats
Read more

Database Training, dB Certification Courses and Seminars

Offering 567 Database training courses from 51 training providers including SkillSoft, ... Database Fundamentals: 7 hours: 4: $169 252779 ...
Read more

AWS Training | Technical Classes and Workshops

Amazon RDS Managed Relational Database Service for ... Hear why people choose AWS Training and ... Learn fundamental AWS security concepts ...
Read more

Microsoft SQL Training | Microsoft Learning

Get the SQL training and certification you need. ... Training Exam; Database Fundamentals: ... Maintain a Microsoft SQL Server 2008 R2 Database:
Read more

Course 40364A: Database Administration Fundamentals: MTA ...

This three-day MTA Training course helps you prepare for Microsoft ... Creating Database ... Database Administration Fundamentals: MTA Exam 98 ...
Read more

Relational Database Fundamentals | Lynda.com

Relational Database Fundamentals. Skip navigation Lynda.com | a LinkedIn company. ... Return to your organization's learning portal to continue training, ...
Read more