Skip to content

Instantly share code, notes, and snippets.

@HanOostdijk

HanOostdijk/description.md

Last active August 18, 2019 13:15
Show Gist options
  • Save HanOostdijk/787e9724dcd63735a431bcd16cbd18a0 to your computer and use it in GitHub Desktop.
Save HanOostdijk/787e9724dcd63735a431bcd16cbd18a0 to your computer and use it in GitHub Desktop.
Download ZIP
tests for OData V4 beta implementation CBS
Raw
description.md

The Rmd file describes some tests I did with the beta implementation of OData V4 for CBS (Statistics Netherlands).
The resulting pdf file can be found here.

Raw
opendata_beta_versie4_dec2018.Rmd
---
knit: (function (...) { HOQCutil::myknit(...) })
title: "Testjes OData V4 beta implementatie CBS"
author: "Han Oostdijk"
date: "file created : `r format(Sys.time(), '%d%b%Y')`"
graphics: yes
output:
# html_document: default
pdf_document:
keep_tex: yes
# latex_engine: xelatex
# template: default-1.17.0.2-hoqc-xe.tex
classoption: landscape,a4paper
keywords: CBS, OData, V4, beta
linkcolor: blue
geometry: [
left=0.6in ,
right=0.5in ,
top=1in ,
bottom=1in
]
header-includes : [
# '\usepackage{caption}' ,
'\usepackage[scaled=1]{DejaVuSansMono}' ,
'\usepackage{rotating}' ,
'\usepackage{longtable}' ,
'\renewcommand*{\familydefault}{\sfdefault}'
]
hoqc_version: "`r format(Sys.time(), '_%Y%m%d')`"
hoqc_output: opendata_beta_versie4_dec2018
hoqc_rmd_out: opendata_beta_versie4_dec2018
params:
show_layout_chunk : FALSE
---
```{r child='setup1.Rmd'}
```
```{r define_captions,include=FALSE}
cap_tab1 = "global data structure for CBS OData V4 beta version"
cap_tab2 = "Catalogs for CBS OData V4 beta version"
cap_tab3 = "Datasets for CBS OData V4 beta version"
cap_tab4 = "Subtables of {table_id} for CBS OData V4 beta version"
cap_tab5 = "Subtable MeasureCodes of {table_id} for CBS OData V4 beta version"
cap_tab6 = "Subtable Dimensions of {table_id} for CBS OData V4 beta version"
cap_tab7 = "Subtable {dt} of {table_id} for CBS OData V4 beta version"
cap_tab8 = "First {nrrows} rows of subtable Observations of {table_id} for CBS Odata V4 beta version"
```
# Load the libraries that will be used
We use the following packages but these are not loaded/attached by a `library` or `require` statement: `dplyr`, `tidyr`, `purrr`, `jsonlite`, `httr`, `glue`, `xlm2`, `fs` and `HOQCutil`.
Functions of these packages are prefixed with the package name and `::` as e.g. in `HOQCutil::ref_tab`.
All packages are on CRAN with the exception of my package `HOQCutil` that can be installed with the following code:
```{r required,echo=T,eval=F}
devtools::install_github("HanOostdijk/HOQCutil")
```
# Introduction
Statistics Netherlands (CBS) has announced the beta version of version 4 for the Open Data Protocol (OData) on the following [webpage](https://beta.opendata.cbs.nl/OData4/index.html). In december 2018 a
[LinkedIn Post](https://www.linkedin.com/feed/update/urn:li:activity:6476445191598276608)
announced some changes in the
[Release Notes](https://acc-ccb.cbs.nl/OData4/releasenotes.html) .
In this document I try to redo the tests done in October 2018 with the new environment.
# Conclusion
An important change is that the names in the new beta version are changed to the names in alpha version.
Also MeasureGroups and DimensionGroups and DimensionCodes are no longer present when they are empty.
In general the tests work as I expected. Because for the only table available there are no hierarchies, I could not check these. NB this is probably no longer true: see [todo](#todo).
I expected that the `$select=` command would work, but apparently it does not.
And indeed it is not mentioned in the
[implementation](https://beta.opendata.cbs.nl/OData4/implement.html) page.
For the December data I thought at one time to have a case for which the `$select=` clause works but I
can not reproduce that case. In any case it does not work for a `select` on Catalog.
In LinkedIn group
[Centraal Bureau voor de Statistiek Open Data](https://www.linkedin.com/groups/5190698/) in answer to
[my question](https://www.linkedin.com/feed/update/urn:li:activity:6449917998194008064/) Dennis Ramondt tells that the `$select=` will only be fully implemented in a later stage.
# Todo {#todo}
things to do:
- check hierarchies
<!---
-->
# Retrieve global information
I fix the `odata_root` and `odata_cat` arguments for the `HOQCutil::get_table_cbs_odata4` function by setting the first to the root of the beta data structures and the second to an empty catalog (assuming that I don't know the names of the currently available catalogs in the beta version).
```{r}
beta_root = "https://acc-ccb.cbs.nl/OData4/"
get_beta_nc =
purrr::partial(HOQCutil::get_table_cbs_odata4,
odata_root=beta_root,odata_cat='')
conts1 = get_beta_nc(verbose=T)
(dim(conts1))
```
The global data structure can be found in `r HOQCutil::ref_tab('tab1',table=T,prefix="")`.
```{r echo=F,results="asis"}
HOQCutil::pxtable (conts1,tablabel='tab1',tabcap=cap_tab1,
scalebox = 1)
```
The currently available `Catalogs` and `Datasets` can be retrieved in the following way:
```{r}
cats1 = get_beta_nc(subtable="Catalogs",verbose=T)
dsets1 = get_beta_nc(subtable="Datasets",verbose=T)
```
They have the following dimensions and column names:
```{r}
(dim(cats1))
(names(cats1))
(dim(dsets1))
(names(dsets1))
```
```{r echo=F,results="asis"}
# my_stf = function(str,w) {
# str = gsub("_", "^", str, fixed = TRUE)
# str = HOQCutil::hard_split(str, w)
# str = gsub("^", "\\_", str, fixed = TRUE)
# # paste(str,collapse = " " )
# str
# }
HOQCutil::pxtable(cats1[,c('Identifier','Title','Description')],
tablabel='tab2',tabcap=cap_tab2,
ap=1:3,av=c('p{4cm}','p{4cm}','p{12cm}'),scalebox=0.95)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(dsets1[,c('Identifier','Title','Catalog','ObservationCount')],
tablabel='tab3',tabcap=cap_tab3,
ap=1:4,av=c('p{4cm}','p{12cm}','p{4cm}','r'),scalebox=0.95)
dm = dsets1[dsets1$Catalog == 'CBS-Maatwerk',]
d1 = dm$Identifier[1]
```
I show some fields of the Catalogs `r HOQCutil::ref_tab('tab2',table=T)` and the `r ifelse(dim(dsets1)[1]<=20," Datasets "," (the first 20 Datasets) ")` `r HOQCutil::ref_tab('tab3',table=T)`. So I see that the beta version currently has
`r ifelse(dim(cats1)[1]==1," only one Catalog ",paste(dim(cats1)[1],"Catalogs"))` and
`r ifelse(dim(dsets1)[1]==1," only one Dataset. ",paste(dim(dsets1)[1],"Datasets."))`
# Retrieving table information
I will use the first Dataset (**`r d1`**) from Catalog (**CBS-Maatwerk**) to see what information is available for tables. Again I will fix the `odata_root` and `odata_cat` arguments for the `HOQCutil::get_table_cbs_odata4` function by setting the first to the root of the beta data structures and the second to this catalog. And again I can request the available catalogs and datasets for this catalog (`CBS-Maatwerk` is then the only catalog of course).
```{r}
beta_root = "https://acc-ccb.cbs.nl/OData4/"
get_beta =
purrr::partial(HOQCutil::get_table_cbs_odata4,
odata_root=beta_root,odata_cat='CBS-Maatwerk')
cats2a = get_beta(subtable="Catalogs",verbose=T)
(dim(cats2a))
dsets2a = get_beta(subtable="Datasets",verbose=T)
(dim(dsets2a))
```
I also set `table_id=900001NED` and use `get_beta` to retrieve the various subtables of this table. These are shown
`r HOQCutil::ref_tab('tab4',table=T)`. NB.&nbsp;subtable&nbsp;**Properties** is not a 'real' table but a list. The difference with the October 2018 version is that the empty subtables are left out. For this table the subtables `MeasureGroups`, `PostCode6Groups`, `PostCode6Groups`, `PostCode6Codes` and `TypegebruikGroups` no longer exist.
```{r}
table_id = '900001NED'
subtables1 = get_beta(table_id=table_id,verbose=T)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(subtables1,tablabel='tab4',tabcap=glue::glue(cap_tab4),
scalebox=0.8)
```
From the CBS
[info page](https://beta.opendata.cbs.nl/OData4/info.html)
I understand that the following subtables exist for each table: **MeasureCodes**, **MeasureGroups**,
**Dimensions**, **Observations** and **Properties** (again with the latter being a list). The table **Dimensions** specifies the extra dimensions (next to the 'MeasureCodes') and for each of these extra dimensions two additional tables exist: one with codes and one with groups in line with **MeasureCodes**, **MeasureGroups**. It is possible that one or more of these subtables are empty and from December 2018 onwards those empty tables no longer exist.
## Table properties
**Properties** is a list that describes the characteristics of the table. See the following `str` output for its contents.
```{r}
# retrieve the names and types of the subtables and the property table
props1 = get_beta(table_id=table_id,subtable="Properties",verbose=T)
HOQCutil::cap.out(str(props1,nchar.max = 5000))
```
## Measure subtables
Subtable **MeasureCodes** lists the code of the measures that are included in the **Observations** subtable with a description of the codes. Subtable **MeasureGroups** can define a hierarchy on these codes but is, as indicated above, not available for table `r table_id`
```{r}
mc1 = get_beta(table_id=table_id,subtable="MeasureCodes",verbose=T)
(dim(mc1))
mg1 = get_beta(table_id=table_id,subtable="MeasureGroups",verbose=T)
(mg1)
```
From this I see that the **MeasureGroups** subtable does not exist and that there are `r dim(mc1)[1]` measures in **MeasureCodes**. I list these with their description
`r HOQCutil::ref_tab('tab5',table=T)`,
```{r echo=F,results="asis"}
pxtable (mc1,tablabel='tab5',tabcap=glue::glue(cap_tab5),
ap=4,av='p{10cm}',scalebox=0.8)
```
## Dimensions
Subtable **Dimensions** lists the extra dimensions of the data besides the measure. These are listed
`r HOQCutil::ref_tab('tab6',table=T)`.
```{r}
dims1 = get_beta(table_id=table_id,subtable="Dimensions",verbose=T)
(dim(dims1))
```
```{r echo=F,results="asis"}
pxtable(dims1,tablabel='tab6',tabcap=glue::glue(cap_tab6),
ap=3,av='p{6cm}',scalebox=1 )
```
```{r dimtables,echo=F}
dimtables = dplyr::pull(dims1,'Identifier')
dimtables2 = c(paste0(dimtables,'Codes'),paste0(dimtables,'Groups'))
```
The extra dimensions are `r glue::glue_collapse(dimtables, ", ", last = " and ")` and therefore I have as possible additional dimension tables `r glue::glue_collapse(dimtables2, ", ", last = " and ")`. .
## Dimension subtables
```{r}
(dimtables = setdiff(dplyr::pull(subtables1,'name'),
c("MeasureCodes", "Dimensions","Observations","Properties")))
```
For this table (`r table_id`) we see that only
`r glue::glue_collapse(dimtables, ", ", last = " and ")`
`r ifelse(length(dimtables)==1,"exists","exist")`.
```{r}
tc =purrr::map(dimtables,
function(dt){
get_beta(table_id=table_id,subtable=dt)
})
names(tc) = dimtables
tc= purrr::keep(tc,function(x) length(x) > 0)
names(tc)
```
So we have `r length(tc)` additional dimension `r ifelse(length(tc)>1,"tables","table")`:
**`r glue::glue_collapse(names(tc), ", ", last = " and ")`**.
See `r glue::glue_collapse(purrr::map(names(tc),function (x) HOQCutil::ref_tab(x,table=T,prefix="")), " and ")`.
```{r echo=F,results="asis"}
d = purrr::imap(tc,
function(dt,nm){
HOQCutil::pxtable(dt,tablabel=nm,
tabcap=glue::glue(cap_tab7,dt=nm),
ap=4,av='p{8cm}') #,scalebox=0.8)
NULL
})
```
```{r lblnrrows,echo=F}
nrrows = 15
```
## Observations subtable
The **Observations** subtable contains the actual data of the table. It makes use of the codes in **MeasureCodes** and the dimension subtables. Here I show how to retrieve the first `r nrrows` rows of table **`r table_id`**. These rows are listed `r HOQCutil::ref_tab('tab8',table=T)`.
```{r ref.label='lblnrrows',eval=F}
```
```{r}
obs1 = get_beta(table_id=table_id,subtable="Observations",verbose=T,
query=glue::glue("$top={nrrows}"))
(dim(obs1))
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(obs1,tablabel='tab8',tabcap=glue::glue(cap_tab8))
```
# Table data
In the document
[OData protocol v4](http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part1-protocol.html)
the various query possibilities are described. The subset supported by CBS can be found in
[Implementatie OData v4 functies](https://beta.opendata.cbs.nl/OData4/implement.html). With these queries one can extract the table data.
## Retrieve data in long form
As an example I show the use of the `startswith` filter option for the **900001NED** table applied on the **Observations** subtable. I also retrieve the **MeasureCodes** and **TypegebruikCodes** subtable. These are used to translate the codes to readable information. This is done outside the OData protocol with the help of the
R&nbsp;package **dplyr**. The result table can be found `r HOQCutil::ref_tab('tab9',table=T)`.
I need only the fields `Identifier` and `Title` from the two code tables, so I would have liked to use the
*$select=* construct, but apparently this does not work. Therefore I used `dplyr::select`.
```{r}
obs2 = get_beta(table_id="900001NED",subtable="Observations",
query="$filter=startswith(PostCode6,'1181N')",verbose=T) %>%
dplyr::select(., Measure,Value,PostCode6,Typegebruik) %>%
dplyr::mutate(.,Value= as.numeric(Value) )
mc = get_beta(table_id="900001NED",subtable="MeasureCodes",verbose=T) %>%
dplyr::select(., Identifier,Title)
tc = get_beta(table_id="900001NED",subtable="TypegebruikCodes",verbose=T) %>%
dplyr::select(., Identifier,Title)
obs3 = obs2 %>%
dplyr::inner_join(.,mc,by=c('Measure'='Identifier')) %>%
dplyr::rename(.,Product=Title) %>%
dplyr::inner_join(.,tc,by=c('Typegebruik'='Identifier')) %>%
dplyr::rename(.,Type=Title) %>%
dplyr::select(.,PostCode6,Product,Type,Value) %>%
dplyr::arrange(.,PostCode6,Product,Type)
```
```{r echo=F,results="asis"}
sc = dim(obs3)[2]
wsc = '{r}'
HOQCutil::pxtable(obs3,tablabel='tab9',tabcap="Energy consumption in 2017 for postcode 1181N*",
ap=sc,av=wsc)
```
## Retrieve data in wide form
Because I know both the `MeasureCodes` and `TypegebruikCodes` tables are very small (see
`r HOQCutil::ref_tab('tab10',table=T,prefix='')` and `r HOQCutil::ref_tab('tab11',table=T,prefix='')`) I can combine the six combinations and do a `spread` for the variables. I use the data that was already gathered in `obs2`. See result `r HOQCutil::ref_tab('tab12',table=T)`.
```{r echo=F,results="asis"}
HOQCutil::pxtable(mc,tablabel='tab10',tabcap="MeasureCodes subtable (only Identifier and Title fields)",
scalebox=1)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(tc,tablabel='tab11',tabcap="TypegebruikCodes subtable (only Identifier and Title fields)",
scalebox=1)
```
```{r}
obs3 = obs2 %>%
dplyr::mutate(.,
Code=dplyr::case_when(
Measure == 'M00000001' & Typegebruik == 'A045558' ~ 'Gas P',
Measure == 'M00000002' & Typegebruik == 'A045558' ~ 'GasKC P',
Measure == 'M00000003' & Typegebruik == 'A045558' ~ 'Elec P',
Measure == 'M00000001' & Typegebruik == 'A045559' ~ 'Gas Z',
Measure == 'M00000002' & Typegebruik == 'A045559' ~ 'GasKC Z',
Measure == 'M00000003' & Typegebruik == 'A045559' ~ 'Elec Z'
)) %>%
dplyr::select(.,PostCode6,Code,Value) %>%
dplyr::group_by(.,PostCode6) %>%
tidyr::spread(.,Code,Value) %>%
dplyr::ungroup(.)
```
```{r echo=F,results="asis"}
nc= dim(obs3)[2]
sc = seq.int(1,nc)
wsc = c('p{3cm}',rep('{r}',nc-1))
HOQCutil::pxtable(obs3,tablabel='tab12',tabcap="Energy consumption in 2017 for postcode 1181N* v2",
ap=sc,av=wsc,scalebox=1)
```
# Tests for implementation
## Metadata Document Request
The indicated url now uses correctly the catalog `CBS-maatwerk`. The document that is returned for this url is not a `json` document but an `xml` document. I can't tell if the document is correct but it is readable.
```{r}
meta1 = get_beta(table_id=table_id,subtable="$Metadata",verbose=T)
class(meta1)
tmp_file = fs::file_temp()
xml2::write_xml(meta1,tmp_file)
HOQCutil::cap.out(readLines(tmp_file))
d = fs::file_delete(tmp_file)
```
## Service Document Request
The indicated url now uses correctly the catalog `CBS-maatwerk`. The following code
is the same as used to produce `r HOQCutil::ref_tab('tab4',table=T,prefix='')`.
```{r}
subtables1 = get_beta(table_id="900001NED",verbose=T)
```
## Query options `$filter(startswith)`
```{r echo=F}
mc2 = get_beta(table_id="900001NED",subtable='MeasureCodes',verbose=F,
query="$filter=startswith(Title,'Gem.')")
```
We used this filter above on subtable `Observations` to create e.g. `r HOQCutil::ref_tab('tab9',table=T)`.
It also works on e.g. `MeasureCodes` (originally `r nrow(mc1)` rows) where with the following code `r nrow(mc2)`&nbsp;row is selected:
```{r}
mc2 = get_beta(table_id="900001NED",subtable='MeasureCodes',verbose=T,
query="$filter=startswith(Title,'Gem.')")
nrow(mc2)
```
## Query options `$count`
Apparently `$count` can only be used on a full (sub) table: it can't be used to find the number of rows after a query.
```{r}
mc2 = get_beta(table_id="900001NED",subtable='MeasureCodes',verbose=T,
query="$filter=startswith(Title,'Gem.')&$count",error_msg=T)
(mc2)
```
On a full table it seems to work fine. Remark: the generated url should not have the question mark!
```{r}
mc3 = get_beta(table_id="900001NED",subtable='MeasureCodes',verbose=T,
query="$count")
print(mc3)
```
## Query options `(id)` {#id-case}
Apparently `(id)` can only be used on the `Observations` subtable: this is also the only table with an `id` field.
```{r}
obs_id = 23
obs4 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query=glue::glue("({obs_id})"),response=TRUE)
jsonlite::prettify(httr::content(obs4,as="text"))
```
In the example above (notice I used `response=TRUE`) I request the observation with number '`r obs_id`'. The result is not a table but a json object.
Wondering why, I did post a question about this in the
[LinkedIn group](https://www.linkedin.com/feed/update/urn:li:activity:6451478262030835713).
Here I also stated the following alternative query and list the object in json format. With `response=FALSE` (the default) a table object would be returned where `response=TRUE` returns the `httr` response object.
```{r}
obs5 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query=glue::glue("$skip={obs_id-1}&$top=1"),response=TRUE)
jsonlite::prettify(httr::content(obs5,as="text"))
```
The answer on my question is that this works as designed. Because I prefer to get the same answer in both cases I adapted the `HOQCutil::get_table_cbs_odata4` function to account for this. `r HOQCutil::ref_tab('tab13',table=T,prefix='')` shows that this works now.
```{r}
obs5 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query=glue::glue("({obs_id})"))
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(obs5,tablabel='tab13',tabcap="Result of id is table after adaption of get\\_table\\_cbs\\_odata4",scalebox=1)
```
## `$format` option
Apparently this option has no influence on the result of queries.
In the [info document](https://beta.opendata.cbs.nl/OData4/info.html) it is said that only json documents are returned.
Apparently this is not the case described in [Metadata Document Request](#metadata-document-request) where a json document is requested and an xml-document is returned.
## `$top` and `$skip` option
These options work for the **Observations** subtable as shown in [Query options `(id)`](#id-case).
They apparently also work for the **MeasureCodes** subtable as show `r HOQCutil::ref_tab('tab14',table=T)`.
```{r}
mc4 = get_beta(table_id="900001NED",subtable="MeasureCodes",
query="$skip=1&$top=1",verbose=T) %>%
dplyr::select(., Identifier,Title)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(mc4,tablabel='tab14',tabcap="\\$skip and \\$top done on MeasureCodes subtable ",
scalebox=1)
```
## $filter (And), (Or) , (Less Than) , (Greater Than), (Equals), (Less Than or Equal), (Greater Than or Equal)
`r HOQCutil::ref_tab('tab15',table=T,prefix='')` shows the results of a query with (most of) the logical and comparison operators. It is remarkable (?) that the ordering appears to be alphabetically. For a numerical ordering the `$orderby` option helps in this case: `r HOQCutil::ref_tab('tab16',table=T,prefix='See')`.
```{r}
obs6 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query="$filter=(Id lt 6 and Id gt 3) or (Id eq 8) or (Id le 15 and Id ge 12)" )
obs7 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query="$filter=(Id lt 6 and Id gt 3) or (Id eq 8) or (Id le 15 and Id ge 12)&$orderby=Id asc" )
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(obs6,tablabel='tab15',tabcap="Test for and, or, lt, gt, eq, le, ge", scalebox=1)
HOQCutil::pxtable(obs7,tablabel='tab16',tabcap="Test for and, or, lt, gt, eq, le, ge but now sorted", scalebox=1)
```
## $filter (Not Equals), mod
`r HOQCutil::ref_tab('tab17',table=T,prefix='')` shows the results of a query with the `ne` operator and the `mod` function.
```{r}
obs8 = get_beta(table_id="900001NED",subtable='Observations', verbose=T,
query="$filter=(Id mod 5000) eq 1 and Id ne 10001" )
dim(obs8)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(head(obs8),tablabel='tab17',tabcap="Test for ne and mod (first 6 rows only)",scalebox=1)
```
## $filter (Not), (endswith) and (startswith)
`r HOQCutil::ref_tab('tab18',table=T,prefix='')` shows the results of a query using `not` in combination with `endswith`. The use of `startswith` was shown in section [Retrieve data in long form](#retrieve-data-in-long-form).
```{r}
obs9 = get_beta(table_id="900001NED",subtable='MeasureCodes', verbose=T,
query="$filter=not endswith(Identifier,'2')" ) %>%
dplyr::select(Identifier,Title)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(head(obs9),tablabel='tab18',tabcap="Test for not and endswith",scalebox=1)
```
The following query only works (`r HOQCutil::ref_tab('tab19',table=T,prefix='')` ) when parentheses are used.
```{r}
obs10 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query="$filter=not (Id gt 25) and Id mod 5 eq 1")
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(obs10,tablabel='tab19',tabcap="Test for not and mod",scalebox=1)
```
Without the parentheses the expression is apparently interpreted as `(not Id) gt 25` . Coding the query as
```{r incor10,eval=F}
obs11 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query="$filter=not Id gt 25 and Id mod 5 eq 1",error_msg=T)
```
gives the following error message:
```{r ref.label='incor10',echo=F}
obs11 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query="$filter=not Id gt 25 and Id mod 5 eq 1",error_msg=T)
```
## $filter (indexof)
`r HOQCutil::ref_tab('tab20',table=T,prefix='')` shows the rows of subtable `MeasureCodes` that have the characters `gas` in the `Title` field starting in index position 9 (i.e. the 10th letter)
```{r}
mc5 = get_beta(table_id="900001NED",subtable='MeasureCodes',verbose=T,
query="$filter=indexof(Title,'gas') eq 9",error_msg=T) %>%
dplyr::select(Identifier,Title)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(mc5,tablabel='tab20',tabcap="Test for indexof",scalebox=1)
```
## $filter (tolower), (toupper)
`r HOQCutil::ref_tab('tab21',table=T,prefix='')` shows the result of a query done on the upper and lower case version of postcodes.
```{r}
obs12 = get_beta(table_id="900001NED",subtable='Observations',verbose=T,
query="$filter=tolower(Postcode6) eq '1181nz' or toupper(Postcode6) eq '1011BA' ",error_msg=T)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(obs12,tablabel='tab21',tabcap="Test for toupper and tolower",scalebox=1)
```
However it looks like nesting of functions is not allowed because the following query results in an incomplete json string:
```{r}
mc6 = get_beta(table_id="900001NED",subtable='MeasureCodes',verbose=T,
query="$filter=indexof(tolower(Title),'gas') eq 9",error_msg=T)
```
## $filter (length)
`r HOQCutil::ref_tab('tab22',table=T,prefix='')` shows the result of a query done with a `length` selection.
```{r}
mc7 = get_beta(table_id="900001NED",subtable='MeasureCodes',verbose=T,
query="$filter=length(Title) gt 30",error_msg=T) %>%
dplyr::select(Identifier,Index,Title)
```
```{r echo=F,results="asis"}
HOQCutil::pxtable(mc7,tablabel='tab22',tabcap="Test with length filter",scalebox=1)
```
<!--
$filter(length) https://beta.opendata.cbs.nl/OData4/CBS-Maatwerk/900001NED/MeasureCodes?$filter=length(Title) gt 30
--->
# SessionInfo
```{r sessionInfo}
sessionInfo()
```
<!---
# Appendix
## Functions used {#functions_used}
```{r ref.label="def_funs1",eval=F}
```
```{r ref.label="def_funs2",echo=F,eval=F}
```
-->
## Links in this document
webpage :
https://beta.opendata.cbs.nl/OData4/index.html
LinkedIn Post :
https://www.linkedin.com/feed/update/urn:li:activity:6476445191598276608
Release Notes :
https://acc-ccb.cbs.nl/OData4/releasenotes.html
Centraal Bureau voor de Statistiek Open Data :
https://www.linkedin.com/groups/5190698/
my question :
https://www.linkedin.com/feed/update/urn:li:activity:6449917998194008064/
info page :
https://beta.opendata.cbs.nl/OData4/info.html
OData protocol v4 :
http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part1-protocol.html
Implementatie OData v4 functies :
https://beta.opendata.cbs.nl/OData4/implement.html
LinkedIn group :
https://www.linkedin.com/feed/update/urn:li:activity:6451478262030835713
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment