filter() behaviour in
bcdata v0.4.0This 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”.