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’)