What's a good way store borehole data in SQLite / SpatiaLite?

Question

I have a bunch of bores / wells with downhole data. The 'header' info for each borehole can easily go into a single table, but I'm wondering how best to store the downhole log data. Typically they are stored in spreadsheets as semi-continuous depth-value type data: e.g.: depth,temperature ...values...

or there may be more than one value for each depth: depth,temperature,gamma,neutron,... ...values...

I want to be able to easily pull them out to plot (e.g. XY scatterplots) in other software. Can I store entire text files in some sort of field?

Answer 1

Personally, I would approach this from a database perspective rather than either a GIS perspective or a spreadsheet perspective.

I would have one table of boreholes. This would contain the fields of BoreholeId, and Geometry. Records would obviously be the values.

I would then have another table of BoreholeData. This would contain the fields of BoreholeId, depth, temperature, etc. The BoreholeId would match the record in the Boreholes table, then the values would be all the readings for each depth. You could have a run field as well, so that you could differentiate the different values for the different runs.

In the end, you would get something like:

borehole table

+------------+------------+
| borehole_id |  geometry  |
+------------+------------+
|          1 | POINT(1 1) |
|          2 | POINT(1 2) |
|          3 | POINT(1 3) |
+------------+------------+

borehole_data Table

+------------+-------+-----+-------+-------------+
| borehole_id| depth | run | gamma | temperature |  
+------------+-------+-----+-------+-------------+
|          1 | 10    |   1 | 0.01  |          42 |  
|          1 | 10.5  |   1 | 0.02  |          43 |  
|          1 | 11    |   1 | 0.1   |          46 |  
|          2 | 10    |   1 | 0.05  |          42 |  
|          2 | 10.5  |   1 | 0.03  |          44 | 
|          2 | 11    |   1 | 0.3   |          47 |  
+------------+-------+-----+-------+-------------+

Now you can use SQL to get a number of different tables of results, based on what you want to return to whatever you are graphing this data in. I am pretty sure R would be able to connect directly to Sqlite, but I'm not positive on this.

You could also create views of the data from queries, so you don't have to manually run a query, or manipulate data to get what you are after each time, you just pump in the data, and select from the view and you have your data all formatted exactly as you wish. These views could also be used to format the data so you can view it in the GIS of your wishes (as long as it supports database views).

I'm not sure what you would want to graph, but say you wanted a graph of location, depth and temperature, you could run the following query to generate the data:

select X( boreholes.geometry) as x, Y( boreholes.geometry) as y,borehole_data.depth as z, borehole_data.temperature  from boreholes
INNER JOIN borehole_data on boreholes.borehole_id = borehole_data.borehole_id

Or to create a view (so the query is stored and can be run easily)

create view vwborehole_temperatures as
    select X( boreholes.geometry) as x, Y( boreholes.geometry) as y,borehole_data.depth as z, borehole_data.temperature  from boreholes
    INNER JOIN borehole_data on boreholes.borehole_id = borehole_data.borehole_id

Which would output (based on my input above)

+---+---+------+-------------+
| x | y |  z   | temperature | 
+---+---+------+-------------+
| 1 | 1 | 10   |          42 |  
| 1 | 1 | 10.5 |          43 |  
| 1 | 1 | 11   |          46 |  
| 1 | 2 | 10   |          42 |  
| 1 | 2 | 10.5 |          44 |  
| 1 | 2 | 11   |          47 |  
+---+---+------+-------------+

This doesn't just apply to Sqlite3/ Spatialite, it could just as easily be Postgres with some slight modifications to the SQL.

Note: All Sql untested

Answer 2

I kind of do the same.

I store all my hydrogeologic data in a SpatiaLite database. One table with general data (and geometry) for each borehole and then additional tables related to each borehole. I handle both borehole logs (non-transient data as e.g. stratigraphy) and time-varying observations (as groundwater level, water chemistry and more).

I also made a QGIS plugin for plotting with python/matplotlib. Here you find more info about plugin usage and data structure: http://sites.google.com/site/midvattenpluginforqgis/