<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/using-postgresql-and-shiny-with-a-dynamic-leaflet-map-monitoring-trash-cans/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://datascienceplus.com/using-postgresql-and-shiny-with-a-dynamic-leaflet-map-monitoring-trash-cans/"> DataScience+</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>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.</p>
<h2>PostgreSQL database</h2>
<p>The code below generates streaming data and inserts it to a local PostgreSQL database. The host can also be a remote server.</p>
<pre>
library(plot3D)
library(RPostgreSQL)

# Open up a database connection.

 pg = dbDriver(&quot;PostgreSQL&quot;)

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

 ## 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(&quot;SN&quot;,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)
        
}
</pre>
<h2>server.R</h2>
<p>The code blow is the <strong>server.R</strong> part of the shiny app.</p>
<pre>
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(&quot;trashcan_&quot;,condition,&quot;.png&quot;),
                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,&quot;warning&quot;,&quot;ok&quot;)
data_at_start$condition[is.na(data_at_start$status)]=&quot;failed&quot;
data_at_start$condition[is.null(data_at_start$status)]=&quot;failed&quot;
data_at_start$condition=ifelse((data_at_start$timestamp+10)&lt; Sys.time(),&quot;failed&quot;,data_at_start$condition)


testfunction %collect()
        df$max
}

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


shinyServer(function(input, output,session) {
        
        sensorData &lt;- 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,&quot;warning&quot;,&quot;ok&quot;)
          dat$condition[is.na(dat$status)]=&quot;failed&quot;
          dat$condition[is.null(dat$status)]=&quot;failed&quot;
          dat$condition=ifelse((dat$timestamp+10)&lt; Sys.time(),&quot;failed&quot;,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(&quot;leaflet_map&quot;, data = last_data()) %>%
                          clearMarkers() %>%
                          addMarkers(data=last_data(),
                                     lng= ~ longitude,
                                     lat= ~ latitude,
                                     icon = ~trashicons(&quot;failed&quot;),
                                     label=~as.character(sensorID),
                                     labelOptions = labelOptions(textOnly = T)
                          )
                  condition_observer$condition=&quot;failed&quot;
                  }else if(any(condition_observer$condition!=dat$condition)){
                  leafletProxy(&quot;leaflet_map&quot;, 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 &lt;- renderPlotly({
          dat= sensorData()
         
          z=ggplot(dat,aes(x=timestamp,y=status*100,color=sensorID))+geom_line()+
                  geom_hline(yintercept = 95,linetype=&quot;dotted&quot;,color=&quot;red&quot;)+ylab(&quot;Status&quot;)+
                  ylim(0,110)+xlab(&quot;&quot;)+ggtitle(&quot;Trash Can Status&quot;)+ylab(&quot;Percet Full&quot;)+
                  theme(axis.title.y = element_text(colour=&quot;blue&quot;,size=14),
                        axis.text = element_text(colour=&quot;darkred&quot;,size=12),
                        plot.title = element_text(colour=&quot;darkgreen&quot;,size=16,hjust=0.5))
          
          ggplotly(z)
  })

})
</pre>
<h2>ui.R</h2>
<p>The code below is the <strong>ui.R</strong> part of the shiny app.</p>
<pre>
library(shiny)
library(leaflet)
library(plotly)

shinyUI(fluidPage(
        tags$h3(&quot;Using PostgreSQL and shiny with a dynamic leaflet map: monitoring trash cans&quot;,
                style=&quot;text-align:center;color:#990099&quot;),
        
     column(width=6,
      leafletOutput(&quot;leaflet_map&quot;,height = &quot;680px&quot;)
     ),
     column(width=6,
            plotlyOutput(&quot;plotly_timeseries&quot;,height = &quot;680px&quot;))
))
</pre>
<p>Code is on <a target="_blank" rel="nofollow" href="https://github.com/fissehab/monitoring_trash_cans_with_shiny_and_leaflet">Github</a></p>
<ul>
<li><a target="_blank" rel="nofollow" href="https://www.youtube.com/watch?v=LVL-CP7NDYk">PostgreSQL and shiny with a dynamic leaflet map</a></li>
</ul>
<p><strong>
<p>Related Post</p>
<p>
<ol>
<li><a title="Permanent Link toVisualizing Streaming Data And Alert Notification with Shiny" target="_blank" rel="nofollow" href="https://datascienceplus.com/visualizing-streaming-data-with-shiny/">Visualizing Streaming Data And Alert Notification with Shiny</a></li>
<li><a title="Permanent Link toMetro Systems Over Time: Part 3" target="_blank" rel="nofollow" href="https://datascienceplus.com/metro-systems-over-time-part-3/">Metro Systems Over Time: Part 3</a></li>
<li><a title="Permanent Link toMetro Systems Over Time: Part 2" target="_blank" rel="nofollow" href="https://datascienceplus.com/metro-systems-over-time-part-2/">Metro Systems Over Time: Part 2</a></li>
<li><a title="Permanent Link toMetro Systems Over Time: Part 1" target="_blank" rel="nofollow" href="https://datascienceplus.com/metro-systems-over-time-part-1/">Metro Systems Over Time: Part 1</a></li>
<li><a title="Permanent Link toOutlier App: An Interactive Visualization of Outlier Algorithms" target="_blank" rel="nofollow" href="https://datascienceplus.com/outlier-app-an-interactive-visualization-of-outlier-algorithms/">Outlier App: An Interactive Visualization of Outlier Algorithms</a></li>
</ol></strong>
<div>
<a target="_blank" rel="nofollow" href="http://feeds.feedburner.com/~ff/datascienceplus?a=ouVwZ_8ZMDU:cR11HqqJCp8:D7DqB2pKExk"><img src="http://feeds.feedburner.com/~ff/datascienceplus?i=ouVwZ_8ZMDU:cR11HqqJCp8:D7DqB2pKExk" border="0"></a> <a target="_blank" rel="nofollow" href="http://feeds.feedburner.com/~ff/datascienceplus?a=ouVwZ_8ZMDU:cR11HqqJCp8:qj6IDK7rITs"><img src="http://feeds.feedburner.com/~ff/datascienceplus?d=qj6IDK7rITs" border="0"></a> <a target="_blank" rel="nofollow" href="http://feeds.feedburner.com/~ff/datascienceplus?a=ouVwZ_8ZMDU:cR11HqqJCp8:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/datascienceplus?d=yIl2AUoC8zA" border="0"></a>
</div>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://datascienceplus.com/using-postgresql-and-shiny-with-a-dynamic-leaflet-map-monitoring-trash-cans/"> DataScience+</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/RtQxHxzuN0g"></a></div>