CSCL - LION files data issues

Motivation

Given that centerline segments are largely "done", this issue is meant for two things

• track lingering data issues that are not resolved
• identify any "new" issues that arise in a new cycle

The latter is particularly important - points to any "instability" of our pipeline, that is some sort of sensitivity to issues that can exist in the source data that don't exist in the prod pipeline and need to handled somehow, OR actual logical issues in our pipeline that only arise for certain edge cases.

25c

There were a handful of changes GRU made to source data for 25C, largely around atomic polygons that were read into our pipeline as empty geoms (at time of reading in with geopandas) as well as some work around coincident segments

So when we get new outputs from 25C, we should check as a follow up to

• #1711
• #1721
• #1713

That the diffs are resolved

Confirmed source data issues

Atomic Polygon

• resolved

Atomicid 2027600919 has small validity issues that cause it to lose its geometry in our pipeline (as in, geom is empty after this layer is ingested)

select * from dcp_cscl_atomicpolygons where ST_ISEMPTY(geom)

Sectional Map

• resolved

Before, we had a few issues arising due to sectional maps overlapping with each other. It seems there are a few more. The following pairs have a significant overlap with each other that cause segments to have endpoints in multiple sectional maps:
1      1N
5      6
12    13
20    21
20    26

select dcs1.sectional_map, dcs2.sectional_map, st_area(st_intersection(dcs1.geom, dcs2.geom))
from dcp_cscl_sectionalmap dcs1
inner join dcp_cscl_sectionalmap dcs2
on dcs1.sectional_map <> dcs2.sectional_map
and st_area(st_intersection(dcs1.geom, dcs2.geom)) > 1

LGC

• lgc entries for centerline segment 0340365 have borough 2 despite this centerline record having borough 1. Not sure which one is "wrong", if any. Worth checkin with GR
• some records not getting face code assigned due to preferred b7sc (from lgc table) not having face code defined in "principal" row in streetname table. GR emailed already

Start with the SQL checks for dcp_cscl_atomicpolygons and dcp_cscl_sectionalmap, then review follow-up issues #1711, #1721, and #1713 and the listed LGC cases. Done means the 25C diffs are checked, sectional-map overlaps and the centerline 0340365 borough discrepancy are resolved or explained, and missing face-code records are investigated.