如何使用DuckDB高效查询大型空间线串文件？

针对你开发道路名称查询可视化应用的场景，目前to_sf()步骤耗时过长，可从以下几个方面优化整体性能：

1. 为GPKG添加索引加速查询 #

在生成GPKG后，为name字段添加文本索引、为geometry添加空间索引，能大幅提升后续查询和数据提取效率：

library(sf)
gpkg_path <- here::here("data/geofabrik_canada-latest.gpkg")

# 写入数据时直接添加索引
st_write(osm_lines, gpkg_path, layer = "lines", delete_layer = TRUE, append = FALSE)
# 为name字段添加B树索引
st_add_index(gpkg_path, layer = "lines", column = "name")
# 为geometry字段添加空间索引
st_add_index(gpkg_path, layer = "lines", column = "geometry")

2. 直接用DuckDB SQL查询并生成SF对象 #

跳过duckplyr中间对象，直接通过DuckDB连接读取并转换为SF，减少数据传输开销：

library(duckdb)
library(sf)

road_name <- "Ontario"
num_of_rows <- 5

# 建立DuckDB连接并加载GPKG
con <- dbConnect(duckdb())
duckdb_read_gpkg(con, "lines", gpkg_path)

# 直接通过SQL查询并返回SF对象
system.time({
  fooz <- st_read(con, query = sprintf(
    "SELECT name, highway, geometry 
     FROM lines 
     WHERE name LIKE '%%%s%%' 
     ORDER BY RANDOM() LIMIT %d", 
    road_name, num_of_rows
  ))
})

dbDisconnect(con)

3. 预处理时过滤冗余数据 #

在从PBF导出GPKG阶段，只保留需要的道路类型数据，缩小文件体积：

library(osmextract)
# 只保留有highway属性的线要素，过滤非道路数据
osm_lines <- oe_read("data/geofabrik_canada-latest.osm.pbf", 
                    layer = "lines",
                    filter = "highway IS NOT NULL",
                    # 仅保留必需字段，进一步减小数据量
                    select = c("name", "highway", "geometry"))
st_write(osm_lines, gpkg_path, layer = "lines", delete_layer = TRUE)

4. 启用DuckDB空间扩展 #

加载DuckDB空间扩展，优化几何数据的处理效率：

con <- dbConnect(duckdb())
dbExecute(con, "INSTALL spatial; LOAD spatial;")
# 后续查询和数据操作会自动利用空间扩展优化性能
dbDisconnect(con)

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