TODO: load into single aois using hstore

Use PostgreSQL hstore to store the many varying fields of any given AOI feature into a single column, say keys.

See issue #13: load areas of interest into single aois table using hstore, starting with sanctuaries.

Sanctuaries

source(here::here("libs/db.R")) # defines variables: con, dir_gdrive
librarian::shelf(
  glue, here, mapview, sf)
mapviewOptions(fgb = FALSE)

# source: https://github.com/noaa-onms/onmsR/blob/master/data/sanctuaries.rda
# sanctuaries_rda <- "/share/data/ONMS-Sanctuaries/sanctuaries.rda"
sanctuaries_rda <- "~/Github/noaa-onms/onmsR/data/sanctuaries.rda"

load(file=sanctuaries_rda)
sanctuaries <- sanctuaries %>% select(-spatial)
mapview(sanctuaries)

Time for the chunk unnamed-chunk-1 to run: 8.116 seconds

Sanctuaries intersecting CalCOFI

stations <- st_read(con, "stations")
mapview(stations)

Time for the chunk unnamed-chunk-2 to run: 0.6741 seconds

hull <- st_convex_hull(st_union(stations))
mapview(hull)

Time for the chunk unnamed-chunk-3 to run: 5.108 seconds

sanctuaries <- st_set_crs(sanctuaries, 4326)
hull        <- st_set_crs(hull, 4326)
x <- st_intersects(sanctuaries, hull, sparse = F)[,1]
sanctuaries <- sanctuaries[x,]
mapview(sanctuaries)

Time for the chunk unnamed-chunk-4 to run: 0.6228 seconds

Load into Database

st_write(sanctuaries, con, "aoi_fed_sanctuaries", delete_layer=T)
# add spatial index
dbSendQuery(con, "CREATE INDEX IF NOT EXISTS aoifedsanctuaries_geom_idx ON aoi_fed_sanctuaries USING GIST (geom);")
## <PqResult>
##   SQL  CREATE INDEX IF NOT EXISTS aoifedsanctuaries_geom_idx ON aoi_fed_sanctuaries USING GIST (geom);
##   ROWS Fetched: 0 [complete]
##        Changed: 0
# set geographic projection for showing up at tile.calcofi.io
dbSendQuery(con, "SELECT UpdateGeometrySRID('aoi_fed_sanctuaries','geom',4326);")
## <PqResult>
##   SQL  SELECT UpdateGeometrySRID('aoi_fed_sanctuaries','geom',4326);
##   ROWS Fetched: 0 [incomplete]
##        Changed: 0
# check read from db to spatial features
sanctuaries <- sf::st_read(con, "aoi_fed_sanctuaries")
sanctuaries
## Simple feature collection with 4 features and 2 fields
## Geometry type: MULTIPOLYGON
## Dimension:     XY
## Bounding box:  xmin: -124.3335 ymin: 33.36241 xmax: -118.9071 ymax: 39
## Geodetic CRS:  WGS 84
##            sanctuary   nms                           geom
## 1    Channel Islands CINMS MULTIPOLYGON (((-118.9088 3...
## 2       Cordell Bank CBNMS MULTIPOLYGON (((-123.7514 3...
## 3 Greater Farallones GFNMS MULTIPOLYGON (((-123.7274 3...
## 4       Monterey Bay MBNMS MULTIPOLYGON (((-122.5 35.9...

Time for the chunk unnamed-chunk-5 to run: 3.183 seconds