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é

Les fonctions suivante (du package glourbi, disponible ici :https://github.com/lvaudor/glourbi ) permettent de réaliser cette opération en filtrant les lignes relatives à une ville donnée (on lui fournit un citycode).

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)
}