1 Goal

  • msens::shlfs|rgns -> pg:shlfs|rgns
    Ingest existing BOEM Shelfs and Regions from msens R package (performed with msens: /data-raw/ply_shlfs_rgns.R after workflow: explore_regions) into the PostGIS spatial database for performing spatial joins with species and other data.

2 Load into PostGIS database

Code 
librarian::shelf(
  here, leaflet, leaflet.extras, # mapview, 
  marinesensitivity/msens, 
  sf, tibble,
  quiet = T)

source(here("libs/db.R")) # define: con
Loading required package: librarian
Code 
tbls <- c(
  "ply_shlfs", "ply_shlfs_s05", 
  "ply_rgns" , "ply_rgns_s05")
redo <- F

if (!all(tbls %in% dbListTables(con))){
  for (tbl in tbls){  # tbl = "ply_shlfs_s05"
    
    # shift from 0:360 to -180:180
    o <- get(tbl) |> 
      st_wrap_dateline()
    
    st_write(
      o,
      dsn           = con,
      layer         = tbl,
      driver        = "PostgreSQL",
      layer_options = "OVERWRITE=true")
  
    # enforce SRID so shows up in tile.marinesensivity.org
    dbExecute(con, glue("SELECT UpdateGeometrySRID('{tbl}','geometry',4326);"))
  }
}

3 Test spatial data in database

Code 
dbListTables(con)
[1] "spatial_ref_sys"   "geography_columns" "geometry_columns" 
[4] "ply_shlfs"         "ply_shlfs_s05"     "ply_rgns"         
[7] "ply_rgns_s05"
Code 
p <- read_sf(con, "ply_shlfs_s05")

# mapView(
#   p,
#   zcol       = "shlf_name",
#   layer.name = "ply_shlfs.shlf_name<br><small>(simplified to 5%)</small>")
# Error: [weirdly only when run quarto render]
# ! number of columns of matrices must match (see arg 2)

msens::ms_basemap() |>
  addPolygons(
    data      = p,
    fillColor = "blue",
    weight      = 2,
    color       = "blue",
    label       = ~shlf_name) |> 
  addFullscreenControl()