Using PostgreSQL and shiny with a dynamic leaflet map: monitoring trash cans

When there is increased social activity, trash cans can get full quicker. On the contrary, during very cold weather, trash cans can take one or a couple of more days to get full. Therefore, knowing when trash cans are full is important to pick them up right away rather than waiting for a specific day of the week to come. In this tutorial, we will see how to integrate PostgreSQL, Shiny and leaflet to create dynamic map of trash can conditions based on streaming data. In the end of this post you will find a video tutorial.

PostgreSQL database

The code below generates streaming data and inserts it to a local PostgreSQL database. The host can also be a remote server.

library(plot3D)
library(RPostgreSQL)

# Open up a database connection.

 pg = dbDriver("PostgreSQL")

 con = dbConnect(pg, user = 'postgres', password = "postgrestutorial",
                 host="localhost", port=5432, dbname="")

 ## Let's generate latitude and longitude locations in Alexandira, VA
 
lats=seq(from=38.80,to=38.8148,by=0.005)
lons=seq(from=-77.06,to=-77.04,by=0.005)
latlons=mesh(lons,lats)
longitude=as.vector(latlons$x)
latitude=as.vector(latlons$y)

sensorID=paste0("SN",10001:10015)  # senorIDS that help to identify trash cans
        

# Insert data to the database
# All 15 sensors will send data every 2 seconds for 20 seconds
# Generating random data in increasing order: ratio of volume full


i=0
status=matrix(0,ncol=15,nrow=1)
while(i=1]=1
        
        sensordata=data.frame(timestamp=Sys.time(),
                              sensorID=sensorID,
                              longitude=longitude,
                              latitude=latitude,
                              status=status,
                              stringsAsFactors = FALSE
                             )
        dbWriteTable(con,'trashcan_sensor_data',sensordata, row.names=FALSE,append=TRUE)
        

i=i+1
Sys.sleep(2)
}


# Insert data to the database
# Let's assume two sensors are not sending data
# 13 sensors will send data every 2 seconds for 20 seconds

i=0

while(i=1]=1
        
        sensordata=data.frame(timestamp=Sys.time(),
                              sensorID=sensorID,
                              longitude=longitude,
                              latitude=latitude,
                              status=status,
                              stringsAsFactors = FALSE
        )
        dbWriteTable(con,'trashcan_sensor_data',sensordata, row.names=FALSE,append=TRUE)
        
        
        i=i+1
        Sys.sleep(2)
}


# Insert data to the database
# Now, let's assume five sensors are not sending data
# 10 sensors will send data every 2 seconds for 20 seconds

## Assume the gateway will fail fater 20+20+20 seconds and hence all
## sensors will not send data at all


i=0

while(i=1]=1
        
        sensordata=data.frame(timestamp=Sys.time(),
                              sensorID=sensorID,
                              longitude=longitude,
                              latitude=latitude,
                              status=status,
                              stringsAsFactors = FALSE
        )
        dbWriteTable(con,'trashcan_sensor_data',sensordata, row.names=FALSE,append=TRUE)
        
        
        i=i+1
        Sys.sleep(2)
        
}

server.R

The code blow is the server.R part of the shiny app.

library(shiny)
library(leaflet)
library(plotly)
library(dplyr)

trashicons=function(condition){  # a function to make our own icons based on the sensor data
        makeIcon(
                iconUrl =paste0("trashcan_",condition,".png"),
                iconWidth =40, 
                iconHeight =45,
                iconAnchorX = 0, iconAnchorY = 0
        )}


localdb % # connect to tables within that database
        collect() # get the data

# get the most recent row for each sensor
data_at_start=arrange(data_at_start,-row_number())
data_at_start=distinct(data_at_start,sensorID,.keep_all = TRUE)

# If the cans are 95% full, change the icons to warning icon
# If a sensor for any can is not sending data, change the icon to failed icon
# If it has been more than 10 seconds since any sensor sent data, change the icon to failed
data_at_start$condition=ifelse(data_at_start$status>0.95,"warning","ok")
data_at_start$condition[is.na(data_at_start$status)]="failed"
data_at_start$condition[is.null(data_at_start$status)]="failed"
data_at_start$condition=ifelse((data_at_start$timestamp+10)< Sys.time(),"failed",data_at_start$condition)


testfunction %collect()
        df$max
}

ReadAllSensorData =function(){ # A function that gets data from the database
                               # based on the testfunction above
        query="SELECT * FROM trashcan_sensor_data"
        temp=tbl(localdb,sql(query))%>%collect(n = Inf)
        temp
}


shinyServer(function(input, output,session) {
        
        sensorData <- reactivePoll(100, session,testfunction, ReadAllSensorData)    
              # 100: number of milliseconds to wait between calls to testfunction

  
      output$leaflet_map %fitBounds(right,bottom,left,top)%>%
                   addTiles()%>%
                  addMarkers(data=dat,
                             lng= ~ longitude,
                             lat= ~ latitude,
                             icon = ~trashicons(dat$condition),
                             label=~as.character(sensorID),
                             labelOptions = labelOptions(textOnly = T,noHide =FALSE)
                  )
     })
 
    last_data=reactive({ # every time there is new data, get the last row for each sensorID
                         # If the cans are 95% full, change the icons to warning icon
                         # If a sensor for any can is not sending data, change the icon to failed icon
                         # If it has been more than 10 seconds since any sensor sent data, change the icon to failed
          dat=sensorData()
          dat=arrange(dat,-row_number())
          dat=distinct(dat,sensorID,.keep_all = TRUE)
          dat$condition=ifelse(dat$status>0.95,"warning","ok")
          dat$condition[is.na(dat$status)]="failed"
          dat$condition[is.null(dat$status)]="failed"
          dat$condition=ifelse((dat$timestamp+10)< Sys.time(),"failed",dat$condition)
          dat
  })
  
isdata_still_coming Sys.time() # return false if no data came in the last 10 seconds
})

 condition_observer=reactiveValues(condition=data_at_start$condition)
 
  # use leafletProxy to manage the dynamic icons: change icon color based on the data
  observe({
           dat=last_data()
            if(isdata_still_coming()==FALSE){
                  leafletProxy("leaflet_map", data = last_data()) %>%
                          clearMarkers() %>%
                          addMarkers(data=last_data(),
                                     lng= ~ longitude,
                                     lat= ~ latitude,
                                     icon = ~trashicons("failed"),
                                     label=~as.character(sensorID),
                                     labelOptions = labelOptions(textOnly = T)
                          )
                  condition_observer$condition="failed"
                  }else if(any(condition_observer$condition!=dat$condition)){
                  leafletProxy("leaflet_map", data = dat) %>%
                          clearMarkers() %>%
                          addMarkers(data=dat,
                                     lng= ~ longitude,
                                     lat= ~ latitude,
                                     icon = ~trashicons(dat$condition),
                                     label=~as.character(sensorID),
                                     labelOptions = labelOptions(textOnly = T)
                          )
                          condition_observer$condition=dat$condition
                  }
  })

  # Time series plot:helps us to verify that the icons are changing based on conditions we provided
  output$plotly_timeseries <- renderPlotly({
          dat= sensorData()
         
          z=ggplot(dat,aes(x=timestamp,y=status*100,color=sensorID))+geom_line()+
                  geom_hline(yintercept = 95,linetype="dotted",color="red")+ylab("Status")+
                  ylim(0,110)+xlab("")+ggtitle("Trash Can Status")+ylab("Percet Full")+
                  theme(axis.title.y = element_text(colour="blue",size=14),
                        axis.text = element_text(colour="darkred",size=12),
                        plot.title = element_text(colour="darkgreen",size=16,hjust=0.5))
          
          ggplotly(z)
  })

})

ui.R

The code below is the ui.R part of the shiny app.

library(shiny)
library(leaflet)
library(plotly)

shinyUI(fluidPage(
        tags$h3("Using PostgreSQL and shiny with a dynamic leaflet map: monitoring trash cans",
                style="text-align:center;color:#990099"),
        
     column(width=6,
      leafletOutput("leaflet_map",height = "680px")
     ),
     column(width=6,
            plotlyOutput("plotly_timeseries",height = "680px"))
))

Code is on Github