R语言sf包中st_read()能否使用SQL子查询？

问题原因分析 #

你遇到的两种错误本质都是GDAL的SQL解析环境和R的运行环境完全隔离导致的：

• 第一种错误（q2）：你把R变量moreStateNames直接写入SQL字符串，GDAL会把它当成Shapefile的字段名，而非R中的向量，自然找不到这个字段。
• 第二种错误（q3）：GDAL对Shapefile执行SQL时，只能访问当前Shapefile的图层，无法直接引用R内存中的数据框作为子查询数据源，因此子查询语法不被识别。

可行解决方案（无需全量读取后过滤） #

1. 动态拼接SQL的IN子句 #

把R向量中的元素转换成SQL能识别的带单引号的字符串，直接拼入IN子句中：

library(sf)
p <- './cb_2018_us_state_20m.shp'

moreStateNames <- c('Oregon','California','Texas')
# 处理向量元素，添加单引号，若元素含单引号需转义（比如O'Neil变成'O''Neil'）
state_quotes <- gsub("'", "''", moreStateNames)
state_str <- paste0("'", state_quotes, "'", collapse = ",")
# 拼接SQL语句
q4 <- sprintf('select * from "cb_2018_us_state_20m" where NAME in (%s)', state_str)
# 执行查询
moreStates <- st_read(p, query = q4)

2. 用GDAL虚拟向量关联R数据框 #

将R数据框转成sf对象后，创建GDAL虚拟数据源（VRT），再通过JOIN语句实现过滤：

library(sf)
p <- './cb_2018_us_state_20m.shp'

dfStates <- data.frame(id=1:3, moreStateNames=c('Oregon','California','Texas'))
# 转成sf对象（无需坐标，仅用于注册虚拟层）
dfStates_sf <- st_as_sf(dfStates, crs = st_crs(p))
# 创建临时虚拟数据源文件
vrt_path <- tempfile(fileext = ".vrt")
gdal_utils("vectortranslate", source = dfStates_sf, destination = vrt_path, options = c("-f", "VRT"))
# 执行JOIN查询，同时指定Shapefile和VRT作为数据源
q5 <- 'SELECT s.* FROM "cb_2018_us_state_20m" s JOIN "dfStates" d ON s.NAME = d.moreStateNames'
moreStates <- st_read(dsn = c(p, vrt_path), query = q5)

这种方法适合复杂关联场景，避免全量读取Shapefile。

3. 结合dplyr语法实现过滤 #

利用sf与dplyr的集成，用R的过滤语法替代手动写SQL，sf会自动转换为GDAL可识别的查询逻辑：

library(sf)
library(dplyr)
p <- './cb_2018_us_state_20m.shp'

moreStateNames <- c('Oregon','California','Texas')
moreStates <- st_read(p) %>% 
  filter(NAME %in% moreStateNames)

注：部分场景下sf可能仍会先读取全量数据，若需确认是否在读取前过滤，可查看底层执行的SQL日志。

内容的提问来源于stack exchange，提问作者S. Robinson