Skip to content
This repository has been archived by the owner on Jul 13, 2023. It is now read-only.

Table mapping

Jump to bottom
Molly Graber edited this page Aug 6, 2020 · 2 revisions

Input data tables

pluto_corrections pluto_input_research pluto_input_condolot_descriptiveattributes pluto_input_numbldgs dcp_edesignation dcas_facilities_colp lpc_historic_districts lpc_landmarks

Spatial Join Tables

dcp_cdboundaries dcp_censustracts dcp_censusblocks dcp_school_districts dcp_councildistricts doitt_zipcodeboundaries dcp_firecompanies dcp_policeprecincts dcp_healthareas dcp_healthcenters dsny_frequencies dcp_pluto dcp_mappluto dpr_greenthumb

From FTP or GitHub

dcp_zoning_maxfar pluto_input_bsmtcode pluto_input_landuse_bldgclass pluto_input_condo_bldgclass pluto_pts

pluto_input_geocodes:

  • Gets overwritten in preprocessing.sql to deduplicate on BBL
  • Gets overwritten in create_rpad_geo.sql to create spatial coordinates and NULL out 0 census tracts

Raw data

pluto_input_cama_dof dof_dtm dof_shoreline dof_condo

DCP Zoning Datasets

dcp_commercialoverlay dcp_limitedheight dcp_zoningdistricts dcp_specialpurpose dcp_specialpurposesubdistricts dcp_zoningmapamendments dcp_zoningmapindex

FEMA

fema_firms2007_100yr fema_pfirms2015_100yr

Intermediate tables

dof_pts_propmaster:

  • Gets created in pts_clean.sql
  • Contains data from pluto_pts, with numeric strings cleaned and type-casted

pluto_rpad_geo:

  • Gets created in create_rpad_geo.sql
  • Contains one record from dof_pts_propmaster for each BBL that has the largest curavt_act (primarily) and land_area (secondarily). Each record has all attributes from dof_pts_propmaster as well as data from pluto_input_geocodes, joined on BBL
  • Several attributes get overwritten in create_rpad_geo.sql to rename and format
  • x-coord and y-coord get backfilled with coordinates extracted from a point created from latitude and longitude
  • Gets updated in zerovacantlots.sql to set bfft, bdft, stort, and bldgs to 0 where bldgcl starts with V
  • Gets updated in lotarea.sql where

pluto_temp_qc_not_geocoded:

  • Created in geocode_not_geocoded.sql
  • Contains records from pluto_rpad_geo that has a NULL cd but a non-NULL BBL, ordered by bbl
  • Gets output as output/qc_notgeocoded.csv
Clone this wiki locally