fetch tethys

source("functions.R") # connection object

library(jsonlite)
library(DT)
library(tidyjson)
library(dplyr)
library(purrr)
library(tibble)
library(listviewer) # devtools::install_github('timelyportfolio/reactR')

tethys_url  <- "https://tethys.pnnl.gov/api/primre_export?modifiedDate=2020-06-01"
tethys_json <- "data/tethys.json"
download.file(tethys_url, tethys_json)
tethys     <- read_json(tethys_json)

tethys_content <- tethys[["..JSON"]][[1]] 
# reactjson(tethys_content[1:20])

Create table

Run once:

sql <- glue("
  CREATE TABLE tethys_pubs (
      uri text NOT NULL PRIMARY KEY,
      data json NOT NULL
  );")
dbExecute(con, sql)

Update table

tethys_uris <- map_chr(tethys_content, "URI")
tethys_data <- map_chr(tethys_content, toJSON) %>% 
  str_replace_all("'","''")

tibble(
  uri = tethys_uris,
  data = tethys_data) %>% 
  write_csv("data/tethys.csv")

dbExecute(con, "DELETE FROM tethys_pubs;")

Run once to install software and test connection to database:

sudo apt-get update
sudo apt-get install postgresql-client

# test
psql -h postgis -p 5432 -U admin gis

# use this password when prompted
cat /share/.password_mhk-env.us

path_csv='/share/github/apps/data/tethys.csv'
cat $path_csv | psql -h postgis -p 5432 -U admin -c "COPY tethys_pubs (uri, data) FROM STDIN WITH (FORMAT CSV, HEADER TRUE);" gis

Query based on tag

tag = "Changes in Flow"

res <- dbGetQuery(
  con, glue("
  SELECT * from (
    SELECT 
      uri, 
      data ->'title' ->> 0 AS title,
      json_array_elements(data->'tags') ->> 0 as tag_text
    FROM tethys_pubs) q
  WHERE q.tag_text = '{tag}';"))

datatable(res)