[OSGeo-Discuss] Raster data on RDBMS

[Lucena, Ivan]

Hi There,

I would like to return to a discussion that we had months ago about raster on RDBMS. But this time I would like to present some number.

As long as I could recall there was basically two major arguments contrary to storing raster on RDBMS. One very pragmatical: "Why waste precious process time with the overhead of dealing with queries, tables, client-sever back and forth just to get the data from BLOB fields on a database when you can get it directly from the file system?". The other argument was semantical: "Why store raster on RDBMS if in general we are not expecting to have a transactions on that data?"

I cannot argue against the second one. I basically agreed with that but after seeing how fragile and complicated even a well defined structure of folders and files could be I would vote in favor of the good and old relational model.

That is my experiment. I downloaded two free data samples from Naveteq website. Two geotiff files with the same size and number of bands (14336, 14336,  3):

ilucena at think:~/Data> du -k Barcelona_2007_R2C2.TIF
602828	Barcelona_2007_R2C2.TIF
ilucena at think:~/Data> du -k San_Francisco_2006_R1C2.TIF
602828	San_Francisco_2006_R1C2.TIF

Then I loaded those images to Oracle Spatial GeoRaster using GDAL. The loading process is comparable than some commercial ETL products on the market. It took about 2 minutes to load each image.

ilucena at think:~/Data> time gdal_translate -of georaster Barcelona_2007_R2C2.TIF georaster:scott,tiger,orcl,RDT_2$,2
Input file size is 14336, 14336
0...10...20...30...40...50...60...70...80...90...100 - done.
Ouput dataset: (georaster:scott,tiger,orcl,RDT_2$,2) on GDAL_IMPORT,RASTER
real  1m54.973s
user 0m4.368s
sys   0m1.936s

If you are a Oracle GeoRaster users you might be excited about those number already but those are not the numbers I want to show. What I would like to do is to compare the time that it takes to extract subset from the original geotiff and compare with the time to extract the same subset from the RDBMS. He are the numbers:

ilucena at think:~/Data> time gdal_translate georaster:scott,tiger,orcl,RDT_2$,2 out.tif -srcwin 0 0 2000 2000
Input file size is 14336, 14336
0...10...20...30...40...50...60...70...80...90...100 - done.
real	  0m0.720s
user 0m0.408s
sys   0m0.108s

ilucena at think:~/Data> time gdal_translate Barcelona_2007_R2C2.TIF out2.tif -srcwin 0 0 2000 2000
Input file size is 14336, 14336
0...10...20...30...40...50...60...70...80...90...100 - done.
real	  0m1.177s
user 0m0.976s
sys	  0m0.188s

And I also checked the integrity of the results to see if I get the same result:

ilucena at think:~/Data> gdalinfo -checksum out.tif
...
Band 1 Block=2000x1 Type=Byte, ColorInterp=Red
  Checksum=58248
Band 2 Block=2000x1 Type=Byte, ColorInterp=Green
  Checksum=21226
Band 3 Block=2000x1 Type=Byte, ColorInterp=Blue
  Checksum=8002

ilucena at think:~/Data> gdalinfo -checksum out2.tif
...
Band 1 Block=2000x1 Type=Byte, ColorInterp=Red
  Checksum=58248
Band 2 Block=2000x1 Type=Byte, ColorInterp=Green
  Checksum=21226
Band 3 Block=2000x1 Type=Byte, ColorInterp=Blue
  Checksum=8002

What are others test would be interesting to perform?

Best regards,

Ivan