Here are 3 options in R:
(1) Use the RSQLite package
With the RSQLite package it is pretty easy to read a database table into R, your table name may be different if you changed it within PAMGuard. You would then need to repeat this for every database (or loop / lapply through a vector of databases). You'll also probably need to convert the time columns to POSIXct depending on what you plan to do with this data.
library(RSQLite)
con <- dbConnect('MyDb.sqlite3', drv=SQLite())
# Your table name may be different than "Spectrogram_Annotation"
sa <- dbReadTable(con, 'Spectrogram_Annotation')
dbDisconnect(con)
(2) Use the PAMmisc package
This is actually something I've needed to do fairly often, so I wrote a function that does all this for you! readSpecAnno does all the steps in (1), renames some of the columns to be more descriptive (ie. f1/f2 -> fmin/fmax), and converts date columns to POSIXct as well as adding an end for each row instead of UTC and Duration. The nice part of having this all in a single function is that it is really easy run it on multiple databases and combine the result.
library(PAMmisc)
# Again, your table name may be different
sa <- readSpecAnno('MyDb.sqlite3', table='Spectrogram_Annotation')
# Easy to run on lots and combine the results
library(dplyr)
dbList <- c('MyDb1.sqlite3', 'MyDb2.sqlite3')
allSa <- bind_rows(lapply(dbList, function(x) {
readSpecAnno(x, table='Spectrogram_Annotation')
}))
(3) Use the PAMpal package for even more info
The above options will only give you information contained within the spectrogram annotation table, but nothing related to the whistle contours from the detector. The PAMpal package can read in those whistle contours and give you additional information (min/max frequency, contour slope, and many more!). Using PAMpal is more involved than the other two options, but depending on your use case it can definitely be worth it.
library(PAMpal)
# PAMpal needs your PAMGuard database and binary files
bin <- './Binaries/'
db <- 'MyDb.sqlite3'
# Tell it which files to process, this will trigger a couple popup questions in the console
pps <- PAMpalSettings(db, bin)
# We'll use the spectrogram annotations to tell PAMpal which times we want processed
library(PAMmisc)
sa <- readSpecAnno(db, table='Spectrogram_Annotation')
data <- processPgDetections(pps, mode='time', grouping=sa, id='SpecAnnoWhistles')
Here data will have all detections between the start and end times of the spectrogram annotations, so to remove the harmonics we can use the filter function to get only frequencies within that range
for(i in seq_along(events(data))) {
# get grouping info
thisGroup <- ancillary(data[[i]])$grouping
# Do filtering. Note fmin/max are in Hz, convert where appropriate
data[[i]] <- filter(data[[i]],
freqBeg > thisGroup$fmin,
freqBeg < thisGroup$fmax,
freqEnd > thisGroup$fmin,
freqEnd < thisGroup$fmax,
UTC + duration < thisGroup$end)
}
More information on PAMpal can be found here, and a more detailed write-up with additional comments on specifically using PAMpal with spectrogram annotation tables can be found here.
