I'm a beginner with both database design and use of forums such as this one so forgive me if I forget to provide some relevant information below.

Basically I'm trying to store a set of arrays, in this case raster images, of 2500*2500 in a database in order to extract timeseries for individual pixels. I currently have roughly 1500 images and every day an image is added.

The original raw files of bytetype are only small, but my database table grows very quickly in the following form:

     pixelnr, date, rastervalue 
     PK clustered (pixelnr,date)

I understand that this is because I need a pixelnr and date for every pixelvalue. Unfortunately I don't know how else to store the data in a database.

I've tried to optimize the table for select statements of timeseries per pixel with a clustered PK on pixelnr,date. The drawback is that inserts of new images take an increasingly long time.

There are only two things I do with this table: - bulk insert from a csv file. I convert a new image to a CSV file with the same form as the table. It will have all pixels, but only for a single date.

  • run simple select queries to derive time series for a number of pixels, usually up to 25000 pixels:

    SELECT Pix,Date,Val
    FROM FullTable INNER JOIN PixelSubset ....

I now have two questions:

1) Is there a more efficient way to store this kind of data? I've not found much useful on storing arrays in an RDBMS.

2) How to improve the performance of bulk inserts (all pixels for a single date) into the the table while keeping the clustered index optimised as described above.

I use SQL server 2012 developer edition on a PC with 8GB RAM and a RAID system. There's not much room for hardware changes....

Any pointers particularly to more efficient storage of such data in a RMDBS would be greatly appreciated!

  • Actually I have no issues with performance of select queries on this table, my main problem is the amount of space that's used to store the data that i actually need. – Joost vdW Sep 26 '13 at 14:09
  • Are the images always going to be the same size? – Jon Seigel Sep 26 '13 at 14:17
  • Yes they are, essentially the pixelvalues are always values between 0 and 100, stored in a raw file with no header. Where my total folder with all images is around 5GB, the table has grown to around 50GB... – Joost vdW Sep 26 '13 at 14:19
  • 1
    Have you considered a columnar database? – A-K Sep 26 '13 at 14:58
  • 1
    a similar question: dba.stackexchange.com/questions/14218/… – A-K Sep 26 '13 at 15:02

Your Answer

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

Browse other questions tagged or ask your own question.