Accès aux bases Vigie-Nature avec R
Simon Bénateau, Charles Thévenin, Martin Jeanmougin, Mathilde Vimont
07/02/2022
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.
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 objectsOn 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 211Query_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 0Quelques 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()
Maintenant, nous pouvons faire un suivi sur plusieurs années.
# 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
GROUP BY users_date.month_created, users_date.year_created"
resultats_users <- import_from_mosaic(query, database_name = "spgp", force_UTF8 = TRUE)Il est possible de changer les couleurs en fonction d’une variable qui n’est pas dans les x ou y et de mettre des boites de dispersion ou à moustaches (comme Martin ;-) ) ou de faire un graph par an.
# faire un graph avec des boxplots pour la participation par mois
ggplot(resultats_users, aes(x = month_created,
y = nombre_inscriptions,
group = month_created)) +
geom_boxplot() +
geom_point(aes(color = year_created)) +
labs(x="Nombre d'inscriptions", y="Mois d'inscription", col = "Mois, Ahhhhhh ouai mais non")
# faire un graph avec des boxplots pour la participation par mois
ggplot(resultats_users, aes(x = month_created,
y = nombre_inscriptions)) +
geom_line(aes(color = factor(year_created))) +
geom_point(aes(color = factor(year_created))) +
facet_wrap(~year_created) +
labs(x="Nombre d'inscriptions", y="Mois d'inscription", col = "Année")
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 2019Maintenant, 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_mapIl est possible d’ajouter de l’infomation sur la carte :
library(leaflet)
# add markers
# etab_map <- leaflet(geo_participation_spipoll) %>%
# addTiles()%>%
# addMarkers(clusterOptions = markerClusterOptions(), icon = icone_spipoll, popup_text = geo_participation_spipoll$date)
# etab_mapIl est aussi possible de faire des filtres simples :
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 ;-) ).