de duckdb à st_to_sf

duckdb

arrow

geoarrow

Comment convertir une extraction de duckdb en objet sf

Auteur·rice

Nicolas Chuche

Date de publication

12 juillet 2025

Jusqu’à récemment, générer un dataframe SF à partir d’une requête duckdb imposait :

d’utiliser ST_AsWKB ou ST_AsText sur la colonne géométrie
de matérialiser les données pour les transférer à sf::st_as_sf

Avec les versions récentes de duckdb, de l’extension spatial et du package geoarrow, vous pouvez lui demander de générer une donnée réutilisable directement par geoarrow :

library(geoarrow)
library(duckdb)
library(sf)

con <- dbConnect(duckdb())

url <- "https://static.data.gouv.fr/resources/sirene-geolocalise-parquet/20240107-143656/sirene2024-geo.parquet"

x <- dbExecute(con, "LOAD spatial;")
x <- dbExecute(con, "LOAD httpfs;")
1x <- dbExecute(con, "CALL register_geoarrow_extensions()")

dplyr::tbl(con, dplyr::sql(glue::glue("SELECT geometry 
                                       FROM read_parquet('{url}')
2                                       LIMIT 5"))) |>
3  arrow::to_arrow() |>
4  st_as_sf(crs=st_crs(2154))

1: demande à duckdb spatial d’ajouter les métadonnées geoarrow dans les colonnes de type géométrie
2: grace à la commande précédente, cette ligne va retourner des géométries lisibles par geoarrow
3: cette ligne transforme l’objet en un objet arrow
4: geoarrow surcharge la fonction st_as_sf pour qu’elle puisse lire directement l’objet arrow

Simple feature collection with 5 features and 0 fields
Geometry type: POINT
Dimension:     XY
Bounding box:  xmin: 3.735375 ymin: 49.38698 xmax: 3.738175 ymax: 49.39506
Projected CRS: RGF93 v1 / Lambert-93
                   geometry
1 POINT (3.738175 49.39245)
2 POINT (3.735375 49.38829)
3 POINT (3.735446 49.39507)
4 POINT (3.738132 49.38698)
5 POINT (3.735748 49.38712)

Une comparaison rapide

Et c’est beaucoup plus rapide que toutes les autres méthodes :

Montre moi le code du benchmark

library(arrow)
library(duckdb)
library(sf)
library(dplyr)
library(glue)
library(timemoir)
library(geoarrow)

sample_size <- 1e8

if (!file.exists("geo.parquet")) {
  download.file("https://static.data.gouv.fr/resources/sirene-geolocalise-parquet/20240107-143656/sirene2024-geo.parquet", "geo.parquet")
}

with_register_geoarrow <- function() {
  conn_ddb <- dbConnect(duckdb())
  dbExecute(conn_ddb, "LOAD spatial;")
  dbExecute(conn_ddb, "CALL register_geoarrow_extensions()")
  
  query <- dplyr::tbl(conn_ddb, sql(glue("SELECT * FROM read_parquet('geo.parquet') LIMIT {sample_size}"))) |>
    arrow::to_arrow() |>
    st_as_sf(crs=st_crs(2154))
  
  dbDisconnect(conn_ddb, shutdown = TRUE)
}

with_st_read <- function() {
  conn_ddb <- dbConnect(duckdb())
  on.exit(dbDisconnect(conn_ddb, shutdown = TRUE))
  dbExecute(conn_ddb, "LOAD spatial;")
  
  a <- st_read(
    conn_ddb, 
    query=glue(
      "SELECT * REPLACE(geometry.ST_ASWKB() AS geometry) FROM read_parquet('geo.parquet') 
      WHERE geometry IS NOT NULL LIMIT {sample_size}"
    ), 
    geometry_column = "geometry") |>
    st_set_crs(2154)
  dbDisconnect(conn_ddb, shutdown = TRUE)
}

with_get_query_aswkb <- function() {
  conn_ddb <- dbConnect(duckdb())
  on.exit(dbDisconnect(conn_ddb, shutdown = TRUE))
  dbExecute(conn_ddb, "LOAD spatial;")
  
  query <- dbGetQuery(
    conn_ddb, 
    glue(
      "
      SELECT * REPLACE(geometry.ST_ASWKB() AS geometry) FROM read_parquet('geo.parquet') 
      WHERE geometry IS NOT NULL LIMIT {sample_size}
      "
    )
  ) |>
    sf::st_as_sf(crs = st_crs(2154))
  dbDisconnect(conn_ddb, shutdown = TRUE)
}

with_get_query_astxt <- function() {
  conn_ddb <- dbConnect(duckdb())
  on.exit(dbDisconnect(conn_ddb, shutdown = TRUE))
  dbExecute(conn_ddb, "LOAD spatial;")
  
  query <- dbGetQuery(
    conn_ddb, 
    glue(
      "
      SELECT * REPLACE(geometry.ST_ASText() AS geometry) FROM read_parquet('geo.parquet')
      WHERE geometry IS NOT NULL LIMIT {sample_size}
      "
    )
  ) |>
    sf::st_as_sf(wkt = "geometry", crs = st_crs(2154))
}

res <- timemoir(
  with_register_geoarrow(), 
  with_st_read(),
  with_get_query_aswkb(),
  with_get_query_astxt())

res |>
  kableExtra::kable()

fname	duration	error	start_mem	max_mem	cpu_user	cpu_sys
with_register_geoarrow()	41.378	NA	255304	26963376	36.374	6.016
with_st_read()	202.641	NA	255272	25284744	192.910	10.382
with_get_query_aswkb()	203.976	NA	257732	25283144	193.012	11.819
with_get_query_astxt()	165.089	NA	281832	24851760	175.733	9.077

plot(res)

Quelques liens

On ne trouve pas grand chose sur cette commande

Information de session

devtools::session_info(pkgs = "attached")

─ Session info ───────────────────────────────────────────────────────────────
 setting  value
 version  R version 4.5.0 (2025-04-11)
 os       Ubuntu 22.04.5 LTS
 system   x86_64, linux-gnu
 ui       X11
 language (EN)
 collate  en_US.UTF-8
 ctype    en_US.UTF-8
 tz       Etc/UTC
 date     2025-07-14
 pandoc   3.7.0.2 @ /usr/bin/ (via rmarkdown)
 quarto   1.7.31 @ /usr/local/bin/quarto

─ Packages ───────────────────────────────────────────────────────────────────
 package  * version date (UTC) lib source
 DBI      * 1.2.3   2024-06-02 [1] RSPM (R 4.5.0)
 duckdb   * 1.3.0   2025-06-02 [1] RSPM (R 4.5.0)
 geoarrow * 0.3.0   2025-05-26 [1] RSPM (R 4.5.0)
 sf       * 1.0-21  2025-05-15 [1] RSPM (R 4.5.0)

 [1] /usr/local/lib/R/site-library
 [2] /usr/local/lib/R/library
 * ── Packages attached to the search path.

──────────────────────────────────────────────────────────────────────────────

Retour au sommet