docs/articles/splitDBinMonths.R

In volkerschmid/luftdaten: Analysing data from luftdaten.info

library(RSQLite)
library(DBI)
files<-fs::dir_ls("db/orig")
for (f in files)
{
  db<-dbConnect(SQLite(),f)
  dbListTables(db)
  print(f)
  fields<-dbListFields(db, "data")

  locid<-dbReadTable(db,"locid")

  res<-dbSendQuery(db,"SELECT * from data")
  temp<-dbFetch(res,50000)
  
  while(dim(temp)[1]>0)
  {
    time<-strsplit(temp$timestamp,"-")
    time<-matrix(unlist(time),ncol=dim(temp)[1])
    time<-t(time[1:2,])
    ti<-unique(time)
    for (i in 1:dim(ti)[1])
    {
      w<-(time[,1]==ti[i,1])&(time[,2]==ti[i,2])
      temp0<-temp[w,]
      for (field in c("P1","P2","temperature","humidity","pressure"))
      {
        if (any(names(temp0)==field))
        {
          w0<-which(names(temp0)==field)
          db0<-dbConnect(SQLite(),paste0("data/",field,"-",ti[i,1],"-",ti[i,2],".sqlite"))
          temp2<-data.frame("timestamp"=temp0$timestamp, "locid"=temp0$locid,"value"=temp0[,w0])
          dbWriteTable(db0,"data",temp2,append=TRUE)
          locid2<-unique(temp2$locid)
          for (l in locid)
          {
            w0<-which(locid$id==l)
          }
          temp2<-try(dbGetQuery(db0, paste0("SELECT * from locid where id=",l)),silent=TRUE)
          locid0<-locid[w0,]
          if (class(temp2)=="try-error")
          {
            dbWriteTable(db0,"locid",locid0)
          }  
          else
          {
            if (dim(temp2)[1]==0)
            {
              dbWriteTable(db0,"locid",locid0)
            }
          }
          dbDisconnect(db0)
        }
      }
    }
  print(ti)
  temp<-dbFetch(res,10000)
  }  

  if(0)
    {
    res<-dbSendQuery(db,"SELECT * from processed")
    temp<-dbFetch(res,10000)
    while(dim(temp)[1]>0)
    {
      time<-strsplit(temp$file,"-")
      time<-matrix(unlist(time),nrow=3)
      time<-t(time[-3,])
      ti<-unique(time)
      for (i in 1:dim(ti)[1])
      {
        w<-(time[,1]==ti[i,1])&(time[,2]==ti[i,2])
        temp0<-temp[w,]
        db0<-dbConnect(SQLite(),paste0("db/DHT22-",ti[1],"-",ti[2],".sqlite"))
        dbWriteTable(db0,"processed",temp0,append=TRUE)
        dbDisconnect(db0)
      }
      
  temp<-dbFetch(res,10000)
}  

}