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

[This article was first published on DataScience+, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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

Related Post

  1. Visualizing Streaming Data And Alert Notification with Shiny
  2. Metro Systems Over Time: Part 3
  3. Metro Systems Over Time: Part 2
  4. Metro Systems Over Time: Part 1
  5. Outlier App: An Interactive Visualization of Outlier Algorithms

To leave a comment for the author, please follow the link and comment on their blog: DataScience+.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Never miss an update!
Subscribe to R-bloggers to receive
e-mails with the latest R posts.
(You will not see this message again.)

Click here to close (This popup will not appear again)