NYCPlanning / NYCPlanning/data-engineering

CSCL - coincident_seg_count double-counts subway/rail crossings

Open Beginner friendly
#2,616 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

db-cscl
Dominant language
Python
Stars
43
Forks
3
Avg merge
23h 3m
Merged PRs (30d)
44

Description

Legacy GetCoincidentSegmentCount() (ShorelineExtractFile.cs:507) scopes its "same type" coincidence check to the literal ArcGIS feature class - Subway and Rail are separate classes there, so a Subway feature is never matched against a Rail one. Our int__noncenterline_coincident_segments.sql merges Subway+Rail into one feature_type = 'rail_and_subway' and matches on that, so a subway/rail crossing gets double-counted.

Confirmed on segmentid 8101066 (Brooklyn, Broadway Junction, BMT Jamaica Line over LIRR Bay Ridge Branch): legacy coincident_seg_count = 2, ours = 3.

Fix: match "same type" on source_table (subway/rail/shoreline/nonstreetfeatures) instead of the merged feature_type in int__noncenterline_coincident_segments.sql.

Related, not yet verified: legacy also gates its centerline-overlap check on the feature's own ROW_TYPE != "1"; ours gates on segmentid NOT IN int__underground_rail - a different condition that may diverge elsewhere.

See docs/prod_bugs/007-sept-2026-remaining-diffs-investigation.md (item 1).

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in int__noncenterline_coincident_segments.sql and compare its coincidence matching with ShorelineExtractFile.cs:507. Use segmentid 8101066 as the confirmation case, checking the Subway/Rail source-table behavior. Done means the SQL no longer double-counts that crossing and matches the legacy coincident_seg_count of 2.

Written by the indexing model from the issue text.

Assessment

Tech stack
sql
Domain
data-engineering, databases
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
84/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.