Map information in database

We will query some information regarding map versions and metadata that is stored in our internal database.

Set up work directory

We use library here to define relative paths

here::i_am("how2/additional-map-details.qmd")

here() starts at /Users/z3529065/proyectos/typology-website/typology-map-info

Database

Database connection

We store databse credentials in the _environment.local file in the project directory. See file _environment.required for the format.

library(dplyr)
library(stringr)
library(tidyr)
library(lubridate)
library(readr)
library("RPostgreSQL")

drv <- dbDriver("PostgreSQL") ## remember to update .pgpass file

con <- dbConnect(drv, 
                 dbname = Sys.getenv("DBNAME"),
                 host = Sys.getenv("DBHOST"),
                 port = Sys.getenv("DBPORT"),
                 user = Sys.getenv("DBUSER"))

Queries

Biome names

This information is actually missing from the profile information workbook:

qry <- "select biome_code, name from biomes;"
biome_info <- dbGetQuery(con,qry)
write_csv(biome_info, file = here::here("gisdata","profiles","biome-names.csv"))

qry <- "
SELECT code, map_code, map_version, license 
FROM map_evaluation 
LEFT JOIN map_metadata 
USING(map_code, map_version) 
WHERE status = 'valid'
AND map_type = 'Indicative Map'
ORDER BY license, map_code;
"
maps_licenses <- dbGetQuery(con,qry)

maps_licenses |> 
  mutate(biome = str_extract(code, "[MFST0-9]+")) |> 
  group_by(biome, license) |> 
  summarise(total = n_distinct(code), .groups = 'drop') |> 
  pivot_wider(names_from=license,values_from = total) |>
  DT::datatable()

Close connection

dbDisconnect(con)

[1] TRUE