Fetch spatial data from postgres database

fetch-fishable-waters.r

library(DBI)
library(sf)
library(dplyr)
library(tidyr)

# replace with your connection details
con = DBI::dbConnect(
  RPostgres::Postgres(),
  dbname = '',
  host = '',
  port = 5432,
  user = '',
  password = ''
)

# fetch data
species = dbReadTable(con, 'species')
joiner = dbReadTable(con, 'species_water_joiner')
water_sf = st_read(con, 'fishable_waters')

# join tables
species_joiner = inner_join(species, joiner, by = c('id' = 'species_id'))

waters = left_join(water_sf, species_joiner, by = c('id' = 'water_id'))

# pivot
pivot_table = waters %>% 
  st_drop_geometry() %>% 
  select(water_id = id, species) %>% 
  group_by(water_id) %>% 
  mutate(rank = row_number()) %>% 
  pivot_wider(names_from = rank, values_from = species, names_prefix = 'species_')


# join to waters table
out = inner_join(pivot_table, water_sf, by = c('water_id' = 'id'))
out$fid = 1:nrow(out)

write_sf(out, 'fishable-water.shp')