Initialize ltree extension

dbExecute(db, glue("create extension if not exists ltree"))
## [1] 0

Notes

PostgreSQL ltree documentation

Each element in the ltree path is called a label.

A label is a sequence of alphanumeric characters and underscores (for example, in C locale the characters A-Za-z0-9_ are allowed). Labels must be less than 256 characters long.

Examples: 42, Personal_Services

A label path is a sequence of zero or more labels separated by dots, for example L1.L2.L3, representing a path from the root of a hierarchical tree to a particular node. The length of a label path cannot exceed 65535 labels.

Example: Top.Countries.Europe.Russia

Caleb’s hack way of ingesting a CSV and doing string replacement

Cant use the following characters for ltree data type:

# List of source CSVs that contain management measure categories
data <- list(
  "receptor"   = "https://raw.githubusercontent.com/marineenergy/apps/master/data/ferc_lookup_receptor.csv",
  "stressor"   = "https://raw.githubusercontent.com/marineenergy/apps/master/data/ferc_lookup_stressor.csv",
  "technology" = "https://raw.githubusercontent.com/marineenergy/apps/master/data/ferc_lookup_technology.csv")
# ,"phase" = "https://raw.githubusercontent.com/marineenergy/apps/master/data/ferc_tags_phase_lut.csv"

d <- tibble(
  category = names(data),
  csv      = unlist(data)) %>% 
  mutate(
    data = purrr::map(
      csv, 
      function(x){
        read_csv(x, col_types = cols()) %>% 
          select(tag0 = 1)})) %>% 
  unnest(data) %>% 
  mutate(
    tag = glue("{str_to_title(category)}.{tag0}") %>% 
      str_replace_all("[^A-Za-z0-9_.]", ""),
    label = tag0 %>% 
      str_replace(".*\\.(.+)$", "\\1"),
    level = tag %>% 
      str_count("\\.")) %>% 
  select(
    tag, label, level) 
#    x_cat = category, x_tag = tag0, x_csv = csv)

# Create database table
dbExecute(db, glue("DROP TABLE IF EXISTS tags CASCADE"))
## [1] 0
dbExecute(db, glue("CREATE TABLE tags (tag LTREE PRIMARY KEY, label VARCHAR, level INT4)"))
## [1] 0
dbWriteTable(db, "tags", d, overwrite = F, append = T, row.names = F)

tbl(db, "tags") %>% 
  collect() %>% 
  datatable()

Query ltree

@ Match case-insensitively, for example a@ matches A

* Match any label with this prefix, for example foo* matches foobar

% Match initial underscore-separated words

Inheritance

Example 1

dbGetQuery(db, glue("SELECT * FROM tags WHERE tag ~ '*.Birds.*'")) %>%
# dbGetQuery(db, glue("SELECT * FROM tags WHERE tag @ '*Birds*'")) %>%
  datatable()

Example 2

dbGetQuery(db, glue("SELECT * FROM tags WHERE tag <@ 'Receptor.Birds'")) %>%
  datatable()

Example 3

dbGetQuery(db, glue("SELECT * FROM tags WHERE tag @ 'GroundNestingBirds'")) %>%
  datatable()

Example 4

dbGetQuery(db, glue("SELECT * FROM tags WHERE tag = 'Receptor.Birds'")) %>%
  datatable()

Conclusion

Unfortunately, we cant query ltree without doing string replacement of the selected management measure.

For example, if we are looking for receptor Ground-Nesting Birds, we will need to do something like this: ... where receptor @ Ground_Nesting_Birds