Skip to content

Instantly share code, notes, and snippets.

@clhenrick

clhenrick/nyc_421a.sql

Last active December 26, 2019 20:29
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save clhenrick/6dc734e97797ef4c36692ef2b296c109 to your computer and use it in GitHub Desktop.
Save clhenrick/6dc734e97797ef4c36692ef2b296c109 to your computer and use it in GitHub Desktop.
Download ZIP
Create table statement for NYC properties with 421a tax exemption status, using data from https://github.com/toolness/nyc-421a-xls
Raw
nyc_421a.sql
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