Réalisé avec l’aide de Pablo Pietro et Diane Gonzalez pour les accès aux bases de données

Prérequis

Installation des logiciels

Pour cet atelier, nous aurons besoin de R et RStudio. R c’est la machine qui calcule, le langage de programmation et RStudio, c’est pour nous aider à coder, organiser les sorties (les grahiques par exemple dans un seul endroit). Pour les téléchager c’est par là attention à l’ordre, il faut installer R en premier :

Pour R

  • Télécharger R
  • Choisir votre système d’exploitation (probablement windows), puis base ou install R for the first time
  • Gardez les paramètres par défaut pour l’installation c’est parfait.

Pour RStudio

Si vous avez déjà R, cela peut-être une bonne idée de le mettre à jour quand même et donc de télécharger la nouvelle version, pareil pour Rstudio.

Vous pouvez maintenant ouvrir RStudio et nous allons entrer dans le vif du sujet.

Comment faire pour utiliser ce tutoriel

Ce document combine des informations (comme cette phrase) et du code (comme le bloc gris juste en dessous). Les inscriptions en marron et qui commencent par un # ne sont pas lues par R et sont là pour vous donner des indices. Le reste, c’est les instructions. Pour que cet atelier soit profitable, il faut que vous colliez les blocs de code dans la console ou un script et que vous l’exécutiez (si vous ne comprenez pas comment faire, nous ferons ça ensemble). Enfin en dessous un cadre blanc vous montre les « sorties », c’est à dire ce qu’R a calculé/représenté/compris (ou pas compris parfoit ;-) ).

# un commentaire pour vous dire que la prochaine ligne va fait un truc :
cat("Bonjour vous !\n\nBienvenue dans cet atelier sur les données.\n")
## Bonjour vous !
## 
## Bienvenue dans cet atelier sur les données.

Installation et chargement des packages

Pour interroger les bases, nous allons utiliser le packages RMySQL. Qu’il faut installer une fois. Ensuite vous pourrez le charger simplement.

Nous allons également en profiter pour installer le reste des packages nécessaires.

# installer les packages s'ils ne le sont pas déjà
packages <- c("RMySQL", "dplyr", "lubridate", "ggplot2", "sf", "tmap", "leaflet")
packages_to_install <- packages[!packages %in% installed.packages()]
if(length(packages_to_install) > 0) install.packages(packages_to_install)

# charger le package
library(RMySQL)    # to query mysql databases
library(dplyr)     # for data manipulation
library(lubridate) # for time and date management
library(ggplot2)   # for plots
library(tmap)      # for static and interactive maps
library(leaflet)   # for interactive maps
library(sf)        # for spacial objects

On voit que le package DBI a été chargé, c’est le package DataBase Interface qui est commun à tous les packages du genre. RMySQL traite les subtilités liées à MySQL. Il existe d’autres packages pour interroger les autres types de bases.

Se connecter à la base

Pour se connecter à la base, il faut avoir des identifiants de connection. Il suffit ensuite de les remplacer dans le code qui suit. On donne donc à ce moment toutes les informations pour que l’on puisse se connecter. C’est Pablo Prieto de Mosaic qui m’a donné les paramètres et pour avoir vos identifiants, vous pouvez demander à Diane Gonzalez. Je rappelle également qu’il faut une connexion internet fonctionnelle sur le résau du mnhn ou en VPN. Pour obtenir un VPN ça marche plutôt bien avec la DSI.

# Informations de connection
db_user <- 'user_name' # votre nom d'utilisateur
db_password <- 'user_mdp' # votre mot de passe
db_name <- 'spgp' # le nom de la base à interroger (espaces_verts, oab, birdlab...)
db_host <- '192.168.40.132' # l'adresse de la base
db_port <- 3308 # le port 

# Connection à la base de données
mydb <-  dbConnect(MySQL(), user = db_user, password = db_password,
                   dbname = db_name, host = db_host, port = db_port)

Requêter dans la base

Requête sous forme de texte

Ensuite il faut envoyer une requête sur le serveur. C’est lui qui va travailler et il faut « seulement » que la requête fonctionne puis récupérer potentiellement beaucoup de données. Si elle est simple, vous pouvez le faire en l’écrivant dans une chaine de caractères :

# la requête est stockée dans un objet
query <- "select temperatureID from spipoll_participation"
# la requête est envoyée en utilisant les parametres rentrés plus haut
raw_query_result <- dbSendQuery(mydb, query)
# les résultats brut sont "traduits" en data.frame
query_result <-  fetch(raw_query_result, n = -1, )
# on ferme la connection avec la base de données
on.exit(dbDisconnect(mydb))

# afficher les premières lignes du résultat
head(query_result)
##   temperatureID
## 1           211
## 2           211
## 3           211
## 4           211
## 5           211
## 6           211

Query_result est un dataframe qui contient les résultats de votre requête.

Pour le moment je n’arrive pas à importer des geopoints mais on va pouvoir y réfléchir un peu.

Requête sous forme de fichier et utilisation de fonctions

Lorque la requête est complexe. Il est compliqué de la faire tenir dans une chaine de caractère et de la tester. De plus, il est quand même plus agréable d’écrire les requêtes dans DBeaver par exemple pour bénéficier de l’autocomplétion. Seul petit bug jusqu’à présent, il faut faire attention à ce qu’il y a une ligne vide pour finir (ou alors c’est moi qui bug ce qui est aussi possible). Il faut aussi enlever la ligne USE database; qui est utile dans DBeaver puisqu’ici nous précisons systématiquement la base de données avec l’argument database_name.

Pour cela on va avoir besoin de la petite fonction qui se trouve ci-après. Vous pouvez aller voir dans le fichier qui se trouve dans le dossier fonction “function_import_from_mosaic.R” à quoi cela ressemble et si vous voulez améliorer les choses… J’ai aussi fais une fonction qui permet de n’avoir à préciser que la requête et la base de données et fais tout ce qui est noté plus haut en une seule étape.

# importation des fonctions à partir du fichier
source("fonctions/function_import_from_mosaic.R")
# transformation de la requete pour qu'elle soit lue
query <- read_sql_query("example_file/extract_example_propage.sql")
# importation des données
resultats_propage <- import_from_mosaic(query, database_name = "espaces_verts", force_UTF8 = TRUE)
head(resultats_propage[, 1:16])
##            structure            nom_espace_vert nom_transect       date
## 1 Mairie de Grenoble site n°1741 Geoffroy David   GRECIM0100 2020-06-16
## 2 Mairie de Grenoble site n°1741 Geoffroy David   GRECIM0100 2020-08-11
## 3 Mairie de Grenoble site n°1741 Geoffroy David   GRECIM0300 2020-08-11
## 4 Mairie de Grenoble site n°1741 Geoffroy David   GRECIM0200 2020-08-11
## 5 Mairie de Grenoble site n°1741 Geoffroy David   GRECIM0100 2020-07-06
## 6 Mairie de Grenoble site n°1741 Geoffroy David   GRECIM0300 2020-07-06
##      debut      fin                        temp
## 1 11:27:00 11:37:00 propage.temperature.21-25°c
## 2 11:30:00 11:35:00 propage.temperature.26-30°c
## 3 11:17:00 11:23:00 propage.temperature.26-30°c
## 4 11:00:00 11:14:00 propage.temperature.26-30°c
## 5 10:52:00 10:59:00 propage.temperature.21-25°c
## 6 10:52:00 10:59:00 propage.temperature.21-25°c
##                                 vent                      couverture_nuageuse
## 1 propage.vent.2-beaufort-(6-11km/h) propage.couvertureNuageuse.nuages-26-50%
## 2    propage.vent.0-beaufort-(0km/h)  propage.couvertureNuageuse.nuages-0-25%
## 3    propage.vent.0-beaufort-(0km/h)  propage.couvertureNuageuse.nuages-0-25%
## 4    propage.vent.0-beaufort-(0km/h)  propage.couvertureNuageuse.nuages-0-25%
## 5    propage.vent.0-beaufort-(0km/h) propage.couvertureNuageuse.nuages-26-50%
## 6    propage.vent.0-beaufort-(0km/h) propage.couvertureNuageuse.nuages-26-50%
##   user_id participation_id robert-le-diable myrtil procris pierides-blanches
## 1     374            10816                0      0       0                 7
## 2     374            11071                0      0       0                 0
## 3     374            11072                0      0       0                 0
## 4     374            11073                0      0       0                 0
## 5     374            11238                0      0       0                 0
## 6     374            11239                0      0       0                 0
##   amaryllis
## 1         0
## 2         0
## 3         0
## 4         0
## 5         0
## 6         0

Quelques commandes utiles en plus :

Si la connection à la base reste ouverte, utiliser la commande dbClearResult(dbListResults(mydb)[[1]]) permet de reprendre la main, notamment lorsque vous avez ce genre de message d’erreur :

Error in .local(conn, statement, ...) : connection with pending rows, close resultSet before continuing

Des exemples d’utilisation avec R

faire un graphique rapide

Comme précédement, nous allons commencer par installer les packages nécessaires à la réalisations des graphiques. Ici nous allons utiliser dplyr pour faire un peu de manipulation de données, lubridate pour traiter les dates et ggplot2 pour faire des graphs. Il y a de nombreuses façon de faire les choses avec R et ma méthode ne sera pas toujours la plus simple ou celle qui vous convient le mieux.

Ensuite il s’agit de lancer une requête comme nous l’avons vu plus haut en détail. Ici nous sélectionnons les dates de création et les ID des users dans la table user.

# Requete qui compte 
query <- "SELECT 
    COUNT(users_date.id) AS nombre_inscriptions,
    month_created,
    year_created
FROM (SELECT 
    u.id,
    MONTH (u.created) AS month_created, 
    YEAR (u.created) AS year_created
FROM users u) users_date
WHERE users_date.year_created  = '2021'
GROUP BY users_date.month_created"

resultats_users <- import_from_mosaic(query, database_name = "spgp", force_UTF8 = TRUE)

ggplot permet d’ajouter des “couches”. On dit d’abord ce que l’on veut utiliser comme jeu de données (ici notre extraction), puis les x et les y, puis si on veut des points, des lignes, les deux.

# graph avec des points
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  geom_point()

# graph avec des lignes
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  geom_line()

# graph avec des points et des lignes
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  geom_point() +
  geom_line()

Attention à l’ordre des couches. Ici nous ajoutons de la couleur pour voir ce qu’il se passe :

# graph avec des points et des lignes
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  geom_point(color = "red") +
  geom_line()

Nous avons déjà un très joli graph, toutefois, il est possible de faire beaucoup beaucoup de choses avec ggplot2.

# faire un graph des jolies légendes
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  
  geom_line() +
  geom_point(color = "red") +
  labs(x="Nombre d'inscriptions", y="Mois d'inscription")

# Mettre de plus jolis noms de mois
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  
  geom_line() +
  geom_point(color = "red") +
  labs(x="Nombre d'inscriptions", y="Mois d'inscription") +
  scale_x_continuous(breaks = seq(1,12,1))

# forcer un départ de l'axe des y à zéro pour ne pas tromper le lecteur
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  
  geom_line() +
  geom_point(color = "red") +
  labs(x="Nombre d'inscriptions", y="Mois d'inscription") +
  scale_x_continuous(breaks = seq(1,12,1)) +
  expand_limits(y = 0)

# utiliser un thème un peu plus minimaliste
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  
  geom_line() +
  geom_point(color = "red") +
  labs(x="Nombre d'inscriptions", y="Mois d'inscription") +
  scale_x_continuous(breaks = seq(1,12,1)) +
  expand_limits(y = 0) +
  theme_minimal()

# Faire dépendre la couleur d'une valeur
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  
  geom_line() +
  geom_point(aes(color = month_created)) +
  labs(x="Nombre d'inscriptions", y="Mois d'inscription") +
  scale_x_continuous(breaks = seq(1,12,1)) +
  expand_limits(y = 0) +
  theme_minimal()

# Donc on réadapte la légende
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  
  geom_line() +
  geom_point(aes(color = month_created)) +
  labs(x="Nombre d'inscriptions", y="Mois d'inscription", col = "Mois") +
  scale_x_continuous(breaks = seq(1,12,1)) +
  expand_limits(y = 0) +
  theme_minimal()

# Pour avoir des couleurs un peu plus différente et pour montrer que ça marche avec des catégorie
ggplot(resultats_users, aes(x = month_created, 
                            y = nombre_inscriptions)) +
  
  geom_line() +
  geom_point(aes(color = factor(month_created))) +
  labs(x="Nombre d'inscriptions", y="Mois d'inscription", col = "Mois") +
  scale_x_continuous(breaks = seq(1,12,1)) +
  expand_limits(y = 0) +
  theme_minimal()

Initialement, j’avais fait cette partie en R, toutefois, c’est beaucoup plus rapide en SQL (la différence n’est toutefois pas perceptible pour un humain étant donné la vitesse de calcul). Je vous laisse les deux syntaxes pour vous montrer que c’est possible de faire la même chose de plusieurs façons différentes. Nous nous concentrerons cependant surtout sur les fonctionnalités graphiques de R.

query <- "select u.created, u.id from users u"
resultats_users <- import_from_mosaic(query, database_name = "spgp", force_UTF8 = TRUE)

# ajouter deux colonnes en année / mois
resultats_users <- resultats_users %>%
  mutate(year_created = year(created),
         month_created = month(created))

# regrouper les résultats par années et par mois et compter le nombre de participants
resultats_users_summarised <- resultats_users %>%
  group_by(month_created, year_created) %>%
  summarise(nombre_inscriptions = n(), .groups = "drop")

# faire un graph avec des boxplots pour la participation par mois
ggplot(resultats_users_summarised, aes(x = month_created, 
                                       y = nombre_inscriptions, 
                                       group = month_created)) +
  geom_boxplot() +
  geom_point(aes(color = year_created)) +
  ylab("Nombre d'inscriptions") +
  xlab("Mois d'inscription")

faire une simple courbe de variation temporelle d’abondance

# Bourdons : requete pour obtenir l'abondance totale mensuelle par espece en 2021
query <- "SELECT *
FROM
(select 
sum(nbTaxons) as abondance_totale,
year(created) as annee,
month(created) as mois,
t.value as nom_espece
from spgp.bj_observation bo
INNER JOIN spgp.thesaurus t on t.id = bo.taxonId
GROUP BY annee, mois, nom_espece) T1
WHERE annee = 2021"
abondance_bourdons <- import_from_mosaic(query, database_name = "spgp", force_UTF8 = TRUE)

#filtrer les donnees pour recuperer une espece d'interet
abondance_bourdon_des_pierres <- abondance_bourdons%>%
  filter(nom_espece == "Bourdon des pierres")

#graphique de la variation mensuelle d'abondance
ggplot(data = abondance_bourdon_des_pierres,
       aes(x = mois,
           y = abondance_totale))+
  geom_line(size=1)+
  xlab("Mois")+
  ylab("Abondance totale")+
  scale_x_continuous(breaks = seq(1,12,1))+
  theme_bw()

faire une carte des dernières participations

Pour faire une carte, c’est aujourd’hui très simple avec R (dans le sens où il y a quelques années c’était compliqué). On distingue plusieurs types de carte. Les cartes statiques et les cartes dynamiques. Les cartes statiques peuvent être imprimées les cartes dynamiques non mais elles permettent d’ajouter de l’information en plus (zoom, info-bulles, etc…).

Cartes statiques

Nous allons commencer par une requête où l’on récupère des données spatiales. Ici, longitude / latitude des collections spipoll.

query <- "SELECT
  sp.id,
  sp.`date`,
  ST_X(sp.geopoint) as longitude,
  ST_Y(sp.geopoint) as latitude,
  YEAR (sp.`date`) AS annee
FROM spipoll_participation sp"

localisation_participation_spipoll <- import_from_mosaic(query, database_name = "spgp")

head(localisation_participation_spipoll)
##   id                date longitude latitude annee
## 1  1 2019-06-12 00:00:00  6.514947 45.36381  2019
## 2  2 2019-06-20 00:00:00  2.358313 48.84498  2019
## 3  3 2019-06-12 00:00:00  4.568069 44.71102  2019
## 4  4 2019-02-21 00:00:00 -3.183325 47.67948  2019
## 5  5 2019-01-01 00:00:00  5.871208 43.29111  2019
## 6  6 2019-01-01 00:00:00 -1.693481 48.22129  2019

Maintenant, nous pouvons les projeter sur une carte.

# importer un fond de carte
carte_france = read_sf("maps/metropole-version-simplifiee.geojson")
# transformation en objet spatial (on explique quelles sont les colonnes)
geo_participation_spipoll <- st_as_sf(localisation_participation_spipoll, coords = c("longitude", "latitude"))
# choix de la projection (très important c'est tout un sujet en réalité)
st_crs(geo_participation_spipoll) <- 4326

spipoll_map = tm_shape(carte_france) + tm_polygons() + # ajout du fond de carte
  tm_shape(geo_participation_spipoll) + tm_dots() # ajout des points
#   tm_shape(nz_height) + tm_symbols(shape = 2, col = "red", size = 0.1) + 
#   tm_shape(nz_region) + tm_borders(lwd = 3) 

spipoll_map

Il est possible de faire des cartes qui résument un peu plus les données, par département par exemple.

# importation de la carte des départements
carte_departement = read_sf("maps/departements-version-simplifiee.geojson")

# extraire le nom des département potentiellement un long calcul
result <- st_within(geo_participation_spipoll, carte_departement, sparse = FALSE)
carte_departement <- carte_departement %>%
  mutate(nombre_collections = apply(result, 2, sum))

spipoll_map <- tm_shape(carte_departement) +  tm_fill(col = "nombre_collections", n = 10) + tm_borders()

spipoll_map

Et il est également possible de superposer les informations :

spipoll_map <- tm_shape(carte_departement) +  tm_fill(col = "nombre_collections", n = 10) + tm_borders()  +
  tm_shape(geo_participation_spipoll) + tm_dots(alpha = .2)
spipoll_map

Cartes intéractives

Ici nous vous proposons de reproduire la carte qui se trouve sur le site du spipoll, mais avec toutes les participations.

# ajouter une icone
icone_spipoll <- makeIcon(iconUrl = "~/Desktop/pinspipoll.png")

# add markers
etab_map <- leaflet(geo_participation_spipoll) %>%
  addTiles()%>%
  addMarkers(clusterOptions = markerClusterOptions(), icon = icone_spipoll)
etab_map

Il est possible d’ajouter de l’infomation sur la carte :

library(leaflet)

# ajouter une icone
icone_spipoll <- makeIcon(iconUrl = "~/Desktop/pinspipoll.png")
print(colnames(geo_participation_spipoll))
## [1] "id"       "date"     "annee"    "geometry"
# add markers
# etab_map <- leaflet(geo_participation_spipoll) %>%
#   addTiles()%>%
# addMarkers(clusterOptions = markerClusterOptions(), icon = icone_spipoll, popup_text = ~date)
# etab_map

Il est aussi possible de faire des filtres simples :