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?

  • When you way there may be more than one value at each depth do mean at say depth 20m, in Borehole 1, there could be multiple temperature, gamma, neutron readings? If so how are these distinguished seperately? Is there a time gap between taking readings? – Kelso Mar 18 '12 at 22:11
  • No, sorry for poor clarity. At any one depth there may be one or more measurement, but only one of each measurement type. So I'll never deal with duplicate gamma values at one depth, say. In reality that happens, but we create a new log for it, e.g. gamma_run1, gamma_run2, and note the difference in the metadata. – a different ben Mar 18 '12 at 23:15

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

  • I was aiming for a database-centric solution, so thanks for this. I use python/matplotlib for plotting, and there are some libraries to deal with data in and out of SQLite. Just curious - did you hand-type those tables? – a different ben Mar 20 '12 at 4:28
  • The tables are kind of hand typed. I used sensefulsolutions.com/2010/10/format-text-as-table.html to generate the pretty text, then pasted that into code markup. The values are all dummy, no idea what they should be. – Kelso Mar 20 '12 at 21:51

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.