Published on March 5, 2014
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 – firstname.lastname@example.org 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
Access the full library of high quality Microsoft training courses for the ... Learn database fundamentals with this free course designed to help beginners ...
This Oracle Database 11g: SQL Fundamentals II Self-Study Course teaches you how to control privileges at the ... WW Oracle training; Why choose Oracle ...
This Oracle Database 11g: SQL Fundamentals course teaches you about the fundamentals of SQL using Oracle Database 11g database ... Training Formats
Offering 567 Database training courses from 51 training providers including SkillSoft, ... Database Fundamentals: 7 hours: 4: $169 252779 ...
Amazon RDS Managed Relational Database Service for ... Hear why people choose AWS Training and ... Learn fundamental AWS security concepts ...
Get the SQL training and certification you need. ... Training Exam; Database Fundamentals: ... Maintain a Microsoft SQL Server 2008 R2 Database:
This three-day MTA Training course helps you prepare for Microsoft ... Creating Database ... Database Administration Fundamentals: MTA Exam 98 ...
Relational Database Fundamentals. Skip navigation Lynda.com | a LinkedIn company. ... Return to your organization's learning portal to continue training, ...