Dataset: ECB Violations - nycdb/nycdb GitHub Wiki
The ecb_violations dataset can be added to NYCDB by running:
nycdb --download ecb_violations
nycdb --load ecb_violations
The dataset comes from the NYC open data portal's DOB ECB Violations dataset.
Violations by the Department of Buildings that are adjudicated by OATH/ECB. There may also be a DOB violation.
ECB Violations from this dataset can also be connected to records in the OATH Hearings for get additional information. For more details on how to do this, see this example query.
You can download the data dictionary XLSX.
Click to Expand the Data Dictionary
| column_name | data_type | description |
|---|---|---|
| isndobbisextract | text | Unique identifier for the record assigned by Department of Buildings |
| ecbviolationnumber | text | Unique identifier for the Environmental Control Board (ECB) violation (This number can be used to check the status on the OATH Hearings Division Case Status dataset, where the same field is called ticketnumber) |
| ecbviolationstatus | text | Indicates whether or not the violation has been corrected. This is the status of the violation with DOB, not the status of the hearing with OATH (Expected values: ACTIVE - still needs to be addressed, RESOLVE - the issue was either fixed with DOB or dismissed by OATH) |
| dobviolationnumber | text | When an ECB violation is issued, Department of Buildings also issues a violation. This is the unique identifier for the violation issued by the Department of Buildings. See the DOB Violations dataset for more information |
| bin | text | Building Identification Number for the locatoin that received the violation. Assigned by Department of City Planning |
| boro | char(1) | A number to indicate the NYC borough where the violation was issued (1= Manhattan, 2= Bronx, 3 = Brooklyn, 4 = Queens, 5 = Staten Island) |
| block | char(5) | Tax Block for the location that received the violation. Assigned by the Department of Finance |
| lot | char(4) | Tax Lot for the location that received the violation. Assigned by the Department of Finance |
| bbl | char(10) | A combination of three numeric codes--a 1-digit borough number, a block number (up to 5 digits) and a lot number (up to 4 digits)--designated and modified by the Department of Finance (DOF). BBLs are used by various city agencies to identify real estate for taxes, zoning, construction, and other purposes |
| hearingdate | date | Date of the latest scheduled hearing for the respondent named on the violation to admit to it or contest the violation (This date may change if, for example, the hearing is postponed) |
| hearingtime | text | Time of the scheduled hearing for the respondent named on the violation to admit to it or contest the violation |
| serveddate | date | Date that the violation was served to the respondent |
| issuedate | date | Date that the violation was issued |
| severity | text | Indicated Violation Severity (Expected values: Hazardous, Non-Hazardous, Unknown) |
| violationtype | text | Violations are grouped into types based on their infraction code (Expected values: Administrative, Boilers, Construction, Cranes and Derricks, Elevators, HPD, Local Law, Padlock, Plumbing, Public Assembly, Quality of Life, Signs, Site Safety, Unknown, Zoning) |
| respondentname | text | Name of the person or entity that the violation was issued to and who is responsible for any penalties |
| respondenthousenumber | text | House number for the mailing address for the respondentname
|
| respondentstreet | text | Street name for the mailing address for the respondentname
|
| respondentcity | text | City for the mailing address for the respondentname
|
| respondentzip | char(5) | Zip Code for the mailing address for the respondentname
|
| violationdescription | text | Comments from the ECB inspector who issued the violation |
| penalityimposed | numeric | Dollar amount of the penalty imposed by OATH after adjudication |
| amountpaid | numeric | Dollar amount that was paid toward the penalty |
| balancedue | numeric | Dollar amount that is left to be paid toward the penalty |
| infractioncode1 | text | The 3-digit code for the first infraction on the violation (Get a complete list of codes and their descriptions) |
| sectionlawdescription1 | text | The section of the applicable law for this violation |
| infractioncode2 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription2 | text | The section of the applicable law for this violation |
| infractioncode3 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription3 | text | The section of the applicable law for this violation |
| infractioncode4 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription4 | text | The section of the applicable law for this violation |
| infractioncode5 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription5 | text | The section of the applicable law for this violation |
| infractioncode6 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription6 | text | The section of the applicable law for this violation |
| infractioncode7 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription7 | text | The section of the applicable law for this violation |
| infractioncode8 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription8 | text | The section of the applicable law for this violation |
| infractioncode9 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription9 | text | The section of the applicable law for this violation |
| infractioncode10 | text | The 3-digit code for the next infraction on the violation |
| sectionlawdescription10 | text | The section of the applicable law for this violation |
| aggravatedlevel | text | This indicates if the respondentname has a pattern of ECB violations or that there was a fatality, serious injury or risk thereof at the place of occurrence |
| hearingstatus | text | Status of the hearing |
| certificationstatus | text | Indicates whether respondent/owner has certified the violation as corrected with DOB |
This dataset has the following tables:
-
ecb_violationsis the only table in this dataset.
Following are some useful SQL queries related to the dataset.
The ecb_violations table has a number of column that contain the section of the law that is in violation. You can search in these columns for specific types of violations. In this example we look in all the columns for NYC Law 28-210.1 to find illegal residential conversions.
To do this search we use the ~ operator and a regular expression pattern for the law secion code. These lines essentially say does this column contain this pattern, and we've connected them with OR, so if it appears in any of the columns we'll get that record in our result.
The pattern we use is (^|\D)28-210\.1(\D|$) and this means: does "28-210.1" appear in the column where it is either at the begining of the field (^) or is preceded by a non-number character (\D), and is followed by a non-number character of the end of the field ($).
SELECT *
FROM ecb_violations
WHERE
( -- NYC Law 28-210.1 Illegal residential conversions.
sectionlawdescription1 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription2 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription3 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription4 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription5 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription6 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription7 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription8 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription9 ~ '(^|\D)28-210\.1(\D|$)' OR
sectionlawdescription10 ~ '(^|\D)28-210\.1(\D|$)'
)In ecb_violations each violation is uniquly identified by ecbviolationnumber, and this number can be used to check the status of the violation in the OATH Hearings dataset, where the same field is called ticketnumber.
Unfortuntately, right now the format of these two columns is not the same so you can to slightly alter the formatting of ecbviolationnumber for the join to work.
SELECT
ecb.*,
oath.*
FROM ecb_violations AS ecb
LEFT JOIN oath_hearings AS oath
ON lpad(ecb.ecbviolationnumber, 10, '0') = oath.ticketnumber