Boreholes and Geology

Data Schema

ESdat stores borehole and geology data that would be encountered in a groundwater or contaminated site investigation,as well as the more common Geotech data.

Tables and their relationships are shown below:

A diagram of a structure

AI-generated content may be incorrect.

Boreholes

Information related to the borehole, is a 1 to 1 relationship with Locations. A Location may not be a borehole, but a borehole record must have a Location.

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
	Logged_By	Text(20)
	Date_Commenced	DateTime
	Date_Finalised	DateTime
	Borehole_Diameter	Numeric
	Diameter_Units	Text(8)
	Total_Depth	Numeric
	Refusal_Encountered	Numeric	Depth at which refusal is encountered
	Drilling_Method	Text(60)
	Drill_Company	Text(50)
	Drill_Rig	Text(30)	The type of drill rig
	Driller	Text(20)	The name of the head driller
	Bearing	Text(20)
	Plunge	Text(20)
	Test_Pit_Length	Text(20)
	Test_Pit_Width	Text(20)
	Test_Pit_Construction	Text(100)
	Comments	Text(1000)
	Licence_Number	Text(30)
	Depth_per_page	Numeric	For borehole outputs in ESlog
	Completion_Description	Text(1000)
	Checked_By	Text(30)	Who has reviewed the bore data / bore log
	Checked_Date	DateTime

Geology

Geology and Lithology data. Data can be stored either in individual components (ie Colour, Grain Size), or a full description can be included as a sentence in the Lithology Description Field.

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Top_Depth	Numeric
	Bottom_Depth	Numeric
*	MultiLith_Num	Integer	Increments for each lithology being described over a single depth interval.
	MultiLith_Descriptor	Text(255)	Allows for multiple lithologies to be logged as separate lithologies within the one depth interval. Descriptor would be "Interbedded With" or similar.
	Lithological_Code	Text(100)	A code used to describe the lithology, from zRef_Lithology_Codes
	Lithological_Description	Text(1000)	Free text description of the lithology
	Primary_Major_Constituent	Text(50)	For example, for a Sandy Clay, the Primary Major Constituent would be Clay.
	Secondary_Major_Constituent	Text(50)	For example, for a Sandy Clay, the Secondary Major Constituent would be Sandy.
	Tertiary_Major_Constituent	Text(50)	For example, for a Silty Sandy Clay, the Tertiary Major Constituent would be Silty.
	Minor_Components_Description	Text(255)	Descripton of the minor components.
	Minor_Constituent_1	Text(100)	Description of a specific minor constituent
	Minor_Constituent_2	Text(100)	Description of a specific minor constituent
	Minor_Constituent_3	Text(100)	Description of a specific minor constituent
	Mineralogy	Text(50)	Describe the Mineralogy.
	Formation	Text(50)
	Stratigraphy1	Text(50)	A stratigraphy interpretation tied to lithology layers. A separate Stratigraphy table is also available if more suitable.
	Stratigraphy2	Text(50)	A second stratigraphy interpretation tied to lithology layers. A separate Stratigraphy table is also available if more suitable.
	Recovery_Method	Text(30)	Method by which the geological samples are being recovered.
	Moisture	Text(50)
	Colour	Text(100)
	Munsell_Colour	Text(100)
	Grain_Size	Text(30)
	Recovery_%	Integer
	Structure	Text(80)
	Fracture_Spacing	Text(40)
	Fracture_Spacing2	Text(40)
	Staining	Text(50)
	Odour	Text(30)
	HCL_Reaction	Text(50)
	Major_Unit_Percent	Numeric	The percentage of the lithology that is the dominant lithology.
	Upper_Boundary_Transition	Text(40)	Description of the upper boundary of the depth interval, it may be a sharp or gradual boundary with the overlying layer.
	Soil_Consistency	Text(50)
	Soil_Gradation	Text(70)
	Soil_Angularity	Text(70)	For example, rounded, angular, sub-angular
	Soil_Hardness	Text(20)
	Soil_Shape	Text(70)
	Soil_Deposition_Environment	Text(50)
	Soil_Cementation	Text(20)
	Soil_Plasticity	Text(50)
	Soil_Dilatancy	Text(25)
	Soil_Toughness	Text(20)
	Soil_Dry_Strength	Text(30)
	Soil_Percent_Fines	Numeric
	Soil_Percent_Sand	Numeric
	Soil_Percent_Gravel	Numeric
	Soil_Fines_Description	Text(150)	Description of the fines component of a soil, where relevant
	Soil_Sand_Description	Text(150)
	Soil_Gravel_Description	Text(150)	Description of the gravel component of a soil, where relevant
	Soil_Cobbles_Description	Text(150)	Description of the cobbles component of a soil, where relevant
	Soil_Boulders_Description	Text(150)	Description of the boulders component of a soil, where relevant
	Soil_USCS	Text(10)
	Max_Grain_Size	Text(10)
	Rock_Compressive_Strength	Text(30)	A first Compressive Strength Value
	Rock_Compressive_Strength2	Text(30)	A second Compressive Strength Value, if needed.
	Rock_Weathering	Text(30)	Description of the weathering state of rock.
	Rock_Bedding_Angle	Text(30)
	Comments	Text(255)

Geology Defect Mapping

Geotech defect data. Many of the field names below are technical, and for definitions of terms, such as "Waviness", "Aperture", "Joint Roughness Coefficient" the appropiate soils reference text should be referred to.

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Top_Depth	Numeric
*	Bottom_Depth	Numeric
*	Fracture_Set	Text(20)
*	Defect_Number	Text(10)
	Type	Text(20)
	Dip_Degrees	Text(10)
	Orientation_Degrees	Text(10)
	Roughness	Text(30)
	Planarity	Text(30)
	Waviness_Wavelength	Text(20)
	Waviness_Amplitude	Text(20)
	Joint_Roughness_Coefficent	Text(10)
	Surface_Appearance	Text(20)
	Aperture_Width_Prefix	Text(3)	<, >
	Aperture_Width	Text(20)
	Width_Units	Text(20)
	Aperture_Observations	Text(255)
	Infilling	Text(20)	Describe the infilling in the defects.
	Termination	Text(20)
	Persistence	Text(20)
	Wall_Strength	Text(20)
	Wall_Weathering	Text(20)
	Seepage_Rate	Text(20)
	Water_Flow_Rating	Text(20)
	Water_Flow_Units	Text(20)
	Comments	Text(255)

Geology Samples

Core Sample Recovered

PK	Column	Type	Description
	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
	Location_Code	Text(20)	References a Location in the Locations Table.
	Top_Depth	Numeric
	Bottom_Depth	Numeric
	Sampling_Method	Text(30)
	Date_Time	DateTime
	Total_Recovery_Length	Numeric
	Solid_Core_Recovery_Length	Numeric
	RQD_Length	Numeric	Rock Quality Designation, used in Geotech.
	Comments	Text(255)
	Length_Units	Text(7)
	Core_Number	Text(7)
	Diameter	Text(7)

Stratigraphy

Interpreted Stratigraphy information

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
	Top_Depth	Numeric
	Bottom_Depth	Numeric
*	Stratigraphic_Unit	Text(60)
	Comments	Text(255)

Aquifer Tests

Stores the results of Aquifer Test Interpretations. ESdat does not store the input data for rising head, falling head or pumping tests interpretations.

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Well	Text(10)	Alternatively known as a Pipe, Piezometer; has a screened interval typically in an aquifer. Multiple Wells can be present for one Location.
*	Depth_From	Numeric
	Depth_To	Numeric
*	Test_Type	Text(15)	For example Rising Head, Falling Head, Pump Test.
*	Test_Date	DateTime
*	Result_Type	Text(20)	For example, estimate, initial, final.
	Conducted_By	Text(100)	Who conducted the test.
	Result	Numeric
	Result_Units	Text(15)
	Interpretation_Method	Text(50)	Method used to obtain the iterpretation.
	Interpreted_By	Text(100)
	Comments	Text(255)

Borehole Advancement

Stores the advancement information when drilling a borehole.

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Depth_From	Numeric
	Depth_To	Numeric
	Drilling_Method	Text(60)
	Diameter	Numeric
	Diameter_Units	Text(10)
	Start_Date	DateTime
	End_Date	DateTime
	Logged_By	Text(255)
	Comments	Text(255)

Borehole Backfill

Backfill in the borehole, such as grout, filter pack.

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Depth_From	Numeric
	Depth_To	Numeric
	Backfill_Code	Text(15)	A code to describe the backfill, from zRef_Backfill_Codes
	Backfill_Description	Text(255)	Free text description of the backfill

Borehole Depth Comments

Comments related to a particular depth in the borehole

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Type	Text(20)
*	Depth_From	Numeric
	Depth_To	Numeric
	Comment	Text(510)
	Date_Time	DateTime

Borehole Penetration Resistance

Resistance in drilling

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Depth_From	Numeric
	Depth_To	Numeric
	Resistance	Text(30)
	Comment	Text(255)
	Upper_Boundary_Transition	Text(40)

Borehole SPT

Standard Penetration Test Data

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Depth	Numeric
	Method	Text(20)
	Penetration_Units	Text(4)
	Seating_Blows	Integer
	Seating_Penetration	Integer
	Blows1	Integer
	Penetration1	Numeric
	Blows2	Integer
	Penetration2	Numeric
	Blows3	Integer
	Penetration3	Numeric
	Blows4	Integer
	Penetration4	Numeric
	Sampler_Correction	Numeric
	SPT_N	Text(30)
	Comments	Text(255)

Borehole Waterstrikes

Waterstrike and recovery when drilling

PK	Column	Type	Description
*	SID	Integer	References Sites.ID - a unique integer for each Site.
	PID	Integer	References Projects.ID - a unique integer for each project.
*	Location_Code	Text(20)	References a Location in the Locations Table.
*	Depth	Numeric
	Date_Time	DateTime
	Minutes_Elapsed	Numeric
	Recovered_Water_Depth	Numeric
	Comments	Text(255)