spatially filter a lazy DuckDB table of GeoParquet data

The sf::st_filter() method for lazy DuckDB tables, like the ones from parquet_tbl(): keeps the rows whose geometry relates to y by .predicate, translated to the DuckDB spatial function of the same name. Remote GeoParquet files only get read where needed, by comparing bounding boxes first.

Usage

# S3 method for class 'tbl_duckdb_connection'
st_filter(
  x,
  y,
  ...,
  .predicate = sf::st_intersects,
  geometry_column = NULL,
  exact = TRUE
)

# S3 method for class 'tbl_r2parquet_connection'
st_filter(
  x,
  y,
  ...,
  .predicate = sf::st_intersects,
  geometry_column = NULL,
  exact = TRUE
)

Arguments

x

lazy DuckDB table, e.g. from parquet_tbl().

y

sf, sfc or bbox object to filter by.

...

dist for sf::st_is_within_distance(), in the units of the crs or as a units object.

.predicate

sf predicate relating the geometry of x to y: sf::st_intersects() (default), sf::st_within(), sf::st_contains(), sf::st_contains_properly(), sf::st_covers(), sf::st_covered_by(), sf::st_touches(), sf::st_overlaps(), sf::st_crosses(), sf::st_equals(), sf::st_disjoint() or sf::st_is_within_distance(). As for sf objects, rows are kept if the predicate holds for any feature of y.

geometry_column

name of the geometry column. NULL (default) takes the column named geometry, as in files written by data_to_parquet(), or else the first GEOMETRY column of x.

exact

which rows to keep:

• TRUE (default): the rows for which the predicate holds.

• FALSE: the rows whose bounding box intersects the bounding box of y (grown by dist), a superset of these.

Value

a lazy dplyr table.

Details

DuckDB skips row groups whose bounding box does not intersect the bounding box of y, using the statistics of the native GEOMETRY type. This only happens for the bounding box comparison, a plain ST_Intersects filter downloads the geometry of all rows, so the filter first compares bounding boxes and then, if exact is TRUE, applies the predicate to the remaining rows. How much gets skipped depends on the file being sorted spatially, like the files written by data_to_parquet(). sf::st_disjoint() can't skip anything.

Unlike sf::st_filter() on sf objects, y gets transformed to the crs of the geometry column if both have a crs. DuckDB treats coordinates as planar, also for longitude/latitude data, where sf uses spherical geometry (sf::sf_use_s2()), so results can differ near the edges of y, and dist is in degrees. The filter warns about this for longitude/latitude data while sf::sf_use_s2() is on.

Examples

# \donttest{
# DuckDB downloads its spatial extension the first time it is needed
if (requireNamespace("curl", quietly = TRUE) && curl::has_internet()) {
  library(sf)
  sf_use_s2(FALSE) # DuckDB is planar
  nc <- read_sf(system.file("shape/nc.shp", package = "sf"))
  path <- tempfile(fileext = ".parquet")
  data_to_parquet(nc, path, metadata = list())
  parquet_tbl(path) |>
    st_filter(nc[nc$NAME == "Wake", ]) |>
    collect_parquet()
}
#> Linking to GEOS 3.12.1, GDAL 3.8.4, PROJ 9.4.0; sf_use_s2() is TRUE
#> Spherical geometry (s2) switched off
#> although coordinates are longitude/latitude, st_union assumes that they are
#> planar
#> Simple feature collection with 8 features and 14 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: -79.55536 ymin: 35.18996 xmax: -77.70051 ymax: 36.54607
#> Geodetic CRS:  NAD27
#> # A tibble: 8 × 15
#>    AREA PERIMETER CNTY_ CNTY_ID NAME   FIPS  FIPSNO CRESS_ID BIR74 SID74 NWBIR74
#>   <dbl>     <dbl> <dbl>   <dbl> <chr>  <chr>  <dbl>    <int> <dbl> <dbl>   <dbl>
#> 1 0.154      1.68  2030    2030 Harne… 37085  37085       43  3776     6    1051
#> 2 0.207      1.85  1989    1989 Johns… 37101  37101       51  3999     6    1165
#> 3 0.219      2.13  1938    1938 Wake   37183  37183       92 14484    16    4397
#> 4 0.18       2.14  1973    1973 Chath… 37037  37037       19  1646     2     591
#> 5 0.077      1.27  1908    1908 Durham 37063  37063       32  7970    16    3732
#> 6 0.143      1.66  1840    1840 Granv… 37077  37077       39  1671     4     930
#> 7 0.128      1.55  1897    1897 Frank… 37069  37069       35  1399     2     736
#> 8 0.142      1.64  1913    1913 Nash   37127  37127       64  4021     8    1851
#> # ℹ 4 more variables: BIR79 <dbl>, SID79 <dbl>, NWBIR79 <dbl>,
#> #   geometry <MULTIPOLYGON [°]>
# }