20Q2 - output QAQC table where there is spatial overlap between NBs and Demos

[AmandaDoyle]

Goal

Output records where a NB spatially intersects with a DM job. There is no date constraint.

Data Engineering needs to decide if we do 1) BBL polygon intersection spatial join or 2) 2 point to polygon spatial joins

This would be similar to the KPDB cluster review output tables. This would be a QAQC table used for research work.

[SPTKL]

@AmandaDoyle Need Clarification: Which date field are we supposed to be using here for this qaqc process

[AmandaDoyle]

@levysamu @kschmidtDCP Should we compare using the status_a date?

So a NB job can only join to a DM jobs that it spatially intersects with if teh status_a date of the NB job is later than the status_a date of the DM job?

[kschmidtDCP]

@AmandaDoyle We'll meet today to clarify the overall plan for joining NBs and DMs, however, to answer your question:

Some developments pull DM and NB permits concurrently, and there may even be instances where the DM is pulled slightly after the NB. They should join if the NB status_a date is up to x months before the DM status_a date, or anytime afterwards. Sam and I will discuss the value of x, but it may be anywhere between 1 and 12.

[AmandaDoyle]

For spatial join BBL polygon intersection or 2 point to polygon intersections

[kschmidtDCP]

@AmandaDoyle We'll meet today to clarify the overall plan for joining NBs and DMs, however, to answer your question:

Some developments pull DM and NB permits concurrently, and there may even be instances where the DM is pulled slightly after the NB. They should join if the NB status_a date is up to x months before the DM status_a date, or anytime afterwards. Sam and I will discuss the value of x, but it may be anywhere between 1 and 12.

@AmandaDoyle Now that we've researched the possible joins, we would like to not use date criteria for joining DMs and NBs.

[mgraber]

Geometries are inconsistent between sources, so merging back with mappluto to get BBL geometries sometimes results in a devdb point falling outside of the lot. We are working to track down this issue.

Comparing geometries from different sources might introduce even more errors (for example, different geosupport functions have different relationships between a point and a lot). Until we better understand the disconnect between Geosupport and mappluto, we are using a join on geo_bbl, rather than spatial matches.