Closed mbh329 closed 1 year ago
Github Action successfully ran and the output can be viewed here: https://cloud.digitalocean.com/spaces/edm-publishing?i=266877&path=db-developments%2F551-enhancement-add-dob-zsq-prop-data%2F
@td928 Did we get final sign off from housing that this is good to go and I can merge this in?
@td928 Did we get final sign off from housing that this is good to go and I can merge this in?
confirming with Alex if the non-usegroup stuff is ready to go as well
This PR addresses issue #551.
Enhancement Issue
Housing had requested updated data from DOB that included the proposed number of parking spaces and the proposed zoning square footage for 4 categories: residential, commercial, community facility, and manufacturing. We normally receive 4 DOB files as inputs for DevDB but the data requested only impacts the
dob_now_applications
file that is sent to us directly by DOB (discussion between DCP housing and DOB around getting this data for BIS leads us to believe that this data does not exist for BIS).The 5 new columns and their logic are as follows:
Note: It is possible for jobs for the same building to have populated data in more than one of the new columns (e.g. mixed use buildings)
Code/Scripts
Logic is pretty simple but took some time figuring out how the tables all combine to make the final devdb. The newest dob_now_applications
20220921
data was ingested via data library but was not set as thelatest
data as we are not sure if this will be the "stable" data moving forward and we don't have an updated dob_now_permits data from DOB (this is important because we want dob_now data to be synced/up to date with each other). The dataloading fordob_now_applications
was set to inlcude the20200921
data we got from DOB but not sure if we want to keep this in when merging to dev but would like to discuss this.The new logic was added to the
sql/now/_init.sql
script where the dob_now_applications data is transformed. Dummy columns of the same name were also added tosql/bis/_init.sql
in order to successfully implement theUNION
in thesql/_init.sql
. After that all the columns were added to the necessary intermediary tables and the final devdb output.