renderDataTable multiple selections update points on shiny leaflet map

shiny

#1

Hi below is a reproducible example, I am trying to select rows in the renderDataTable (with rows highlighted that contain id and date) that will allow the user to select the id that will trigger a query based on the selected row numbers match rows with the original dataset display the points (this works ok as below).

However in my own example im linking to an external postgres database and where dat2 =subset(alldat, id %in% dat1) is displayed im using dat2 = dbGetQuery(con, paste(), sep=") etc… and it gets the data ok but does not update the plot when clicking on the rows unless its the same row selection. Do i need to trap or store the event each time I click on a row…? Any help will greatly received.


library(shiny)
library(leaflet)

ui=shinyUI(fluidPage(
  
  # Application title
  titlePanel("Test tracks"),
  
  sidebarLayout(
    sidebarPanel(
      checkboxGroupInput(inputId="shps.select", label="Select layer",
                         choices=c("tracks" = "tracks", "MPA" = "mpa")),
      DT::dataTableOutput('x3')),
    
    # Show a plot of the generated distribution
    mainPanel(
      leafletOutput("map", width = "100%", height = 400)
    )
  )
))


# Global variables 

# Example coords data
coords <- list(
  c(-122.36075812146,  47.6759920119894),
  c(-122.360781646764, 47.6668890126755),
  c(-122.360782108665,  47.6614990696722),
  c(-122.366199035722, 47.6614990696722),
  c(-122.366199035722,  47.6592874248973),
  c(-122.364582509469, 47.6576254522105),
  c(-122.363887331445,  47.6569107302038),
  c(-122.360865528129, 47.6538418253251),
  c(-122.360866157644,  47.6535254473167),
  c(-122.360866581103, 47.6533126275176),
  c(-122.362526540691,  47.6541872926348),
  c(-122.364442114483, 47.6551892850798),
  c(-122.366077719797,  47.6560733960606),
  c(-122.368818463838, 47.6579742346694),
  c(-122.370115159943,  47.6588730808334),
  c(-122.372295967029, 47.6604350102328),
  c(-122.37381369088,  47.660582362063),
  c(-122.375522972109, 47.6606413027949),
  c(-122.376079703095,  47.6608793094619),
  c(-122.376206315662, 47.6609242364243),
  c(-122.377610811371,  47.6606160735197),
  c(-122.379857378879, 47.6610306942278),
  c(-122.382454873022,  47.6627496239169),
  c(-122.385357955057, 47.6638573778241),
  c(-122.386007328104,  47.6640865692306),
  c(-122.387186331506, 47.6654326177161),
  c(-122.387802656231,  47.6661492860294),
  c(-122.388108244121, 47.6664548739202),
  c(-122.389177800763,  47.6663784774359),
  c(-122.390582858689, 47.6665072251861),
  c(-122.390793942299,  47.6659699214511),
  c(-122.391507906234, 47.6659200946229),
  c(-122.392883050767,  47.6664166747017),
  c(-122.392847210144, 47.6678696739431),
  c(-122.392904778401,  47.6709016021624),
  c(-122.39296705153, 47.6732047491624),
  c(-122.393000803496,  47.6759322346303),
  c(-122.37666945305, 47.6759896300663),
  c(-122.376486363943,  47.6759891899754),
  c(-122.366078869215, 47.6759641734893),
  c(-122.36075812146,  47.6759920119894)
)

coords <- as.data.frame(do.call("rbind", coords))
colnames(coords)  = c("longitude", "latitude")
coords$longitude  = round(coords$longitude, 2)
#create unique id as a test
coords = transform(coords,id=as.numeric(factor(longitude)))
# add example dates column
start <- as.POSIXct(Sys.time())
end <- start - as.difftime(7, units = "days")
length <- nrow(coords)
exampledates <- seq(from = start, to = end, length.out = length)
coords$timestamp <- exampledates
#dummy ids
id = data.frame(matrix(c(1,2,3,4),nrow=4,ncol=2)) 
colnames(id)  = c("id", "test") 
  
server = shinyServer(function(input, output) {
  
  output$map <- renderLeaflet({ leaflet() %>% addTiles() %>% setView(-122.37, 47.6659, zoom = 14)})
  
  
  datsel =  reactive({coords})
  idx     = reactive({id})
  output$x3 = DT::renderDataTable(idx(), server = FALSE)
  
  observe({
    
    # Create map
    map <- leafletProxy("map")
    map %>% clearShapes()
    
    # Get select inputs
    shps.select <- input$shps.select # the function is triggered when the select option changes
    
    if (length(shps.select) > 0) {
      
      if ('tracks' %in% shps.select) {
        dat=idx()
        alldat = datsel()
        ids <- input$x3_rows_selected
        
        print(paste(ids))
        #dat1 =  dat[ids, , drop = FALSE]
        
        dat1 =  isolate(paste0(dat[ids, , drop = FALSE][[1]], sep = ""))
  
       
        dat2 =subset(alldat, id %in% dat1)
        
        if (nrow(dat2) == 0){
          return(NULL)}
        
        leafletProxy("map")  %>% addCircles(lng=~longitude, lat= ~latitude, group = "track_id",
                                            data = dat2)
      }
    }  
  })
  
  
})
shinyApp(ui = ui, server = server)

#2

The issue is that within the postgres query the object pasted in the query is one value rather than a string of values. id= “1” “2” “4” as the user clicks on the selections, “1” “4” etc… What is the best way to subset/append the data depending on the click id, plot, and add and remove the data points like the example but based on individual queries within the reactive environment?

Thanks in advance for your help.