GSWA MDHDB Full-Pivot Dataset (Nov 2023) Read Me
==================================================

The datasets included in this download are harmonised downhole (dh)and surface(ss) assays derived from the GSWA MDHDB product.  This dataset is from the current edition.

To view the datasets you will need to restore the Microsoft SQL Server (compatible with 2016 onwards) backup file to a database capable of handling large datasets.  Typically the free (Express) version would not be able to restore this .bak file, due to the size of the database.

There is also a Postgres version of this dataset available for download. Note that the SQL examples given below are in SQL Server syntax.


Row Counts
----------
gswa_dh_collar 							=  2,941,652 
Gswa_dh_assay_flatsampleassay 			= 61,046,331
Gswa_ss_assay_flat 						=  8,814,304


Tables
------
Gswa_ss_assay_flat is the surface samples, locations as latitude and longitude, report number, sample types etc, along with approximately 150 element / oxide assays.  This could be loaded directly as a theme in a GIS package.

Gswa_dh_assay_flatSampleAssay is the drill hole assays (approximately 161 elements / oxides) as well as company drill hole, sample and down hole depths.  There is no location data in this table.  To view the samples with location data there will need to be a join made between gswa_dh_collar and Gswa_dh_assay_flatSampleAssay.  Please note that this is not a desurveyed dataset and typically a mining package would be needed to view drill hole traces with assays.

All assays are represented as ppm.


Instructions to join Drilling tables
====================================

To facilitate this join, you should first create appropriate indexes on the collar and dh assay tables:

CREATE NONCLUSTERED INDEX [idx_id] ON [dbo].[gswa_dh_collar]
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [idx_dhgeochemid] ON [dbo].[Gswa_dh_assay_flatSampleAssay]
(
	[DHGeochemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO


To join gswa_dh_collar to Gswa_dh_assay_flatSampleAssay in sql queries:

select * from gswa_dh_collar a inner join Gswa_dh_assay_flatSampleAssay b on a.id = b.DHGeochemid (PLUS your specific query conditions/criteria).

Note: The 'DHGeochemId' column  holds  the Collar Id key for each sample.

The result will be the collar data repeated for every assay row.  You will see your position information for every down hole sample.


Indexes
=======

The database is provided without indexes. We recommend creating suitable indexes on the data to improve query performance. These are
dependent on your specific requirements, but three sample statements are provided below:

anumber on dh_collar:
--------------------
CREATE NONCLUSTERED INDEX [idx-dh-anumber] ON [dbo].[gswa_dh_collar]
(
	[Anumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO


anumber on ss_assay_flat:
------------------------
CREATE NONCLUSTERED INDEX [idx-ss-anumber] ON [dbo].[Gswa_ss_assay_flat]
(
	[Anumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO


anumber+companyholeid on dh_collar:
CREATE NONCLUSTERED INDEX [idx-dh-anumber-companyholeid] ON [dbo].[gswa_dh_collar]
(
	[Anumber] ASC,
	[CompanyHoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO