2024-04-25_cama-data-analysis-example.R

library(arcgislayers)

layer <- arc_open(
  "https://geodata.md.gov/imap/rest/services/PlanningCadastre/MD_ComputerAssistedMassAppraisal/MapServer/1"
)


bldg_layer <- arc_open(
  "https://geodata.md.gov/imap/rest/services/PlanningCadastre/MD_ComputerAssistedMassAppraisal/MapServer/0"
)

bldg_data <- bldg_layer |> 
  arc_select(
    where =  paste0(
      c(
        "(BL_ENCSQFT > 20000)",
        "(JURSCODE = 'BACI')"
      ),
      collapse = " AND "
    )
  )

init_query <- paste0(
  c(
    "((PR_ENCSQFT > 25000) OR (PR_ENCOVRD > 25000))",
    paste0(
      "(",
      paste0(
        "(CM_DSCIUSE LIKE '", c("AUTO", "RESTAURANT", "STORE", "BANK"), "%')" ,
        collapse = " OR "
      ),
      ")"
    ),
    "(JURSCODE = 'BACI')"),
  collapse = " AND "
)

update_query <- paste0(
  c(
    "((PR_ENCSQFT >= 25000) OR (PR_ENCOVRD >= 25000))",
    "(JURSCODE = 'BACI')"),
  collapse = " AND "
)

data <- layer |> 
  arc_select(
    where = update_query
  )

data_joined <- data |> 
  sf::st_drop_geometry() |> 
  select(
    !c(PTYPE, OBJECTID, CAMADATE, MDPVDATE, PTYPE, DIGXCORD, DIGYCORD)
  ) |> 
  left_join(
    bldg_data
  )

data |> 
  sf::st_drop_geometry() |> 
  filter(
    !is.na(CM_DSCIUSE)
  ) |> 
  mutate(
    PR_ENCOVRD = coalesce(PR_ENCOVRD, PR_ENCSQFT),
    use_category = case_when(
      str_detect(CM_DSCIUSE, "^CARE") ~ "CARE",
      str_detect(CM_DSCIUSE, "^AUTO") ~ "AUTO",
      str_detect(CM_DSCIUSE, "^STORE") ~ "STORE",
      str_detect(CM_DSCIUSE, "^RESTAURANT") ~ "RESTAURANT",
      str_detect(CM_DSCIUSE, "^COMMUNITY") ~ "COMMUNITY",
      str_detect(CM_DSCIUSE, "^ELEC") ~ "ELEC",
      str_detect(CM_DSCIUSE, "^HOUSING") ~ "HOUSING",
      str_detect(CM_DSCIUSE, "^INDUSTRY") ~ "INDUSTRY",
      str_detect(CM_DSCIUSE, "^OFFICE") ~ "OFFICE",
      str_detect(CM_DSCIUSE, "^PUBLIC") ~ "PUBLIC",
      str_detect(CM_DSCIUSE, "^REC") ~ "REC",
      str_detect(CM_DSCIUSE, "^SAFETY") ~ "SAFETY",
      str_detect(CM_DSCIUSE, "^WAREHOUSE") ~ "WAREHOUSE",
      str_detect(CM_DSCIUSE, "^TRAVEL") ~ "TRAVEL",
      str_detect(CM_DSCIUSE, "^TRANSPORT") ~ "TRANSPORT",
      str_detect(CM_DSCIUSE, "^BANK") ~ "BANK",
      str_detect(CM_DSCIUSE, "^BOAT") ~ "BOAT",
      str_detect(CM_DSCIUSE, "^RAIL") ~ "RAIL",
      str_detect(CM_DSCIUSE, "^BURIAL") ~ "BURIAL",
      str_detect(CM_DSCIUSE, "^ALCOHOL") ~ "ALCOHOL",
      str_detect(CM_DSCIUSE, "^CANNABIS") ~ "CANNABIS",
      str_detect(CM_DSCIUSE, "^OTHER") ~ "OTHER",
      .default = NA_character_
    ),
    use_subcategory = str_remove(
      CM_DSCIUSE,
      paste0("^", use_category)
    ),
    use_category = str_to_title(use_category),
    use_subcategory = str_to_title(use_subcategory)
  ) |> 
  arrange(CM_DSCIUSE) |> 
  summarise(
    CM_BLDTOTL = sum(CM_BLDTOTL, na.rm = TRUE),
    PR_ENCSQFT = sum(PR_ENCSQFT, na.rm = TRUE),
    PR_ENCOVRD = sum(PR_ENCOVRD, na.rm = TRUE),
    .by = c(use_category, use_subcategory)
  ) |> 
  group_by(use_category) |> 
  gt::gt(
    rowname_col = "use_subcategory"
  ) |> 
  gt::cols_label(
    use_subcategory ~ "Use category",
    CM_BLDTOTL ~ "Total buildings",
    PR_ENCSQFT ~ "Enclosed sq. ft.",
    PR_ENCOVRD ~ "Enclosed sq. ft. (assessor)"
  ) |> 
  gt::summary_rows(
    columns = starts_with("PR_"),
    fns = list(
      Total = ~ sum(., na.rm = TRUE)
    ),
    fmt = ~ gt::fmt_number(., decimals = 0),
    side = "top"
  ) |> 
  gt::grand_summary_rows(
    columns = starts_with("PR_"),
    fns = list(
        "Overall total" = ~ sum(., na.rm = TRUE)
      ),
    fmt = ~ gt::fmt_number(., decimals = 0),
    side = "top"
  ) |> 
  gt::fmt_number(
    columns = starts_with("PR_"),
    decimals = 0
  ) |> 
  gt::tab_source_note(
    "Source: Maryland Computer Assisted Mass Appraisal (2022). Table created by Baltimore City Department of Planning, 2024 April 25."
  ) |> 
  gt::tab_header(
    title = "Baltimore City property with 25K sq.ft. or more enclosed space"
  ) |> 
  gtExtras::gt_theme_pff(
    # heading.title.font.size = 16
  )

data |> 
  sf::st_drop_geometry() |> 
  select(
    `Total buildings (on parcel)` = CM_BLDTOTL,
    `Total units (on parcel)` = CM_BLDUNTS,
    `Enclosed sq. ft.` = PR_ENCSQFT,
    `Enclosed sq. ft. (assessor)` = PR_ENCOVRD,
    `Use description` = CM_DSCIUSE
  ) |> 
  gtsummary::tbl_summary()