How to create a shapefile with median age of buildings by block. Data is from NYSGIS which has been loaded into Postgres database.
library(RPostgreSQL)
library(tidyverse)
library(sf)
library(tigris)rm(list=ls())
con <- DBI::dbConnect(RPostgres::Postgres(), dbname=’gis’, host=’localhost’,
port=5432, user=’postgres’, password=’XXXXXXXX’)bb <- blocks(‘ny’) %>% st_transform(26918)
str_c(“select yr_blt, st_transform(shape, 26918) as geom FROM nytax_ctr_pt WHERE yr_blt>0”) -> sql
yrblt <- st_read(con, query=sql)
bb %>%
st_intersection(yrblt) %>%
st_drop_geometry() %>%
group_by(GEOID20) %>%
summarize(blockyr = median(yr_blt)) %>%
inner_join(mb, bb) %>%
write_sf(‘/tmp/median-block.gpkg’)
