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!