Issues filtering using bcdc_query_geodata()

[a-potapova]

I am running into issues with filtering to download a subset of data using the bcdc_query_geodata() function. I first ran into the issue with the Freshwater Atlas Stream Network data (https://catalogue.data.gov.bc.ca/dataset/92344413-8035-4c08-b996-65a9b3f62fca) but I am also unable to filter the Local and Regional Greenspaces data used in the vignette on querying spatial data (https://catalogue.data.gov.bc.ca/dataset/6a2fea1b-0cc4-4fc2-8017-eaf755d516da).

The issue is that filtering using logical operators (==, >) is resulting in the following error message:

Error: Error in glue_sql2(sql_current_con(), "{.val x} {f} {.val y}") :
could not find function "glue_sql2"

Filtering using the %in% operator works correctly. This applies to both the FWA stream network data and the data sources used in the vignette. Filtering the same exact query using == does not work, even with the greenspaces data from the vignette. Specifying bcdata::filter does not solve the issue.

#Mapping test
library(bcdata)
library(sf)

#This does not work:
streams <- bcdc_query_geodata("92344413-8035-4c08-b996-65a9b3f62fca") %>%
  filter(GNIS_NAME == "Puntledge River") %>%
  collect()

#This works: 
streams <- bcdc_query_geodata("92344413-8035-4c08-b996-65a9b3f62fca") %>%
  filter(GNIS_NAME %in% c("Puntledge River")) %>%
  collect()

#This does not work:
streams <- bcdc_query_geodata("92344413-8035-4c08-b996-65a9b3f62fca") %>%
  filter(STREAM_ORDER > 5) %>%
  collect()

#This works:
streams <- bcdc_query_geodata("92344413-8035-4c08-b996-65a9b3f62fca") %>%
  filter(STREAM_ORDER %in% c(5)) %>%
  collect()

#Examples from the vignette: 
#This works: 
bcdc_query_geodata("78ec5279-4534-49a1-97e8-9d315936f08b") %>%
  filter(SCHOOL_DISTRICT_NAME %in% c("Greater Victoria", "Prince George","Kamloops/Thompson"))

#This does not work: 
bcdc_query_geodata("6a2fea1b-0cc4-4fc2-8017-eaf755d516da") %>%
  filter(PARK_PRIMARY_USE == "Park")

My package and R version:

packageVersion("bcdata")
[1] ‘0.4.1’

R version 4.3.0

Any insights would be appreciated as the stream network data is too big to download all at once. My main issue is that I need to filter by stream order. I am able to filter using a list of integers (i.e. STREAM_ORDER %in% c(5,6,7)) but only because I am familiar enough with the data to know which values I need. It seems like it would be preferable to be able to subset logically, especially if this issue also persists with other fields that are non-integer numbers that can't easily be listed.

Thanks!

[bevingtona]

All of those commands seem to work fine with bcdata 0.5.0, maybe try updating?

#Mapping test
library(bcdata)
#> Warning: package 'bcdata' was built under R version 4.3.3
#> 
#> Attaching package: 'bcdata'
#> The following object is masked from 'package:stats':
#> 
#>     filter
library(sf)
#> Warning: package 'sf' was built under R version 4.3.3
#> Linking to GEOS 3.11.2, GDAL 3.8.2, PROJ 9.3.1; sf_use_s2() is TRUE

(streams <- bcdc_query_geodata("92344413-8035-4c08-b996-65a9b3f62fca") %>%
  filter(GNIS_NAME == "Puntledge River") %>%
  collect())
#> Simple feature collection with 157 features and 27 fields
#> Geometry type: LINESTRING
#> Dimension:     XYZ
#> Bounding box:  xmin: 1046621 ymin: 496625.9 xmax: 1072539 ymax: 520900.3
#> z_range:       zmin: 2.635 zmax: 560
#> Projected CRS: NAD83 / BC Albers
#> # A tibble: 157 × 28
#>    id               LINEAR_FEATURE_ID WATERSHED_GROUP_ID EDGE_TYPE BLUE_LINE_KEY
#>  * <chr>                        <int>              <int>     <int>         <int>
#>  1 WHSE_BASEMAPPIN…         710247973                 34      1250     354154204
#>  2 WHSE_BASEMAPPIN…         710248681                 34      1250     354154204
#>  3 WHSE_BASEMAPPIN…         710256675                 34      1000     354154204
#>  4 WHSE_BASEMAPPIN…         710254836                 34      1050     354154204
#>  5 WHSE_BASEMAPPIN…         710256206                 34      1200     354154204
#>  6 WHSE_BASEMAPPIN…         710256642                 34      1000     354154204
#>  7 WHSE_BASEMAPPIN…         710256932                 34      1200     354154204
#>  8 WHSE_BASEMAPPIN…         710246261                 34      1250     354154204
#>  9 WHSE_BASEMAPPIN…         710256868                 34      1000     354154204
#> 10 WHSE_BASEMAPPIN…         710257326                 34      1000     354154204
#> # ℹ 147 more rows
#> # ℹ 23 more variables: WATERSHED_KEY <int>, FWA_WATERSHED_CODE <chr>,
#> #   LOCAL_WATERSHED_CODE <chr>, WATERSHED_GROUP_CODE <chr>,
#> #   DOWNSTREAM_ROUTE_MEASURE <dbl>, LENGTH_METRE <dbl>, FEATURE_SOURCE <chr>,
#> #   GNIS_ID <int>, GNIS_NAME <chr>, LEFT_RIGHT_TRIBUTARY <chr>,
#> #   STREAM_ORDER <int>, STREAM_MAGNITUDE <int>, WATERBODY_KEY <int>,
#> #   BLUE_LINE_KEY_50K <int>, WATERSHED_CODE_50K <chr>, …

(streams <- bcdc_query_geodata("92344413-8035-4c08-b996-65a9b3f62fca") %>%
  filter(STREAM_ORDER > 8) %>%
  collect())
#> Simple feature collection with 9930 features and 27 fields
#> Geometry type: LINESTRING
#> Dimension:     XYZ
#> Bounding box:  xmin: 555480 ymin: 458829.4 xmax: 1655067 ymax: 1670437
#> z_range:       zmin: 1 zmax: 878.385
#> Projected CRS: NAD83 / BC Albers
#> # A tibble: 9,930 × 28
#>    id               LINEAR_FEATURE_ID WATERSHED_GROUP_ID EDGE_TYPE BLUE_LINE_KEY
#>  * <chr>                        <int>              <int>     <int>         <int>
#>  1 WHSE_BASEMAPPIN…         700930773                 42      1250     356364114
#>  2 WHSE_BASEMAPPIN…         700938576                 42      1250     356364114
#>  3 WHSE_BASEMAPPIN…         702134364                133      1250     356364114
#>  4 WHSE_BASEMAPPIN…         702123565                133      1250     356364114
#>  5 WHSE_BASEMAPPIN…          87046478                 88      1250     360887278
#>  6 WHSE_BASEMAPPIN…          87058550                 88      1250     360887278
#>  7 WHSE_BASEMAPPIN…          87029898                 88      1250     360887278
#>  8 WHSE_BASEMAPPIN…         702921849                179      1250     356364114
#>  9 WHSE_BASEMAPPIN…         702944026                179      1250     356364114
#> 10 WHSE_BASEMAPPIN…         702945082                179      1250     356364114
#> # ℹ 9,920 more rows
#> # ℹ 23 more variables: WATERSHED_KEY <int>, FWA_WATERSHED_CODE <chr>,
#> #   LOCAL_WATERSHED_CODE <chr>, WATERSHED_GROUP_CODE <chr>,
#> #   DOWNSTREAM_ROUTE_MEASURE <dbl>, LENGTH_METRE <dbl>, FEATURE_SOURCE <chr>,
#> #   GNIS_ID <int>, GNIS_NAME <chr>, LEFT_RIGHT_TRIBUTARY <chr>,
#> #   STREAM_ORDER <int>, STREAM_MAGNITUDE <int>, WATERBODY_KEY <int>,
#> #   BLUE_LINE_KEY_50K <int>, WATERSHED_CODE_50K <chr>, …

(parks <- bcdc_query_geodata("6a2fea1b-0cc4-4fc2-8017-eaf755d516da") %>%
  filter(PARK_PRIMARY_USE == "Park") %>% 
  collect())
#> Simple feature collection with 4459 features and 19 fields
#> Geometry type: MULTIPOLYGON
#> Dimension:     XY
#> Bounding box:  xmin: 478413.6 ymin: 369334.3 xmax: 1798077 ymax: 1684764
#> Projected CRS: NAD83 / BC Albers
#> # A tibble: 4,459 × 20
#>    id                LOCAL_REG_GREENSPACE…¹ PARK_NAME PARK_TYPE PARK_PRIMARY_USE
#>  * <chr>                              <int> <chr>     <chr>     <chr>           
#>  1 WHSE_BASEMAPPING…                     23 Brighous… Local     Park            
#>  2 WHSE_BASEMAPPING…                     24 Minoru P… Local     Park            
#>  3 WHSE_BASEMAPPING…                     25 Dover Ne… Local     Park            
#>  4 WHSE_BASEMAPPING…                     27 Cook Nei… Local     Park            
#>  5 WHSE_BASEMAPPING…                     28 Katsura … Local     Park            
#>  6 WHSE_BASEMAPPING…                     29 Birch Ne… Local     Park            
#>  7 WHSE_BASEMAPPING…                     32 Garratt … Local     Park            
#>  8 WHSE_BASEMAPPING…                     33 Morris N… Local     Park            
#>  9 WHSE_BASEMAPPING…                     34 Marringt… Local     Park            
#> 10 WHSE_BASEMAPPING…                     35 Heather … Local     Park            
#> # ℹ 4,449 more rows
#> # ℹ abbreviated name: ¹​LOCAL_REG_GREENSPACE_ID
#> # ℹ 15 more variables: REGIONAL_DISTRICT <chr>, MUNICIPALITY <chr>,
#> #   CIVIC_NUMBER <int>, 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 <MULTIPOLYGON [m]>

packageVersion("bcdata")
#> [1] '0.5.0'

[a-potapova]

That solved it! Sorry, I was convinced that I was up to date on my package version. Thanks for the quick reply!

[smnorris]

FYI, other channels for FWA stream downloads:

FTP (official):
curl -o FWA_STREAM_NETWORKS_SP.gdb.zip ftp://ftp.geobc.gov.bc.ca/sections/outgoing/bmgs/FWA_Public/FWA_STREAM_NETWORKS_SP.zip

R wrapper of fwapg API / postgis database:
https://github.com/poissonconsulting/fwapgr

Parquet file on BC object storage (draft):
https://nrs.objectstore.gov.bc.ca/bchamp/fwapg/fwa_stream_networks_sp.parquet

[boshek]

Thanks for the answer @bevingtona. Glad this was a simple fix!