Rework PLUTO's create_allocated

[fvankrieken]

Using this largely as a place to take notes. This ended up being pretty rambling so I'll tidy up later. Very much a WIP issue

In pluto, unit information, lot/bldg value information, and bldgarea are calculated on an aggregate basis, based on primebbl. There are some odd cases - condos where each condo has the building's total condo_units as its condo_units field, rows that have the same primebbl where there are two rows, one seemingly as a primary condo row, the other as simply a single non-condo row.

Original motivation was to solve that first case, but in the process, it's become clear that it would be helpful to try to classify PTS rows.

Current approach

Currently, pluto_rpad_geo is used to sequentially create then update pluto_allocated. First, distinct primebbls (860610 of them) are inserted into the empty table. Then, they're updated by a few criteria to get their non-aggregate values

label	filtering clause	record count in 24v2	distinct primebbl in 24v2
standard	tl NOT LIKE '75%' AND condo_number IS NULL	847910	847910
condo (primebbl)	tl LIKE '75%' AND condo_number <> '0'	10258	10258

One issue with our current strategy is that there are 6 primebbls that have rows that meet both these criteria. So while our code is deterministic, these primebbls are inserted without data into pluto_allocated, then updated with data based on their "standard" rows, then updated with data based on their "condo" rows.

There are also 2448 distinct primebbls where condo_number is not null or zero despite having a lot like 75xx. Most of these do not get geoms in pluto. Not sure if these should be getting geom info from condo data, or where.

Regardless, it seems like it would be useful to start from more of a mode of classifying these rows.

Potential new structure

It makes sense instead of doing this update logic, where we insert bbls and then attempt to find their primary rows, to start with the "primary row" logic. There are 860609 rows where bbl = primebbl, with primebbl being distinct in this table. This seems like a good place to start. We lose one primebbl (3047927502), but that seems to be an odd case. We also in this case are maybe assuming that the 6 bbls that met both clauses above should be treated as condos, this seems largely right. We also then are assuming that the 2448 "semi-condo"s should be getting information from pluto_rpad_geo. These can be classified based on their lot number as condos or not (other than the semi-condos), and we can go from there

With that, we can continue to classify rows in PTS

• primary rows -> these can just go straight into pluto_allocated (860609)
  • standard lots (847903)
  • condo lots (10258)
  • these "semi-condo" lots (2448)
• condo rows belonging to primary rows (condo_number is not null) (291890)
• seemingly "non-condo" rows that still have bbl != primebbl despite sharing a primebbl with a condo (6)
• our floating row, primebbl 3047927502 (1)

These numbers do add up to 1152506, which is the total number of rows in PTS, so I didn't drop anything by accident.

Then, we can join primary condo rows to their subrows, and categorize what we see there a bit

[fvankrieken]

Unreasonably small apartments

As part of #831 , I've found a couple different instances

• condos
  • primary row and all unit rows have same units (so 17 total rows of 16 units each, for example)
  • primary row has 1 or 2 more, but each unit row has same (so primary has 18 units, each of 16 condo rows has 16)
  • either of above, but additional rows without res units (but with nonres units)
  • 3 or 4 unit rows which each have same units as primary (so 1 primary row, 3 unit rows, all with 217 tot units and 216 res units)
• vacant lots -> ongoing construction where there aren't units yet. There have been cases where it seems one development spans multiple tax lots (which presumably will join eventually), but each have area counts, unit counts, etc despite the site being only foundation or a vacant lot
• class b units. maybe can be identified by owner (nycha or other), or by bringing in some sort of dob dataset
• building spanning multiple lots. This seemed to be a recently completed development. Unclear how to distribute units across lots in pluto, or capture this (different addresses in PTS despite it being a single building)
• lower resarea than expected (sometimes listed in other area, sometimes missing)

[fvankrieken]

For a messy condo case, 4006197501 is a great example

• 1 primarybbl row. gross_sqft of 20,309, residarea of 0, 18 coop_apts (res units), 18 units (total units)
• 8 rows with aptno like 'SP[0-9]+'. Seem to be storage areas. 0 coop_apts, 8 "units" each
• 9 rows for parking spaces. 3 of them have no areas and 3 units each, the other 6 have areas and 6 units each
• 16 condo unit rows. Each has gross_sqft, residarea, 16 coop_apts, 16 units

Every non-primary row has distinct monetary values. Summed sq footage of all unit rows is 11684.

Currently, this bbl ends up with 11684 bldgarea (summed - seems wrong), 10569 resarea (summed - seems right), 256 unitsres (obviously wrong), 365 units total (obviously wrong).

So for this, it would seem like maybe we do the following

• sum areas. if greater than primary row, update primary row, otherwise keep primary row
• for units (both res and total)
  • group by primebbl, coop_apts, units
  • check size of group
  • if size of group = number of units, simply use units
  • if size of group <> number of units, multiply each type of unit by size of group (same as summing)

This doesn't capture the case of where 3 or 4 unit rows which each have same units as primary (so 1 primary row, 3 unit rows, all with 217 tot units and 216 res units - as of time of writing, 3070627501 is a good example). But maybe these we want to be checking manually as we currently are, because they seem more difficult to assume that they're in error