INBO data in R

class: center, top

![:scale 30%](/assets/images/coding_club_logo_1.png)

<!-- Adjust the presentation to the session. Focus on the challenges,
this is not a coding tutorial.

Note, to include figures, store the image in the `/docs/assets/images`
folder and use the jekyll base.url reference as done in this template
or see https://jekyllrb.com/docs/liquid/tags/#links.
  using the scale attribute ![:scale 30%](...), you can adjust the image size.
-->

# 29 SEPTEMBER 2026

## INBO coding club

Herman Teirlinck 
00.48 - Keldermans

---
class: left, top

# Reminders

1. Did we confirm the room reservation on the _roomie_?
2. Did we start the recording?

---
class: center, middle

![:scale 90%](/assets/images/20260929/20260929_badge.png)

---
class: left, top

## INBO databases

INBO databases are Microsoft SQL Server databases.

From Wikipedia:

> Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel")*

They can be accessed via Virtual private network (VPN) if you are not in the VO network, i.e. you are not in one of the official Flemish Authority (**V**laamse **O**verheid) buildings. See [instructions](https://ict-intranet.inbo.be/tutorials/vpn/index.html).

* Wikipedia contributors. (2026, August 14). Microsoft SQL Server. In Wikipedia, The Free Encyclopedia. Retrieved 18:44, September 18, 2026, from https://en.wikipedia.org/w/index.php?title=Microsoft_SQL_Server&oldid=1369402327

---
class: left, top

## Introduction

We will explore the built-in functionalities provided by the "made in INBO" package [inbodb](https://inbo.github.io/inbodb/index.html) and then we will play with SQL and tidyverse.

.center[![:scale 80%](/assets/images/20260929/20260929_inbodb.png)]

---
class: left, top

## `inbodb` package

1. Connect to any INBO database
2. Run specific queries for four INBO databases:
  - [Florabank](https://www.vlaanderen.be/inbo/datasets/florabank/): see [functions](https://inbo.github.io/inbodb/reference/index.html#functions-to-query-florabank)
  - [INBOVEG](https://www.vlaanderen.be/inbo/datasets/inboveg/): see article ["How to retrieve data from the INBOVEG database"](https://inbo.github.io/inbodb/articles/get_data_inboveg.html) and [functions](https://inbo.github.io/inbodb/reference/index.html#functions-to-query-inboveg)
  - [meetnetten.be](https://www.vlaanderen.be/inbo/datasets/meetnettenbe/): see article [How to retrieve data from the Meetnetten database](https://inbo.github.io/inbodb/articles/get_data_meetnetten.html) and [functions](https://inbo.github.io/inbodb/reference/index.html#functions-to-query-the-meetnetten-database)
  - taxonlijsten: see article [How to retrieve data from the Taxonlijsten database](https://inbo.github.io/inbodb/articles/get_data_taxonlijsten.html) and [functions](https://inbo.github.io/inbodb/reference/index.html#functions-to-query-database-taxonlijsten)

Check all the available [inbodb functions](https://inbo.github.io/inbodb/reference/index.html).

.center[![:scale 60%](/assets/images/20260929/20260929_functions_inbodb.png)]

---
class: left, Topic

## inbodb vs DBI

In the [last coding club](https://coding-club.inbo.be/sessions/20260827_sql_in_r.html#1) (Aug 27) we spoke about "SQL in R" and we used the [DBI](https://dbi.r-dbi.org/) package to connect to a database and run SQL queries. The `inbodb` package is built on top of DBI and provides a more user-friendly interface to access INBO databases. So, what you could have learned in the last coding club, is still useful. But, no worries if you missed it, we will provide you some material to get started where needed.

.center[![:scale 80%](/assets/images/20260929/20260929_dbi.png)]

---
class: left, top

No cheatsheets today, but we have several sources to start with:
- a well-written [INBO tutorial](https://inbo.github.io/tutorials/tutorials/r_database_access/) is available about access to INBO databases
- the [inbodb package website](https://inbo.github.io/inbodb/index.html), with its articles and function documentation, does the rest

![:scale 100%](/assets/images/20260929/20260929_tutorial_INBO_database_access_in_r.PNG)

---
class: center, top

### How to get started?

Check the [Each session setup](https://inbo.github.io/coding-club/gettingstarted.html#each-session-setup) to get started.

### First time coding club?

Check the [First time setup](https://inbo.github.io/coding-club/gettingstarted.html#first-time-setup) section to setup.

---
class: left, top

![:scale 100%](/assets/images/coding_club_sticky_concept.png)

No yellow sticky notes online. We use hackmd (see next slide) but basic principle doesn't change.

---
class: center, top

### Share your code during the coding session!

Go to https://hackmd.io/tOalKnyaTfSgAMdYKFXJww?both

---
class: left, top

# Code

No data files to download today: we will get the data from the databases :-)

R scripts:
- [20260929_challenges.R](https://github.com/inbo/coding-club/blob/master/src/20260929/20260929_challenges.R): R script to start from

Download it automatically via `inborutils::setup_codingclub_session()` or manually and place it in `src/20260929`.

---
class: left, top

## Challenge 0

1. People out of the VO network: log in to the INBO VPN. Instructions available on the specific [IT INBO tutorial](https://ict-intranet.inbo.be/tutorials/vpn/index.html).
2. If not done, install `inbodb` via `pak::pak("inbo/inbodb")` or `remotes::install_github("inbo/inbodb")`.
3. Load `inbodb` by running `library(inbodb)`. Load also `dplyr`, `DBI` and `glue` packages with `library()` as well. Code already provided in `20260929_challenges.R` script.
4. Open connections to some databases to test (be patient, it could take a while):
```
inboveg <- connect_inbo_dbase("D0010_00_Cydonia")
florabank <- connect_inbo_dbase("D0152_00_Flora")
taxonlijsten <- connect_inbo_dbase("D0156_00_Taxonlijsten")
vis <- connect_inbo_dbase("W0001_10_Vis")
```
5. If it worked, you are ready to fly into the challenges 🚀

---
background-image: url(/assets/images/background_challenge_1.png)
class: left, top

# Challenge 1

1. Find the R connection pane of RStudio and find all tables under `dbo`* of Florabank database.

2. Open a view of table `Taxon` of `florabank` (`D0152_00_Flora`), table `ivRecording` of INBOVEG (`D0010_00_Cydonia`) and table `TaxonlijstItem` of taxonlijsten (`D0156_00_Taxonlijsten`). Tips: check [RStudio connection pane documentation](https://docs.posit.co/ide/user/ide/guide/data/data-connections.html#connections-pane); tables are under the (default) `dbo` _schema_.

3. Still using the RStudio connection pane, find the type of the columns of (one of the) tables in 1, e.g. *char*acter, *int*eger.

4. What type does `bit` stand for? Tip: check e.g. the values contained in columns `Addendum` and `Inactief` of table `TaxonlijstItem` of taxonlijsten database opened in 2.

5. How to get the names of the fields of the tables in 2 directly in R? Tip: use `dbListFields()` function from the DBI package. Check examples in the [DBI homepage](https://dbi.r-dbi.org/#example).

* dbo is an acronym for database object(s) and it's the standard place in a database to place the data. More about it in Intermezzo 1.

---
class: left, top

# Intermezzo 1 - database schemas

The DBI function `dbListFields()` worked well. So, why not trying the DBI function `dbListTables()` to get the names of all the tables? Just try:

```
dbListTables(florabank)
```

What?!? 789 tables, and all that strange names?!? 😐

Well, that's because the `florabank` database is a SQL Server database and the DBI function `dbListTables()` is not able to filter on the default schema `dbo`. So, you get all tables in all schemas and much, much more.

---
class: left, top

# Intermezzo 1 - database schemas

But wait a minute: what is a **schema**? And why do we have multiple schemas in a database?

A schema is a way to **logically group** database objects like tables within a database. It helps organize and manage the database structure, especially in larger databases with many objects. In SQL Server, the **default** schema is **`dbo`**, but other schemas can be created for different purposes or users (check e.g. the INBOVEG database `D0010_00_Cydonia`). And there are also **system schemas**, like **`sys`**, which contain system tables that are used by SQL Server for internal management and operations.

System schemas are NOT for user data, but for advanced database management and troubleshooting = never, I say, never touch them unless you are Frederic, Geert, Jo or some very few else from the data management team!*

* Typically and luckily we don't even have the rights to modify the sys schema. Still, it's good to know what it is and why we should never touch it :-)

---
class: left, top

# Intermezzo 1 - database schemas

Do you really want to get table names from the dbo schema? Filter on `dbo` using the following code, where we use the function `dbGetQuery()` to run a SQL query on the database connection `florabank`. Notice we have rights to read from the system schema to get the table names and their corresponding schema names.

```r
library(DBI)
dbGetQuery(
  florabank,
  "
    SELECT s.name AS schema_name, t.name AS table_name
    -- in sys there is a table, sys.tables, containing all tables information
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE s.name = 'dbo'
  "
)
```

---
class: left, top

# Intermezzo 1 - database views

What else can you find in a schema? Well, besides tables, there are also **views**. A view is a kind of "**virtual table**"", a "**saved search*" or a "**saved recipe**" that is based on the result of a SQL query.

It looks and acts like a table (you can query it, filter it, join it), but it doesn't store any data of its own. It's just a stored instruction: _"go fetch this specific slice/combination of the real tables, and show it to me as if it were one table."_

How to recognize them?
- In RStudio, you cannot open a view of a "view" 😄 There is no "table" symbol 𝄜 next to its name in the connection pane
- Their names typically start with `vw`. Example: the `inbo` schema of INBOVEG database `D0010_00_Cydonia` has no tables, only views. Try it yourself using the connection pane.

.center[![:scale 30%](/assets/images/20260929/20260929_views.png)]

---
background-image: url(/assets/images/background_challenge_2.png)
class: left, top

# Challenge 2

Use inbodb functions to:

1. Get observations of invasive species _Impatiens glandulifera_ and _Hydrocotyle ranunculoides_ from Florabank database.

2. Get recordings collected during survey `ABS-LIM2011` in INBOVEG database.

3. Get the most recent taxon lists with important species at provincial level. Tip: search for taxon lists containing the words `"provinciaal belangrijke soorten"`. See [taxon lists](https://inbo.github.io/inbodb/articles/get_data_taxonlijsten.html#taxonlists-overview) overview documentation for an example. Link is a valid tip for the remaining exercises as well.

4. Which **features** (kenmerken) are associated with this kind of taxon lists?

5. Get the **items**, i.e. taxa,  from the latest version of the taxon list `Provinciaal belangrijke soorten - Limburg`.

6. Get the habitat typical taxa from the latest version of the taxon list `Provinciaal belangrijke soorten - Limburg`.

---
class: left, top

# Intermezzo 2 - R is lazy

You have probably found already while working on challenge 2. What if you do not "collect" the data with `collect = TRUE`? Can you use `View()`? Yes, but you get something like this:

```
obs <- inbodb::get_florabank_observations(
 florabank,
 names = c("Impatiens glandulifera", "Hydrocotyle ranunculoides")
)
View(obs)
```

.center[![:scale 80%](/assets/images/20260929/20260929_view_lazy.png)]

---
class: left, top

# Intermezzo 2 - R is lazy

And you cannot save it as csv file for example:

```r
readr::write_csv(x = obs, file = "/data/20260929/obs.csv")

> Error in write_delim(x, file, delim = ",", na = na, append = append, col_names = col_names,  :
  is.data.frame(x) is not TRUE
```

Is `obs` not a data.frame???

```r
class(obs)
[1] "tbl_Microsoft SQL Server" "tbl_dbi"
[3] "tbl_sql"                  "tbl_lazy"
[5] "tbl"
```

All you have is a table (`tbl`), but most of all, it's a **LAZY** table (`tbl_lazy`).

---
class: left, top

# Intermezzo 2: R is lazy

.center[![:scale 100%](/assets/images/20260929/20260929_print_lazy.png)]

`obs` is an SQL query, no data are really loaded on your laptop.
It is like a **reference** to the result of the query but the full result of the query is not brought into memory.

Try also running `obs %>% show_query()`, which shows the SQL query running behind the scenes.

---
class: left, top

# Intermezzo 2: R is lazy

To avoid bad surprises (crashes, out of memory issues, ...), it's good practice to first run your queries lazily. You can fetch the output in two ways:
- rerun the inbodb function with `collect = TRUE`
- use the `collect()` dplyr function. This will bring the data into memory as a data frame.

```r
obs %>% collect()
# A tibble: 6,937 × 15
 NaamNederlands NaamWetenschappelijk AcceptedNaamWetensch…¹
 <chr> <chr> <chr>
 1 Reuzenbalsemien Impatiens glandulifera Royle Impatiens glandulifer…
 2 Reuzenbalsemien Impatiens glandulifera Royle Impatiens glandulifer…
 3 Reuzenbalsemien Impatiens glandulifera Royle Impatiens glandulifer…
 4 Reuzenbalsemien Impatiens glandulifera Royle Impatiens glandulifer…
 5 Reuzenbalsemien Impatiens glandulifera Royle Impatiens glandulifer…
...
# ℹ 6,927 more rows
# ℹ abbreviated name: ¹AcceptedNaamWetenschappelijk
# ℹ 10 more variables: ...
```
---
class: left, top

# Intermezzo 2 - R is lazy

<best-practices-for-working-with-databases-march-2018-edgar-ruiz>![:scale 10%](/assets/images/20220125/20220125_mister_lazy.jpg)</center>

R is not only lazy with databases, R is ALWAYS lazy

```r
number <- 5
multiply_by_2 <- function(a, b) {
 a * 2 # b is not used
}
multiply_by_2(number) # no value given to argument b
```

`b` is a function argument, but never evaluated, so no error. This is what we mean by being **lazy**: doing **the strict minimum** of evaluation*. For this reason lazy evaluation is sometimes referred as _call-by-need_.

**IMPORTANT**: lazy evaluation is a very powerful feature of R. Great power, great responsability. In other words, always be sure that all arguments are used in the function body, unless you have a very good reasons to not do it.

* More about lazy evaluation? Give a look at [this tutorial](https://www.r-bloggers.com/2018/07/about-lazy-evaluation/)... or spend a coffeebreak with Thierry ;-)

---
background-image: url(/assets/images/background_challenge_3.png)
class: left, top

# INTERMEZZO 3 - How to get your data?

Not everything is already wrapped in a function. So, how to get your data?

Do you know SQL? Excellent! Otherwise, just use dplyr syntax, which is translated into SQL behind the scenes using [dbplyr](https://dbplyr.tidyverse.org/), the database backend of dplyr. Just pay attention to use `tbl()` to connect to the table you are interested to and it will be interpreted as it would be a data frame.

Example adapted from the "Use inbodb" section of the [inbodb homepage](https://inbo.github.io/inbodb/index.html):

```r
# read a whole table
dbReadTable(florabank, "Bron")

# query a database using a SQL query
dbGetQuery(florabank, "SELECT ID, Code, Beschrijving FROM Bron")

# compose a query using R-code and dplyr syntax
tbl(florabank, "Bron") |>
  select("ID", "Code", "Beschrijving") |>
  collect()
```

---
background-image: url(/assets/images/background_challenge_3.png)
class: left, top

# Challenge 3 - Templates

Feel free to use dplyr syntax or SQL syntax or experiment both!

1. Get the first 10 rows of table `Taxon` of `florabank`. Warning: if you use SQL, notice that SQL Server databases do NOT support `LIMIT` clause.
2. Get the scientific name (`NaamWetenschappelijk`) of Dutch name (`NaamNederlands`) `Slank snavelmos`.
3. Make the query above reusable by creating a *template* so that you can provide any kind of Dutch name and get its scientific name back. Tip: the section ["Create and use query templates"](https://tutorials.inbo.be/tutorials/r_database_access/#create-and-use-query-templates) from the INBO tutorial "Read data from INBO databases (SQL Server) with R" is very helpful.
4. Make a template out of the provided query in `20260929_challenges.R`. The function should allow the users to select any kind of fish species.*
5. Similar to 4: make a template ouf of the provided query in `20260929_challenges.R`. Variables: fish species (field `dt.NaamNL`), year(s) (field `dd.Jaar`), project(s) (field `dp.ProjectCode`), watercourse(s) (field `dv.VHAVispuntNaam`), basin(s) (field `dv.VHAVispuntBekkenNaam`) and observations status code (field `WaarnemingStatusCode`).
6. Do not forget to disconnect to the databases when you are done with your queries.

* Understanding the complex query is actually out of scope! Just find out how to change it to make a template out of it.

---
class: left, top

# BONUS CHALLENGE - Contribute to inbodb

We have just learned that R is lazy. We shouldn't be such as lazy! Please, [contribute](https://inbo.github.io/inbodb/CONTRIBUTING.html) to inbodb: future-you will thank you. Together we make of inbodb a more and more useful package.

Contact Hans Vancalster and Els Lommelen (via GitHub issues or email) and provide them your SQL queries, some context and what are the expected inputs and outputs. Have you already an R function with the query template? Great! Send that too.

.center[![:scale 100%](/assets/images/20260929/contributing_inbodb.png)]

---
class: left, top

# The R package of the month: watina

Next to inbodb, INBO has another in-house package: [watina](https://inbo.github.io/watina/index.html)! The maintainer is "our" Floris Vanderhaeghe. What does watina do?

1. Connect to watina database
2. Run specific queries for watina database
3. Provide some useful helper functions

Start maybe by giving a look to the [Getting Started](https://inbo.github.io/watina/articles/v010_getstarted.html) article. Check also the [list](https://inbo.github.io/watina/reference/index.html) of all watina functions for a rapid scan of all functionalities.

.center[![:scale 100%](/assets/images/20260929/20260929_watina.png)]

---
class: left, top

# Resources

- Comprehensive [solutions](https://github.com/inbo/coding-club/blob/main/src/20260929/20260929_challenges_solutions.R) are available on GitHub. You can opt to download the solutions automatically by using `inborutils::setup_codingclub_session("20260929")`.
- Fragments of the video recording are available on [vimeo](https://vimeo.com/1234795203).
- The [inbodb](https://inbo.github.io/inbodb/index.html) R package documentation website to connect and work with INBO databases in R.
- The [RStudio connection pane](https://docs.posit.co/ide/user/ide/guide/data/data-connections.html#connections-pane) documentation
- The [DBI](https://dbi.r-dbi.org/) R package documentation website.
- [INBO tutorial](https://tutorials.inbo.be/tutorials/r_database_access/) about database access and how to create and use query templates and more.
- The [dbplyr](https://dbplyr.tidyverse.org/) R package documentation website.
- The [watina](https://inbo.github.io/watina/index.html) R package documentation website.
- A [blogpost](https://www.r-bloggers.com/2018/07/about-lazy-evaluation/) about lazy evaluation in R.
- The [hackMD](https://hackmd.io/tOalKnyaTfSgAMdYKFXJww?view) of this session.
- The tutorial to [connect to INBO network](https://ict-intranet.inbo.be/tutorials/vpn/index.html)*

* Only available for INBO people

---
class: center, middle

![:scale 30%](/assets/images/coding_club_logo_1.png)

Topic: to be decided 
Room: HT - 01.19 - Paul Van Ostaijen 
Date: **29/10/2026** (Tuesday), van **10:00** tot **12:30** 
Help needed with the technical setup? You are welcome from 9:45am