Plot images directly from SQLite BLOB

Author

Affiliation

Roberto Scotti (scotti@uniss.it)

NuoroForestrySchool-DipAGR-UniSS.it

Intro

Having loaded the images in the SQLite DB file, here is a demonstration of how to display them without saving to a file, just reading directly from the SQLite Blob field

library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.1.2     ✔ readr     2.1.4
✔ forcats   1.0.0     ✔ stringr   1.5.0
✔ ggplot2   3.4.2     ✔ tibble    3.2.1
✔ lubridate 1.9.2     ✔ tidyr     1.3.0
✔ purrr     1.0.1     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

library(DBI)

DBfile <- "OUTPUT/2018_SoilErosionTrials_Marganai_Sardegna.sqlite"
db <- dbConnect(RSQLite::SQLite(), DBfile)
Images <- dbReadTable(db, "Images")

plot_img <- function(source, add=FALSE) {
  if (!exists("rasterImage")) # 
    stop("can plot only in R 2.11.0 and higher")
  else {
    # Define magic numbers for image formats
    jpeg_magic <- as.raw(c(0xFF, 0xD8, 0xFF))
    png_magic <- as.raw(c(0x89, 0x50, 0x4E, 0x47, 0x0D, 0x0A, 0x1A, 0x0A))
    # Check if the image bytes match any of the magic numbers
    if (identical(source[1:3], jpeg_magic)) {
      print("Image format: JPEG")
      require('jpeg')
      img <- readJPEG(source, native=T)
    } else if (identical(source[1:8], png_magic)) {
      print("Image format: PNG")
      require('png')
      img <- readPNG(source, native=T)
    } else {
      if(exists("img")) rm(img)
    }
    if(!exists("img")){
      print("Unknown image format")
    } else {
      # read the file
      res <- dim(img)[2:1] # get the resolution, [x, y]
      if (!add) # initialize an empty plot area if add==FALSE
        plot(1,1,xlim=c(1,res[1]),ylim=c(1,res[2]),asp=1,type='n',
             xaxs='i',yaxs='i',xaxt='n',yaxt='n',xlab='',ylab='',bty='n')
      interpolate <- TRUE
      if (names(dev.cur()) == "windows") {
        # windows device doesn't support semi-transparency so we'll need
        # to flatten the image
        transparent <- img[,,4] == 0
        img <- as.raster(img[,,1:3])
        img[transparent] <- NA
        
        # interpolate must be FALSE on Windows, otherwise R will
        # try to interpolate transparency and fail
        interpolate <- FALSE
      }
      rasterImage(img,1,1,res[1],res[2], interpolate)
    }
  }
}

plot_img(Images$Blob[[1]])

[1] "Image format: JPEG"

Loading required package: jpeg

plot_img(Images$Blob[[5]])

[1] "Image format: PNG"

Loading required package: png