NYC Open Data field reference

Dataset ids, query conventions and field names for the eleven NYC Open Data (Socrata) datasets Complied ingests, with how each field lands in public_events or buildings. For engineers writing or debugging a feed; architecture and scheduling are in ingestion. Column lists were checked against each dataset's live metadata.

Query conventions

Every dataset is a Socrata endpoint:

https://data.cityofnewyork.us/resource/<DATASET_ID>.json?<SoQL>

• Page with $limit (50,000 in the pipeline), $offset and a stable $order (:id, or a unique column). Loop until a page is shorter than the limit.
• $where, $select and $order are the filter, projection and sort. count(*) ($select=count(*) as n) sizes a backfill window.
• Send the app token as X-App-Token or $$app_token (NYC_OPEN_DATA_TOKEN) for higher rate limits.
• The hidden :updated_at system field supports incremental pulls where the publisher updates a few rows per night: $where=:updated_at > '<floating timestamp>'. It is only usable when the fraction of changed rows is small. Where a dataset is replaced wholesale each night, or its behaviour is unverified, the pipeline pulls it in full.
• Date shapes differ. timestamp columns are floating timestamps (2020-01-01T00:00:00). DOB violations and ECB use compact YYYYMMDD text, and the literal string "0" (or an impossible date such as month 20) means "no date"; sanitizeCompactDate and toDayString in feeds/socrata.ts normalise these.
• Every normalized event carries agency, event_type, a source_id prefixed by the feed, status_norm (OPEN, CLOSED, DISMISSED), an optional status_detail in the agency's own vocabulary, and the whole source row in raw_data.

Feed	Dataset	Id	agency	source_id	Update mode
hpd-violations	Housing Maintenance Code Violations	wvxf-dwi5	hpd	hpd-violation:<violationid>	Incremental
hpd-complaints	HMC Complaints and Problems	ygpa-z7cr	hpd	hpd-complaint:<complaint_id>	Incremental
hpd-litigation	Housing Litigations	59kj-x8nc	hpd	hpd-litigation:<litigationid>	Full pull
dob-violations	DOB Violations	3h2n-5cm9	dob	dob-violation:<number>	Incremental
dob-violations	DOB Complaints Received	eabe-havv	dob	dob-complaint:<complaint_number>	Full pull
ecb-violations	DOB ECB Violations	6bgk-3dad	ecb_oath	ecb-violation:<ecb_violation_number>	Full pull
oath-hearings	OATH Hearings Division Case Status	jz4z-kudi	fdny, dsny, dohmh	oath-hearing:<ticket_number>	Full pull
nyc-311	311 Service Requests from 2020 to Present	erm2-nwe9	hpd, dob, dep, dsny, fdny, dot	311:<unique_key>	Incremental
dof-liens	Tax Lien Sale Lists	9rz4-mjek	dof	dof-lien:<bbl>_<cycle>_<month>	Full pull
Layer 1	BUILDING (footprints)	5zhs-2jue			Manual
Layer 1	PLUTO	64uk-42ks			Manual

HPD violations, wvxf-dwi5

Housing Maintenance Code violations, including the lead orders. Filter by bin; page by violationid; backfill window on novissueddate.

Columns: violationid, buildingid, registrationid, boroid, boro, housenumber, lowhousenumber, highhousenumber, streetname, streetcode, zip, apartment, story, block, lot, class, inspectiondate, approveddate, originalcertifybydate, originalcorrectbydate, newcertifybydate, newcorrectbydate, certifieddate, ordernumber, novid, novdescription, novissueddate, currentstatusid, currentstatus, currentstatusdate, novtype, violationstatus, rentimpairing, latitude, longitude, communityboard, councildistrict, censustract, bin, bbl, nta.

Field	Meaning and mapping
violationid	Unique id; source_id.
bin, boroid + block + lot	Resolution keys: BIN, then a BBL composed as borough + 5-digit block + 4-digit lot.
class	Severity A, B, C, or I. Used as citation_code when there is no order number.
ordernumber	HPD order code; citation_code. Lead orders: 616 presumed lead, 617 lead hazard, 618 RPO filing (child at risk), 619 RPO annual certification, 620 RPO turnover certification, 621 turnover abatement, 622 turnover certification failure, 623 turnover clearance failure, 624 inconclusive XRF, 625 turnover notice failure, 626 child blood lead. These become event_type; any other order is violation.
novdescription	Notice text; description.
approveddate, novissueddate	issued_date (approved date preferred).
newcorrectbydate, originalcorrectbydate	due_date: the new date if present, else the original.
certifieddate	Set when the owner certified correction; used for overdue logic and as closed_date when not open.
currentstatusid, currentstatus, violationstatus	Status. The id is authoritative (see below); the text and violationstatus are fallbacks.
apartment	Unit label; linked to units at persist time, subject to the creatable-label guard.

Status ids map to status_detail: 1 OPEN; 2 OPEN_NOV_SENT; 3 OPEN_CERTIFIED_ON_TIME; 4 OPEN_CERTIFIED_LATE; 6 OPEN_POSTPONEMENT_GRANTED; 7 OPEN_POSTPONEMENT_DENIED; 8 OPEN_FALSE_CERT; 10 OPEN_CIV14_MAILED; 11 OPEN_WILL_BE_REINSPECTED; 20 OPEN_DEFECT_LETTER; 21 OPEN_NOT_COMPLIED; 22 and 23 OPEN_NO_ACCESS_1ST / _2ND; 24 OPEN_REOPENED; 27 OPEN_INFO_NOV_SENT; 28 OPEN_INVALID_CERT; 36 OPEN_PARTIAL_ACCESS (all OPEN); 19 is CLOSED; 9 is DISMISSED. An OPEN row whose correct-by date has passed and that has no certifieddate gets the suffix _OVERDUE on the day it is synced. Unrecognised ids fall back to violationstatus, then keyword matching on currentstatus, then plain OPEN.

HPD complaints and problems, ygpa-z7cr

One row per complaint problem. Page by complaint_id; backfill window on received_date (the one date every row carries; complaint_status_date is null while open). Rows carry bin and bbl.

Columns: received_date, problem_id, complaint_id, building_id, borough, house_number, street_name, post_code, block, lot, apartment, community_board, unit_type, space_type, type, major_category, minor_category, problem_code, complaint_status, complaint_status_date, problem_status, problem_status_date, status_description, problem_duplicate_flag, complaint_anonymous_flag, unique_key, latitude, longitude, council_district, census_tract, bin, bbl, nta.

Field	Mapping
complaint_id	source_id.
bin	Resolution key.
type, else major_category	event_type (fallback housing_complaint).
major_category, minor_category	description, joined with " - ".
complaint_status	Status: contains CLOSE, RESOLVED or COMPLETED is CLOSED; OPEN, PENDING or ACTIVE is OPEN; anything else is OPEN with detail UNKNOWN.
received_date, complaint_status_date	issued_date; closed_date when not open.
apartment	Unit label.

HPD litigation, 59kj-x8nc

Housing-court cases against owners. Full pull each run; filterable by bin or bbl.

Columns: litigationid, buildingid, boroid, housenumber, streetname, zip, block, lot, casetype, caseopendate, casestatus, casejudgement, findingofharassment, findingdate, penalty, respondent, latitude, longitude, community_district, council_district, census_tract, bin, bbl, nta.

Mapping: litigationid is source_id; casetype is event_type and citation_code; caseopendate is issued_date; casestatus containing DISMISS is DISMISSED, containing CLOSED is CLOSED, otherwise OPEN; description appends "judgement entered" when casejudgement is YES. Resolves by bin, then bbl.

DOB violations, 3h2n-5cm9

Dates are compact YYYYMMDD text with "0" as no date. Backfill window on issue_date.

Columns: isn_dob_bis_viol, boro, bin, block, lot, issue_date, violation_type_code, violation_number, house_number, street, disposition_date, disposition_comments, device_number, description, ecb_number, number, violation_category, violation_type.

Mapping: source_id uses number; event_type is violation_category; citation_code is violation_type; issue_date is issued_date; disposition_date is closed_date. Status: a dismiss keyword in category is DISMISSED; a disposition date or a "resolved" category is CLOSED; otherwise OPEN. Resolves by bin.

DOB complaints, eabe-havv

Replaced wholesale each night, so always pulled in full. Backfill window on date_entered (timestamp).

Columns: complaint_number, status, date_entered, house_number, house_street, zip_code, bin, community_board, special_district, complaint_category, unit, disposition_date, disposition_code, inspection_date, dobrundate.

Mapping: complaint_number is source_id; complaint_category is event_type and citation_code; date_entered is issued_date; disposition_date is closed_date. Status from status and disposition date: dismiss keyword is DISMISSED, a disposition date or "clos" / "resolved" is CLOSED, otherwise OPEN. Resolves by bin.

ECB violations, 6bgk-3dad

The money and hearings feed. Compact YYYYMMDD dates with "0" as no date. Backfill window on issue_date.

Columns: isn_dob_bis_extract, ecb_violation_number, ecb_violation_status, dob_violation_number, bin, boro, block, lot, hearing_date, hearing_time, served_date, issue_date, severity, violation_type, respondent_name, respondent_house_number, respondent_street, respondent_city, respondent_zip, violation_description, penality_imposed (sic), amount_paid, balance_due, infraction_code1 to infraction_code10 with section_law_description1 to 10, aggravated_level, hearing_status, certification_status.

Mapping: ecb_violation_number is source_id; violation_type is event_type and citation_code; issue_date is issued_date; hearing_date is due_date; violation_description is description. Status: ecb_violation_status = RESOLVE is CLOSED, or DISMISSED when hearing_status says dismissed or vacated; ACTIVE is OPEN, with detail OPEN_OVERDUE if the hearing date has passed; otherwise hearing_status decides. The map reads hearing_date, hearing_status and balance_due from raw_data. Resolves by bin.

OATH hearings, jz4z-kudi

Tickets from all issuing agencies; the feed excludes issuing_agency LIKE '%BUILDINGS%' (DOB tickets arrive through ECB). Backfill window on violation_date, not hearing_date, which is null until a hearing is scheduled.

Columns: ticket_number, violation_date, violation_time, issuing_agency, respondent_last_name, balance_due, violation_location_borough, violation_location_block_no, violation_location_lot_no, violation_location_house, violation_location_street_name, violation_location_floor, violation_location_city, violation_location_zip_code, violation_location_state_name, respondent address columns, hearing_status, hearing_result, scheduled_hearing_location, hearing_date, hearing_time, decision columns, total_violation_amount, violation_details, date_judgment_docketed, penalty_imposed, paid_amount, additional_penalties_or_late_fees, compliance_status, violation_description, and charge_1_* to charge_10_* (code, section, description, infraction amount).

Mapping: ticket_number is source_id; issuing_agency chooses agency per row (FIRE is fdny; SANITATION, RECYCLING or POLICE is dsny; DOHMH, DOH or HEALTH is dohmh; anything else is skipped); event_type is oath_hearing; violation_date is issued_date; hearing_date is due_date. Status: dismissal or vacatur in hearing_result / compliance_status is DISMISSED; "closed", "paid in full", "satisfied", a settled balance, or paid in full is CLOSED; otherwise OPEN. Resolution keys are the house number and street plus borough (violation_location_borough), the lowest-confidence tier; violation_location_block_no and _lot_no are present in the dataset.

311 service requests, erm2-nwe9

Filtered to agency IN ('HPD','DOB','DEP','DSNY','FDNY','DOT'). The dataset updates several times a day. Backfill window on created_date (default window 7 days, the densest feed).

Columns include: unique_key, created_date, closed_date, agency, agency_name, complaint_type, descriptor, descriptor_2, location_type, incident_zip, incident_address, street_name, cross_street_1, cross_street_2, address_type, city, status, due_date, resolution_description, resolution_action_updated_date, community_board, council_district, police_precinct, bbl, borough, coordinates, latitude, longitude, location, and channel, park, vehicle, taxi and bridge columns that the pipeline does not read.

Mapping: unique_key is source_id; the agency code maps to the lowercase agency id; complaint_type is event_type; created_date and closed_date are issued_date and closed_date; descriptor is description. Status: closed_date set or status "closed" is CLOSED; "cancel" is DISMISSED (detail CANCELLED); otherwise OPEN. Resolution keys are incident_address plus borough.

DOF tax lien sale list, 9rz4-mjek

The lien-sale eligibility roster: one row per lot per cycle and month, with no amounts or dates.

Columns: month, cycle, borough, block, lot, tax_class_code, building_class, community_board, council_district, house_number, street_name, zip_code, water_debt_only.

Mapping: a 10-digit BBL is composed from borough, block, lot and is both the resolution key and part of source_id; event_type is tax_lien, or tax_lien_water_debt when water_debt_only is true; description is "Water debt only", else the building class or the address. Every row is OPEN. A row with no BBL is skipped.

Building footprints, 5zhs-2jue

Layer 1 identity source: one row per building.

Columns: the_geom, name, bin, doitt_id, shape_area, base_bbl, objectid, construction_year, feature_code, geom_source, ground_elevation, height_roof, last_edited_date, last_status_type, mappluto_bbl, shape_length.

Field	Use
bin	The building's identity; a row without one is dropped.
mappluto_bbl, else base_bbl	Tax lot for the PLUTO join (10 digits).
construction_year	Year built fallback when PLUTO has none.

PLUTO, 64uk-42ks

Tax-lot facts; 108 columns, of which the pipeline reads bbl, address, borough, zipcode, latitude, longitude, yearbuilt and unitstotal. PLUTO's bbl is a decimal string (for example 4087860042.00000000), truncated and left-padded to 10 digits. PLUTO has no BIN, and one lot can hold several buildings, so a footprint row is joined to its lot's facts on BBL: yearbuilt becomes buildings.year_built and unitstotal the unit count. A footprint with no PLUTO match still becomes a buildings row, without address and year facts.

Field-name gotchas

• penality_imposed in ECB is misspelled in the source.
• DOB and ECB use "0" for no date, and DOB contains impossible dates.
• HPD violation status text is unreliable; use currentstatusid.
• Datasets that expose a BBL do not all format it alike: HPD violations compose it from boroid, block, lot; PLUTO's is decimal; Footprints' is already 10 digits.