Accès à une BDD PostgreSQL

Cette page décrit la procédure pour se connecter à une base de données PostgreSQL

Paramètres

Pour vous connecter à une base de données, vous allez avoir besoin de connaître les paramètres suivants:

• • host**: par exemple "blabla.fr"
  • port**: par exemple 5440
  • database**: par exemple "gougoudb"
  • user**: par exemple "gougougaga"
  • password**: par exemple "fqsdfa_76864!_zer5678"

Ces paramètres vous seront fournis par l'administrateur de la base de données (probablement Samuel!).

En fonction de ces paramètres, vos droits sur la base de données peuvent être différents (droits en lecture seule, ou en lecture/écriture)

Contraintes

Pour accéder à la base de données, vous aurez besoin d'être sur le réseau ENS (soit physiquement à l'ENS, soit passer par le VPN)

Accéder à la base de données depuis QGIS

Il faut configurer la base de données en renseignant les paramètres, et choisir un mot de passe maître sur QGIS (qui servira rarement, à l'installation de QGIS uniquement).

Pour cela, aller dans l'explorateur => PostgreSQL => clic droit Nouvelle Connexion.

Pour voir le contenu de la BDD, aller dans l'onglet Base de Données => DB Manager

Si vous souhaitez visualiser des couches dans votre projet: choisissez les dans l'explorateur => PostgreSQL => BDD => public puis choisir la table et clic droit => Ajouter la couche au projet.

Depuis R

Dans R, il faudra enregistrer les paramètres de connexion dans le fichier .Renviron (qui est situé assez "bas" dans l'arborescence de fichiers, peut-être dans votre Home).

Si vous ne connaissez pas son emplacement, vous pouvez utiliser la fonction R suivante qui vous ouvrira le fichier automatiquement, quel que soit son emplacement.

usethis::edit_r_environ()

Vous allez y enregistrer quelque chose qui ressemble à ceci (ici par exemple pour accéder à la BDD glourb, évidemment avec de fausses valeurs pour les paramètres...):

GLOURB_HOST="blabla.fr" 
GLOURB_PORT="5440" 
GLOURB_NAME="glourb" 
GLOURB_USER_APP="gougougaga" 
GLOURB_PASS_APP="fqsdfa_76864!_zer5678"

Vous pourrez ensuite créer une connexion "conn" à la BDD à travers la commande suivante

  conn <- DBI::dbConnect(RPostgres::Postgres(),
                         host = Sys.getenv("GLOURB_HOST"),
                         port = Sys.getenv("GLOURB_PORT"),
                         dbname = Sys.getenv("GLOURB_NAME"),     
                         user = Sys.getenv("GLOURB_USER_APP"),
                         password = Sys.getenv("GLOURB_PASS_APP"))

Cette connexion vous servira alors à accéder aux données. Si la table est de dimension modeste (par exemple sur la base de données GloUrb, wikidata_table qui a environ 40 000 lignes et des colonnes "légères"), vous pouvez la lire directement de cette manière

DBI::dbReadTable(name="wikidata_table",conn=conn)

ou pour une table comprenant des géométries:

sf::st_read(dsn=conn,layer="StudyArea_reach_zone")

Pour des tables plus volumineuses, il peut être pertinent de réaliser un filtre (en SQL) avant de récupérer la donnée (voir section suivante).

Quelques fonctions utiles en R pour interagir avec la BDD glourb

Pour interagir avec la BDD glourb depuis R, quelques fonctions utiles sont disponibles dans le package glourbi, installable via la commande suivante:

devtools::install_github("lvaudor/glourbi")

Les identifiants uniques de villes dans la BDD glourb sont sous forme de citycode (par exemple "596477_20972" pour Lyon).

On peut passer du nom de ville au citycode et inversement par les fonctions suivantes:

glourbi::to_citycode("Lyon") # renverra "596477_20972"
glourbi::to_cityname("596477_20972")  # renverra "Lyon"

La fonction glourbi::connect_to_glourb() permet de créer une connexion à la base de données. On peut l'appeler de cette façon (une fois que les paramètres d'accès à la BDD sont enregistrés dans .Renviron)

conn=glourbi::connect_to_glourb()

Les fonctions suivante (du package glourbi, disponible ici :https://github.com/lvaudor/glourbi ) permettent de filtrer les lignes relatives à une ville donnée dans une table(on lui fournit un citycode en entrée).

Pour une table sans géométries

get_city_tib <- function (name, thisCityCode, conn) 
{
    sql <- glue::glue("SELECT * FROM {name} WHERE citycode LIKE ?thisCityCode")
    query <- DBI::sqlInterpolate(conn, sql, thisCityCode = thisCityCode)
    result <- DBI::dbGetQuery(conn = conn, statement = query)
    return(result)
}

et pour une table avec géométrie:

get_city_sf <- function (name, thisCityCode, conn) 
{
    sql <- glue::glue("SELECT * FROM {name} WHERE citycode LIKE ?thisCityCode")
    query <- DBI::sqlInterpolate(conn, sql, thisCityCode = thisCityCode)
    result <- sf::st_read(dsn = conn, query = query)
    return(result)
}

On peut donc s'en servir de la manière suivante:

conn=connect_to_glourb()
gsw_patches_Denver=get_city_sf(name="gsw_patches",
                               thisCityCode=glourbi::to_citycode("Denver-Aurora"),
                               conn=conn)

txt_page_Douala=get_city_tib(name="txt_page",
                             thisCityCode=glourbi::to_citycode("Douala"),
                             conn=conn)