community districts city council districts assignment

[td928]

587 two reviewers preferred ☔ 🌧️ but one required

Overview

switch over from the rolling up from the census tract to community districts and council districts to using spatial join to fill the missing geometries after Geosupport didn't provide. Then downstream output needs to be modified accordingly to reflect the changes.

_functions.sql

the new get_cd and get_council are pretty much copying over the other spatial join functions. get_csd also got modified because it was pointing incorrectly to the city council geographies.

Review Tips

I would recommend running the spatial part of the pipeline to get the new geographies then check if the mismatch between CDs and boro mostly seems resolved (on my end it went from 4000+ records to 382 mismatch). Also just in general checking if the new comunitydist and councildist values make sense.

[AmandaDoyle]

I reviewed the logic and the commntydst, councildst, schcommnty fields in the new output table.

@mbh329 or @SashaWeinstein need to run the code and review outputs.

The logic for generating the values for commntydst, councildst, schcommnty looks correct. Though, some values in councildst are left padded with 0 whereas others are are not (i.e. 1 vs 01). Do we need to left pad the councildst values coming from the spatial join or geosupport?

[mbh329]

The logic looks good to me. I have one outstanding question and a comment:

• Should we be using Pluto 22v1 or Pluto 22v? The final devdb table uses 22v1 but not sure if want to be using the more recent build of pluto for the associated columns
• I took a look at a couple of the addresses that didn't have a corresponding CD or admin boundary, the streets are valid but it looks like the addresses are out of range of what geosupport is expecting. Specifically I looked at Walton Ave in the Bronx records and Hooker place in staten island records. I think Housing could just add these in a manual correction at a later point or maybe in the next release of geosupport these new address ranges will be addressed

Ran on my machine and output looks good as well.

[td928]

The logic looks good to me. I have one outstanding question and a comment:

• Should we be using Pluto 22v1 or Pluto 22v? The final devdb table uses 22v1 but not sure if want to be using the more recent build of pluto for the associated columns
• I took a look at a couple of the addresses that didn't have a corresponding CD or admin boundary, the streets are valid but it looks like the addresses are out of range of what geosupport is expecting. Specifically I looked at Walton Ave in the Bronx records and Hooker place in staten island records. I think Housing could just add these in a manual correction at a later point or maybe in the next release of geosupport these new address ranges will be addressed

Ran on my machine and output looks good as well.

I'm going with whichever pluto is latest when we did the first DevDB 22Q2 build. Granted the DevDB is also quite different from that first version but I think in principle still to keep the source data consistent across a single data update.

[mbh329]

Got it, that makes sense. forgot this has been quite the long process haha

[td928]

@AmandaDoyle addressed comment about left padding for council districts. Rerunning the whole thing here and a look at the output here. Let me know if the latest commit looks good and waiting for approval @mbh329

[mbh329]

Got it, let me run with the latest changes

[mbh329]

One thing to be mindful (but doesn't necessarily impact this PR) is that we should try to always stay consistent across our data products with our leftpadding of admin boundaries