E01 Data and Field Mapping - gcivil-nyu-org/team2-mon-fall26 GitHub Wiki

1. Source and acquisition

Item Value
Dataset NYC Wi-Fi Hotspot Locations
Publisher NYC Office of Technology and Innovation (OTI), via NYC Open Data
Page https://data.cityofnewyork.us/City-Government/NYC-Wi-Fi-Hotspot-Locations/yjub-udmw
Dataset ID yjub-udmw
Acquired 2026-10-14
Size 3,319 rows, 29 columns (each row is one Wi-Fi hotspot location)
Source freshness Update frequency "As needed". The page lists data last updated September 26, 2019, so treat it as a static snapshot.

Reproduce the import

curl -s "https://data.cityofnewyork.us/resource/yjub-udmw.csv?\$limit=5000" -o wifi_hotspots_full.csv

connectnyc_hotspots_sample.csv (36 rows) is a representative sample: two rows per provider, plus rows with a missing name and rows that share coordinates.

Load steps

  1. Download the CSV with the command above.
  2. Clean sentinels: set activated to null when it starts with 9999-09-09, and set sourceid to null when it is blank or 0.
  3. Map each column to the hotspot table using section 3.
  4. Upsert on hotspot_id (from doitt_id). Never delete and recreate rows (see section 4).
  5. Run python3 verify_hotspot_data.py wifi_hotspots_full.csv and compare against the numbers in section 2. Any difference means the source changed.

Review fixtures used for demos must be labeled as test/demo data, and are never loaded into the hotspot table.

2. What the data looks like

Check Result
Rows with missing latitude/longitude 0
objectid, doitt_id Unique, never null
borough code vs boroname Always consistent (1 Manhattan, 2 Bronx, 3 Brooklyn, 4 Queens, 5 Staten Island)
Distinct providers 17 (LinkNYC is 1,868 rows, 56 percent of the data)
type values Free (2,736), Limited Free (581), Partner Site (2)
Boroughs Manhattan 1,672, Brooklyn 700, Queens 531, Bronx 316, Staten Island 100

Gaps and traps

  1. name is missing in 6.9 percent of rows. All of them are Downtown Brooklyn (100), Harlem (100) and Chelsea (30). Display fallback: provider plus location.
  2. remarks is missing in 16.5 percent of rows. Optional.
  3. sourceid is not a general key. It holds a kiosk id for LinkNYC (for example LINK-000049), an organization name for Fiberless ("Governors Island Trust") and Spot On Networks ("NYC HOUSING AUTHORITY"), 0 for all 343 Spectrum rows, and is blank for 32 percent of rows. Not unique (1,871 distinct values across 2,257 non-blank rows).
  4. activated uses 9999-09-09 as "unknown" for 1,497 rows. Treat that value as null, never as a date.
  5. Coordinates are not unique. 69 groups of rows share the same point (example: 7 Downtown Brooklyn access points at one location). Do not de-duplicate or key on lat/long.
  6. city is not the borough. It has 44 values (Flushing, Jamaica and so on). Use boroname for borough filters.
  7. Neighborhood fields use 2010 NTAs (ntacode, ntaname, example QN02). They are already in the file, so no join to the 2020 NTA dataset is needed for neighborhood search.
  8. location_t has 16 values, 10 of them are one-off "Indoor AP - ..." labels (16 rows, all Spot On Networks / NYCHA). Group these under Indoor for filtering.

3. Source-to-application field mapping

Table: hotspot

App field Source column Type Null? Notes
hotspot_id (PK) doitt_id integer No Unique in all 3,319 rows. Stable identifier that reviews attach to.
source_object_id objectid integer No Generated by ArcMap, may change between exports. Kept for traceability only.
name name text Yes See gap 1.
location_description location text No Short description of where the hotspot is.
latitude latitude decimal No Complete.
longitude longitude decimal No Complete.
borough boroname text No Do not use city.
neighborhood_name ntaname text No 2010 NTA.
neighborhood_code ntacode text No 2010 NTA.
zip_code zip text No Store as text, not a number.
provider provider text No 17 values. Consider a separate provider table later.
access_type type text No Free, Limited Free, Partner Site.
location_type location_t text No Group the Indoor AP variants.
ssid ssid text No Network name to show users.
remarks remarks text Yes For example "3 free 10 min sessions".
source_ref sourceid text Yes Kiosk id (LinkNYC) or organization name (Fiberless, Spot On). Treat 0 as null.
activated_on activated date Yes Treat 9999-09-09 as null.

Not carried over (internal or redundant): x, y, borough (code), borocode, city, coundist, borocd, ct2010, bctcb2010, bin, bbl, location_lat_long.

Fields the app creates, not in the source

App data Where it lives
Ratings, reviews E03 tables, linked by review.hotspot_id to hotspot.hotspot_id
Average rating, review count Calculated from reviews, never stored in the hotspot import
Reliability score Planned MLP (E06), not part of this mapping
Source label ("official" vs "community") Every hotspot row is official. Community content is labeled in the review tables.

4. ERD

5. Keys, uniqueness, and reloading official data

  • Primary key: hotspot_id from doitt_id (unique, no nulls in this snapshot).
  • Relationship for the ERD: one hotspot to many review (a review belongs to exactly one hotspot). A hotspot can exist with zero reviews.
  • Reloads: match on hotspot_id and update existing rows. Do not delete and recreate, or reviews lose their hotspot. If a hotspot disappears from a later export, mark it inactive instead of deleting it.
  • Open assumption to verify: that doitt_id stays the same across future exports. The Open Data dictionary file (Data_Dictionary_WiFi_Hotspots.xlsx, attached on the dataset page) should be checked to confirm.

6. Display rules (from the Definition of Done, section 6)

  • Official hotspot records show as official information and are never presented as a live connectivity check. The source was last updated in 2019.
  • A hotspot with no reviews shows "no ratings yet," never a rating of 0.
  • Known limitations are the gaps listed in section 2.

7. Verification

Checked against the full 3,319-row export acquired 2026-10-14: row count, column count, null rates per field, distinct counts, key uniqueness, and borough code consistency. Anyone can reproduce this by running verify_hotspot_data.py (included). The sample CSV was drawn from the same file.

8. Definition of Done check (Week 4 Sprint Plan, section 5)

DoD item Status
Source data documented Done (section 1)
Field mappings documented Done (section 3)
Setup and loading instructions Done (section 1, load steps)
Official records keep their identity Done (hotspot_id from doitt_id, upsert on reload)
Review fixtures labeled as test/demo data Rule stated (section 1). Applies to the E03 fixtures, not this task.
Another contributor can reproduce the verification Done (verify_hotspot_data.py)
ERD Pending, owned by Suvan
Official vs community info distinguished, no zero ratings Rules stated (sections 3 and 5)