update_datasets-sitemap.qmd

---
title: "Generate a datasets sitemap.xml for ODIS crawling"
editor: visual
editor_options: 
  chunk_output_type: console
format:
  html:
    code-fold: true
---

**Goal**: Generate `datasets/sitemap.xml` with datasets already in authoritative repositories with JSON-LD content for ODIS crawling.

-   Tracking in Github issue(s):
    -   [register datasets with ODIS (using JSON-LD) · Issue #24 · CalCOFI/workflows](https://github.com/CalCOFI/workflows/issues/24)
-   Techniques:
    -   Use RESTful based APIs directly where possible. Avoid web scraping since most brittle to website changes. Avoid custom R packages (e.g., [`rerddap`](https://docs.ropensci.org/rerddap/) or [`rdataone`](https://github.com/DataONEorg/rdataone) for EDI) since have more R package dependencies, may be out of date and add unnecessary complexity.
    -   Store CSV tables of repositories and datasets gleaned as snapshot in Github along with this report output as html.

## Read GoogleSheet of repos

Columns from original GoogleSheet:

-   `repo`: repository name
-   `link`: url to repository

Extra columns added by this script:

-   `to_ds`: custom function to fetch datasets from repository
-   `status`: check if repository is accessible (`OK`) or not (`Not Found`); optionally set in code with `ck_status <- T`

```{r}
# libraries ----
librarian::shelf(
  curl, dplyr, DT, here, httr2, glue, googlesheets4, janitor, knitr, purrr, 
  readr, stringr, tidyr)
options(readr.show_col_types = F)

# variables ----
d_gs      <- "https://docs.google.com/spreadsheets/d/1uhviF2ecfOqGaSbC_JE8B5jPRqqjMaFc9TNCK_m297c/edit?gid=1271784325#gid=1271784325"
d_csv     <- here("datasets/repo_links.csv")
ds_csv    <- here("datasets/repo_datasets.csv")
dsi_csv   <- here("datasets/repo_datasets_info.csv")
dss_csv   <- here("datasets/repo_datasets_summary.csv")
sm_xml    <- here("datasets/sitemap.xml")
ck_status <- F

# helper functions ----
erddap_ds <- function(link){
  # link = d$link[3]
  
  x <- link |> 
    str_replace(fixed("index.html"), fixed("index.csv")) |> 
    read_csv() |> 
    filter(
      Accessible == "public") |> 
    mutate(
      pfx = dplyr::if_else(
        !is.na(tabledap),
        tabledap |> str_replace("tabledap", "info"),
        griddap  |> str_replace("griddap",  "info")),
      url = glue("{pfx}/index.html"))

  ds <- x |> 
    select(
      title = Title, url)
  attr(ds, "datasets") <- x
  ds
}

edi_ds <- function(link){
  # link = d$link[2]

  u <- url_parse(link)
  u$path  <- str_replace(u$path, "simpleSearch", "downloadSearch")
  u$query <- list(
    q = paste(names(u$query), u$query, sep = "=", collapse = "&"))
  #     curl_escape(httr2:::query_build(u$query)))
  
  x <- read_csv(url_build(u)) |> 
    mutate(
      url = glue("https://portal.edirepository.org/nis/mapbrowse?packageid={packageid}"))
  
  ds <- x |> 
    select(title, url)
  attr(ds, "datasets") <- x
  ds
}

# individual dataset functions (not yet used) ----
erddap_ds_indiv <- function(link){
  # link = "https://coastwatch.pfeg.noaa.gov/erddap/tabledap/erdCalCOFIzoovol.html"
  # link = "https://coastwatch.pfeg.noaa.gov/erddap/info/erdCalCOFIzoovol/index.html"
  
  title <- link |> 
    str_replace("tabledap/([^.]+)\\.html", "info/\\1/index.csv") |>
    str_replace("info/([^/]+)/index\\.html", "info/\\1/index.csv") |> 
    read_csv() |> 
    filter(
      `Attribute Name` == "title") |>
    pull(Value)

  tibble(
    title = title, 
    url   = link)
}

edi_ds_indiv <- function(link){
  # link = "https://portal.edirepository.org/nis/mapbrowse?packageid=knb-lter-cce.188.4"
  # link = "https://portal.edirepository.org/nis/mapbrowse?scope=knb-lter-cce&identifier=313"
  # link = "https://portal.edirepository.org/nis/mapbrowse?scope=knb-lter-cce&identifier=104&revision=12"

  u <- url_parse(link)
  # trim revision to return latest packageid
  if (length(u$query) == 1 && names(u$query) == "packageid"){
    id <- u$query |> str_replace("\\.[0-9]+$", "")
  } else if (all(c("scope", "identifier") %in% names(u$query))){
    id <- paste(u$query$scope, u$query$identifier, sep = ".")
  } else {
    stop("Unsupported link format for EDI repository")
  }
  res <- search_data_packages(query = glue("q=id:{id}&fl=packageid,title"))
  stopifnot(nrow(res) == 1)
  
  tibble(
    title = res$title,
    url   = glue("https://portal.edirepository.org/nis/mapbrowse?packageid={res$packageid}"))
}

# read googlesheet repos ----
dir.create(dirname(d_csv), showWarnings = F)

gs4_deauth()
read_sheet(d_gs, "CalCOFI data repositories") |> 
  write_csv(d_csv)

d <- read_csv(d_csv) |> 
  clean_names() |> # for now, simply translates to lower case
  mutate(
    to_ds = map_chr(
      link, 
      \(link){
        case_when(
          str_detect(link, "erddap")        ~ "erddap_ds(link)",
          str_detect(link, "edirepository") ~ "edi_ds(link)",
          .default = NA) } ) ) |> 
  relocate(to_ds, .after = repo)

if (ck_status){
  d <- d |> 
    mutate(
      status = map_chr(
        link, 
        \(x){
          request(x) |> 
            req_perform() |> 
            resp_status_desc() } ) )
}

# write repos to csv ----
d |> 
  select(repo, to_ds, link) |> 
  write_csv(d_csv)

# show repos ----
d |> 
  mutate(
    link   = glue("<a href='{link}' target='_blank'>{link}</a>")) |>
  datatable(
    escape = F,
    options = list(
      dom = "ft",
      pageLength = nrow(d))) |> 
  formatStyle(
    "to_ds",
    `font-family` = 'monospace')
```

-   source: [Living program document: CalCOFI Data Inventory_updated Oct2024 - Google Sheets](https://docs.google.com/spreadsheets/d/1uhviF2ecfOqGaSbC_JE8B5jPRqqjMaFc9TNCK_m297c/edit?gid=1271784325#gid=1271784325)

## Issues

-   GoogleSheet typo under `repo`: "ER**R**DAP" -\> "ER**D**DAP"
-   Repositories require login:
    -   [ZooDB](%60r%20filter(d,%20repo%20==%20%22ZooDB%22)%20%7C%3E%20pull(link)%60)
    -   [ZooScan](%60r%20filter(d,%20repo%20==%20%22ZooScan%22)%20%7C%3E%20pull(link)%60)
-   <a name="todo"></a>TODO:
    -   [ ] Add datasets from other repositories

    -   [ ] Extract JSON-LD from dataset links (see [CalCOFI/workflows#24](https://github.com/CalCOFI/workflows/issues/24))

    -   [ ] Update `lastmod` to dataset's last modified date

    -   [x] Create an ODISCat entry

        > (and point to the sitemap there). See steps at [book.odis.org/gettingStarted.html](https://book.odis.org/gettingStarted.html). We'll be driving the connection through that entry (to find your sitemap etc).\
        > -- \@jmckenna, per [iodepo/odis-arch#461](https://github.com/iodepo/odis-arch/issues/461#issuecomment-2429931617)

## Fetch datasets per repo

Always return a data frame with columns:

-   `title`: title of dataset
-   `url`: url to dataset

And attach the original data frame as an attribute `datasets`.

```{r}
# fetch datasets per repo ----
datasets <- d |> 
  filter(!is.na(to_ds)) |> # View()
  mutate(
    ds = map2(
      to_ds, link,
      \(to_ds, link){
        eval(parse(text = to_ds)) } ) ) |> 
  unnest(ds)

# write datasets to csv ----
datasets |> 
  write_csv(ds_csv)

# show datasets ----
datasets|> 
  mutate(
    dataset = glue("<a href='{url}' target='_blank'>{title}</a>")) |>
  select(repo, dataset) |> 
  datatable(escape = F)
```

### Summary of datasets per repo

```{r}
# tabulate datasets per repo ----
dss <- datasets |> 
  count(repo, name = "n_datasets")

write_csv(dss, dss_csv)

dss |> 
  datatable(
    options = list(
      dom = "t",
      pageLength = nrow(d)))
```

## Write sitemap.xml

-   [Build and Submit a Sitemap \| Google Search Documentation](https://developers.google.com/search/docs/crawling-indexing/sitemaps/build-sitemap)
-   [Protocol \| sitemaps.org](https://www.sitemaps.org/protocol.html)\
    -   `loc`: dataset link
    -   `lastmod`: today's date\
        TODO: change to dataset's last modified date
    -   `changefreq`: weekly; valid values: always, hourly, daily, weekly, monthly, yearly, never
    -   `priority`: SKIP; valid values: 0 to 1, e.g. 0.8

```{r}
# write sitemap.xml ----
datasets <- read_csv(ds_csv)

sm_body <- datasets |>
  glue_data(
    "<url>
      <loc>{url}</loc>
      <lastmod>{Sys.Date()}</lastmod>
      <changefreq>weekly</changefreq>
    </url>") |> 
  paste(collapse = "\n")

write_lines(
  list(
    glue('
      <?xml version="1.0" encoding="UTF-8"?>
        <urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'), 
    sm_body, 
    '</urlset>'), 
  path = sm_xml)

# copy entire datasets folder to _output
dir_from <- here("datasets")
dir_to   <- here("_output")
dir.create(dir_to, recursive = T, showWarnings = F)
file.copy(
  from      = dir_from, 
  to        = dir_to, 
  recursive = T, 
  overwrite = T)
```

**Datasets** `sitemap.xml`:

-   [`calcofi.io/workflows/datasets/sitemap.xml`](https://calcofi.io/workflows/datasets/sitemap.xml)

Contents of `sitemap.xml`:

``` xml
{{< include datasets/sitemap.xml >}}
```

## Create an ODISCat entry

### NEW: Spatial Coverage using `ctd_casts.geom`

We'll get the spatial extent for the entirety of the CalCOFI CTD casts going back to 1949.

``` sql
SELECT
  MIN(date) AS date_min,
  MAX(date) AS date_max,
  ST_Extent(geom) AS bbox
FROM ctd_casts
```

|            |            |                                                  |
|------------|------------|--------------------------------------------------|
| date_min   | date_max   | bbox                                             |
| 1949-02-28 | 2020-01-26 | BOX(-164.083333 18.416666,-105.966666 47.916666) |

Looking at JSON-LD in source of page [California Cooperative Oceanic Fisheries Investigations (CalCOFI)Database \| InPort](https://www.fisheries.noaa.gov/inport/item/20691), it wants minY minX maxY maxX, so for `Spatial coverage` using:

```
"box": "18.4 -164.1 47.9 -106.0"
```

### OLD: Spatial Coverage using `calcofi4r`

Per example under [Essential Ocean Variables — The Ocean InfoHub Project and the development of the ODIS-architecture](https://book.odis.org/thematics/variables/index.html#references):

``` json
"spatialCoverage": {
        "@type": "Place",
        "geo": {
            "@type": "GeoShape",
            "description": "schema.org expects lat long (Y X) coordinate order",
            "polygon": "10.161667 142.014,18.033833 142.014,18.033833 147.997833,10.161667 147.997833,10.161667 142.014"
        },
        "additionalProperty": {
            "@type": "PropertyValue",
            "propertyID": "https://dbpedia.org/page/Spatial_reference_system",
            "value": "https://www.w3.org/2003/01/geo/wgs84_pos"
        }
    }
```

#### CalCOFI zones

Let's use the CalCOFI zones described in the [`calcofi4r`](https://calcofi.io/calcofi4r/articles/calcofi4r.html) R package to construct the study envelope for the `spatialCoverage` term.

```{r}
librarian::shelf(
  calcofi/calcofi4r, dplyr, glue, leaflet, mapview, rmapshaper, sf)

mapview(cc_grid_zones, zcol="zone_key") +
  mapview(cc_grid_ctrs, cex = 1)
```

#### Dissolve zones

```{r}
# dissolve zones into a single polygon
cc_ply <- cc_grid_zones |> 
  st_union()

# mapview() not working on cc_ply, so switching to leaflet()
lmap <- function(ply){
  leaflet(ply) |> 
  addProviderTiles(providers$Esri.OceanBasemap) |> 
  addPolygons()
}
lmap(cc_ply)
```

#### Remove holes

```{r}
# remove holes and cast to simple polygon
cc_ply <- st_multipolygon(lapply(cc_ply, function(x) x[1])) |> 
  st_sfc(crs = 4326) |> 
  st_cast("POLYGON")
lmap(cc_ply)
```

Number of characters: `r st_as_text(cc_ply, digits=4) |> nchar()`

#### Simplify

To reduce number of characters in text string.

```{r}
cc_ply <- cc_ply |> 
  st_simplify(preserveTopology = T, dTolerance = 10*1000) |> # simplify by 10 km
  st_cast("POLYGON") |>  
  st_as_sf() |> 
  slice(1)

cc_txt <- cc_ply |> 
  st_geometry() |> 
  st_as_text(digits=4)

lmap(cc_ply)
```

Number of characters: `r nchar(cc_txt)`

#### Transform text

Transform to "schema.org expects lat long (Y X) coordinate order", versus the default "X Y" order for well-known text (WKT) (and every other geospatial standard, including GeoJSON).

Original:

```{r}
cc_txt
```

Converted:

```{r}
cc_txt = cc_txt |>
  # Swap coordinates
  gsub("([-0-9.]+)\\s+([-0-9.]+)", "\\2 \\1", x = _) |>
  # Remove space after comma
  gsub(",\\s",",", x = _) |>
  # Remove outer MULTIPOLYGON (( and ))
  gsub("POLYGON \\(\\((.*)\\)\\)", "\\1", x = _)
# nchar(cc_txt) # 2,476
cc_txt
```

#### Enter `polygon` into `spatialCoverage`

```{r}
sc <- glue(
  '{
      "@type": "Place",
      "geo": {
          "@type": "GeoShape",
          "description": "schema.org expects lat long (Y X) coordinate order",
          "polygon": "{{cc_txt}}"
      },
      "additionalProperty": {
          "@type": "PropertyValue",
          "propertyID": "https://dbpedia.org/page/Spatial_reference_system",
          "value": "https://www.w3.org/2003/01/geo/wgs84_pos"
   }',
  .open  = "{{",
  .close = "}}")
sc
```

### Add ODIS record `3318`

Added record here:

-   [catalogue.odis.org/view/3318](https://catalogue.odis.org/view/3318)
    -   print: [CalCOFI on ODIS record 3318.pdf](./datasets/CalCOFI%20on%20ODIS%20record%203318.pdf)

screenshot...

![](./datasets/CalCOFI%20on%20ODIS%20record%203318.png)

## Parse JSON-LD from dataset links

```{r}
# Load required libraries
librarian::shelf(
  dplyr, httr2, jsonlite, listviewer, readr, rvest, dplyr, purrr, tibble, tidyr, yaml)
redo_dsi = F

# Define the function to extract and flatten JSON-LD data
extract_jsonld <- function(url) {
  # url <- d$url[1]
  
  # Fetch the web page using httr2
  response <- request(url) %>%
    req_perform()
  
  # Check if the request was successful
  if (response$status_code == 200) {
    page_content <- response %>%
      resp_body_string()
  } else {
    warning(paste("Failed to retrieve the web page:", url))
    return(NULL)
  }
  
  # Parse the HTML content and find the script tag with type="application/ld+json"
  script_node <- page_content |> 
    read_html() |> 
    html_node(
      xpath = "//script[@type='application/ld+json']")
  
  if (is.na(script_node)) {
    warning(paste("No JSON-LD found in the page:", url))
    return(NULL)
  }
  
  # Extract the JSON-LD content
  html_text(script_node) |> 
    fromJSON() |> 
    as.yaml()
}

if (!file.exists(dsi_csv) | redo_dsi){
  d <- read_csv(ds_csv) |> 
    select(url) |> 
    mutate(
      jsonld_yaml = map_chr(url, extract_jsonld)) 
  
  write_csv(d, dsi_csv)
}

# show outputs
read_csv(dsi_csv) |> 
  mutate(
    jsonld = map(jsonld_yaml, yaml::yaml.load)) |> 
  select(-jsonld_yaml) |>
  deframe() |> 
  jsonedit()
```