Skip to content

Instantly share code, notes, and snippets.

@clhenrick

clhenrick/nyc_421a.sql

Last active Dec 26, 2019
Embed
What would you like to do?
Create table statement for NYC properties with 421a tax exemption status, using data from https://github.com/toolness/nyc-421a-xls
DROP TABLE IF EXISTS nyc_421a;
CREATE TABLE nyc_421a (
years numeric,
borough_name varchar,
borough numeric,
neighborhood varchar,
building_class_category varchar,
tax_class_at_present varchar,
block numeric,
lot numeric,
building_class_at_present varchar,
address varchar,
zipcode numeric,
residential_units numeric,
commercial_units numeric,
total_units numeric,
land_sq_ft numeric,
gross_sq_ft numeric,
year_built numeric
);
COPY nyc_421a
FROM '/Users/clhenrick/projects/nyc-421a-xls/data/421a.csv'
WITH CSV HEADER;
-- correct column data types
ALTER TABLE nyc_421a
ALTER COLUMN years type integer,
ALTER COLUMN borough type integer,
ALTER COLUMN block type integer,
ALTER COLUMN lot type integer,
ALTER COLUMN zipcode type integer,
ALTER COLUMN residential_units type integer,
ALTER COLUMN commercial_units type integer,
ALTER COLUMN total_units type integer,
ALTER COLUMN year_built type integer;
-- add column for concatenated borough, block, lot
ALTER TABLE nyc_421a
ADD COLUMN bbl varchar,
ADD CONSTRAINT bbl CHECK (char_length(bbl) <= 10);
UPDATE nyc_421a SET bbl = borough::text || LPAD(block::text, 5, '0') || LPAD(lot::text, 4, '0');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.