qc_bbldiffs file fails to include two BBLs where ZMA/ZDs have changed/been created

[caseysmithpgh]

Two BBLs that should be in Feb. open data qc_bbldiffs file were not included.

• 4022460031
• 4022460041

Findings from our initial investigation:

• above listed bbls are not listed in zoningtaxlot_db.csv
• We confirmed correct source data was uploaded to DO prior to running actions
  • Zoning Districts (current version confirmed uploaded, and other bbldiffs from this month showed changes in ZD)
  • DTM (Ensured latest version was published to DO, confirmed DTM layer used in our Pro QC workspace was same one uploaded to DO)

Below image shows new Feb. Zoning Map Amendment (blue highlighted selection), lots intersecting with this ZMA should be included in qc_bbldiff layer, but are not. Happy to follow up with screen share or in-person walk though of the issue.

[AmandaDoyle]

@caseysmithpgh I don't see either of the BBLs you listed in the DTM that's on Digital Ocean. Based on this that's my rationalization as to why they are not in the reports. I'm happy to hop on a call to talk through and visualize you're process.

[caseysmithpgh]

@AmandaDoyle interesting. Ok, I will take a look through the previous DTM and current DTM (and those that fell in between) to try to isolate the issue.

[jackrosacker]

Hey Amanda, we just ran some double checks and are seeing the two BBLs in the latest DTM. We've checked most of the things we can think of on this end, and next steps would probably be to see what DE can dig up or to sit down together and figure out where we're going wrong.

This image is from ArcGIS, shows the data source in Cyberduck, the file pulled up in the map view, and the lots highlighted. Trying to be explicit with a screenshot in case we're separately looking at different datasets or something.

Also +@croswell81

[AmandaDoyle]

@jackrosacker @caseysmithpgh I was looking at the dof_dtm dataset in edm-recipies here. I'm happy to meet to talk this through (unfortunately today is not a good day with meetings). If I'm not free please feel free to troubleshoot with someone in DE

[AmandaDoyle]

@damonmcc for awareness and he'll be point of contact

[damonmcc]

gonna make sure the dof_dtm data used by the build action (in edm-recipes) is identical to the source of source data (in edm-publishing)

if it isn't, I'll run our archiving and start a ZTLB build to see if that was the root cause 🤞🏾

[damonmcc]

seeing significant differences between input data, specifically dof_dtm

• build #132 run on 1/4/2023
  • dot_dtm appears to have 858,327 rows
• build #133 run on 2/3/2023
  • dof_dtm appears to have 858,317 rows
• build #134 run last week and Build #135 run today
  • dof_dtm appears to have 394,957 rows

from asking Max, the # of rows shouldn't change so much

[damonmcc]

@jackrosacker

the screenshot of the recent DOT DTM data you all have been uploading monthly to edm-publishing:

[jackrosacker]

@damonmcc I'm looking at the datasets pre-Digital Ocean upload and I'm seeing the following counts:	date	row count	column count
2022-12-30	858,328	33
2023-01-27	858,318
2023-02-03	858,316	33
2023-03-03	858,279	33

Edit: fixed to show 1/27 instead of 2/3

(for GIS internal reference, all three above are from the scrape output locations on M:\DOF_Tax_Maps)

[jackrosacker]

@damonmcc I also confirmed the row counts for the corresponding shapefiles in edm-publishing for the three dates, all counts are the same as for the feature classes in the table above

[damonmcc]

I'm currently trying to convert the shapefile to a sql file locally and using the least amount of data-library code as possible to minimize the likelihood of losing rows and quickly upload the data needed to build ZTLB

so far, 20220429 appears to be the last source dataset in edm-publishing. 20220603 is the very next dataset and has the "half file" issue we're seeing

[damonmcc]

use of QGIS to reproject and convert a shapefile seemed feasible, but a build with the result failed because of how QGIS structures the sql file (every line is a long INSERT statement rather than our usual lines of text)

reproject with command line tools (ogr2ogr and shp2pgsql) is in progress and hopefully produces a sql file the ZTLB build process can use

update: seems promising!

[damonmcc]

very promising. here's the current approach

• reprojecting shapefile with ogr2ogr, converting to sql with shp2pgsql
• manually changing first few lines of sql file to align with usual format
• uploading to edm-recipes in two folders: latests and the latest date since the latter is what the build actually pulls in
• run build using development branch (relevant PR https://github.com/NYCPlanning/db-zoningtaxlots/pull/132)

most recently, the build failed because some geometries are invalid. fixing by reprojecting with --makevalid flag

but got this error

ERROR 1: Attempt to write non-polygon (LINESTRING) geometry to POLYGON type shapefile.
ERROR 1: Unable to write feature 394957 from layer dof_dtm_tax_lot_polygon.

[damonmcc]

@jackrosacker @caseysmithpgh a successful build!

with new exports in edm-publishing/db-zoningtaxlots/latest/, would love to have extra eyes on inspecting the results. perhaps worth clarifying February vs March releases since I'm not sure which one we're on and whether the input data for this build was ideal

[caseysmithpgh]

@damonmcc @jackrosacker I took a look at the edm-publishing/db-zoningtaxlots/latest/qc_bbldiffs.csv and the record count is significantly higher, and more in line with what we would typically expect. The missing bbls that originally tipped us off to the issue are also included--so this output on that front looks good to me!

Many thanks Damon!

[caseysmithpgh]

@damonmcc confirming that I'm clear to QA items that are in edm-publishing/db-zoningtaxlots/latest/

[damonmcc]

@caseysmithpgh yup!

and looks like this is meant to be for the February release so, in case it's helpful, here's a link to the source data versions in the build logs

[damonmcc]

per Data Update issue https://github.com/NYCPlanning/edm-overview/issues/866, ZTLDB has been QAed and pushed to Bytes. closing this issue as complete