Testing different ways of setting up postgis grid data and querying it in R

rpostgis-raster.Rmd

---
title: "rpostgis"
output: html_document
date: "2023-03-18"
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
```

## rpostgis

```{r rpostgis}
library(DBI)
library(terra)
library(rpostgis)
library(RPostgres)

con <- RPostgres::dbConnect(
  RPostgres::Postgres(),
  host = Sys.getenv("postgres_host"),
  user = Sys.getenv("postgres_user"),
  password = Sys.getenv("postgres_password")
)

dbListTables(con)

bdy <- vect("save/reed_creek.gpkg")

# accessing reed creek extent from 256x256 tiled "in db" raster of upper tuolumne river HUC
# raster2pgsql -s 4326 -t 256x256 /home/ubuntu/geodata/upper_tuolumne_dem_mask.tif public.upper_tuolumne_dem_256 | sudo -u postgres psql
system.time({
  x <- rpostgis::pgGetRast(con, name =  "upper_tuolumne_dem_256",
                           boundary = as(bdy, 'Spatial'))
})
#>  user  system elapsed 
#> 4.948   0.420  16.922 

# accessing reed creek extent from 256x256 indexed+tiled "in db" raster of upper tuolumne river HUC
# raster2pgsql -s 4326 -t 256x256 -I /home/ubuntu/geodata/upper_tuolumne_dem_mask.tif public.upper_tuolumne_dem_256i | sudo -u postgres psql
system.time({
  x <- rpostgis::pgGetRast(con, name =  "upper_tuolumne_dem_256i",
                           boundary = as(bdy, 'Spatial'))
})
#>  user  system elapsed 
#> 4.512   0.246  15.031 

# accessing reed creek extent from 64x64 indexed+tiled "in db" raster of upper tuolumne river HUC
# raster2pgsql -s 4326 -t 64x64 -I /home/ubuntu/geodata/upper_tuolumne_dem_mask.tif public.upper_tuolumne_dem_64i | sudo -u postgres psql
system.time({
  x <- rpostgis::pgGetRast(con, name =  "upper_tuolumne_dem_64i",
                           boundary = as(bdy, 'Spatial'))
})
#>  user  system elapsed 
#> 3.495   0.174  14.731

# accessing reed creek extent from 256x256 tiled "out db" raster of upper tuolumne river HUC
#### raster2pgsql -s 4326 -t 256x256 -R /home/ubuntu/geodata/upper_tuolumne_dem_mask.tif public.upper_tuolumne_dem_256r | sudo -u postgres psql
system.time({
  x <- rpostgis::pgGetRast(con, name =  "upper_tuolumne_dem_256r",
                           boundary = as(bdy, 'Spatial'))
})
#>   user  system elapsed 
#>  4.287   0.246  50.743

# accessing reed creek extent from 256x256 tiled+indexed "out db" raster of upper tuolumne river HUC
#### raster2pgsql -s 4326 -t 256x256 -R -I /home/ubuntu/geodata/upper_tuolumne_dem_mask.tif public.upper_tuolumne_dem_256ri | sudo -u postgres psql
system.time({
  x <- rpostgis::pgGetRast(con, name =  "upper_tuolumne_dem_256ri",
                           boundary = as(bdy, 'Spatial'))
})
#>   user  system elapsed 
#>  4.143   0.417  49.554 

# accessing reed creek extent from "out db" raster of upper tuolumne river HUC
#### raster2pgsql -s 4326 -R /home/ubuntu/geodata/upper_tuolumne_dem_mask.tif public.upper_tuolumne_dem_r | sudo -u postgres psql
# system.time({
#   x <- rpostgis::pgGetRast(con, name =  "upper_tuolumne_dem_r",
#                            boundary = as(bdy, 'Spatial'))
# })
#> # after a while... 
#> Error: Failed to fetch row: ERROR: invalid memory alloc request size 1210519296
#> CONTEXT: SQL function "st_dumpvalues" statement 1
```