Include station information in akgfmaps data?

[MargaretSiple-NOAA]

I'm putting this as a question mark because I think it depends on your editorial decision as package developer. Lewis mentioned that it would be nice to include some basic "gold standard" grid info in the akgfmaps package but I think it could also be well suited to just staying in the upcoming "Gap Products" tables when they make the new Oracle schema. So anyway, just putting that as a note here and will chat about it when you get back from the survey.

[sean-rohan-NOAA]

@MargaretSiple-NOAA What sort of metadata are you thinking about? There are some unresolved issues related to what constitutes the 'gold standard' grid info. There has been a persistent perspective that OFIS maintains the 'gold standard' shapefiles although (1) they haven't had someone maintaining shapefiles for nearly a decade and (2) the shapefiles we use for index calculation are not the same as the OFIS 'gold standard' shapefiles, which had errors.

The current shapefiles contain some of the OFIS 'gold standard' grid information. In other products that use the package (e.g. navmaps) I combine station info with information from Oracle tables.

[MargaretSiple-NOAA]

Just adding this here for posterity from our convo-- I think Ned was thinking of the grid info that is stored in the AI.GRID and GOA.GRID tables in the AI and GOA schemas. From that, I tend to use the T/UT information and grid cell area.

[sean-rohan-NOAA]

Thought about this a bit more. I'm a bit concerned that adding the AI and GOA grid tables to the package would would require an extra step to regularly update/maintain the tables.

Could be convinced otherwise, but I'm not sure it's worth the additional complexity when it's not too burdensome to join data from the grid tables with the shapefiles.

AI:

library(akgfmaps)
library(RODBC)

channel <- ### your favorite fn to setup an RODBC connection here ###

map_layers <- akgfmaps::get_base_layers(select.region = "ai",
                                          set.crs = "EPSG:3338")

  # Query AIGRID table and join with survey grid shapefile
    trawlable <- RODBC::sqlQuery(query = "select AIGRID_ID, TRAWLABLE, STRATUM, STATIONID, CENTER_LAT, CENTER_LONG, SOUTH_LAT, EAST_LONG, WEST_LONG from ai.aigrid_gis",
                                 channel = channel)

    trawlable_grid <- map_layers$survey.grid |>
      dplyr::select(AIGRID_ID, STRATUM, geometry) |>
      dplyr::inner_join(trawlable, 
                        by = c("AIGRID_ID", "STRATUM"))

  }

GOA:

library(akgfmaps)
library(RODBC)

channel <- ### your favorite fn to setup an RODBC connection here ###

map_layers <- akgfmaps::get_base_layers(select.region = "goa",
                                          set.crs = "EPSG:3338")

    trawlable <- RODBC::sqlQuery(query = "select GOAGRID_ID, TRAWLABLE, STRATUM, STATIONID, CENTER_LAT, CENTER_LONG, SOUTH_LAT, EAST_LONG, WEST_LONG from goa.goagrid_gis",
                                 channel = channel)

    trawlable_grid <- map_layers$survey.grid |>
      dplyr::select(GOAGRID_ID, STRATUM, geometry) |>
      dplyr::inner_join(trawlable,
                        by = c("GOAGRID_ID", "STRATUM")) |>
      dplyr::filter(!is.na(STATIONID))
  }

[MargaretSiple-NOAA]

Personally I am more of a fan of keeping database queries consolidated in certain packages, so I think these should actually be included in the gapindex package, since that has a bunch of database query stuff in it already. I think we can add a function to that package pretty easily. I will close this in a few when I figure out what I'm gonna do!

[MargaretSiple-NOAA]

OK I added this to the gapindex package, so it is out of your hands! PR for it is here: https://github.com/afsc-gap-products/gapindex/pull/32