R的sf包st_read函数query参数能否支持含SQL函数的查询？有何替代方案？

我有一个shapefile，希望仅加载满足特定条件的观测数据以避免加载整个数据集。使用R的sf包时，向st_read函数的query参数传入SQL查询，但发现SUBSTRING、LEFT这类SQL函数不被支持。以美国国会选区shapefile为例，尝试的代码如下：

library(sf)

query <- "SELECT * FROM \"districts080\" WHERE SUBSTRING(\"ID\", 2, 2) = '01'"
dfr <- st_read(file.path('C:', 'Downloads', 'districts080.shp'), 
               query = query)

运行后出现错误：

Error in CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  : 
  Query execution failed, cannot open layer.
In addition: Warning message:
In CPL_read_ogr(dsn, layer, query, as.character(options), quiet,  :
  GDAL Error 1: Undefined function 'SUBSTRING' used.

请问st_read的query参数是否限制使用SQL函数？如果是，该如何仅加载所需的观测子集？

st_read的query参数依赖GDAL的OGR SQL引擎，它支持的SQL语法和函数与标准SQL有差异，SUBSTRING这类字符串函数不在OGR SQL的内置函数列表中，因此会报错。以下是几种可行的解决方法：

方法一：用OGR SQL支持的通配符替代 #

OGR SQL支持LIKE操作符和通配符，可替代字符串截取的逻辑。针对你要匹配ID第2-3位为'01'的需求，可用_（匹配单个任意字符）和%（匹配任意长度字符）实现：

query <- "SELECT * FROM \"districts080\" WHERE \"ID\" LIKE '_01%'"
dfr <- st_read(file.path('C:', 'Downloads', 'districts080.shp'), query = query)

方法二：先筛选属性表再加载对应几何数据 #

如果通配符满足不了复杂的字符串判断，可以先单独读取属性表（不加载几何，速度更快），用R原生函数筛选目标ID，再用OGR SQL的IN语句加载对应数据：

# 仅读取ID属性，不加载几何
attrs <- st_read(file.path('C:', 'Downloads', 'districts080.shp'), 
                 query = "SELECT \"ID\" FROM \"districts080\"",
                 quiet = TRUE)
# 用R的substr函数筛选符合条件的ID
target_ids <- attrs$ID[substr(attrs$ID, 2, 3) == '01']
# 构造IN查询语句
query <- sprintf("SELECT * FROM \"districts080\" WHERE \"ID\" IN (%s)", 
                 paste0("'", target_ids, "'", collapse = ", "))
# 加载目标数据
dfr <- st_read(file.path('C:', 'Downloads', 'districts080.shp'), query = query)

方法三：用sf的延迟加载配合dplyr筛选 #

sf支持lazy = TRUE参数实现延迟加载，此时不会立即读取整个数据集，配合dplyr的filter函数可在R端完成复杂筛选，同时保证效率：

library(sf)
library(dplyr)

# 创建延迟加载的sf对象
dfr_lazy <- st_read(file.path('C:', 'Downloads', 'districts080.shp'), 
                    lazy = TRUE)
# 筛选符合条件的数据，此时才会实际加载子集
dfr <- dfr_lazy %>% filter(substr(ID, 2, 3) == '01')

内容的提问来源于stack exchange，提问作者DOS