Add SCA scripts to KPDB pipeline + fix borough geometry assignment

[mbh329]

motivations

• resolves issue #399.
• The original motivation behind this PR was to get the existing SCA aggregate scripts (that were written a few years ago and executed in carto) running so that we can deliver outputs to Housing/Capital Planning and they would perform any necessary clean up and formatting work before it was delivered to SCA.
• To Incorporate them into the KPDB build process I created a separate 04_sca_aggregate.sh to handle the execution and creation of the scripts and subsequent output files (longform_csd_output , longform_es_zone_output, longform_subdist_output_cp_assumptions). As of now this is NOT incorporated into the build workflow/Github Action and can only be done locally.

changes

• These 3 scripts are essentially the same scripts that we received with some minor tweaks to the table names so that they are pulling in KPDB specific tables which are "like for like" input tables (e.g. kpdb -> _kpdb, Doe_school_zones_es_2019 -> doe_eszones)
• Add in a separate script that preprocesses the input source data needed to run the scripts, creates a table called zap_project_many_bbls that was needed and appeared in each script, runs the each of the 3 scripts (boundaries_es_zone.sql, boundaries_school_districts.sql, and boundaries_school_subdistricts.sql) and then exports the CSV's. I decided to keep this separate from the existing build and export process because this is typically the very last thing that DE or Housing does. I don't think it makes sense to run the scripts and write new outputs each time we rebuild KPDB locally.
• Update the dev branch on the main KPDB repo with the latest data submodule to avoid confusion. This has been signed off by housing as the "final' version of KPDB with all the latest corrections.
• I updated the logic to apply the borough to a specific record based on that records geometry by taking the dcp_boroboundary file without water included and used st_intersect instead of st_within. The motivation behind this was "The issue was that when I was using st_intersect with the water included boundaries, Manhattan's water boundary is right up to the land boundary of Brooklyn so all the records on the BK waterfront with overlap into Manhattan were getting assigned to Manhattan".

• notes

• This should be rerun locally by another engineer to make sure it works on their machine.
• The sql scripts related to the SCA aggregate tables are a mess (weird formatting, etc) but I was hesitant to change the formatting due to the tight timeline. Once we have turned the page on this chapter of KPDB, we should go and reformat those scripts in another PR. Issue created here #401

todo

• [ ] ...

[fvankrieken]

A bit of cleanup - I would just delete the old scq sql files (5a, 5c, etc), they're checked in to main from the last PR if we really need to find them. And maybe update the comments (or mostly delete) at the top of our sca aggregate files

[fvankrieken]

Seems to all be building cleanly for me locally 👍

[fvankrieken]

Copying from #403

After fixes in said PR, 9 records are still having no boro assigned

source	record_id	record_name
HPD Projected Closings	3d3a2dd3c6ec864f5e18cec8e03eb2a7	784 COURTLANDT AVENUE
HPD Projected Closings	ff4c3b3e09d4c3a0d527a5c7988242d0	1559 PROSPECT PLACE
HPD RFPs	0ce4a0321f576ad9b397dec9358f255f	MWBE Site B - 1921 Atlantic Ave
HPD RFPs	100c52f92560bc161659f99b08340e9f	97 West 169 St
HPD RFPs	1db4d18c803723face138966e70095d8	NYCHA West Brighton RFP
HPD RFPs	473c582483789bb07b5837ecdb746ae4	Bedford-Stuyvesant Community Wealth and Wellness RFP (Fulton-Saratoga)
HPD RFPs	862c3d3640912f5ec6561375a03449ef	Hunters Point F + G
HPD RFPs	9bd199e88bb8dd9b128d0d3c71b520b5	Hunters Point F + G
HPD RFPs	defae821da6ac0b1a7610be5c7d5f484	SustaiNYC (E. 111th Street)

We'll likely need to add these to the corrections

[fvankrieken]

Merged my changes into this branch @mbh329. I'm going to rebuild from scratch locally now on this branch

Linking @AmandaDoyle's comment on my PR here - waiting on confirmation but then we'll need to apply corrections

[mbh329]

rebuilding locally now @fvankrieken

[mbh329]

ran into a storage issue, cleaning stuff up and will try to rerun

[damonmcc]

if this is a WIP, should we convert it to a draft PR and re-request review when it's ready?

[fvankrieken]

LGTM especially considering the urgency!

is it worth running locally before merging to ensure the csvs in sca_output/ are definitely the result of the latest code changes? looks like they were generate 2 days ago

Yes, we should commit latest generated sca outputs. Or @mbh329 are those supposed to go in the data repo instead?

[mbh329]

I've been generating them separately from the data repo, I think we can commit the most recent sca agg tables and then merge. Would you like to do that or should I? I have the latest ones from this morning but can rerun to make sure

[fvankrieken]

I've been generating them separately from the data repo, I think we can commit the most recent sca agg tables and then merge. Would you like to do that or should I? I have the latest ones from this morning but can rerun to make sure

I just dropped my local kpdb db to make room for edde so please do!

[mbh329]

rebuilding now with latest data/corrections

[mbh329]

Taking longer than I would like, ran into some more obtuse memory issues but building now

[mbh329]

[fvankrieken]

I didn't have any issue earlier - do you want me to build locally?

[mbh329]

got it going, on the sca_aggregate step now

[mbh329]

Still running the aggregate step but the corrections are not being applied to the 9 records without a borough. I double checked that I have the most recent data and I can confirm that the corrections_main table is up to date

[mbh329]

confirming that the units_gross corrections (the ones before our borough corrections) are being successfully applied to the kpdb table

[mbh329]

@AmandaDoyle @fvankrieken @damonmcc

[mbh329]

ran this and everything looked good on my local build