Accessing NZGD data in our SQLite database
Introduction
Geotechnical investigations, such as Cone Penetration Tests (CPTs), involve measuring soil properties at different depths. This data can be conveniently organized and stored in a spreadsheet using programs like Microsoft Excel or LibreOffice Calc. An example of a spreadsheet containing CPT data is shown in the following figure.
The New Zealand Geotechnical Database (NZGD) contains data from approximately 50 thousand geotechnical investigations, which can be thought of as 50 thousand spreadsheets. We extracted the useful information from these spreadsheets and compiled it into an SQLite database, as such databases enable highly efficient data retrieval. An SQLite database is a single .db file that can contain many data tables. So you can think of our SQLite database as being like a regular computer folder that contains many spreadsheet files in its tables.
Viewing the database
There are several computer programs that make it easy to view SQLite databases. A convenient and free option is DB Browser for SQLite. With this program, we can open the database by clicking File → Open Database as shown in the following figure. Alternatively, to access this data with Python, please refer to our GitHub repository.
Opening our NZGD database shows a list of its tables in the "Database Structure" tab. These tables contain the measured data and other metadata.
Expanding the cptmeasurements table shows that this table has five columns: measurement_id, cpt_id, depth, qc, fs, and u2.
To see the actual data in the cptmeasurements table, click the "Browse Data" tab and select the cptmeasurements table in the drop-down menu
This single table contains all CPT measurements from all investigations. However, every investigation is assigned a unique integer ID, so we can easily focus on a single investigation. For example, to only show CPT Number 1, enter "=1" in the filter box of the cpt_id column
The data for CPT Number 1 can then be exported as a CSV file by clicking the button showing a floppy disk and selecting "Export to CSV". This method works for a few investigations, but if you need data from a large number of investigations, it will be much faster to use our Python tools.
Every NZGD record has extensive metadata that can also be accessed. To find the metadata for CPT Number 1, we must first find its NZGD ID. CPT investigations have their own ID number in our database, as some NZGD records have several associated CPT investigations. To find the NZGD ID of CPT Number 1, change to the cptreport table and enter "=1" in the cpt_id column's filter box. This shows that CPT ID = 1 corresponds to NZGD ID = 2.
To see the metadata for NZGD ID = 2, change to the nzgdrecord table and enter "=2" in the filter box for nzgd_id column.