Tutorials needed on connecting to and using INBO data from R

[florisvdh]

INBO hosts many databases, so tutorials are needed for scientific and scientific support staff on how to use them from within R.

These tutorials should focus on:

• connecting to the respective (spatial and non-spatial) databases / datawarehouses from within R, in a dplyr / DBI compliant way (rather than using RODBC). Interesting examples can be found at https://db.rstudio.com/.
  • to achieve reproducibility (transferability to docker, ...) and scalability (servers), methods should be agnostic of the user's environment as much as possible. E.g.: the user's location, the operating system, the network.
  • one connection method that works with all INBO (spatial and non-spatial) databases / datawarehouses is the preferred way to go.
• best practices using each specific database, by clearly defining the available tables and fields, and providing key scripting examples.

There is an urgent need for the following INBO databases:

• Meetnetten
• Watervogels
• Watina
• INBOVEG
• GIS data (using GDAL; see vignette on sf and the raster package for practical implementations in R) - see also issue Functionality to quickly export all raster layers from a filegdb #29
• Aquachem

Further, the need is also urgent for these databases:

• LIMS
• BWK-database
• INBODEM

This list is most probably incomplete. (So having an overview of data is another, related need.)

[stijnvanhoey]

With respect to GIS-data, the available dbase of mercator-vonet should be in scope ass well. I cite here the information provided by Carine Wils:

INBO netwerk connecteren naar de nieuwe Read only databank van MercatorNet (= PostGIS databank). Daarin zitten een groot deel van de (vector)lagen van AIV (vroegere AGIV), maar ook van ANB – INBO – dep OMG (ex-dLNE+dRV) – OVAM – MOW – Erfgoed – VMM, ed.
Het gros van deze lagen is ook beschikbaar op onze S-schijf, maar dan in de vorm van compressed FileGDB's, dus niet zo handig voor niet-ArcGIS gebruikers.

Toegang tot deze databank is mogelijk via je milieuinfo account (diegene waarmee je inlogt op JIRA), maar je moet daarvoor wel een mail sturen naar help@milieuinfo.be met vraag om toegang tot de RO databank Mercator.

Als extra ondersteuning voor de gebruikers zijn er van elke dataset op de nieuwe databank connectie/legende bestanden aangemaakt (LYR en QLR). Deze zijn te vinden op de MercatorNet website: https://www.mercator.vonet.be/intern-ontsloten-data-lyr-qlr en zijn hier: https://github.com/MercatorNet/MercatorUpload in één keer te downloaden.

Meer info op deze website: https://www.mercator.vonet.be.

Voor de geïnteresseerden, de connectieparameters zijn als volgt:

• Database: mercator_ro_productie
• User: <milieuinfo user productie>
• Password: <eigen paswoord>
• Instance: 5613
• Hostname: db-pr.mercator.vonet.be

[peterdesmet]

Just tested DBI/odbc and rodbc for a project:

• RODBC: has better documentation.
• odbc: is faster, but can't find more documentation than the same README that is copy/pasted everywhere. Still, it is the technology that is used in the RStudio connections pane, so I agree we should use that rather than RODBC.

Best for INBO would be to provide examples that can be added to the connections pane in RStudio. Or bundle it into something that can easily be installed for everyone?

[florisvdh]

With respect to odbc and DBI, some information from two talks at useR!2017 can be found here. (google doc; will jump to the odbc talk; DBI is next)

[stijnvanhoey]

at https://inbo.github.io/tutorials/tutorials/r_database_access/ a introduction is provided about R access to databases

[florisvdh]

Woohaa! This rocks. I'll certainly test this further, for now I'll propose a few tweaks here or there.

[ElsLommelen]

Sorry for a late response, but I only now noticed this issue.

With respect to RODBC and DBI, our styleguide recommends to use RODBC to connect to SQL Server and Access, and DBI to connect to other databases. For me as a Windows user, this seems the right guideline to follow: the odbc-driver that is used with DBI for SQL Server on Windows causes a lot of difficulties with special characters. (Apparently Linux machines have a better driver and do not have these problems.)

As @peterdesmet already mentions: the documentation in RODBC is better, and to be honest: I think the error handling in DBI is terrible. Often I have to paste my query in SQL Server Management Studio to find out what is wrong.

For databases without any special characters, it could be ok to suggest to use DBI (e.g. advantage to use dbplyr), but otherwise I would recommend to test first in a Windows environment if all characters can be imported well. The majority of the INBO users has Windows, so I think choices should mainly be based on this OS.

[hansvancalster]

A way to circumvent problems with special characters, yet still use DBI is given in this function. The input parameter scient_name in that function often contains special characters. This parameter is passed on to glue_sql with {} and afterwards converted to the correct encoding with iconv before passing it to dbGetQuery(). The problem was solved by @Jo-Loos.

[ElsLommelen]

As you know, I knew this solution, and it works indeed for some special characters, but unfortunately not for all (e.g. characters for male, female, <= in one character,...). (The examples indeed have an alternative and can be replaced in the database, but there will probably pop up others without alternative that give the same problem.)

But what actually worries me, is that this all makes it more complicated for starting or occasional R programmers to use DBI. The whole process of connecting to a database and running a query is already complicated, and then they have to add another statement to translate these characters that are not presenting well,... Furthermore, we already searched for weeks to find this (partial) solution, they might already search for some time before they figure out it is not their mistake (which can be demotivating) and ask for a solution.

So my main concern was not if there is a solution, but: if we know there are some difficulties with DBI that will make things complicated for less experienced users and there is an alternative (RODBC), will we still recommend DBI for using with SQL Server? What is actually the advantage of using DBI over RODBC? Except for the presence of a connection pane in RStudio, I don't see any arguments to choose for DBI in the discussion of this issue.

So please convince me with some good arguments why DBI is prefered over RODBC.

(I don't know why @ThierryO mentioned RODBC as a recommended package for SQL Server in the styleguide. It seems he wanted to promote DBI (as he did for most database types), but for some reason he made an exception for SQL Server and Access.)

[hansvancalster]

I was just pointing to that solution because it matters a lot where in your code the conversion to the correct encoding is done. For instance, encoding the input parameter and then passing it to glue_sql will not work.

I don't have strong opinions, about DBI or RODBC, but I find exploring the database via the connection pane a big plus and it is considered the "best in class" package by rstudio.

[florisvdh]

Something else to consider here is that the tidyverse package dbplyr is designed to work with DBI connections (see next coding club).

[florisvdh]

Fast forward to 2020: we now have the inbodb package to address some challenges! It would be good to have a pointer to inbodb in the tutorials website, both in the database-R tutorial and in the overview article of INBO software. @ElsLommelen can you give your opinion? (you're best placed to implement this as well)

Also the INBOVEG tutorial will need some updating @ElsDeBie

Beside that, I think we should further investigate / specify the need for functionality for specific databases, i.e. database-specific queries and preprocessing through R functions. This relates to the second sub-part of this issue, i.e. "best practices using each specific database".

[ElsLommelen]

Some ideas for the database-R tutorial:

• one easy step to start with: replace inborutils by inbodb to guide users to the right database
• it could be useful to mention in this tutorial that for some databases (INBOVEG and Florabank), ready-to-use functions exist to query the database, with of course a reference to the INBOVEG tutorial
• setting up the connection yourself: for Windows users it is certainly useful to mention here to add parameter encoding = "latin1"
• certainly for Access we should do some further tests:
  • are special symbols and complex data types an issue as well in DBI/odbc?
  • should we add a specific function in inbodb that overcomes these problems (if possible), or mention RODBC as an alternative package, with some advantages and disadvantages for both? (Access provides possibilities to explore the database and compose queries as an alternative to the connection pane and dbplyr)
• maybe change the url https://inbo.github.io/tutorials/tutorials/r_database_access/ because the word access can be confusing as it mainly deals with SQL server?
• would it be useful to add a short note on other database types (postgresql, sqlite,...) and the fact that dedicated packages exist to make this connection?
• is there a tutorial on dbplyr, or should this be added here?
• the tutorial is rather long, which might make it difficult to look up specific information. Would it be an idea to split it in 2 (or 3) tutorials: one on a connection and one on queries, and maybe a third one as a general page that explains that one needs 2 steps: connection and queries.

INBO software: I'll add inbodb and a short description

I'll start working on a PR with the first idea for the database tutorial and the software and I'll wait on some more opinions for the other ideas. Maybe it would be better if someone with experience on it would add something on dbplyr (if necessary)?

Maybe it is a good idea to involve @hansvancalster in this discussion as well? (For the tutorials on Florabank and INBOVEG)