Problem in Shiny reactive objects from Google Big Query

Question

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_sfin a reactive object from a Big Query table (stands_sel()) 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?

Answer 1

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)).

The solution code:

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(
        fluidRow(
          splitLayout(plotOutput("myplot")))
      
    )
  )
)
server <- function(input, output){
  
  # # Selection of variables for plots constructions
  
  sqlInput <- reactive({
    glue::glue_sql("SELECT * FROM states WHERE state = {x}", x = input$selectedvariable1, .con=bq_con)
  })
  stands_sel <- function() dbGetQuery(bq_con, as.character(sqlInput()), stringsAsFactors = T)

  print(sqlInput)
  
  observe({

  output$myplot <- renderPlot({

  #Create the plot 
    stands_sel_sf <- st_as_sf(stands_sel(), wkt = "state_geom", crs = 4326) 
    ggplot() + geom_sf(data=stands_sel_sf) }) 
  }) #end of observe function.
}
shinyApp(ui, server)
#

Answer 2

This error occurs because the “$” operator is not designed to access vector elements. If we use the “$” operator to access the vector elements then the R does not understand it and consider it invalid; therefore, we must be very careful about where we should use the “$” operator. It happens when we give a name to our elements and start thinking that we can treat them as data frame columns which is a wrong approach. To access the vector elements, we should use single square brackets.

Individual elements of an atomic vector can be accessed sequentially. For example, position 1, position 2, etc. Notation for this is vect[1], vect[2], vect[3].

You can also express this in a different format as a variable, for inclusion in an iterative loop. However, when accessing parts of an atomic vector, you have to be careful. If certain parts of the vector are accessed improperly or in the incorrect sequence, you will likely see the “$ operator is invalid for atomic vectors” error message.

You can fix these errors by converting your “$” operator to a bracket reference. You can also use the getElement() function.

You can check in your code, in which part are you using the “$” symbol.

> testlist <- c(1,2,3) 
> testlist$s
Error in testlist$s : $ operator is invalid for atomic vectors
> testlist[1]
[1] 1
> getElement(testlist,1)
[1] 1

As you can see, we successfully triggered the error in this example by using the “$” operator to attempt to access an atomic vector (our array) improperly. By using brackets or getElement(), we get the desired result.

You can see more documentation.

You can see this example with this vector.

> set.seed(1)
> x1<-sample(1:10,20,replace=TRUE)
> x1
[1] 9 4 7 1 2 7 2 3 1 5 5 10 6 10 7 9 5 5 9 9
> names(x1)<-LETTERS[1:20]
> x1
A B C D E F G H I J K L M N O P Q R S T
9 4 7 1 2 7 2 3 1 5 5 10 6 10 7 9 5 5 9 9
> x1$K
Error in x1$K : $ operator is invalid for atomic vectors

Here, we are getting the error that “$ operator is invalid for atomic vectors”. Now we should access the elements of the vector x1 with single square brackets as shown below −

> x1["K"]
K
5
> x1["T"]
T
9
> x1["A"]
A
9
> x1[1]
A
9

You can see more information in this link.