Update to `filter()` behaviour in bcdata v0.4.0

This vignette describes a change in bcdata v0.4.0 related to using locally-executed functions in a filter() query with bcdc_query_geodata():

When using bcdc_query_geodata() with filter(), many functions are translated to a query plan that is passed to and executed on the server - this includes the CQL Geometry predicates such as INTERESECTS(), CROSSES(), BBOX() etc, as well as many base R functions. However you sometimes want to include a function call in your filter() statement which should be evaluated locally - i.e., it’s an R function (often an sf function) with no equivalent function on the server. Prior to version 0.4.0, bcdata did a reasonable (though not perfect) job of detecting R functions inside a filter() statement that needed to be evaluated locally. In order to align with recommended best practices for dbplyr backends, as of v0.4.0, function calls that are to be evaluated locally now need to be wrapped in local().

For example, say we want to create a bounding box around two points and use that box to perform a spatial filter on the remote dataset, to give us just the set of local greenspaces that exist within that bounding box.

library(sf)
library(bcdata)

two_points <- st_sfc(st_point(c(1164434, 368738)),
                     st_point(c(1203023, 412959)),
                     crs = 3005)

Previously, we could just do this, with sf::st_bbox() embedded in the call:

bcdc_query_geodata("local-and-regional-greenspaces") %>%
  filter(BBOX(st_bbox(two_points, crs = st_crs(two_points))))

## Error: Error : Cannot translate a <sfc_POINT> object to SQL.
## ℹ Do you want to force evaluation in R with (e.g.) `!!x` or `local(x)`?

However you must now use local() to force local evaluation of st_bbox() on your machine in R, before it is translated into a query plan to be executed on the server:

bcdc_query_geodata("local-and-regional-greenspaces") %>%
  filter(BBOX(local(st_bbox(two_points, crs = st_crs(two_points)))))

## Querying 'local-and-regional-greenspaces' record
## • Using collect() on this object will return 1158 features and 19 fields
## • At most six rows of the record are printed here
## ────────────────────────────────────────────────────────────────────────────────────────────────────
## Simple feature collection with 6 features and 19 fields
## Geometry type: POLYGON
## Dimension:     XY
## Bounding box:  xmin: 1200113 ymin: 385903.5 xmax: 1202130 ymax: 388026
## Projected CRS: NAD83 / BC Albers
## # A tibble: 6 × 20
##   id      LOCAL_REG_GREENSPACE…¹ PARK_NAME PARK_TYPE PARK_PRIMARY_USE REGIONAL_DISTRICT MUNICIPALITY
##   <chr>                    <int> <chr>     <chr>     <chr>            <chr>             <chr>       
## 1 WHSE_B…                    689 Cranford… Local     Water Access     Capital           District of…
## 2 WHSE_B…                    634 <NA>      Local     Water Access     Capital           District of…
## 3 WHSE_B…                    725 <NA>      Local     Water Access     Capital           District of…
## 4 WHSE_B…                    665 Konukson… Local     Green Space      Capital           District of…
## 5 WHSE_B…                    622 <NA>      Local     Trail            Capital           District of…
## 6 WHSE_B…                    698 <NA>      Local     Water Access     Capital           District of…
## # ℹ abbreviated name: ¹LOCAL_REG_GREENSPACE_ID
## # ℹ 13 more variables: CIVIC_NUMBER <chr>, CIVIC_NUMBER_SUFFIX <chr>, STREET_NAME <chr>,
## #   LATITUDE <dbl>, LONGITUDE <dbl>, WHEN_UPDATED <date>, WEBSITE_URL <chr>,
## #   LICENCE_COMMENTS <chr>, FEATURE_AREA_SQM <dbl>, FEATURE_LENGTH_M <dbl>, OBJECTID <int>,
## #   SE_ANNO_CAD_DATA <chr>, geometry <POLYGON [m]>

There is another illustration in the “querying spatial data vignette”.

2024-12-11