20Q2 - Description of Tables

[mgraber]

Description of tables

A running description of tables, what they contain, and when they are created

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_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_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

QAQC

init_QAQC

• created in qaqc_init.sql
• contains checks for invalid dates and test records

units_QAQC

• created in qaqc_units.sql
• contains all fields from QAQC_init, as well as checks for outliers and large reduction alterations

status_QAQC

• created in qaqc_status.sql
• contains all fields from QAQC_units, as well as checks for records with recent updates that are specified as inactive in manual corrections

mid_QAQC

• created in qaqc_mid.sql
• contains all fields from status_QAQC as well as several checks relating to units, occupancy, class b, CO mismatch, potential duplicates, incomplete tract homes, and overlapping demolitions and new buildings

MATCH_dem_nb

• created in qaqc_mid.sql
• contains all cases where a demolition shares a bbl with a new building
• if a job_number is in one of these matches, it will be reflected in a bool check in mid_QAQC

DUP_diff_job_number

• created in qaqc_mid.sql
• contains potential duplicate records -- the job_numbers of the match as well as their BBL and addresses
• equal_units field indicates whether the match is also on classa_net
• if a job_number is in one of these matches, it will be reflected in a bool check in mid_QAQC

[mgraber]

Change initial issue comment to reflect #101

[SPTKL]

built with mermaid

```mermaid 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 ```

[SPTKL]

moving all this to https://github.com/NYCPlanning/db-developments/wiki/Description-of-tables