This error can also occur if you have a field of type geometry. You should SELECT AS and use a spatial method to convert to WKT before pulling into a data.table. See below, where you know that Shape is a field of type geometry. By using the spatial method .STAsText() it converts it to Well-Known Text (WKT) format, which R can work with more easily.
library(sf)
library(tidyverse)
query <- "
SELECT company, revenue, address, Shape.STAsText() AS ShapeWKT
FROM businesses_table
"
results <- dbFetch(dbSendQuery(con, query)) %>%
filter(!is.na(ShapeWKT)) %>%
mutate(Shape = st_as_sfc(ShapeWKT)) %>%
select(-ShapeWKT) %>%
st_as_sf()
I built a more complex function that allows you to specify a db connection, table name, a field exception (defaults to return all fields EXCEPT those in exception field), a WHERE clause to filter your query, and specify a spatial field name, since it varies from table to table (i.e., could be Shape could be Geo, etc.).
spatial_query <- function(con, tablename,
exception = "",
wherefilter = "",
spatialfieldname = "Shape",
spatial = TRUE){
## Get last element (table name) from explicit table name
tmp <- strsplit(tablename, "[.]")[[1]]
tablename_short <- tmp[length(tmp)]
fields <- dbListFields(con, tablename_short)
if(spatial){
fields_to_grab <- fields[!(fields %in% c(exception, spatialfieldname))]
## TODO adjust query to be responsive to Shape
qstring <- paste0('SELECT ', paste(fields_to_grab, collapse = ", "), ', ', spatialfieldname, '.STAsText() AS ShapeWKT FROM ', tablename, ' ', wherefilter)
cat(paste("Sending following SQL statement:", qstring, "\n", sep = "\n"))
ret <- dbFetch(dbSendQuery(con, qstring)) %>%
filter(!is.na(ShapeWKT)) %>%
mutate(Shape = st_as_sfc(ShapeWKT)) %>%
select(-ShapeWKT) %>%
st_as_sf()
}
else{
fields_to_grab <- fields[!(fields %in% exception)]
qstring <- paste0('SELECT ', paste(fields_to_grab, collapse = ", "),' FROM ', tablename, ' ', wherefilter)
cat(paste("Sending following SQL statement:", qstring, "\n", sep = "\n"))
ret <- dbFetch(dbSendQuery(con, qstring))
}
return(ret)
}
