Big Query tables doesn't work with sf in reactive objects

[Leprechault]

I'd like to download and plot just only the state geometry selected by selectInput in Shiny. But when I try to use st_as_sf in a reactive object (stands_sel <- st_as_sf(stands_sel(), wkt = "state_geom", crs = 4326)) from a Big Query table:

        stands_sel <- reactive({ 
          var0 <- unique(currentvariable1()$state)
          sqlInput <- glue::glue_sql("SELECT * FROM states WHERE state = {var0}", .con=bq_con)
          dbGetQuery(bq_con, as.character(sqlInput), stringsAsFactors = T)
          })

doesn't work and the output is:

    Listening on http://127.0.0.1:5221
    Warning: Error in $: $ operator is invalid for atomic vectors
      186: unique
      185: <reactive:stands_sel> [C:/Users/fores/Downloads/teste_sf.R#60]
      183: .func
      180: contextFunc
      179: env$runWith
      172: ctx$run
      171: self$.updateValue
      169: stands_sel
      167: renderPlot [C:/Users/fores/Downloads/teste_sf.R#71]
      165: func
      125: drawPlot
      111: <reactive:plotObj>
       95: drawReactive
       82: renderFunc
       81: output$myplot
        1: runApp

In my example I make:

    library(shinythemes)
    library(dplyr)
    library(ggplot2)
    library(bigrquery)
    library(DBI)
    library(sf)
    library(glue)
    
    # Open a public BigQuery dataset eg. "geo_us_boundaries"
    bq_con <- dbConnect(
      bigrquery::bigquery(),
      project = "bigquery-public-data",
      dataset = "geo_us_boundaries",
      billing = "my-project"
    )
    bigrquery::dbListTables(bq_con) # List all the tables in BigQuery data set
    
    
    # Take the table
    dataset <- dplyr::tbl(bq_con, 
                          "states") # connects to a table
    
    
    # Enumerate the states
    dataset_vars <- dataset %>% dplyr::distinct(geo_id, state, state_name)%>% 
      collect() 
    str(dataset_vars)
    
    
    # Create the shiny dash
    ui <- fluidPage(
      theme = shinytheme("cosmo"),
      titlePanel(title="States Dashboard"),  
      sidebarLayout(
        sidebarPanel(
          selectInput(inputId = "selectedvariable0",
                      label = "Geo ID", 
                      choices = c(unique(dataset_vars$geo_id)),selected = TRUE ), 
          selectInput(inputId = "selectedvariable1",
                      label = "State", 
                      choices = c(unique(dataset_vars$state)),selected = TRUE ), 
          selectInput(inputId = "selectedvariable2",
                      label = "State name", 
                      choices = c(unique(dataset_vars$state_name)),selected = TRUE )
        ),
        mainPanel(
          textOutput("idSaida"),
          fluidRow(
            splitLayout(plotOutput("myplot")))
        )
      )
    )
    server <- function(input, output){
      
      
      # # Selection of variables for plots constructions
          currentvariable1 <- reactive({input$selectedvariable1})
      
        stands_sel <- reactive({ 
          var0 <- unique(currentvariable1()$state)
          sqlInput <- glue::glue_sql("SELECT * FROM states WHERE state = {var0}", .con=bq_con)
          dbGetQuery(bq_con, as.character(sqlInput), stringsAsFactors = T)
          })
      
       
      observe({
    
           output$myplot <- renderPlot({
             
             #Create the plot
             stands_sel <- st_as_sf(stands_sel(), wkt = "state_geom", crs = 4326)
             ggplot() +
               geom_sf(data=stands_sel) 
           })
       }) #end of observe function.
    }
    shinyApp(ui, server)
    #

Please, any help to solve it?

[etiennebr]

I would suspect from the error message that the table returned is not exactly what sf would like.

I notice that you have two variables named stands_sel. I would suggest you choose another variable name inside your observe to avoid collision.

My next step would be to make the code work in a plain (non-reactive) R session. That way you'll probably receive more informative error messages. Start from:

dataset <- dplyr::tbl(bq_con,  "states")

Then

currentvariable1 <- unique(dataset_vars$state[1]
var0 <- unique(currentvariable1()$state)
sqlInput <- glue::glue_sql("SELECT * FROM states WHERE state = {var0}", .con=bq_con)
stands_sel <- function() dbGetQuery(bq_con, as.character(sqlInput), stringsAsFactors = T)
stands_sel_sf <- st_as_sf(stands_sel(), wkt = "state_geom", crs = 4326)
             ggplot() +
             geom_sf(data=stands_sel_sf)

[Leprechault]

Thank you @etiennebr, but unfortunately, the function glue_sql does not accept the reactive objects. I try {var0} and {var0()} without success.

   var0 <- reactive({input$selectedvariable1})
  sqlInput <- glue::glue_sql("SELECT * FROM states WHERE state = {var0}", .con=bq_con)
  print(sqlInput)
Warning: Error in as.vector: cannot coerce type 'closure' to vector of type 'character'
  55: as.character.default
  53: .transformer
  52: <Anonymous>
Error in as.vector(x, "character") : 
  cannot coerce type 'closure' to vector of type 'character'

[Leprechault]

@Leprechault

the function glue_sql does not accept the reactive objects. I try {var0} and {var0()} without success

The function glue_sql does not accept the reactive objects using glue::glue_sql("SELECT * FROM states WHERE state = {var0}", .con=bq_con) , but glue_sql call needs to be move into the reactive expression ( glue::glue_sql("SELECT * FROM states WHERE state = {x}", x = input$selectedvariable1, .con=bq_con) ).