Product: DevDB

Home

Developments Database (DevDB)

GitHub release (latest SemVer)

The goal of the developments database is to provide a detailed view of both residential and non-residential development that has occured since the 2010 census. The core of the developments data base is a compilation of NYC DOB job and permit information. This data captures changes in units resulting from new buildings, major alterations, and demolitions. Rough timelines of development are captured in the DOB job status dates. To more reliably capture development completion, the database also includes dates associated with certificates of occupancy. Where possible, the database also tracks Housing New York affordable units created as part of each development.

We provide context for each development record by geocoding, then merging the data with various political, geographic, and administrative boundaries. Lot-level zoning and land-use information comes from PLUTO.

Source data

The majority of the data contained in the developments database comes from NYC DOB. These include permit issuance data, job application records, and new certificates of occupancy. Housing New York affordable housing unit information comes from HPD. Administrative and geographic boundary information primarily comes from DCP. Other contextual data comes from PLUTO

Workflow Diagram

built with mermaid

Mermaid Markdown

graph TD
    dob_jobapplications[dob_jobapplications]
    _INIT_devdb(_INIT_devdb)
    INIT_devdb{INIT_devdb}
    _GEO_devdb(_GEO_devdb)
    GEO_devdb[GEO_devdb]
    _SPATIAL_devdb(_SPATIAL_devdb)
    SPATIAL_devdb[SPATIAL_devdb]
    _OCC_devdb(_OCC_devdb)
    OCC_devdb[OCC_devdb]
    _UNITS_devdb(_UNITS_devdb)
    UNITS_devdb[UNITS_devdb]
    occ_lookup[OCC_lookup]
    status_lookup[STATUS_lookup]
    CO_devdb[CO_devdb]
    STATUS_Q_devdb[STATUS_Q_devdb]
    dob_permitissuance[dob_permitissuance]
    dob_cofos[dob_cofos]
    _MID_devdb(_MID_devdb)
    MID_devdb{MID_devdb}
    STATUS_devdb[STATUS_devdb]
    HNY_devdb[HNY_devdb]
    dob_jobapplications --> _INIT_devdb
    _INIT_devdb -->|geocoding| _GEO_devdb
    occ_lookup --> _OCC_devdb
    status_lookup --> _OCC_devdb
    _INIT_devdb --> INIT_devdb
    INIT_devdb --> |recode with occ_lookup| _OCC_devdb
    INIT_devdb --> _UNITS_devdb
    _GEO_devdb --> GEO_devdb
    GEO_devdb --> |spatial joins| _SPATIAL_devdb
    _SPATIAL_devdb --> SPATIAL_devdb
    SPATIAL_devdb --> INIT_devdb
    _OCC_devdb --> |corrections + occ_category| OCC_devdb
    OCC_devdb --> _UNITS_devdb
    _UNITS_devdb --> |corrections + units_net| UNITS_devdb
    INIT_devdb --> CO_devdb
    dob_cofos --> CO_devdb
    INIT_devdb --> STATUS_Q_devdb
    dob_permitissuance --> STATUS_Q_devdb

    INIT_devdb --> _MID_devdb
    STATUS_Q_devdb --> _MID_devdb
    CO_devdb --> _MID_devdb
    UNITS_devdb --> _MID_devdb
    OCC_devdb --> _MID_devdb

    _MID_devdb --> STATUS_devdb
    STATUS_devdb --> MID_devdb
    _MID_devdb --> MID_devdb
    MID_devdb --> | find hny matches| HNY_matches
    hpd_hny_units_by_building --> | find dob matches| HNY_matches
    HNY_matches -->  | hny_id + all_hny_units + affordable_units|HNY_devdb
    MID_devdb -->  HNY_devdb

Description of tables

Source data

`dob_jobapplications`

NYC Opendata

This dataset contains all job applications submitted through the Borough Offices, through eFiling, or through the HUB, which have a "Latest Action Date" since January 1, 2000. This dataset does not include jobs submitted through DOB NOW. See the DOB NOW: Build – Job Application Filings dataset for DOB NOW jobs.

`dob_permitissuance`

NYC Opendata

The Department of Buildings (DOB) issues permits for construction and demolition activities in the City of New York. The construction industry must submit an application to DOB with details of the construction job they would like to complete. The primary types of application, aka job type, are: New Building, Demolition, and Alterations Type 1, 2, and 3. Each job type can have multiple work types, such as general construction, boiler, elevator, and plumbing. Each work type will receive a separate permit. (See the DOB Job Application Filings dataset for information about each job application.) Each row/record in this dataset represents the life cycle of one permit for one work type. The dataset is updated daily with new records, and each existing record will be updated as the permit application moves through the approval process to reflect the latest status of the application.

`dob_cofos`

received by email from DOB
containing historical (from 2000) records as well (dob_cofos_append)
NYC Opendata

A Certificate of Occupancy (CO) states a building’s legal use and/or type of permitted occupancy. New buildings must have a CO, and existing buildings must have a current or amended CO when there is a change in use, egress or type of occupancy. No one may legally occupy a building until the Department has issued a Certificate of Occupancy or Temporary Certificate of Occupancy. The Department issues a final Certificate of Occupancy when the completed work matches the submitted plans for new buildings or major alterations. It issues a Letter of Completion for minor alterations to properties. These documents confirm the work complies with all applicable laws, all paperwork has been completed, all fees owed to the Department have been paid, all relevant violations have been resolved and all necessary approvals have been received from other City Agencies.

`dcp_mappluto`

shoreline clipped version here

`doitt_building_footprints`

NYC Opendata

Shapefile of footprint outlines of buildings in New York City. Please see the following link. Previously posted versions of the data are retained to comply with Local Law 106 of 2015 and can be provided upon request made to Open Data.

`doitt_building_footprints_historical`

NYC Opendata

Building Footprints Historical Shape Shapefile of historical footprint outlines of buildings in New York City. Please see the following link for additional documentation. Previously posted versions of the data are retained to comply with Local Law 106 of 2015 and can be provided upon request made to Open Data.

`hpd_hny_units_by_building`

NYC Opendata

Housing New York Units by Building The Department of Housing Preservation and Development (HPD) reports on buildings, units, and projects that began after January 1, 2014 and are counted towards the Housing New York plan. The Housing New York Units by Building file presents this data by building, and includes building-level data, such as house number, street name, BBL, and BIN for each building in a project. The unit counts are provided by building. For additional documentation, including a data dictionary, review the attachments in the “About this Dataset” section of the Primer landing page.

`census_units10`

US Census Bureau
count of total housing units by NYC census block

`census_units10_adj`

provided by NYC DCP Population Division
count of total housing units by NYC census tract, adjusted for Census under-count of housing units in Queens

Lookups

`LOOKUP_occ`

maps occupancy from DOB codes to descriptive names
maintained in a csv

`LOOKUP_ownership`

maps ownership from three DOB fields (cityowned, ownertype, nonprofit) to a single descriptive ownership field
maintained in a csv

`LOOKUP_geo`

maps census tracts to the larger administrative units they nest into -- puma, nta, borough
maintained in a csv

`council_members`

maps council district to the name of the current council member
from NYC Open Data

Spatial boundary data

`dco_boroboundaries_wi`

borough boundaries including water

`dcp_cd_boundaries`

community district boundaries

`dcp_census_blocks`

census block boundaries (2010)

`dcp_census_tracts`

census tract boundaries (2010)

`dcp_councildistricts`

council district boundaries

`dcp_firecompanies`

fire company boundaries

`dcp_policeprecincts`

police precinct boundaries

`dcp_school_districts`

school district boundaries

`doe_eszones`

elementary school zone boundaries

`doe_mszones`

middle school zone boundaries

`doe_school_subdistricts`

school sub-districts

`doitt_zipcodeboundaries`

zip code boundaries

`dof_shoreline`

DOF's shoreline file

`dof_shoreline_subdivide`

created in _function.sql
a simplified version of dof_shoreline

Initial attribute mapping

`_INIT_devdb`

created in _init.sql
It creates an initial field mapping from dob_jobapplications to devdb
fields columns that are 1-to-1 mapping and temporary fields noted with _ (such as _occ_prop) for upcoming calculation

`INIT_devdb`

created in init.sql
_INIT_devdb + SPATIAL_devdb = INIT_devdb
The idea is that INIT_devdb is _INIT_devdb with spatial columns (geo_bbl, geo_bin ...)

Geocoding and spatial joins

`_GEO_devdb`

created in running geocode.py
this is generated by geosupport, taking address info and uid from _INIT_devdb

`GEO_devdb`

created in running _geo.sql
Since we have logic assigning geom, this table contains all the spatial columns from _GEO_devdb but with geom coming from centroid based on hierarchy (building foot print centroid using bin -> building foot print centroid using geo_bin -> geosupport lot centroid -> mappluto lot centroid & etc)
So this table is basically the same as _GEO_devdb but different geom and latlon
this table contains all the spatial attributes extracted through geosupport

`_SPATIAL_devdb`

created in _spatial.sql
Taking newly assigned geom from GEO_devdb, we do spatial joins to extract spatial boundaries (cd, nta, zipcode & etc)
this table contains all the spatial attributes extracted through spatial join

`SPATIAL_devdb`

created in spatial.sql
this table is creating a consolidated spatial attribute table from _SPATIAL_devdb and GEO_devdb. Depends on the scenario, there's logic to pick spatial attributes from either _SPATIAL_devdb or GEO_devdb.
it has the same schema as GEO_devdb, _SPATIAL_devdb

Occupancy and units

`OCC_lookup`

created in _lookup.sql
containing dob_occ and their translation to devdb occ

`_OCC_devdb`

created in _occ.sql
this table translates dob_occ to devdb occ using OCC_lookup
occ_prop and occ_init are assigned and corrected

`OCC_devdb`

created in _occ.sql
this table assigns occ_category and makes corrections on occ_category
this table contains occ_prop, occ_init, and occ_category

`_UNITS_devdb`

created in _units.sql
units_prop and units_init are assigned and corrected

`UNITS_devdb`

created in _units.sql
assigning units_net using finalized units_prop and units_init
contains units_prop, units_init and units_net

Assigning status

`CO_devdb`

created in _co.sql
assigns co related columns

`_MID_devdb`

created in _mid.sql
A temporary table that consolidates fields from UNITS_devdb, OCC_devdb, CO_devdb, STATUS_Q_devdb for the purpose of computing the status field

`MID_devdb`

created in mid.sql
_MID_devdb + STATUS_devdb -> MID_devdb

`STATUS_devdb`

created in _status.sql
computed from _MID_devdb

Merging with Non-DOB attributes

`HNY_geocode_results`

contains results of running hpd_hny_units_by_building through Geosupport 1B
created in geocode_hny.py

`HNY_geo`

contains relevant columns from hpd_hny_units_by_building, joined with spatial information from HNY_geocode_results

`HNY_matches`

created in _hny.sql
contains matches between hny building-level records and dob jobs, where the match_priority is the lowest number of
- 1: Residential new building matched on both BIN & BBL
- 2: Residential new building matched only on BBL
- 3: Residential new building matched spatially
- 4: Alteration or non-residential non-demolition matched on both BIN & BBL
- 5: Alteration or non-residential non-demolition matched only on BBL
- 6: Alteration or non-residential non-demolition matched spatially

`HNY_no_match`

created _hny.sql
contains the subset of HNY_geo that did not match with a DOB job using any of the above methods. This is the opposite subset to HNY_matches

`CORR_hny_matches`

contains corrections to HNY_matches. This file is populated by manual review. Matches are either added or removed.
applied in _hny.sql

`HNY_devdb`

created in _hny.sql
contains fields from MID_devdb, along with hny fields
join method includes logic to handle one-to-many, many-to-one, and many-to-many cases

`PLUTO_devdb`

created in _pluto.sql
contains all the pluto fields

Converting to final schema for export

`FINAL_devdb`

created in final.sql
takes columns for final output from MID_devdb, PLUTO_devdb, HNY_devdb and puts them in desired order.
for boro, bin, bbl, address_numbr, address_st, and address, data comes from the geosupport fields (geo_ prefixed) where possible, otherwise from source data.

`EXPORT_devdb`

contains a subset of FINAL_devdb based on the desired capture date

`EXPORT_housing`

contains a subset of FINAL_devdb based on the desired capture date, where resid_flag indicates residential

QAQC tables

`_INIT_qaqc`

created in qaqc_init.sql
contains flags for invalid dates

`GEO_qaqc`

created in qaqc_geo.sql
contains flags for jobs that are in water, that don't fall inside a tax lot, or that are missing coordinates

`STATUS_qaqc`

created in qaqc_status.sql
contains a flag for jobs that have manual corrections setting them to inactive, but have had an update since the previous release

`UNITS_qaqc`

created in qaqc_units.sql
contains flags for alterations with large reductions in units, new buildings with 500+ units, demolitions of 20+ unit buildings, and the 20 alterations resulting in the largest net changes in units

`MID_qaqc`

created in qaqc_mid.sql
contains flags for:
- demolitions/alterations with null initial units,
- new buildings/alterations with null proposed units,
- potential duplicates,
- jobs that are non-residential but have residential units,
- jobs that are likely garages or gazebos,
- jobs that are likely class B,
- jobs with a mismatch between CO unit count and the proposed number of units,
- jobs that are tract homes but are labeled as incomplete

`FINAL_qaqc`

created in qaqc_final.sql
contains all QAQC flags with columns in alphabetical order

`MATCH_dem_nb`

created in qaqc_mid.sql
contains pairwise matches between demolitions and new buildings on bbl

Aggregation tables

`YEARLY_devdb`

created in yearly.sql
contains classa_net sorted into columns -- year-by-year breakdowns for complete jobs, and status breakdowns for incomplete jobs

`_AGGREGATE_block`

created in aggregate.sql
contains fields from YEARLY_devdb aggregated by census block, along with census unit counts from census_units10

`_AGGREGATE_tract`

created in aggregate.sql
contains fields from YEARLY_devdb aggregated by census tract, along with census unit counts from census_units10 and adjusted census unit counts from census_units10adj

`AGGREGATE_block`

created in aggregate.sql
a subset of relevant columns from _AGGREGATE_block

`AGGREGATE_tract`

created in aggregate.sql
a subset of relevant columns from _AGGREGATE_tract

`AGGREGATE_nta`

created in aggregate.sql
contains of relevant columns from _AGGREGATE_tract, further aggregated to the level of nta

`AGGREGATE_puma`

created in aggregate.sql
contains of relevant columns from _AGGREGATE_tract, further aggregated to the level of puma

`AGGREGATE_comunitydist`

created in aggregate.sql
contains of relevant columns from _AGGREGATE_tract, further aggregated to the level of community district

`AGGREGATE_councildist`

created in aggregate.sql
contains of relevant columns from _AGGREGATE_tract, further aggregated to the level of council district
also contains a field for council member name

Logs

`CORR_devdb`

contains a running log of changes to the attributes of each job, which come from the application of the manual corrections file

Before Refactor

Input tables

dob_cofos

dob_jobapplications

hpd_hny_units_by_building

Intermediate tables

developments

Created in create.sql, but not populated until jobnumber.sql (these two scripts should be combined)

developments_hny

Created in dob_hny_create.sql. Initially contains a copy of developments.

hny

Created in hny_create.sql
Initially contains content from hpd_hny_units_by_building merged with geocoded records from hpd_hny_units_by_building which aren't flagged as confidential, and where reporting_construction_type is "New Construction". Also merged with geocoded records from housing_input_hny_job_manual.

hny_job_lookup

Created as an empty table in hny_job_lookup.sql (CHANGE THIS)
Contains hny_id (a hash) from hny, and job_number, job_type from developments_hny NB records where they are merged on:
- geo_bbl, geo_bin, having total_units within 5 of units_prop: match_method = 'BINandBBL'
- geo_bbl, having total_units within 5 of units_prop: match_method = 'BBLONLY'
- hny geom within developments geom: match_method = 'Spatial'
Also populated in hny_manual_match.sql, w

hny_geocode_results

Created in geocode_hny.py
Contains geocoded records from hpd_hny_units_by_building which aren't flagged as confidential, and where reporting_construction_type is "New Construction".

hny_manual_geocode_results

Created in geocode_hny.py
Contains geocoded records from housing_input_hny_job_manual

hny_manual

Created in hny_manual_geomerge.sql
Contains housing_input_hny_job_manual fields merged with hny_manual_geocode_results, along with a hash hny_id

QAQC tables

dev_qc_water

Created in latlong.sql

qc_millionbinresearch

Created in dropmillionbin.sql
Contains job_number,job_type, dob_bin, geo_bin from developments for records with million bins

developments_co

Created in cotable.sql
Contains a subset of fields from dob_cofos where the job numbers exist in developments

Output tables (exports)

developments_export

Created in export.sql
The only purpose of this table, as distinct from developments_hny, is to rename and reorder columns. This was a last-step way of changing schema and should be built in to the process upstream.
Other last-step fixes update developments_export in export.sql, including
- Forcing occ_category to be residential for records where corrections resulted in non-null unit fields, or there are keyword matches in occ_category, occ_proposed, occ_initial, excluding HNY hotels or dorms that are not mixed-use. Every other record has the occ_category forced to "Other." This should move upstream.
- Overwriting units_initial, units_net, and units_prop to zero for non-residential rows (after the forced correction above). This should be moved upstream.

devdb_export

Created in export.sql
This is a cut of developments_export where dates are in the desired range and the outlier flag is FALSE (this logic isn't relevant anymore)

housing_export

Created in export.sql
Same time cut as devdb_export, but only includes records where occ_category is "Other." Currently, the time filter logic gets repeated, rather than extracting housing_export as a subset of devdb_export.

Spatial Attributes Assignment

About

Spatial Attributes can be put into two categories, geometries (geom, longtitude, latitude) and spatial boundaries (geo_cd, geo_censusblock2010, geo_censustract2010, etc). You can find all final consolidated geospatial fields from the table SPATIAL_devdb

    CREATE TABLE SPATIAL_devdb (
        uid integer,
        geo_bbl text,
        geo_bin text,
        geo_address_numbr text,
        geo_address_street text,
        geo_address text,
        geo_zipcode text,
        geo_boro text,
        geo_cd text,
        geo_council text,
        geo_ntacode2010 text,
        geo_ntaname2010 character varying,
        geo_censusblock2010 text,
        geo_censustract2010 text,
        geo_csd text,
        geo_policeprct text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_puma text,
        geo_schoolelmntry character varying,
        geo_schoolmiddle character varying,
        geo_schoolsubdist character varying,
        geo_latitude double precision,
        geo_longitude double precision,
        latitude double precision,
        longitude double precision,
        geom geometry,
        geomsource text
    );

Geosupport Logic

Input:

-- for Devdb main table
    SELECT
        distinct uid,
        address_numbr as house_number,
        address_street as street_name,
        boro as borough
    FROM _INIT_devdb

Pass through 1B regular mode
If 2 failed, then pass through 1B tpad mode
If 3 failed, then mark status as failure

Geom Assignment

Relevant Files

db-developments/developments_build/python/geocode.py
db-developments/developments_build/sql/_geo.sql

For every job in _INIT_devdb we will pass address_numbr,address_street and boro. Geocoded results will be recorded in _GEO_devdb. Here are the columns included below:

    CREATE TABLE _GEO_devdb (
        geo_address_street text,
        geo_address_numbr text,
        latitude text,
        longitude text,
        geo_bin text,
        geo_bbl text,
        geo_boro text,
        geo_cd text,
        geo_puma text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_censustract2010 text,
        geo_censusblock2010 text,
        geo_council text,
        geo_csd text,
        geo_policeprct text,
        geo_zipcode text,
        geo_ntacode2010 text,
        grc text,
        grc2 text,
        msg text,
        msg2 text,
        uid text,
        mode text,
        func text,
        status text
    );

To create the field geom, we use longitude and latitude from _GEO_devdb as default. For all records, we will create geom using the following hierachy:
1. Take geometry from using DoITT building footprints centroid using bin as a join_key (note bin from dob_jobapplications directly)
2. Take geometry from using DoITT building footprints centroid using geo_bin as a join_key (note that geo_bin are bins from geosupport, taking from _GEO_devdb)
3. Then take lat / long from BBL point via GeoSupport
4. Lastly, take BBL centroid from shoreline clipped MapPLUTO using bbl as join_key (note bbl from dob_jobapplications directly)
Table GEO_devdb will be created with consolidated geometries with the following schema.

    CREATE TABLE geo_devdb (
        uid integer,
        job_number character varying,
        bbl text,
        bin character varying,
        date_lastupdt character varying,
        job_desc character varying,
        geo_bbl text,
        geo_bin text,
        geo_address_numbr text,
        geo_address_street text,
        geo_address text,
        geo_zipcode text,
        geo_boro text,
        geo_cd text,
        geo_council text,
        geo_ntacode2010 text,
        geo_censusblock2010 text,
        geo_censustract2010 text,
        geo_csd text,
        geo_policeprct text,
        geo_puma text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_latitude double precision,
        geo_longitude double precision,
        mode text,
        latitude double precision,
        longitude double precision,
        geom geometry,
        geomsource text
    );

Corrections are applied on GEO_devdb with manually researched longitude, latitude and geom. Note that you can find where geoms are coming from by looking at geomsource. e.g.

BIN DOB buildingfootprints BBL DOB MapPLUTO Lat/Long geosupport Lat/Long DCP

Spatial Attributes

Relevant Files

db-developments/developments_build/sql/_spatial.sql

Building on GEO_devdb, we will use the geom field to extract the spatial boundary fields using spatial join and create _SPATIAL_devdb. This table is created to help filling NULLs in any of the spatial attributes. Note that geo_schoolelmntry, geo_schoolmiddle, and geo_schoolsubdist are only coming from spatial joins.

    CREATE TABLE _SPATIAL_devdb (
        uid integer,
        geo_cd character varying,
        geo_ntacode2010 character varying,
        geo_censusblock2010 character varying,
        geo_censustract2010 character varying,
        geo_csd character varying,
        geo_boro character varying,
        geo_council character varying,
        geo_bbl character varying,
        geo_zipcode character varying,
        geo_policeprct character varying,
        geo_firecompany character varying,
        geo_firebattalion character varying,
        geo_firedivision character varying,
        geo_puma character varying,
        geo_bin character varying,
        base_bbl character varying,
        geo_schoolelmntry character varying,
        geo_schoolmiddle character varying,
        geo_schoolsubdist character varying
    );

Merging _SPATIAL_devdb and GEO_devdb we will create a consolidated spatial attributes with both geom and other spatial boundaries.

    CREATE TABLE SPATIAL_devdb (
        uid integer,
        geo_bbl text,
        geo_bin text,
        geo_address_numbr text,
        geo_address_street text,
        geo_address text,
        geo_zipcode text,
        geo_boro text,
        geo_cd text,
        geo_council text,
        geo_ntacode2010 text,
        geo_ntaname2010 character varying,
        geo_censusblock2010 text,
        geo_censustract2010 text,
        geo_csd text,
        geo_policeprct text,
        geo_firedivision text,
        geo_firebattalion text,
        geo_firecompany text,
        geo_puma text,
        geo_schoolelmntry character varying,
        geo_schoolmiddle character varying,
        geo_schoolsubdist character varying,
        geo_latitude double precision,
        geo_longitude double precision,
        latitude double precision,
        longitude double precision,
        geom geometry,
        geomsource text
    );

Occupancy Code

About

Assigning new Occupancy Codes

New lookup table

DOB Occupancy Code	20Q2 Value
.RES	Residential: Not Specified (.RES)
A	Industrial: High Hazard (A)
A-1	Assembly: Theaters, Churches (A-1)
A-2	Assembly: Eating & Drinking (A-2)
A-3	Assembly: Other (A-3)
A-4	Assembly: Indoor Sports (A-4)
A-5	Assembly: Outdoors (A-5)
B	Commercial: Offices (B)
B-1	Storage: Moderate Hazard (B-1)
B-2	Storage: Low Hazard (B-2)
C	Commercial: Retail (C)
COM	Commercial: Not Specified (COM)
D-1	Industrial: Moderate Hazard (D-1)
D-2	Industrial: Low Hazard (D-2)
E	Unknown (E)
F-1	Industrial: Moderate Hazard (F-1)
F-1A	Assembly: Theaters, Churches (F-1A)
F-1B	Assembly: Theaters, Churches (F-1B)
F-2	Unknown (F-2)
F-3	Assembly: Museums (F-3)
F-4	Assembly: Eating & Drinking (F-4)
G	Educational (G)
H-1	Unknown (H-1)
H-2	Unknown (H-2)
H-3	Industrial: High Hazard (H-3)
H-4	Industrial: High Hazard (H-4)
H-5	Industrial: High Hazard (H-5)
I-1	Institutional: Assisted Living (I-1)
I-2	Institutional: Incapacitated (I-2)
I-3	Institutional: Restrained (I-3)
I-4	Institutional: Day Care (I-4)
J-0	Residential: 3 or More Units (J-0)
J-1	Residential: Hotels, Dormitories (J-1)
J-2	Residential: 3 or More Units (J-2)
J-3	Residential: 1-2 Family Houses (J-3)
K	Miscellaneous (K)
M	Commercial: Retail (M)
NC	Residential: 3 or More Units (NC)
PUB	Assembly: Other (PUB)
R-1	Residential: Hotels, Dormitories (R-1)
R-2	Residential: 3 or More Units (R-2)
R-3	Residential: 1-2 Family Houses (R-3)
RES	Residential: Not Specified (RES)
S-1	Storage: Moderate Hazard (S-1)
S-2	Storage: Low Hazard (S-2)
U	Miscellaneous (U)

Housing New York workflow

Overview

Goal

Match records from hpd_hny_units_by_building with the DOB records in developments, in order to merge on columns related to the number of affordable units.

Challenge

The relationship between hpd_hny_units_by_project records and developments records are not one-to-one
There are multiple ways to match records
- BIN & BBL
- BBL
- Spatially

General outline of steps

Geocode hny data
Find matches using the three different methods, excluding confidential hny projects and demolitions. All match methods also have the constraint that the total units in the hny record cannot be more than 5 units different than the units proposed for the developments record.
Identify cases where the matches are not one-to-one
Resolve ambiguous matches
- Using logic:
  - Matches with residential new buildings take precedence over matches with non-residential or A1
  - There is a hierarchy of match type: BIN & BBL, then BBL, then spatial
  - An associative join is performed where in a cluster of HNY and DevDB records every single HNY records will be matched with the other records in the cluster
  - Identify records both HNY and DevDB that are also matched with multiple counterpart records and create flags.

Two output tables for HNY and DevDB join

DevDB_hny_lookup
- Even after applying this hierarchy, not all matches are one-to-one, and so we need to resolve this.
  - In cases where one development record matches with many hny records, affordable units get aggregated
  - In cases where one hny record matches with many developments records, the units get assigned to the DOB job that has the lowest job number
  - Resolving many-to-one and one-to-many matches using the above two techniques also resolves many-to-many matches
HNY_devdb_lookup
- this is the table where HNY attributes can be then linked back to DevDB records via job number. It relies on the matches and relationship tables created in step 4 but resolve the relationship with somewhat different logics. Please note the descriptor below is always DevDB first and HNY second e.g. one-to-many means one DevDB records is identified as associated with many HNY records.
  - one-to-one: no need for resolution
  - one-to-many: the HNY ids will be joined together as an array and their units would be summed up and earliest dates will be set to the whole group e.g. project_start_date.
  - many-to-one: the DevDB record with the least job number will be picked to be associated with HNY record
  - many-to-many: first combine the HNY ids by grouping by DevDB record to create the HNY ids array. Then since the same array of HNY ids would be associated with multiple different DevDB record, pick the DevDB with the least job number to be associated with the entire group of HNY ids.

What is the roll of manual research?

Add matches that didn't exist
Remove matches

Corrections process:

We output matches we find automatically, prior to the automatic treatment of one-to-many and many-to-one cases.

job_number	hny_id	hny_project_id	all_counted_units	total_units	...
1234567	abcdef	a	5	10	...

We also automatically populate a table with all HNY records that did not match to a DOB record.

Corrections would involve filling in the action column, and adding rows if necessary.

job_number	hny_id	hny_project_id	action
1234567	abcdef	a	remove
1234567	ghijklm	g	add
7654321	ghijklm	g	remove
1234567	nopqrst	n	add

We apply these changes to the matches file, then use the logic above to

Sum up all hny records that match with a single developments record
Find the developments record with the smallest job_number to assign to a hny record

Manual corrections workflow

Sometimes source data will be incorrect or incomplete. Before publishing DevDB, research teams conduct an extensive manual research effort to review, verify, and correct data.

Table of Content

Corrections Table
HNY Corrections

Correction table

Records are added to the Correction table to edit an attribute of an existing record. The Correction table is also used exclude records. By capturing the old attribute value, it is easy to keep track of the original, pre-edited data. Additionally, a value is only overwritten if the old value matches the current value for the specified field. Therefore, if the source data is updated between versions, erroneous corrections won't be applied.

Table schema

job_number	field	old_value	new_value	editor	date	Notes
Unique ID of the project	Field where the value needs to be updated	Value that needs to be changed	New value	Name of person who made the edit	Date the edit was made	Notes from the editor

It is important that the editor input the job_number, field, and old value values exactly as they appear in DevDB, including any capitalization and special characters. Additionally, job_number + field must be unique within the Correction table. If there are instances where job_number + field are not unique, we will not ingest the data.

Types of Corrections

Below describes what types of corrections can be made via the Correction table, and gives an example of how to make each correction. If you'd like to make a correction that is not described, please reach out to Data Engineering.

Update the value of an existing record

A value is incorrect or missing and you know the correct information, so you'd like it to be reflected in the final output.

job_number	field	old_value	new_value	editor	date	Notes
520360101	classa_init	200	180	baiyue	6/20/2020	some comments
520360207	classa_init		180	baiyue	6/20/2020	filling in missing unit count

Remove a record

You do not want the record to be included in DevDB

job_number	field	old_value	new_value	editor	date	Notes
520360207	remove			baiyue	6/20/2020	some comments

Correcting geometries

If the spatial data for a record is incorrect or missing, you can correct or create the geometry by updating the latitude and longitude information for a record. Latitude and longitude values will only be applied from the Correction table is the initial point falls outside of a tax lot or within water.

job_number	field	old_value	new_value	editor	date	Notes
520360207	latitude		40.6257	baiyue	6/20/2020	filling in missing latitude
520360207	longitude	-75.0149	-74.0114	baiyue	6/20/2020	correcting latitude

!> Note: Please make sure for each geometry correction, you record both longitude and latitude in the corrections table. If only latitude or longitude is recorded, the correction will be discarded

Dependent variables

Sometimes the value in one field is dependent on another value. It's important to keep these dependencies in mind as you're updating values. If you update a value that has a dependency, be mindful of it's downstream implications because researched values from the manual corrections table are applied to original data values before dependent fields are calculated. See how variables relate to each other in the Attribute Mapping documentation.

Manual corrections are applied to the following fields in the following order:

_init.sql

stories_prop
bin
bbl
date_lastupdt
date_filed
date_statusd
date_statusp
date_statusr
date_statusx

_geo.sql

longitude
latitude
geom

init.sql

Remove records based on job_number
Remove bbl values

_occ.sql

occ_initial
occ_proposed

_units.sql

classa_init
classa_prop
hotel_init
hotel_prop
otherb_init
otherb_prop

_mid.sql

date_permittd (added 2021/02/22)
resid_flag

_status.sql

classa_complt
classa_incmpl
job_inactive

Manual corrections are used to match and unmatch Housing NY records to DOB jobs.

HNY Corrections

Making manual corrections to the HNY and DOB records matchings is a seperate manual corrections process, that is described here

QAQC Process

EDM

Overview

From an EDM perspective, the QAQC of the Developments and Housing Database's will focus on whether the code behaves the way we expect it to and whether the each step of the process was successful in generating the full extent of the data. Of particular importance is reviewing the ingestion, build, and exporting steps executed in the Github Workflow, the final outputs exported to Digital Ocean edm-publishing space and the Data Engineering QAQC app.

Besides the QAQC reports generated in the Data Engineering QAQC app, there are some tests that can be performed on a build of DevDB and HousingDB to help checks for accurate production run of the data product. Obviously, the accuracy of each individual record is outside the scope of this QAQC process but the Housing team does do an extensive research phase before release.

Dates

Some of the more indicative fields that can shed light onto the accuracy of the data are certain date fields in the final outputs (and as proxy, intermediate tables used to create the final tables, these fields almost exclusively come from DOB data). These checks should be performed for every production build of DevDB and Housing DB. Those date fields should reflect the most recent source data and associated timeline the data is meant to cover e.g. 22Q4 version should include data up to the date of the DOB data. Three useful fields to check for are:

permit_qrtr - field comes from DOB Permits data
date_filed - field comes from the DOB Job Application Filings
date_completed - field comes from the DOB COFO data

The above graphs come from a jupyter notebook using the 21Q4 DevDB data. Note that the graph shows the accumulative counts by week.

Check Manual Correction's were applied

A crucial step to check in the build process are whether or not the corrections files received from the Housing team are properly applied to the final DevDB and HousingDB tables which is provided by the housing research team. Currently, we do random spot checks of certain records comparing them with the manual_corrections.csv vs. the final outputs.

Unique ID Check

unique ID job number should be unique id so

assert df.shape[0] == len(df.job_number.unique())

Job Type There should only be three types of jobs in the final data:

Alteration
Demolition
New Building

A good sanity check is also plotting the aggregate tables into a map to showcase where the most development is taking place, this should match SME projections.

In the above graph, you see that a large majority of the development is in Long Island City and Downtown Brooklyn which has a large majority of the overall development compared to the rest of the city. This geographic distribution of the data tracks with what we know about development in NYC.

Housing Team

Data Dictionary

Job_Number

Longform Name: DOB Job Number
Old Name: job_number
Description: The DOB job application number assigned when the applicant begins the application. This is the unique identifier for the application submitted to the Department of Buildings (DOB). It may contain several work types, and more work types may be added as the application review and the work continues. It is a 9-digit number where the first digit indicates the borough where the building is located.
How it is created: These values are mapped from the dob_jobapplications field jobnumber

Job_Type

Longform Name: DOB Job Type
Old Name: job_type
Description: DOB's type category for the job application. More information is available here. The following types are included in this database:
- New Building (NB): an application to build a new structure. “NB” cannot be selected if any existing building elements are to remain—for example a part of an old foundation, a portion of a façade that will be incorporated into the construction, etc.
- Alteration Type I (A1): a major alteration that will change the use, egress, or occupancy of the building.
- Demolition (DM): an application to fully or partially demolish an existing building. Note that many demolition permits are only for partial demolitions and for garages (these are also captured).
How it is created:
- Mapped from dob_jobapplications field jobtype as follows:
  - A1 to Alteration
  - DM to Demolition
  - NB to New Building
- Administrative records are removed based on this field in combination with job_description.

Residflag

Longform Name: Residential Flag
Old Name: occ_category
Description: This field is used to identify jobs in buildings containing residential uses. A value of "residential" indicates that the job affects residential units in some way through new construction, alteration, or demolition. Only those jobs with a value of "residential" are included in the housing database. Manual research was conducted at DCP to help ensure that all work on buildings with residences receive this flag, though some records may remain misclassified.
How it is created:
- This field is dependent on hotel_init, hotel_prop, otherb_init, otherb_prop, classa_init, and classa_prop.
- If any of the dependent fields are not NULL, then the job is flagged as being residential.
- Manual research adds/removes this flag as necessary

Nonresflag

Longform Name: Non-residential Flag
Old Name: *NEW*
Description: This field is used to identify jobs in buildings containing non-residential uses. A value of "non-residential" indicates that the job affects some use type beyond residential, including commercial, industrial, or community facility uses. Mixed-use buildings will have values in both the Resid_Flag and Nonresid_Flag, since they contain both residential and nonresidential uses, but having flags in both of those columns does not necessarily mean that it is a mixed-use development since the flags do not distinguish between the initial and proposed uses in the building.
How it is created:
- This field is dependent on job_description, occ_initial, occ_proposed, and resid_flag
- nonresid_flag is true if:
  1. job_description has one of these keywords OR
  2. occ_initial or occ_proposed are one of these categories. OR
  3. resid_flag IS NULL

Job_Inactv

Longform Name: Job Inactive
Old Name: x_inactive
Description: This field is used to identify job applications that are likely inactive, either because the job is withdrawn, is a duplicate of another job, or the application has stalled for three or more years. Records flagged as inactive should be excluded in most analyses of incomplete jobs since they are extremely unlikely to ever reach completion. Note that this definition is probability based and therefore can not capture all permits that may eventually become inactive.
How it is created:
- This field is dependent on date_complete, job_status, date_lastupdt
- A job can only be inactive if date_complete is NULL
- Possible values:
  - Inactive: Withdrawn: The job status is 9. Withdrawn
  - Inactive: Stalled: A job has a status of Filed Application, Approved Application, OR Permitted for Construction AND date_lastupdt is 3 or more years before the vintage date.
  - Inactive: Duplicate: A job with a status that is Filed Application, Approved Application, OR Permitted for Construction MATCHES with a job that has a status of either 4. Partially Completed Construction OR 5. Completed Construction ON the following fields:
    - classa_init (where units are NOT NULL) AND
    - classa_prop (where units are NOT NULL) AND
    - address AND
    - job_type AND
    - the date_lastupdt of the incomplete job is before the date_lastupdt of the complete job.
  - NULL: Not inactive
  - Note, job_inactive can also get set to Inactive: Duplicate through manual research

Job_Status

Longform Name: DOB Job Status
Old Name: status
Description: DCP recode of DOB's status label. This describes the status of the job at the date of the data vintage. For example, a job marked as "3. Permitted" was at that status as of June 30, 2020 if using version 20Q2 of the DCP Developments Database. More details on each DOB status is available here. Jobs typically move through status A through X over time as they reach certain approval milestones:
- 1. Filed: job application is at status A - G at the time of publication. Application submitted, but review is not yet in progress.
- 2. Plan Examination: application is at status H - P. Plan examination is in progress but not yet approved.
- 3. Permitted: application is at status Q and R and may begin construction.
- 4. Partial Complete: application at status U and X, and CO issued for NB or A1 job type, and the CO is a Temporary CO AND less than 80% of the units are completed for a building with 20 or more units.
- 5. Complete: For new buildings and alterations, application is at status U and X, or a CO has been issued. For demolitions, the application is at status X. DCP has decided to mark demolitions as complete when they reach status X, but list the completion date as equal to status Q because this is likely when the building must be vacated, and it appears that many buildings are physically demolished some time before receiving sign off (status X).
- 9. Withdrawn: application is at status 3. The application has been withdrawn by the applicant.
How it is created:
- This field is dependent on on job_type, co_latest_certtype, classa_complt_pct, classa_complt_diff, classa_net, x_withdrawal, and the date fields

Compltyear

Longform Name: Completed Year
Old Name: *NEW*
Description: Year the job was completed. For new buildings and alterations, this is defined as the year of the first certificate of occupancy issuance. For demolitions, this is the year that the demolition was permitted (reached status Q).
How it is created:
- Dependent on date_complete, this field is the year of date_complete

Compltqrtr

Longform Name: Completed Quarter
Old Name: *NEW*
Description: The year and quarter a job was completed. Follows the same logic as Complete_Year
How it is created:
- Dependent on date_complete, this field is the year-quarter of date_complete

Permityear

Longform Name: Permitted Year
Old Name: *NEW*
Description: Year the job was permitted. For all job types, this is defined as the status Q year.
How it is created:
- Dependent on date_permittd, this field is the year of date_permittd

Permitqrtr

Longform Name: Permitted Quarter
Old Name: *NEW*
Description: The year and quarter the job was permitted. For all job types, this is defined as the status Q quarter.
How it is created:
- Dependent on date_permittd, this field is the year-quarter of date_permittd

Classainit

Longform Name: Units Class A Initial
Old Name: units_init
Description: Number of units that initially existed in the building at the time of the job application, as reported by the applicant. This field is edited by DCP to only count Class A units, which are units in houses or apartment buildings intended for long-term residential use (greater than 30 days), and typically do not require the use of shared kitchens and bathrooms. The definition of Class A and other unit types is available here.
How it is created:
- Mapped from the dob_jobapplications field existingdwellingunits
- Set to 0 for New Buildings
- If resid_flag is NULL after manual corrections get applied, this field gets set to NULL

Classaprop

Longform Name: Units Class A Proposed
Old Name: units_prop
Description: Number of units proposed in the job application after the proposed work has been completed, as reported by the applicant and edited by DCP to count only Class A units.
How it is created:
- Mapped from the dob_jobapplications field proposeddwellingunits
- Set to 0 for Demolitions
- If resid_flag is NULL after manual corrections get applied, this field gets set to NULL

Classanet

Longform Name: Units Class A Net Change
Old Name: units_net
Description: Net change in Class A unit count between the number of units existing at the time of application and the number of units proposed.
How it is created:
- Dependent on classa_init and classa_prop
- The difference between classa_init and classa_prop
- If resid_flag is NULL after manual corrections get applied, this field gets set to NULL

ClassaHNY

Longform Name: Units Affordable in Housing NY
Old Name: affordable_units
Description: The total number of affordable units that are in the proposed building, counted towards the Housing New York plan.
How it is created:
- Mapped from hpd_hny_units_by_building field all_counted_units
- This is sum of all_counted_units for HNY records associated with the job

HotelInit

Longform Name: Units Hotel Initial
Old Name: *NEW*
Description: Number of hotel units that initially existed in the building at the time of the job application, as determined through DCP research. Note that hotel units are a subtype of Class B units which are typically occupied as for-profit businesses for short-term (less than 30 days), full-service lodging.
How it is created:
- This field is programatically set to 0 if the job_type is 'New Building'. All other values come directly from the manual research table.
- If resid_flag is NULL after manual corrections get applied, this field gets set to NULL

HotelProp

Longform Name: Units Hotel Proposed
Old Name: *NEW*
Description: Number of hotel units proposed in the job application after the proposed work has been completed, as determined through DCP research.
How it is created:
- This field is programatically set to 0 if the job_type is 'Demolition'. All other values come directly from the manual research table.
- If resid_flag is NULL after manual corrections get applied, this field gets set to NULL

OtherBInit

Longform Name: Units Other B Initial
Old Name: *NEW*
Description: Number of Class B units (excluding hotel units) that initially existed in the building at the time of the job application, as determined through DCP research. Other Class B units include all dwellings that are not Class A units or hotels, and may include single room occupancy units, dormitories, certain kinds of supportive housing and assisted living, homeless shelters, convents and monasteries, among many other forms of temporary lodging or lodging with communal kitchens or bathrooms. The definition of Class B and other unit types is available here.
How it is created:
- This field is programatically set to 0 if the job_type is 'New Building'. All other values come directly from the manual research table.
- If resid_flag is NULL after manual corrections get applied, this field gets set to NULL

OtherBProp

Longform Name: Units Other B Proposed
Old Name: *NEW*
Description: Number of Class B units (excluding hotel units) proposed in the job application after the proposed work has been completed, as determined through DCP research.
How it is created:
- This field is programatically set to 0 if the job_type is 'Demolition'. All other values come directly from the manual research table.
- If resid_flag is NULL after manual corrections get applied, this field gets set to NULL

Units_CO

Longform Name: Units on CO
Old Name: units_complete
Description: Number of dwelling units provided on the temporary or final certificates of occupancy. This unit count may include Class A units in addition to Class B units and hotel units.
How it is created:
- Mapped from dob_cofos field numofdwellingunits

Boro

Longform Name: Borough
Old Name: boro
Description: The NYC borough where the proposed work will take place.

BIN

Longform Name: BIN
Old Name: bin
Description: Building Identification Number (BIN) supplied by Geosupport.

BBL

Longform Name: BBL
Old Name: bbl
Description: Borough-Block-Lot tax ID number of the parcel where the proposed work will take place.

AddressNum

Longform Name: Street Number
Old Name: address_house
Description: The house number for the building where the proposed work will take place.
How it is created:
- This is the house number returned by Geosupport during geocoding. If, however, Geosupport did not return address information, then this is the housenumber from dob_jobapplications.

AddressSt

Longform Name: Street Name
Old Name: address_street
Description: The street name for the building where the proposed work will take place.
How it is created:
- This is the street name returned by Geosupport during geocoding. If, however, Geosupport did not return address information, then this is the streetname from dob_jobapplications.

Address

Longform Name: Address
Old Name: address
Description: Concatenated street number and street name for the building where the proposed work will take place.
How it is created:
- This is a concatenation of the house number and street name returned by Geosupport during geocoding. If, however, Geosupport did not return address information, then this is the concatenation of housenumber and streetname from dob_jobapplications.

Occ_Init

Longform Name: Occupancy Initial
Old Name: occ_init
Description: Description of the existing occupancy type at the time of the job application. This indicates what a site was used for before the proposed job. It is a more descriptive recode of the occupancy code that the applicant submitted to DOB as the initial occupancy type of the building. Note that the applicant may only choose one occupancy code, even in a building containing multiple uses, so this code is only able to describe one of many possible uses.
How it is created:
- This field is mapped from the dob_jobapplications field existingoccupancy and translated using the lookup table
- If job_type is 'New Building', this field gets set to 'Empty Site'

Occ_Prop

Longform Name: Occupancy Proposed
Old Name: occ_prop
Description: Description of the proposed occupancy type at the time of the job application. This indicates what a site will be used for after the proposed job is complete. It is a more descriptive recode of the occupancy code that the applicant submitted to DOB as the proposed occupancy type of the building. Note that the applicant may only choose one occupancy code, even in a building containing multiple uses, so this code is only able to describe one of many possible uses.
How it is created:
- This field is mapped from the dob_jobapplications field proposedoccupancy and translated using the lookup table
- If job_type is 'Demolition', this field gets set to 'Empty Site'

Bldg_Class

Longform Name: Building Class
Old Name: *NEW*
Description:
How it is created:
- This field is mapped from the dob_jobapplications field buildingclass

Job_Desc

Longform Name: DOB Job Description
Old Name: job_description
Description: The general description of the work being applied for. This field is free text, and is filled out by the applicant.
How it is created and used:
- This field is mapped from the dob_jobapplications field jobdescription.
- Administrative records, or jobs with no actual construction work, are removed based on this field in combination with job_type.
- Test records are removed based on this field.

Desc_Other

Longform Name: Other Description
Old Name: *NEW*
Description:
How it is created:
- This field is mapped from the dob_jobapplications field otherdesc

Date_Filed

Longform Name: Date Filed (Status A)
Old Name: status_a
Description: Date of job status A (pre-filing application). This is the first step in the process for all job applications. The job application # is assigned at this status. This occurs when the applicant submits any part of the application (even a single form), in person or electronically.
How it is created:
- This field is mapped from the dob_jobapplications field prefilingdate

Date_StatusD

Longform Name: Status D
Old Name: status_d
Description: Date of job status D (completed application on file). This is when all data entry is complete and payments have been made.
How it is created:
- This field is mapped from the dob_jobapplications field fullypaid

Date_StatusP

Longform Name: Status P
Old Name: status_p
Description: Date of job status P (plan examination approval). This is when the entire job has been approved by the plan examiner. The applicant can now apply for a permit.
How it is created:
- This field is mapped from the dob_jobapplications field approved

Date_Permittd

Longform Name: Date Permitted (Status Q)
Old Name: status_q
Description: Date of job status Q (first partial permit issuance). This is when construction work may begin. This field should be used for identifying the number of permits approved in a given year.
How it is created:
- This field is mapped from the earliest date in the dob_permitissuance field issuancedate

Date_StatusR

Longform Name: Status R
Old Name: status_r
Description: Date of job status R (full permit issuance). This is when all necessary permits have been approved for a job.
How it is created:
- This field is mapped from

Date_StatusX

Longform Name: Status X
Old Name: status_x
Description: Date of job status X (job completion). For new buildings and alterations, date of earliest certificate of occupancy issuance (date_complete) is more reliable in determining completion date.
How it is created:
- This field is mapped from the dob_jobapplications field signoffdate

Date_LastUpdt

Longform Name: Date Last Status
Old Name: status_date
Description: The date of the last update to the DOB record for the job filing.
How it is created:
- This field is mapped from the dob_jobapplications field latestactiondate

Date_Complete

Longform Name: Date Completed (First CO)
Old Name: co_earliest_effectivedate
Description: DCP's best estimate of completion date for all jobs. For new buildings and alterations, date complete is equal to the date of the earliest certificate of occupancy. For demolitions, date complete is equal to status Q (permit issued), since demolitions do not receive certificates of occupancy. Blank indicates no certificate of occupancy has been issued. Typically, a building can be considered complete at this stage. Large buildings with many units may have units receiving certificates of occupancy over a longer period of time.
How it is created:
- This field depends on job_type, date_permitted, and date_statusx
- For New Buildings and Alterations, this field is mapped from the earliest effectivedate from dob_cofos
- For Demolitions, this field is mapped from date_permitted for as long as date_statusx IS NOT NULL

ZoningDist1

Longform Name: Zoning District 1
Old Name: *NEW*
Description: The primary zoning district of the tax lot per the applicant at time of application. For more information see http://www1.nyc.gov/site/planning/zoning/about-zoning.page.
How it is created:
- This field is the dob_jobapplications field zoningdist1

ZoningDist2

Longform Name: Zoning District 2
Old Name: *NEW*
Description: The secondary zoning district of the tax lot per the applicant at time of application.
How it is created:
- This field is the dob_jobapplications field zoningdist2

ZoningDist3

Longform Name: Zoning District 3
Old Name: *NEW*
Description: The tertiary zoning district of the tax lot per the applicant at time of application.
How it is created:
- This field is the dob_jobapplications field zoningdist3

SpecialDist1

Longform Name: Special District 1
Old Name: *NEW*
Description: The primary special zoning district of the tax lot per the applicant at time of application. Other zoning designations may appear in this field, such as industrial business zones (IBZ), mandatory inclusionary housing (MIH) areas, or other zoning designations. This field is provided by the applicant, and is likely inconsistent.
How it is created:
- This field is mapped from the dob_jobapplications field specialdistrict1

SpecialDist2

Longform Name: Special District 2
Old Name: *NEW*
Description: The secondary special zoning district of the tax lot per the applicant at time of application.
How it is created:
- This field is mapped from the dob_jobapplications field specialdistrict2

Landmark

Longform Name: Landmark
Old Name: *NEW*
Description: Indicates that the building has been designated as a landmark building by the Landmarks Preservation Commission.
How it is created:
- This field is mapped from the dob_jobapplications field landmarked

ZSF_Init

Longform Name: Existing Zoning Sqft
Old Name: *NEW*
Description:
How it is created:
- This field is mapped from the dob_jobapplications field existingzoningsqft

ZSF_Prop

Longform Name: Proposed Zoning Sqft
Old Name: *NEW*
Description:
How it is created:
- This field is mapped from the dob_jobapplications field proposedzoningsqft

Stories_Init

Longform Name: Floors Initial
Old Name: stories_init
Description: The existing number of stories/floors in the building, as reported by the applicant.
How it is created:
- This field depends on job_type
- This field is mapped from the dob_jobapplications field existingnumstories if the record is an Alteration or Demolition
- Values of '0' are replaced with NULL

Stories_Prop

Longform Name: Floors Proposed
Old Name: stories_prop
Description: The number of stories/floors in the building after the work is done, as reported by the applicant.
How it is created:
- This field depends on job_type
- This field is mapped from the dob_jobapplications field proposednumstories if the record is an Alteration or New Building
- Values of '0' are replaced with NULL

Height_Init

Longform Name: Height Initial
Old Name: *NEW*
Description: The height of the existing building in feet, as reported by the applicant.
How it is created:
- This field depends on job_type
- This field is mapped from the dob_jobapplications field existingheight if the record is an Alteration or Demolition
- Values of '0' are replaced with NULL

Height_Prop

Longform Name: Height Proposed
Old Name: *NEW*
Description: The proposed height of the building in feet after the proposed work has been completed, as reported by the applicant.
How it is created:
- This field depends on job_type
- This field is mapped from the dob_jobapplications field proposednumstories if the record is an Alteration or Demolition
- Values of '0' are replaced with NULL

ZoningSF_Init

Longform Name: Zoning SqFt Initial
Old Name: zoningsft_init
Description: The total zoning floor area for the existing building, if any, as reported by the applicant.
How it is created:

ZoningSF_Prop

Longform Name: Zoning SqFt Proposed
Old Name: zoningsft_prop
Description: The total zoning floor area for the building after the proposed work is completed, as reported by the applicant.
How it is created:

ConstructnSF

Longform Name: Total Construction SqFt
Old Name: *NEW*
Description: The square footage of the floor area of the construction, as reported by the applicant.
How it is created:
- This field is mapped from the dob_jobapplications field totalconstructionfloorarea

Enlargement

Longform Name: Enlargement Type
Old Name: *NEW*
Description: This indicates if the work to be done under the application will result in a horizontal and/or vertical enlargement, as reported by the applicant. Values include: Horizontal, Vertical, Horizontal and Vertical, [blank].
How it is created:
- This field is mapped from the dob_jobapplications fields horizontalenlrgmt and verticalenlrgmt
  - When horizontalenlrgmt = 'Y' AND verticalenlrgmt <> 'Y' then 'Horizontal'
  - When horizontalenlrgmt <> 'Y' AND verticalenlrgmt = 'Y' then 'Vertical'
  - When horizontalenlrgmt = 'Y' AND verticalenlrgmt = 'Y' then 'Horizontal and Vertical'

EnlargementSF

Longform Name: Enlargement SqFt
Old Name: *NEW*
Description: The additional square footage added by the construction enlargement, if any, as reported by the applicant.
How it is created:
- This field is mapped from dob_jobapplications field enlargementsqfootage

CostEstimate

Longform Name: Cost Estimate
Old Name: *NEW*
Description: Dollar amount that indicates the applicant's estimate for how much the job will cost.
How it is created:
- This field is mapped from dob_jobapplications field initialcost

LoftBoardCert

Longform Name: Loft Board Certification
Old Name: *NEW*
Description: Indicates that the job application involves a interim multiple dwelling (IMD) building. A loft board certificate is required for alteration of a registered IMD building. Check the Loft Board website for a list of IMD buildings or the BIS property Profile for LOFT designation.
How it is created:
- This field is mapped from dob_jobapplications field loftboard

eDesignation

Longform Name: e-Designation
Old Name: *NEW*
Description: Indicates whether or not the lot is designated with an “E” on the Zoning Maps of the City of New York for potential hazardous material contamination, air and/or noise quality impacts. Little “E” or RD Site: Anytime there is an environmental cleanup in the City of New York, the location is identified as “E” on DCP zoning maps. The Department of Environmental Protection must approve proposed work to ensure that the work satisfies environmental requirements.
How it is created:
- This field is mapped from dob_jobapplications field littlee
- WHEN littlee = 'Y' or 'H' then edesignation = 'Yes'

CurbCut

Longform Name: Curb Cut
Old Name: *NEW*
Description: Indicates that the job application includes curb cut work. An angled drop cut to the curb in the roadway, street, public right of way, or similar, which provides access to the zoning or tax lot.
How it is created:
- This field is mapped from dob_jobapplications field curbcut

TractHomes

Longform Name: Tract Homes
Old Name: *NEW*
Description: This indicates that a job filing is part of a housing development where all of the buildings will be built exactly the same way. There is a main job folder with all of the paperwork and plans. This was a common practice when there was a development boom, but is not common anymore.
How it is created:
- This field is mapped from dob_jobapplications field cluster

Ownership

Longform Name: Ownership Type
Old Name: *NEW*
Description: This indicates whether the property is government owned, the ownership structure, and non-profit status, as reported by the applicant and recoded by DCP.
How it is created:
- This field is mapped from dob_jobapplications fields cityowned, ownertype, and nonprofit
- The three input values are translated into a single value using this lookup table

Owner_FirstNm

Longform Name: Owner First Name
Old Name: *NEW*
Description: The first name of the building owner, as reported by the applicant.
How it is created:
- This field is mapped from dob_jobapplications field ownerfirstname

Owner_LastNm

Longform Name: Owner Last Name
Old Name: *NEW*
Description: The last name of the building owner, as reported by the applicant.
How it is created:
- This field is mapped from dob_jobapplications field ownerlastname

Owner_BizNm

Longform Name: Owner Business Name
Old Name: *NEW*
Description: The business name of the building owner, as reported by the applicant.
How it is created:
- This field is mapped from dob_jobapplications field ownerbusinessname

Owner_Address

Longform Name: Owner Address
Old Name: *NEW*
Description: The house number and street for the building owner's address, as reported by the applicant.
How it is created:
- This field is mapped from dob_jobapplications field ownerhousestreetname

Owner_ZipCode

Longform Name: Owner Zip Code
Old Name: *NEW*
Description: The zip code for the building owner's address, as reported by the applicant.
How it is created:
- This field is mapped from dob_jobapplications field zip

Owner_Phone

Longform Name: Owner Phone Number
Old Name: *NEW*
Description: The phone number for the building owner, as reported by the applicant.
How it is created:
- This field is mapped from dob_jobapplications field ownerphone

PLUTO_UnitRes

Longform Name: PLUTO Units Residential
Old Name: *NEW*
Description: Per MapPLUTO, the sum of residential units in all buildings on the tax lot. If there are no residential units in the tax lot the field is zero.Hotels/motels, nursing homes and SROs do not have residential units, but boarding houses do. Basement units for building superintendents are counted as a residential unit. Update of residential units triggered when a DOB permit is issued. Field Name: TOTALUNITS.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_BldgSF

Longform Name: PLUTO Building SqFt
Old Name: *NEW*
Description: Per MapPLUTO, the total gross area in square feet, except for condominium measurements, which come from the Condo Declaration and are net square footage not gross.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_ComSF

Longform Name: PLUTO Commercial SqFt
Old Name: *NEW*
Description: Per MapPLUTO, an estimate of the exterior dimensions of the portion of the structure(s) allocated for commercial use.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_OffcSF

Longform Name: PLUTO Office SqFt
Old Name: *NEW*
Description: Per MapPLUTO, an estimate of the exterior dimensions of the portion of the structure(s) allocated for office use.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_RetlSF

Longform Name: PLUTO Retail SqFt
Old Name: *NEW*
Description: Per MapPLUTO, an estimate of the exterior dimensions of the portion of the structure(s) allocated for retail use.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_ResSF

Longform Name: PLUTO Residential SqFt
Old Name: *NEW*
Description: Per MapPLUTO, an estimate of the exterior dimensions of the portion of the structure(s) allocated for residential use.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_YrBuilt

Longform Name: PLUTO Year Built
Old Name: *NEW*
Description: Per MapPLUTO, the year construction of the building was completed. Note the DCP HED has found this to be untrue.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_YrAlt1

Longform Name: PLUTO Most Recent Alteration Year
Old Name: *NEW*
Description: Per MapPLUTO, if a building has only been altered once, YEAR ALTERED 1 is the date that alteration began.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_YrAlt2

Longform Name: PLUTO Second Most Recent Alteration Year
Old Name: *NEW*
Description: Per MapPLUTO, if a building has only been altered once, this field is blank.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_Histdst

Longform Name: PLUTO Historic District
Old Name: *NEW*
Description: Per MapPLUTO, the name of the Historic District as designated by the New York City Landmarks Preservation Commission.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_Landmk

Longform Name: PLUTO Landmark
Old Name: *NEW*
Description: Per MapPLUTO, the name of an individual landmark, landmark site( e.g. Richmondtown Restoration) or an interior landmark, as designated by the New York City Landmarks Preservation Commission
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_BldgCls

Longform Name: PLUTO Building Class
Old Name: *NEW*
Description: Per MapPLUTO, a code describing the major use of structures on the tax lot.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_LandUse

Longform Name: PLUTO Land Use
Old Name: *NEW*
Description: Per MapPLUTO, a code for the tax lot's land use category. The Department of City Planning has created 11 land use categories and assigns each BUILDING CLASS to the most appropriate land use category.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_Owner

Longform Name: PLUTO Owner Name
Old Name: *NEW*
Description: Per MapPLUTO, the name of the tax lot owner.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_OwnType

Longform Name: PLUTO Ownership Type
Old Name: *NEW*
Description: Per MapPLUTO, a code indicating type of ownership for the tax lot.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_Condo

Longform Name: PLUTO Condominium Number
Old Name: *NEW*
Description: Per MapPLUTO, the condominium number assigned to the complex. Condominium numbers are unique within a borough.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_Bldgs

Longform Name: PLUTO Number of Buildings
Old Name: *NEW*
Description: Per MapPLUTO, the number of buildings on the tax lot. Extensions are not counted as separate buildings.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_Floors

Longform Name: PLUTO Number of Floors
Old Name: *NEW*
Description: Per MapPLUTO, the number of full and partial stories starting from the ground floor, for the tallest building on the tax lot.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_Version

Longform Name: Version of PLUTO
Old Name: *NEW*
Description: The version number of MapPLUTO joined to the Developments Database.

CenBlock2010

Longform Name: FIPS Census Block 2010
Old Name: *NEW*
Description: Census Block FIPS code.
How it is created:
- This is a reformatted version of bctcb2010 that uses county FIPS instead of one-digit borough code.

BCTCB2010

Longform Name: BCTCB 2010
Old Name: *NEW*
Description: The Borough - Census Tract - Census Block code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with Census Block boundaries.

BCT2010

Longform Name: BCT 2010
Old Name: *NEW*
Description: The Borough - Census Tract code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with Census Tract boundaries.

NTA2010

Longform Name: NTA 2010
Old Name: *NEW*
Description: Neighborhood Tabulation Area (NTA) code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with NTA boundaries.

NTAName2010

Longform Name: NTA Name 2010
Old Name: *NEW*
Description: Neighborhood Tabulation Area (NTA) descriptive name.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with NTA boundaries.

PUMA2010

Longform Name: PUMA 2010
Old Name: *NEW*
Description: Public Use Microdata Area (PUMA) code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with PUMA boundaries.

ComunityDist

Longform Name: Community District
Old Name: *NEW*
Description: NYC Community District code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with community district boundaries.

CouncilDist

Longform Name: Council District
Old Name: *NEW*
Description: NYC Council District code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with council district boundaries.

SchoolSubDist

Longform Name: School Subdistrict
Old Name: *NEW*
Description: NYC School Subdistrict code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with school subdistrict boundaries.

SchoolCommnty

Longform Name: Community School District
Old Name: *NEW*
Description: NYC Community School District code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with community school district boundaries.

SchoolElmntry

Longform Name: Elementary School Zone
Old Name: *NEW*
Description: NYC Elementary School Zone code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with elementary school zone boundaries.

SchoolMiddle

Longform Name: Middle School Zone
Old Name: *NEW*
Description: NYC Middle School Zone code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with middle school zone boundaries.

FireCompany

Longform Name: Fire Company
Old Name: *NEW*
Description: NYC Fire Company code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with fire company boundaries.

FireBattalion

Longform Name: Fire Battalion
Old Name: *NEW*
Description: NYC Fire Battalion code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with fire battalion boundaries.

FireDivision

Longform Name: Fire Division
Old Name: *NEW*
Description: NYC Fire Division code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with fire division boundaries.

PolicePrecnct

Longform Name: Police Precinct
Old Name: *NEW*
Description: NYC Police Precinct code.
How it is created:
- This field comes from Geosupport, as long as the address returns a value and the record is not in TPAD. Otherwise, the value is generated from a spatial join with police precinct boundaries.

DEPDrainArea

Longform Name: DEP Drainage Planning Area
Old Name: *NEW*
Description: NYC Dept. of Environmental Protection (DEP) Drainage Planning Area code.
How it is created:

DEPPumpStatn

Longform Name: DEP Pump Station
Old Name: *NEW*
Description: NYC Dept. of Environmental Protection (DEP) Pump Station code.
How it is created:

PLUTO_FIRM07

Longform Name: PLUTO FIRM 2007
Old Name: *NEW*
Description: A value of 1 means that some portion of the tax lot falls within the 1% annual chance floodplain as determined by FEMA’s 2007 Flood Insurance Rate Map. Note that buildings on the tax lot may or may not be in the portion of the tax lot that is within the 1% annual chance floodplain.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

PLUTO_PFIRM15

Longform Name: PLUTO PFIRM 2015
Old Name: *NEW*
Description: A value of 1 means that some portion of the tax lot falls within the 1% annual chance floodplain as determined by FEMA’s 2015 Preliminary Flood Insurance Rate Map. Note that buildings on the tax lot may or may not be in the portion of the tax lot that is within the 1% annual chance floodplain.
How it is created:
- This field is mapped from dcp_mappluto which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

Latitude

Longform Name: Latitude
Old Name: latitude
Description: Latitude in WGS84 / SRID:4326
How it is created:
- The latitude and longitude value are assigned in the following order. The associated geomsource values are in parentheses:
  1. DoITT building footprints center point using dob_bin as a join_key (BIN DOB buildingfootprints or BIN DOB buildingfootprints (historical))
  2. DoITT building footprints center point using geo_bin as a join_key
  3. Returned from GeoSupport 1B function (Lat/Lon DCP, Lat/Lon geosupport)
  4. BBL centroid from shoreline clipped MapPLUTO using dob_bbl as a join_key (BBL DOB MapPLUTO)
  5. latitude and longitude from dob_jobapplications (Lat/Lon DOB)

Longitude

Longform Name: Longitude
Old Name: longitude
Description: Longitude in WGS84 / SRID:4326
How it is created: See the description in latitude above

GeomSource

Longform Name: Geography Source
Old Name: x_geomsource
Description: Source of the geographic coordinates for the record.
How it is created:
- This field is dependent on latitude and longitude
- It is assigned as follows:
  - BIN DOB buildingfootprints: Geometry is the centroid of the DOITT building footprint that has the same BIN as the DOB job BIN
  - BIN DCP geosupport: Geometry is the centroid of the DOITT building footprint that has the same BIN as the BIN returned by Geosupport
  - Lat/Lon geosupport: Geometry is created from the latitude and longitude values returned by Geosupport
  - BBL DOB MapPLUTO: Geometry is the centroid of the Mappluto lot that has the same BBL as the DOB job BBL
  - BIN DOB buildingfootprints (historical): Geometry is the centroid of the historical DOITT building footprint that has the same BIN as the DOB job BIN
  - Lat/Lon DOB: Geometry is created from the latitude and longitude values in the DOB source data
  - Lat/Lon DCP: Geometry is created from latitude and longitude provided in the manual corrections file

DCPEditFields

Longform Name: DCP Edit Fields
Old Name: x_reason
Description: List of fields that were edited by DCP. This only lists fields where original DOB source data was overwritten by DCP, and doesn't include fields where DCP recodes data as part of the standard methodology.
How it is created:
- Whenever a field is altered by a manual correction (as opposed to the programatic logic described in this Data Dictionary), the field name gets added to dcpeditfields

HNY_ID

Longform Name: Housing New York ID
Old Name: hny_id
Description: nan
How it is created:
- This field is mapped from the hpd_hny_units_by_building fields project_id and building_id

HNY_JobRelate

Longform Name: Housing NY to Job Join Relationship
Old Name: hny_to_job_relat
Description: nan
How it is created:
- This field describes how many HNY jobs match to DOB jobs. See the HNY workflow documentation for more information about how records are matched.
  - one_to_one: a HNY job matched to a single record in the developments database
  - many_to_one: multiple HNY jobs matched to a single record in the developments database
  - one_to_many: one HNY job matched to a multiple records in the developments database
  - many_to_many: multiple HNY jobs matched to a multiple records in the developments database

Version

Longform Name: Version
Old Name: *NEW*
Description: Version of the Developments Database.

Attribute mapping

`job_number`

Mapped from dob_jobapplications jobnumber
The DOB job application number assigned when the applicant begins the application. This is the unique identifier for the application submitted to the Department of Buildings (DOB). It may contain several work types, and more work types may be added as the application review and the work continues. It is a 9-digit number where the first digit indicates the borough where the building is located.

`job_type`

Mapped from dob_jobapplications jobtype.
- A1 to Alteration
- DM to Demolition
- NB to New Building
Administrative records are removed based on this field in combination with job_description.
DOB's type category for the job application. The following types are included in this database:New Building (NB): an application to build a new structure.
- NB cannot be selected if any existing building elements are to remain—for example a part of an old foundation, a portion of a façade that will be incorporated into the construction, etc.
- Alteration Type I (A1): a major alteration that will change the use, egress, or occupancy of the building.
- Demolition (DM): an application to fully or partially demolish an existing building. Note that many demolition permits are only for partial demolitions and for garages (these are also captured).

For more information see https://www1.nyc.gov/site/buildings/homeowner/permits.page.

`resid_flag`

Dependent on hotel_init, hotel_prop, otherb_init, otherb_prop, classa_init, and classa_prop.
If any of the dependent fields are not NULL then the job is flagged as being residential.

`nonres_flag`

Dependent on job_description, occ_initial, occ_proposed, and resid_flag
nonresid_flag is true if

job_description has one of these keywords OR
occ_initial or occ_proposed are one of these categories. OR
resid_flag IS NULL

`job_inactive`

Dependent on date_complete, job_status, date_lastupdt
A job can only be inactive if date_complete is NULL
A job is set to inactive for:

All jobs with a status of 9. Withdrawn
Jobs with a status of Filed Application, Approved Application, OR Permitted for Construction AND date_lastupdt is 3 or more years before the vintage date.
If a job with a status that is Filed Application, Approved Application, OR Permitted for Construction MATCHES with a job that has a status of either 4. Partially Completed Construction OR 5. Completed Construction ON the following fields:

classa_init (where units are NOT NULL) AND
classa_prop (where units are NOT NULL) AND
address AND
job_type AND the date_lastupdt of the incomplete job is before the date_lastupdt of the complete job.

`job_status`

Dependent on on job_type, co_latest_certtype, classa_complt_pct, classa_complt_diff, classa_net, x_withdrawal, and date fields

if the withdrawn flag as a value of W, C THEN 9. Withdrawn
when it's a NB or A1 and the CO is a Temporary CO AND less than 80% of the units are completed for a building with 20 or more - units THEN 4. Partially - Completed Construction
date_complete IS NOT NULL THEN 5. Completed Construction
date_statusr IS NOT NULL THEN 3. Permitted for Construction
date_permittd IS NOT NULL THEN 3. Permitted for Construction
date_statusp IS NOT NULL THEN 2. Approved Application
assigned IS NOT NULL THEN 1. Filed Application
date_statusd IS NOT NULL THEN 1. Filed Application
paid IS NOT NULL THEN 1. Filed Application
date_filed IS NOT NULL THEN 1. Filed Application

`complete_year`

Dependent on date_complete.
The year of date_complete.

`complete_qrtr`

Dependent on date_complete.
The quarter of the year of date_complete.

`permit_year`

Dependent on date_permittd.
The year of date_permittd.

`permit_qrtr`

Dependent on date_permittd.
The quarter of the year of date_permittd.

`classa_init`

Mapped from dob_jobapplications existingdwellingunits
Set to 0 for New Buildings

`classa_prop`

Mapped from dob_jobapplications proposeddwellingunits
Set to 0 for Demolitions

`classa_net`

Dependent on classa_init and classa_prop
The difference between classa_init and classa_prop

`units_co`

Mapped from dob_cofos numofdwellingunits

`classa_hnyaff`

Mapped from hpd_hny_units_by_building all_counted_units
The sum of all_counted_units for HNY records associated with the job

`hotel_init`, `hotel_prop`, `otherb_init`, and `otherb_prop`

All are directly from the manual research table.

`boro`, `bin`, `bbl`

`address_numbr`, `address_st`, `address`

The address information for the record from Geosupport; however, if Geosupport did not return any information then this is the address information from the housenumber and streetname in dob_jobapplications.

`occ_initial`

Mapped from dob_jobapplications existingoccupancy and translated using the lookup table

`occ_proposed`

Mapped from dob_jobapplications proposedoccupancy and translated using the lookup table

`job_desc`

Mapped from dob_jobapplications jobdescription.
Administrative records are removed based on this field in combination with job_type.
Test records are removed based on this field.

`date_filed`

Mapped from dob_jobapplications prefilingdate

`date_statusd`

Mapped from dob_jobapplications fullypaid

`date_statusp`

Mapped from dob_jobapplications approved

`date_permittd`

The earliest issuancedate from dob_permitissuance

`date_statusx`

Mapped from dob_jobapplications signoffdate

`date_lastupdt`

Mapped from dob_jobapplications latestactiondate

`date_complete`

Mapped from the earliest effectivedate from dob_cofos for NBs and Alts
Mapped from date_permitted for DM IF date_statusx IS NOT NULL

`zoningdist1`, `zoningdist2`, `zoningdist3`, `specialdist1`, `specialdist2`, `landmark`, `zsf_init`, `zsf_prop`, `bldg_class`, `desc_other`

Mapped from dob_jobapplications zoningdist1, zoningdist2, zoningdist3, specialdistrict1, specialdistrict2, landmarked, existingzoningsqft, proposedzoningsqft, buildingclass, otherdesc

`stories_init`

Mapped from dob_jobapplications existingnumstories for only A1 and DM job types
'0' is replaced with NULL

`stories_prop`

Mapped from dob_jobapplications proposednumstories for only A1 and NB job types
'0' is replaced with NULL

`height_init`

Mapped from dob_jobapplications existingheight for only A1 and DM job types
'0' is replaced with NULL

`height_prop`

Mapped from dob_jobapplications proposedheight for only A1 and NB job types
'0' is replaced with NULL

`constructnsf`

Mapped from dob_jobapplications totalconstructionfloorarea

`enlargement`

Mapped from dob_jobapplications horizontalenlrgmt and verticalenlrgmt
When horizontalenlrgmt = 'Y' AND verticalenlrgmt <> 'Y' then 'Horizontal'
When horizontalenlrgmt <> 'Y' AND verticalenlrgmt = 'Y' then 'Vertical'
When horizontalenlrgmt = 'Y' AND verticalenlrgmt = 'Y' then 'Horizontal and Vertical'

`enlargementsf`

Mapped from dob_jobapplications enlargementsqfootage

`costestimate`

Mapped from dob_jobapplications initialcost

`loftboardcert`

Mapped from dob_jobapplications loftboard

`edesignation`

Mapped from dob_jobapplications littlee
WHEN littlee = 'Y' or 'H' then edesignation = 'Yes'

`curbcut` and `tracthomes`

Mapped from dob_jobapplications curbcut and cluster

`ownership`

Mapped from dob_jobapplications cityowned, ownertype, and nonprofit
The three input values are translated into a single value using this lookup table

`owner_name`

Mapped from dob_jobapplications ownerfirstname and ownerlastname

`owner_biznm`, `owner_address`, `owner_zipcode`, and `owner_phone`

Mapped from dob_jobapplications ownerbusinessname, ownerhousestreetname, zip, and ownerphone

`pluto_*`, `pluto_firm07`, and `pluto_pfirm15`

All of these fields are mapped from dcp_mappluto, which is the clipped version of MapPLUTO, by joining on the geo_bbl to the PLUTO bbl

`cenblock2010`, `bctcb2010`, `bct2010`, `nta2010`, `ntaname2010`, `puma2010`, `comunitydist`, `councildist`, `schoolsubdist`, `schoolcommnty`, `schoolelmntry`, `schoolmiddle`, `firecompany`, `firebattalion`, `firedivision`, `policeprecnct`

All of these fields come from Geosupport if the address returns a value and the record is not in TPAD; otherwise, the value is generated from a spatial join.

`latitude` and `longitude`

The latitude and longitude value are assigned in the following order:

DoITT building footprints point using dob_bin as a join_key (BIN DOB buildingfootprints or BIN DOB buildingfootprints (historical))
DoITT building footprints point using geo_bin as a join_key
GeoSupport 1B function (Lat/Lon DCP, Lat/Lon geosupport)
BBL centroid from shoreline clipped MapPLUTO using dob_bbl as a join_key (BBL DOB MapPLUTO)
latitude and longitude from dob_jobapplications (Lat/Lon DOB)

`geomsource`

Dependent on latitude and longitude
Describes how point geometry was created
- BIN DOB buildingfootprints: Geometry is the centroid of the DOITT building footprint that has the same BIN as the DOB job BIN
- BIN DCP geosupport: Geometry is the centroid of the DOITT building footprint that has the same BIN as the BIN returned by Geosupport
- Lat/Lon geosupport: Geometry is created from the latitude and longitude values returned by Geosupport
- BBL DOB MapPLUTO: Geometry is the centroid of the Mappluto lot that has the same BBL as the DOB job BBL
- BIN DOB buildingfootprints (historical): Geometry is the centroid of the historical DOITT building footprint that has the same BIN as the DOB job BIN
- Lat/Lon DOB: Geometry is created from the latitude and longitude values in the DOB source data
- Lat/Lon DCP: Geometry is created from latitude and longitude provided in the manual corrections file

`hny_id`

Mapped from hpd_hny_units_by_building project_id and building_id

`hny_jobrelate`

Dependent on how many HNY jobs match to one DOB job, and vice versa

`version`

The version of the database

Getting started

Code/Infrastructure

Data Products

Operations/Product Lifecycle
Data Catalog (Docs for products)