State-wide location display of selected maximum downhole geochemistry

State-wide location display of selected maximum downhole geochemistry

Required Elements
Ag, Au, Co, Cu, Fe3O4, Fe2O3, FeO, Fe, Li, Ni, Pb, U, U3O8, Zn
The SARIG maximum downhole geochemistry data package is an extract from SA Geodata, originally sourced from the Department for Energy and Mining, its predecessors,...

Required Elements
Ag, Au, Co, Cu, Fe3O4, Fe2O3, FeO, Fe, Li, Ni, Pb, U, U3O8, Zn

The SARIG maximum downhole geochemistry data package is an extract from SA Geodata, originally sourced from the Department for Energy and Mining, its predecessors, and from various mining/mineral exploration companies. Geochemistry values held in SA Geodata are collected from drill core and rock sample analysis, but only down hole data is used in the derived product (i.e. no surface samples)
The process to extract and transform measured single element data to maximum downhole geochemistry data from SA Geodata is summarised below.

Required Columns
From the drill hole chem data tables in SA Geodata the following columns are required: SAMPLE_NO, DRILLHOLE_NO, DRILLHOLE_NAME, DH_DEPTH_FROM, DH_DEPTH_TO, CHEM_CODE, CHEM_VALUE_TIMES_CRUSTAL, CHEM_VALUE, CHEM_UNIT, ANALYSIS_METHOD, LATITUDE_GDA2020, LONGITUDE_GDA2020, EASTING_GDA2020, NORTHING_GDA2020, ZONE_GDA2020

Selection Method
For the above table columns:
-SELECT (SAMPLE_NO, DRILLHOLE_NO, CHEM_CODE, CHEM_UNIT, CHEM_VALUE) where DRILLHOLE_NO is not null, CHEM_CODE = 'Ag', 'Au', 'Co', 'Cu', 'Fe', 'FeO', 'Fe2O3', 'Fe3O4','Li', 'Ni', 'Pb', 'U', 'U3O8', 'Zn', CHEM_UNIT is not in ('cps' 'NOINIT', 'us/cm' 'X') and CHEM_METHOD is not ('BLEG', 'FA','FAS1', 'FAS1?', 'FAS2', 'FIRE', 'FAS4', 'AqReg/ C', 'ANA FAS1', 'ANA FAS', '1', 'GLS FAS', 'PAR1', 'P-XRF', 'XRFInnovX', 'XRFNiton', 'UKN', 'ES3', 'ES1', 'ES4', 'ES2', 'ES6', 'ME-SCRPH22', 'LW500', 'MET5B', 'MET1M', 'MET2A', 'ORE5/5', 'O1','R3/3', 'ARM40', 'CMB', '100', '207','2I', '402','A1', 'A1/1.2', 'A2','A7/1', 'A7/2', 'A7/3', 'AA','C1', 'C3', 'C3/3', 'CHEC', 'CHEM', 'CIP', 'H1', 'H3', 'I2', 'I3', 'IC',  'IC58', 'ICP', 'ICP', 'ICP/WCM', 'ICP1', 'ICP2', 'ICP5', 'ICP8', 'ICPL', 'LOI', 'MS', 'MS53', 'MSID', 'O', 'PAD', 'PM', 'PM21', 'QEM', 'RF1', 'SPEC', 'SPECT', 'V', 'VAP/HYD', 'X1', 'D3(a)', 'EMISSPECT', 'Scan/201', 'C2', 'C1/C2', 'Electrode', 'CRUCIBLE', 'SUBLIM', 'D2(a)', 'D5/50', 'K4/1', 'E4', 'D5/20', 'D5720', 'A6/3', 'ROC', 'A1/1', 'SPEC.', 'B1', 'C3/1' ,'DITHIOL', 'Scan', 'Dithiol', 'Gallein', 'Spectro.', 'A1/1, 2',  'A1/1,2', 'X3', 'A2/4', 'ALS ?', 'A1/2,A2/2', 'AMDEL C1/2', 'ALS CODE 1', 'ALS CODE 2', 'ALS CODE5B', 'ALS CODE 8', 'COM ?', 'ACS ?', 'SPECTRO', 'AMD I1 ICP', 'AMD CODEB1', 'AMD ?', 'MASSPEC', 'SRS', 'GS201', 'SPECSCAN', 'ACSA ?', 'AMD A1/1', 'AMD C1', 'AMD C1/C2', 'AMD ICP1', 'AMD C3/3', 'AMD J3/2', 'AMD K4/2', 'AMD SQSCAN', 'AMD X1', 'SEL ICP1', 'SEL SP1', 'SPEC SCAN', 'GI211', 'TBE', 'ANA UNSPEC', 'ORE2/1', 'ICP3', 'CALC', 'GLS B/HYD', 'AMD ICP2', 'COM FAS1', 'COM ROC1', 'MET7A', '"A1/1,2"', 'E1052', 'V988', 'EVAP', 'FIA', 'FICR', 'FIS', 'GCMS', 'Labmeter', 'Ncal', 'PO', 'TitrA', 'TOCAA', 'IND6F', 'AG4', 'EC', 'FP', 'PLA', 'RAD', 'UCalGam', 'UCalPfn', 'LST', 'EXPL 110', 'EXPLGR135G', 'METHOD 1', 'METHOD 3', 'ARN-10', 'METHOD 2', 'ARSTEP', 'U5', 'AR101', 'AR002', 'AES', 'IC3M', 'IC3R')

Transformations
Laboratory results are commonly presented in oxide %, ppb, ppm, g/T, u/L and mg/L.
All oxides (e.g. Fe3O4, Fe2O3, FeO, U3O8) need to be converted to elemental values using a factor value:
-FeO [VALUE] / 1.287
-Fe2O3 [VALUE] / 1.430
-Fe3O4 [VALUE] / 1.382
-U3O8 [VALUE] / 1.179
The factor value is obtained by [molecular weight of oxide] / [molecular weight of element].
For example, Fe2O3 has a molecular weight of [159.687] / [ 2 x Fe 55.845] = 1.430, which is the factor listed above.
Then using a hypothetical measured lab value for Fe2O3 of 1.25wt% for example, Fe elemental value = [1.25] / 1.430 = 0.87

All elemental values then need to be converted to ppm. With the CHEM_Code = 'Ag', 'Au', 'Co', 'Cu', 'Fe', 'Li', 'Ni', 'Pb', 'U', 'Zn', select (CHEM_UNIT)
-When '%' then [CHEM_VALUE] * 10000 - value is elemental Fe or U value as above
-When 'ppb' then [CHEM_VALUE] / 1000
-When 'g/T' then [CHEM_VALUE] - no conversion necessary
-When 'ug/L' then [CHEM_VALUE] / 1000
-When 'mg/L' then [CHEM_VALUE] - no conversion necessary
For example, using calculated Fe value as previously outlined above [0.87] * 10000 = 8700 ppm

All ppm values are then normalised to times_average_crustal_abundance [CHEM_VALUE_TIMES_CRUSTAL], which is taken from the following reference:
Rudnick, R.L. and Gao, S. (2014). 4.1 - Composition of the continental crust. Treatise on Geochemistry (Second Edition), Vol. 4, p. 1-51. https://doi.org/10.1016/B978-0-08-095975-7.00301-6
-Ag [VALUE] / 0.056
-Au [VALUE] / 0.0013
-Co [VALUE] / 26.6
-Cu [VALUE] / 27
-Fe [VALUE] / 67100
-Li [VALUE] / 16
-Ni [VALUE] / 59
-Pb [VALUE] / 11
-U [VALUE] / 1.3
-Zn [VALUE] / 72
For example, using calculated Fe value 8700 ppm as above, normalisation is [8700] / 67100 = 0.13
This calculated value can now be used in the max value selection criteria.

Max Value Selection Criteria
Once all the units are converted and normalised to times_average_crustal_abundance, the max value for each drill hole is selected. 
This can be achieved using groupby [DRILLHOLE_NO] and then select Max VALUE or Max times_average_crustal_abundance.
# CHEM_VALUE cutoff values have been applied to element ppm data for optimal display and usage in SARIG. These limits are:
CHEM_CODE_NORM = 'Ag' and CHEM_VALUE_NORM >= 0.05
CHEM_CODE_NORM = 'Au' and CHEM_VALUE_NORM >= 0.001
CHEM_CODE_NORM = 'Co' and CHEM_VALUE_NORM >= 0.1
CHEM_CODE_NORM = 'Cu' and CHEM_VALUE_NORM >= 1
CHEM_CODE_NORM = 'Fe' and CHEM_VALUE_NORM >= 1000
CHEM_CODE_NORM = 'Li' and CHEM_VALUE_NORM >= 1
CHEM_CODE_NORM = 'Ni' and CHEM_VALUE_NORM >= 1
CHEM_CODE_NORM = 'Pb' and CHEM_VALUE_NORM >= 5
CHEM_CODE_NORM = 'U' and CHEM_VALUE_NORM >= 0.05
CHEM_CODE_NORM = 'Zn' and CHEM_VALUE_NORM >= 1

More +