Pluto 24v3

[sf-dcp]

Main tasks

• source data extracted
• draft build succeeded
• draft build passed QA
• data packaged and distributed

Data loading

Manual Updates

Updated 2x a year typically in June and December

• dcp_colp (check here)

Automated Updates

Open data automated pull

Check here to see the latest run

• dsny_frequencies
• dpr_greenthumb
• lpc_historic_districts
• lpc_landmarks

DOF Automated Pull and Number of Buildings

• pluto_pts (Check here)
• pluto_input_geocodes (Check here)
• pluto_input_cama_dof (Check here)
• pluto_input_numbldgs (Check here)

Updated with Quarterly updates (check here)

• dcp_cdboundaries_wi
• dcp_ct2010
• dcp_cb2010
• dcp_ct2020
• dcp_cb2020
• dcp_school_districts
• dcp_councildistricts_wi
• dcp_firecompanies
• dcp_policeprecincts
• dcp_healthareas
• dcp_healthcenters

Updated with Zoning Taxlots

(check here for latest run).

These are all produced by GIS, who typically update them sometime in the first week of each month.
Check in with them before archiving with data library

• dof_dtm
• dof_shoreline
• dof_condo
• dcp_commercialoverlay
• dcp_limitedheight
• dcp_zoningdistricts
• dcp_specialpurpose
• dcp_specialpurposesubdistricts
• dcp_zoningmapamendments
• dcp_edesignation

Never Updated (Safe to ignore)

• doitt_zipcodeboundaries (almost never updated, check here)
• fema_firms2007_100yr
• fema_pfirms2015_100yr
• dcp_zoningmapindex

[damonmcc]

waiting for PR #977 for a PLUTO zoning fix and for COLP to pass QA

[sf-dcp]

Zoning fix has been merged. COLP is still in QA. We decided to proceed with PLUTO build based on the previous COLP version (Dec '23) to avoid release delays

[sf-dcp]

Preliminary DE QA review based on the QAQC page ("last of same version type" comparison type):

Build: sf-pluto-24v3

• Mismatch graph — finance fields
  • high exempttot but seems fine, assuming it’s the result of final tax roll in May
• Mismatch graph — area fields
• Mismatch graph — zoning fields
  • edesignum has a big spike - not sure why
• Mismatch graph — geo fields
  • Not sure why there is a spike in sanitsub, edsignum, and sanitdistrict. And whether this is considered to be a spike
• Mismatch graph — building fields
  • ~10k records changed ownername between 24v3 and 24v2. 24v2 Had same change. Not sure if that’s okay

Aggregate Changes

• Null graph
  • Pretty high spike in ownertype.
    • ~Abount ~500 records
    • All values are the ones that went from X to NULL. X definition: “Fully tax-exempt property that may be owned by the city, state, orfederal government; a public authority; or a private institution”
    • If the tax lot is not in COLP, PTS is checked to see if the lot’s EXEMPT TOTAL VALUE equals its ASSESSED TOTAL VALUE. If the two values are the same, the lot is given a code of X. Otherwise the tax lot is not given any TYPE OF OWNERSHIP CODE.
    • The value is derived from PTS
    • It’s most likely related to DOF final roll (assesstot & exempttot values coming from DOF)
    • See sql query results
  • Pretty high spike in edesignum
    • About ~300 became NULL
    • Not sure why

Expected Value Comparison

• Value differences for bldgclasslanduse - not sure why there is a change

Outlier Analysis

• Table of BBLs with Unreasonable Increase in Building Area 24v3-24v2 - doesn’t seem alarming: area has increased for most if not all records
• Report of BBLs with buildings containing unreasonably small apartments
  • ~50 records
  • Will investigate as a part of the new qa process
• Table of BBLs where bldgarea/lotarea > numfloors*2 - no new entries

New and Vanished BBLs

• Around ~1,200 bbls vanished - not sure why
• Around ~250 bbls are new

[fvankrieken]

Couple random comments

• ownertype - looks like we've seen similar drops in the past (24v1 had 1000 go from non-NULL to NULL)
• lost bbls seems slightly high? But don't know that we'd hold anything up for it. Some querying of dtm could be useful

So seems like two big things to flag for gis

• big edes shift. My guess is MC has more context on whether this is expected or not
• big shift in sanitation districts. I've googled a bit but don't see any news around it. Updating qgis so I can open parquet files to see if there was a big shift

[fvankrieken]

The sanitation districts is odd - looks like this only comes from dsny_frequencies? Which really hasn't changed in the last year

[sf-dcp]

With GIS for QA review.

[caseysmithpgh]

QA'ed PLUTO today. Everything seems to be in order, just going to have Jack take a look at it tomorrow for final conformation.

Two quick flags:

• BBL 3000160017 had a significant increase in floor area--appears to be a park or general greenspace on cyclomedia/google maps. Worth checking out.
• This is the second QA we've noticed that neither ZD3 or ZD4 have seen any changes. Sort of odd, and worth looking into.

FYI @NYCPlanning/data-engineering @jackrosacker @croswell81

[fvankrieken]

Copying my note on zd3/4 from last time - only 215 records have zd3, only 13 have zd4.

And we actually have a single zd3 change this time, which works out to a higher percentage of zd3 lots that had zd3 change than normal lots had zd1 change. So I don't think that's something we need to worry about.

[caseysmithpgh]

Sweet thanks. I'll note this in our QA doc so it doesn't come up again next time.

[caseysmithpgh]

FYI @sf-dcp

GIS is signing off--with the caveat that DE should verify the lot area (mistakenly labeled floor area, according to finn) increase of BBL 3000160017. Barring any concern on DE's end, ready for promotion and subsequent publication.

[sf-dcp]

Hi @caseysmithpgh , @fvankrieken and I checked the lot and the result is... interesting.

• Confirmed, the lot area matches DOF PTS data: 19,800.

• When calculating the lot area from its polygon from mappluto unclipped, it's ~19,000 sq ft.

• When calculating from mappluto clipped, it's ~1,200 sq ft.

Unclipped:

Is the lot area in PLUTO supposed to include land area only or its total area?

[caseysmithpgh]

Hmm--interesting case. I don't know the answer, happy to take a look at the data dictionary to see if there is something in there about it.

[caseysmithpgh]

The Data dictionary says the following:

Total area of the tax lot, expressed in square feet rounded to the nearest integer. LOT AREA contains street beds when the tax lot contains “paper streets” i.e., street mapped but not built. If the tax lot is not an irregularly shaped lot (see IRREGULAR LOT CODE) the Department of Finance calculates the LOT AREA by multiplying the LOT FRONTAGE by the LOT DEPTH. If the tax lot is irregularly shaped, DOF calculates the LOT AREA from the Digital Tax Map. If PTS contains a zero value for LOT AREA, this field is changed to show the area of the tax lot’s geometric shape in the Digital Tax Map and DCPEdited is set to “1”.

Nothing explicitly stated about land vs non-land area, but I imagine since that's the case the entirety of the lot is included in the LotArea

[sf-dcp]

Hi @caseysmithpgh, thank you for checking the data dictionary. Since the area aligns with the unclipped lot size and there is nothing in data dictionary indicating land-only area, we are moving forward with publishing PLUTO.

[caseysmithpgh]

@sf-dcp Great, thanks! Just let me know once it's been promoted and I will get started with our distribution process.

[sf-dcp]

@sf-dcp Great, thanks! Just let me know once it's been promoted and I will get started with our distribution process.

Done!

[jackrosacker]

Hello @NYCPlanning/data-engineering! Found some weirdness while prepping QA data for this version of PLUTO today. 9 tax lots are showing a change in the BCT2020 field (census tract), when comparing 24v2 and 24v3. We're going to hold off on publishing until we get a chance to chat with Matt on Monday, and wanted to note the lots to you all as well.

BBL	24v2_BCT2020	24v3_BCT2020
1000160003	1031704	1031703
1012540001	1018300	1017500
2023760048	2006700	2006900
3024140001	3055100	3055500
3067260087	3053200	3076800
4080510001	4003700	4148300
4142600001	4071600	4066401
5004870100	5000600	5002100
5035630042	5029106	5011402

These lots fall into three categories: (1) long linear lots that span multiple census tracts, (2) lots that just brush the edge of a census tract, and (3) small, regular lots that are fully contained within a census tract but have still changed in value.

My query that found these variable values selected BCT2020 arbitrarily, and it's possible that similar changes exist for other fields as well.

Also note that there are some odd lot boundary changes in the vicinity of 1000160003 (Battery Park City), but these exist in the DTM version we pulled from DOF, and we are reaching out to them separately. Worth looking at though, as none of our existing QA procedures would necessarily catch tax lot overlap errors like these ones, since the zoning isn't changing.

[fvankrieken]

Hmm. These tracts come from geocoding PTS, not via spatial joins, so it's odd that we'd see inconsistent behavior. So it would seem that this is due to either

1. input to geocoding call changing (PTS address info for these lots)
2. difference in geocoding (24B vs 24C1 or something like that)
3. these are lots where we have multiple geocoded PTS rows and we don't aggregate in a consistent/deterministic manner

[sf-dcp]

@jackrosacker @caseysmithpgh

Looking into the issue more, we get census tract field either through geocoding BBLs with Geosupport or via spatial join with census tract dataset. And these BBLs identified by Jack have one of the two situations:

• These BBLs received different census tract values between PLUTO 24v2 and 24v3 from Geosupport. These are typically the big lots.
• These BBLs didn't get geocoded last time and were spatially joined to the census tract dataset for PLUTO 24v2 while the BBLs in the current version got a census tract values from Geosupport.

We will be meeting with Amanda to research the issue and see if/how we can solve the changing census tract values long-term. We can move forward with publishing the current PLUTO version.

[jackrosacker]

We can move forward with publishing the current PLUTO version.

@sf-dcp thanks for the summary and sounds good. We also had a chance to check in with @croswell81 re the tax lot errors that we found in the Battery Park City area, and it looks like we will need to re-build PLUTO 24v3 to incorporate the DTM that DOF fixed at end of day last Friday.

We're in the process of republishing today's DTM to Digital Ocean and will update when the build is ready to go.

Worth discussing at some point how to add some topology rules to our QA process to catch things like significant tax lot overlaps - might be a good topic for when we sit down for the next PLUTO QA together.

[caseysmithpgh]

@sf-dcp updated DTM has been published to staging

FYI @croswell81 @jackrosacker

[sf-dcp]

Sounds good! Will let you know when it's ready for the 2nd round of QA

[sf-dcp]

@caseysmithpgh @jackrosacker @croswell81

Not creating a GH issue for QA since we've started this data update before the new process.

PLUTO 24v3 has been re-built and promoted to draft folder under draft/24v3/2-update-dtm-and-correct-units/.

Notes:

• New DOF DTM data was used to fix the previous tax lot errors
• Other input datasets, including COLP, were also updated. You will notice a slightly bigger change to ownername field compared to previous draft (result of newer COLP data).
• We edited unitsres and unitstotal values for 13 condo lots as a result of manual research.

[croswell81]

[like] Matthew Croswell (DCP) reacted to your message:

…

________________________________ From: sf-dcp ***@***.***> Sent: Wednesday, August 21, 2024 9:25:54 PM To: NYCPlanning/data-engineering ***@***.***> Cc: Matthew Croswell (DCP) ***@***.***>; Mention ***@***.***> Subject: [EXTERNAL] Re: [NYCPlanning/data-engineering] Pluto 24v3 (Issue #976) CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe. Forward suspect email to ***@***.******@***.***> as an attachment (Click the More button, then forward as attachment). @caseysmithpgh<https://github.com/caseysmithpgh> @jackrosacker<https://github.com/jackrosacker> @croswell81<https://github.com/croswell81> Not creating a GH issue for QA since we've started this data update before the new process. PLUTO 24v3 has been re-built and promoted to draft folder under draft/24v3/2-update-dtm-and-correct-units/. Notes: * New DOF DTM data was used to fix the previous tax lot errors * Other input datasets, including COLP, were also updated. You will notice a slightly bigger change to ownername field compared to previous draft (result of newer COLP data). * We edited unitsres and unitstotal values for 13 condo lots as a result of manual research<https://github.com/NYCPlanning/data-engineering/pull/1089/files>. — Reply to this email directly, view it on GitHub<#976 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AICWQSVDFEW26Z2G6XH7EXDZSUAWFAVCNFSM6AAAAABKNNLCSSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMBTGAZTSOBUHA>. You are receiving this because you were mentioned.Message ID: ***@***.***>

[jackrosacker]

Hey DE, noting that we're still seeing issues with the DTM that was used for this latest draft build of PLUTO. We've flagged this to DOF for troubleshooting, and to Amanda to help determine best next steps for publication, so just keeping you all up to date. The issue is either on the DOF or GIS Team side, so not DE build-related.

[caseysmithpgh]

Hey DE, Matt was able to get a clean copy of the DTM from DOF and/or their consultant. I'll plan to process it first thing tomorrow so y'all can get started with a re-build.

Can you confirm what zoning data was used to build 24v3 draft 2? If it was June, would it be reasonable for draft 3 to be built with July zoning (latest)?

FYI @AmandaDoyle @croswell81

[damonmcc]

@caseysmithpgh the version I'm seeing for zoning data used in 23v3 draft 2 is 20240807 and that seems to be the latest

so draft 3 with also have the latest

[caseysmithpgh]

@damonmcc sounds good. I can confirm 20240807 reflects GIS 20240731 version.
Clean DTM files have been published to /staging and /20240826 -- so y'all are good to go for draft 3.

FYI @croswell81 @jackrosacker

[sf-dcp]

Per discussion with @AmandaDoyle, we will manually correct 2020 census tract value for the JFK airport BBL (4142600001) as a part of this PLUTO version (@jackrosacker, this BBL is from your list above). We will continue researching the other BBLs with GRU.

We will let you know when new draft is ready for your review.

cc: @croswell81 @caseysmithpgh

[sf-dcp]

Hi GIS team, new draft 3-update-dtm-fix-jfk-lot-censustract/ is ready for your review.

In this draft, we used updated DTM data. We also corrected 2020 census tract value for the JFK lot.

@caseysmithpgh @jackrosacker @croswell81

[caseysmithpgh]

Draft 3 QA Failed

Hard Fail:

• edesignation: Pluto 24v3 is built with edes DE v 20240807 (GIS v 20240712). Unfortunately a significant number of Rs missing from this edes data (our bad!), presuming this happened under the context of manual data production. This resulted in a significant spike of lots having null edesignum. We have to rebuild as a result :(. Feel free to reach out with questions!
• Lost value bldgclasslanduse R7/05: needs to be confirmed prior to rebuild.

Flags:

• Sanitation districts and sub-districts--significant change. Worth confirming accuracy prior to rebuild.
• ownertype saw significant spike in null values. Previous comparison saw 45 changes, latest comparison has 563 changes. This may be associated to new DOF roll, but worth confirming before rebuild.
• Unreasonable increase in building area: parking lot, no identified construction. BBL 5001840280

@sf-dcp
FYI @croswell81 @damonmcc @fvankrieken @jackrosacker

[sf-dcp]

Hi @caseysmithpgh, q about edes: should we use the version 20240712 instead of the one we used in the latest draft?

[caseysmithpgh]

Morning @sf-dcp, sorry I could have been more clear. According to the QA page, the 20240712 version was used in the latest build. The version that should be used is GIS v 20240806, which was uploaded to digital ocean on 8/16/24 and can be found in the usual staging folder. Jack and I did confirm the same issue did not persist in the latest version.

[caseysmithpgh]

@sf-dcp

All data relevant to the new pluto build has been staged in DO. Please ensure you've pulled the latest versions of the following into recipes prior to building.

• Zoning datasets
• E Designations
• DOF tax lots

fyi @croswell81 @damonmcc

[sf-dcp]

Hi @caseysmithpgh,

PLUTO has been rebuilt and can be found under 24v3/4-update-source-data on DO (link).

• Latest versions of input data were used in this draft

• Addressing your earlier flags:

  • Lost value bldgclasslanduse R7/05 in all draft builds for this PLUTO version. Note, in previous PLUTO version, there were only 3 BBLs with this value, and these BBLs disappeared from the current version. So I think it's safe to ignore this flag.
  • ownertype spike: his comes from PTS data, and the spike in NULLs is probably related to the DOF roll. I also left a comment regarding the ownertype in this GH issue (DE QA).
  • sanitsub & sanitdistrict spikes: these spikes have been seen across all drafts for this version. These fields come from Geosupport. My guess is that the change is related to change in Geosupport version.
  • BBL 5001840280 with spike in bldg area... So it's actually not building but lot area and the value comes from DOF PTS. DE will rename the report in the QAQC app accordingly and create a new report for building area for the next version review.

• This draft changes: new values for zoning and special_district columns. These must come from new zoning data.

cc: @jackrosacker @croswell81

[caseysmithpgh]

Thanks for the rundown @sf-dcp, jack and I will schedule some time to QA the latest draft on Monday or Tuesday.

[croswell81]

@fvankrieken I can confirm the sanitsub & sanitdistrict spikes are expected. The mid-cycle geosupport release is specifically for the new organics changes to those two fields.

Reminder to all - we need to update pluto data dictionary, readme, and maybe change file with the zoning and special district values before sending to webteam.

[caseysmithpgh]

Draft 4 QA Failed

Hard Fail:

• New ZoneDist1 Value Error: As was discussed with Damon and Sasha today, new ZD district M1-1A/R7-3 (10 characters) was clipped to 9 characters as a result of a varchar definition.
  • Short term, we can go with 12 character limits for all ZoneDist fields, mirroring the special district limit. Just to muse on the logic, if we take M1-1A/R7-3 as an example, the reason it has increased the max length is because of the bolded subsection. The pre-slash character count is 5, so if we were to see something like M1-1A/R7-3B that will still be within the 12 character limit.
  • Long term, we can have data governance conversations about what fields should have defined character limits.
  • Involved BBLs: 2040420200, 2040420201, 2040420204

Flags:

• EDesignation: still seeing ~300 value changes. In the grand scheme of things, this is not that large. However we are wondering: is this a result of non-deterministic flip-flopping, since it's possible for lots to have more than one edesignation? GIS also discussed potentially wanting to concatenate all edesignations down the road so all can be displayed for all lots. Lets discuss.
• Sanitation districts and sub-districts: Non-insignificant number of changed values. Confirmed with Matt that this is not a result of the organics changes, as this is not yet in geosupport. Likely not a major blocker, but I will confirm with Rodrigo.
• Nit: What's up with "Notes" field? All values are NULL.

@sf-dcp
FYI @croswell81 @damonmcc @fvankrieken @jackrosacker

[croswell81]

FYI: Re: Notes field

Field Name: NOTES (Notes)
Format: Alphanumeric – 20 characters
Data Source: Department of City Planning
Description: A text field containing notes of importance to one or more lots.

• Value 1: All zoning regulations pertaining to the Special Inwood District Rezoning, which amended the Zoning Resolution (N 180205A ZRM) and Zoning Map (C 180204A ZMM), and which have been in effect since 8/8/18, are no longer in effect as of 12/19/19 per court order. For the applicable zoning designations currently in effect, please see zoning maps 1b, 1d, 3a, and 3c. In July 2020, the Appellate Division First Department ruled that the Special Inwood District Rezoning could proceed. The value of 1 has been removed from all lots.

[damonmcc]

Re: EDesignation changes, edited to correct record counts and explanation at the end
@sf-dcp, @caseysmithpgh

TLDR

For lots with multiple records in dcp_edesignation, we sort by ceqr_num, ulurp_num, enumber and select the first EDes number.

So if a lot has a new record which ends up "first" in that sorted list and has a different EDes number than the previous "first", it'll change for that lot in PLUTO.

details

That count of ~300 values changing seems to be a combination of "lots which gained or lost an EDes number" and "lots where the EDes number changed" (relevant sql here).

It seems like 21 lots have had their EDes number changed. This is how we choose the EDes number (edesignation.sql):

• Use the source dataset dcp_edesignation which can have multiple rows per lot
• If a lot has more than one row, sort by ceqr_num, ulurp_num, enumber and use the first enumber

Below is an example of a lot (3011420048) where two different EDes Numbers have the same ULURP Number but with different formatting. This table has the current EDes number, previous EDes number, and the lot's records from the current dcp_edesignation.

current_version	current_bbl	current_edesignum	previous_version	previous_bbl	previous_edesignum	bbl	enumber	ceqr_num	ulurp_num	row_number
24v4	3011420048	R-24	24v2	3011420048	E-128	3011420048	R-24	03DCP036K	030294 ZMK	1
24v4	3011420048	R-24	24v2	3011420048	E-128	3011420048	E-128	03DCP036K	C030294ZMK	2
24v4	3011420048	R-24	24v2	3011420048	E-128	3011420048	E-302	13DCP105K	C130213ZMK	3

Without having seen the previous version of dcp_edesignation used in PLUTO 24v2, I'm not sure if this lot had ULURP Number 030294 ZMK recently added or if they've both always been there and we only recently started sorting.

[sf-dcp]

Hi GIS,

I re-built PLUTO with the corrected zoning column ZoneDist1. Should we wait for more info about sanitation districts from GRU or can I promote the build to draft for your review?

@caseysmithpgh @jackrosacker @croswell81

[jackrosacker]

Hey @sf-dcp, it looks like, yes, we should wait to sign off on this for right this moment. We'll stop by and check with GR today so that we have a prompt turnaround on this.

[sf-dcp]

@jackrosacker & @caseysmithpgh

I saw Casey's email and I promoted new PLUTO build to draft here.

[croswell81]

Draft 5 QA - Passed

Flags:
EDesignation: still seeing ~300 value changes. In the grand scheme of things, this is not that large. However we are wondering: is this a result of non-deterministic flip-flopping, since it's possible for lots to have more than one edesignation? GIS also discussed potentially wanting to concatenate all edesignations down the road so all can be displayed for all lots. Lets discuss.

Sanitation districts and sub-districts: Non-insignificant number of changed values. Confirmed with Matt that this is not a result of the organics changes, as this is not yet in geosupport. Not a major blocker, but we would like to understand why the changes.

Community Gardens - noticed the record count decreased by 5, this is valid since a housing development merged 5 former CG lots into one development lot.

QA report notes:

• Add rows with latest dates for the OTI Building Footprint Centroids & DPR – GreenThumb Garden Info? We need them to update info in the PLUTO Readme doc.
• would be great to de-duplicate value differences for zoning. M1-1A/R7-3 & R6-1 are new zoning districts but they both show up twice in the Value difference section.

Full report available on SharePoint here

@sf-dcp
FYI @damonmcc @fvankrieken @jackrosacker @caseysmithpgh

[sf-dcp]

Nice!

We will modify the qaqc page. Here are the requested source versions in the meantime:

• OTI Building Footprint Centroids: 20240916
• DPR: 20240914

PLUTO Readme: the only logic change in PLUTO is increasing the field size from 9 to 12 characters for zonedist1, zonedist2, zonedist3, and zonedist4

PS: PLUTO has been promoted to the publish folder.

cc: @croswell81 @jackrosacker @caseysmithpgh

[damonmcc]

24v3 seems to be on Bytes, so we can distribute to Open Data now

[croswell81]

@damonmcc Yes, I tagged @alexrichey in our Monthly open data issue 1074. We can tag all of Data Engineering in the future if that is helpful.

[damonmcc]

@croswell81 no worries on who to tag. the need to wait for Bytes is temporary, so improving any "it's on Bytes!" notifications probably isn't worth it

[sf-dcp]

PLUTO has been distributed to Bytes & Open Data.