Query for ping pong tables failed

[philippgaertner]

Hi,

I tried to download ping pong tables in Berlin, but failed with the oe_get(). [Tag:leisure=pitch]https://wiki.openstreetmap.org/wiki/Tag:leisure%3Dpitch#Table_tennis_table)

Any advice would be awesome, greetings, Philipp.

library(osmextract)

ping_pong_Berlin = oe_get(
  "Berlin",
  quiet = FALSE,
  query = "SELECT * FROM 'points' WHERE 'leisure' = 'pitch' AND 'sport' = 'table_tennis'")

My sessionInfo()

sessionInfo()

R version 4.0.3 (2020-10-10) Platform: x86_64-apple-darwin17.0 (64-bit) Running under: macOS Big Sur 10.16

Matrix products: default LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib

locale: [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages: [1] stats graphics grDevices utils datasets methods base

other attached packages: [1] osmextract_0.2.1

loaded via a namespace (and not attached): [1] tinytex_0.29 tidyselect_1.1.0 xfun_0.20 remotes_2.2.0 purrr_0.3.4
[6] sf_0.9-7 vctrs_0.3.6 generics_0.1.0 testthat_3.0.1 usethis_2.0.0
[11] htmltools_0.5.1.1 yaml_2.2.1 rlang_0.4.10 pkgbuild_1.2.0 pillar_1.4.7
[16] e1071_1.7-4 later_1.1.0.1 glue_1.4.2 withr_2.4.1 DBI_1.1.1
[21] sessioninfo_1.1.1 lifecycle_0.2.0 blogdown_1.1 devtools_2.3.2 memoise_2.0.0
[26] evaluate_0.14 knitr_1.31 callr_3.5.1 fastmap_1.1.0 httpuv_1.5.5
[31] ps_1.5.0 class_7.3-17 Rcpp_1.0.6 KernSmooth_2.23-17 promises_1.1.1
[36] classInt_0.4-3 cachem_1.0.3 desc_1.2.0 pkgload_1.1.0 jsonlite_1.7.2
[41] fs_1.5.0 servr_0.21 digest_0.6.27 processx_3.4.5 dplyr_1.0.4
[46] rprojroot_2.0.2 grid_4.0.3 cli_2.3.0 tools_4.0.3 magrittr_2.0.1
[51] tibble_3.0.6 pkgconfig_2.0.3 crayon_1.4.0 ellipsis_0.3.1 prettyunits_1.1.1 [56] assertthat_0.2.1 rmarkdown_2.6 R6_2.5.0 units_0.6-7 compiler_4.0.3

[agila5]

Hi! The problem is that the leisure and sport fields are not included by default in the points layer:

colnames(osmextract::oe_get(
  "Berlin", 
  query = "SELECT * FROM 'points' LIMIT 5"
))
#> The input place was matched with: Berlin
#> Warning: The query selected a layer which is different from layer argument. We
#> will ignore the layer argument.
#> The chosen file was already detected in the download directory. Skip downloading.
#> Start with the vectortranslate operations on the input file!
#> 0...10...20...30...40...50...60...70...80...90...100 - done.
#> Finished the vectortranslate operations on the input file!
#> Reading layer `points' from data source `C:\Users\Utente\Documents\osm_data\geofabrik_berlin-latest.gpkg' using driver `GPKG'
#> Simple feature collection with 5 features and 10 fields
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 13.26789 ymin: 52.49484 xmax: 13.34323 ymax: 52.56662
#> geographic CRS: WGS 84
#>  [1] "osm_id"     "name"       "barrier"    "highway"    "ref"       
#>  [6] "address"    "is_in"      "place"      "man_made"   "other_tags"
#> [11] "geometry"

You can extract those fields using hstore_get_value function

osmextract::oe_get(
  "Berlin", 
  query = "
  SELECT * 
  FROM 'points' 
  WHERE 
  hstore_get_value(other_tags, 'leisure') = 'pitch' 
  AND 
  hstore_get_value(other_tags, 'sport') = 'table_tennis'
  "
)
#> The input place was matched with: Berlin
#> Warning: The query selected a layer which is different from layer argument. We
#> will ignore the layer argument.
#> The chosen file was already detected in the download directory. Skip downloading.
#> The corresponding gpkg file was already detected. Skip vectortranslate operations.
#> Reading layer `points' from data source `C:\Users\Utente\Documents\osm_data\geofabrik_berlin-latest.gpkg' using driver `GPKG'
#> Simple feature collection with 823 features and 10 fields
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 13.10219 ymin: 52.37523 xmax: 13.61851 ymax: 52.62092
#> geographic CRS: WGS 84

or you can add those fields to the .gpkg file

osmextract::oe_get(
  "Berlin", 
  extra_tags = c("leisure", "sport"),
  query = "
  SELECT * 
  FROM 'points' 
  WHERE 
  leisure = 'pitch' 
  AND 
  sport = 'table_tennis'
  "
)
#> The input place was matched with: Berlin
#> Warning: The query selected a layer which is different from layer argument. We
#> will ignore the layer argument.
#> The chosen file was already detected in the download directory. Skip downloading.
#> Start with the vectortranslate operations on the input file!
#> 0...10...20...30...40...50...60...70...80...90...100 - done.
#> Finished the vectortranslate operations on the input file!
#> Reading layer `points' from data source `C:\Users\Utente\Documents\osm_data\geofabrik_berlin-latest.gpkg' using driver `GPKG'
#> Simple feature collection with 823 features and 12 fields
#> geometry type:  POINT
#> dimension:      XY
#> bbox:           xmin: 13.10219 ymin: 52.37523 xmax: 13.61851 ymax: 52.62092
#> geographic CRS: WGS 84

^{Created on 2021-02-24 by the reprex package (v0.3.0)}

[philippgaertner]

Thank you for the explanation and examples.

[Robinlovelace]

Is the issue fixed for you @philippgaertner ? Please close the issue if so.