Air: Update filter for DEP dataset

NYCPlanning / ceqr-app-data-archive

(DEPRECATED)data pipelines for CEQR app, managed by data engineering

https://github.com/NYCPlanning/ceqr-app-data

1 stars 1 forks source link

Air: Update filter for DEP dataset #79

Closed AmandaDoyle closed 4 years ago

AmandaDoyle commented 4 years ago

Remove the following records from the DEP Permits dataset

All Cancelled permits (no change)
All G-prefix permits (no change)
All C-prefix permits (change)
All P-prefix permits with Registration (including Registration, Boiler Registration II, and Registration Inspection) (change)

mgraber commented 4 years ago

SELECT * FROM dep_cats_permits.latest
WHERE TRIM(status) != 'CANCELLED'
AND LEFT(applicationid, 1) != 'G'
AND LEFT(applicationid, 1) != 'C'
AND NOT (LEFT(applicationid, 1) = 'P'
    AND (requesttype = 'REGISTRATION'
    OR requesttype = 'REGISTRATION INSPECTION'
    OR requesttype = 'BOILER REGISTRATION II'));

Filtering out cases where LEFT(applicationid, 1) = 'C' might need to happen after geocoding if the review subset in #80 needs to be geocoded.