1

I am not a database administrator, but only a scientist who would appreciate your help in solving my issue with storing histogram data in an SQLite database.

I have several of them to be stored and to be later analysed with pandas (python).

Each histogram is made by two arrays,

  1. one for the bins or buckets that are regularly spaced, let's say from min to max with a given step.
  2. one for the values.

First question: how would you store the two arrays? They are rather long, up to 65k. I don't need to store the bin values, I can in principle recalculate them having the min, max and step. The value array may have several zeros, so it may be convenient to store them sparsely.

Second question: I would like to retrieve them with a select returning something like:

bin1, value1
bin2, value2
...
binN, valueN

Sorry if my questions is looking too stupid to you, but I'm scratching my head with this problem since too long without finding any way out.

Thanks in advance for your help!

Update

As a preliminary, not really disk space effective solution, I have implemented something like the suggestion of @Whitel Owl. Instead of storing the two arrays as text, I'm storing them as binary BLOBs.

HEre is my code:

CREATE TABLE HistogramTable (
  HistogramID as INTEGER PRIMARY KEY,
  ImageID as INTEGER,
  Bins as BLOB,
  Histo as BLOB,
  FOREIGN KEY ImageID REFERENCE ImageTable(ImageID)
 );

To get the two blobs I'm using pickle.

import pickle
import sqlite3
import numpy as np

db.connect('mydb.db')

histo, bins = np.histogram(data)

histo_blob = sqlite3.Binary(pickle.dumps(histo))
bins_blob = sqlite3.Binary(pickle.dumps(bins))
3
  • Instead of describing the histogram object, could you please show us its definition in code (e.g. as a class with properties and child classes)?
    – J.D.
    Jun 29, 2023 at 12:39
  • the histogram is generated by numpy.histogram. It returns two ndarrays. one for the bins and one for the histogram values.
    – user41796
    Jun 29, 2023 at 19:30
  • What you implemented doesn't seem to solve your second question, as you don't have the ability to query the data as (bin1, value1) etc. If you don't need to query the data at all, then what you did is fine and works (though blob columns should generally be avoided) but if you need to query the data of those arrays, then you should normalize them into a table.
    – J.D.
    Jun 29, 2023 at 20:54

1 Answer 1

0

If you are ready to retrieve them as a list of bins and values - just store them in the same way.

You can also add (should add) an identifier for which histogram you are storing. You can also keep each one in a separate table, but that would be very inconvenient to automate later.

So I would go with a single table:

create table data_storage(
   data_id text,
   bin text,
   value real
)

select bin, value
from data_storage
where data_id = "Measurements from ABC"

And if you can restore set of bins from min-max-step - you do not need to worry about storing bins at all. If, on the other hand, it is not possible, but you can actually have an empty bin, you can make a second table for bins:

create table bins (
   data_id text,
   bin text
)

In this case, to learn that the bin is empty (or how many values it has) you can do:

select b.data_id, b.bin, count(*)
from bins b
left join data_storage ds on b.data_id=ds.data_id and b.bin=ds.bin
1
  • Thanks for your suggestion. I have implemented a similar solution but in binary, as you can see in the edited question.
    – user41796
    Jun 29, 2023 at 19:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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