fetch_tethys-primre-export.Rmd

---
title: "fetch tethys"
output: html_document
editor_options: 
  chunk_output_type: console
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE, eval = F, message = F, warning = F)
```

```{r get db connection, eval = T}
source("functions.R") # connection object

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

```{r read json}
#today <- Sys.Date()
#tethys_url  <- glue("https://tethys.pnnl.gov/api/primre_export?modifiedDate={today}")
tethys_url  <- glue("https://tethys.pnnl.gov/api/primre_export")
tethys_json <- "data/tethys.json"
if (!file.exists(tethys_json))
  download.file(tethys_url, tethys_json)
tethys <- read_json(tethys_json)

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

## Create table

Run once:

```{r create tbl, eval = F}
sql <- glue("
  CREATE TABLE tethys_pubs (
	  uri text NOT NULL PRIMARY KEY,
	  data json NOT NULL
  );")
dbExecute(con, sql)
```

## Update table

```{r json to csv}
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")
```

```{r clear tethys_pubs}
dbExecute(con, "DELETE FROM tethys_pubs;")
```

Run once to install software and test connection to database:

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

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

```bash
# 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
```


## Update tables for easier querying

```{r}
source("functions.R")

pubs <- dbGetQuery(
  con, 
  "SELECT 
     uri, 
     data -> 'title'         ->> 0  AS title, 
     data -> 'tags'                 AS tags, 
     data -> 'technologyType'       AS technologyType
   FROM tethys_pubs") %>% 
  arrange(uri) %>% 
  tibble()
pubs # 6,484 rows
pubs %>% head(10) %>% View()

# TODO: evaluate counts of tags, esp. "Environment"

pub_tags <- dbGetQuery(
  con, 
  "SELECT 
     uri, 
     json_array_elements(data->'tags')           ->> 0 as tag
   FROM tethys_pubs
  UNION
   SELECT
     uri, 
     json_array_elements(data->'technologyType') ->> 0 as tag
   FROM tethys_pubs") %>% 
  arrange(uri, tag) %>% 
  tibble()

dbWriteTable(con, "tethys_pub_tags", pub_tags)
pub_tags # 14,505 rows
pub_tags # 16,034 rows after UNION

pub_tech <- dbGetQuery(
  con, 
  "SELECT
     uri, 
     json_array_elements(data->'technologyType') ->> 0 as tag
   FROM tethys_pubs") %>% 
  arrange(uri, tag_tech) %>% 
  tibble()
pub_tech



tethys_pub_tags

pubs_without_tags <- setdiff(pubs %>% select(uri), pub_tags %>% select(uri))
pubs_without_tags # 0 rows
pubs_without_tech <- setdiff(pubs %>% select(uri), pub_tech %>% select(uri)) %>% 
  left_join(
    pubs %>% 
      select(uri, title, tags)) %>% 
  arrange(desc(title))
pubs_without_tech # 5,158 rows
View(pubs_without_tech)
```


## Query based on tag

```{r query tags, eval = T}
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)
```