Quick makefile for loading the current Pluto dataset into MySQL.

Makefile

URL = http://www.nyc.gov/html/dcp/download/bytes/
FILE = nyc_pluto_15v1.zip

VERSION = 15v1

boroughs = Mn BK BX QN SI

MYSQL = mysql --user="$(USER)" -p$(PASS) $(MYSQLFLAGS)
DATABASE = pluto

mysql: mysql$(VERSION)

.PHONY: mysql mysql% clean
mysql%: pluto%.csv
	$(MYSQL) --execute "CREATE DATABASE IF NOT EXISTS $(DATABASE);"

	head -n 1000 $< | csvsql -imysql --table pluto$* | \
	sed -e 's/CREATE TABLE/CREATE TABLE IF NOT EXISTS/' | \
	$(MYSQL) --database $(DATABASE)

	$(MYSQL) --execute "ALTER TABLE $(DATABASE).pluto$* ADD INDEX BBL (Borough, Block, Lot);"

	$(MYSQL) --execute "LOCK TABLES $(DATABASE).pluto$* WRITE; \
	LOAD DATA LOCAL INFILE '$<' INTO TABLE $(DATABASE).pluto$* \
	FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES; \
	UNLOCK TABLES;"

pluto%.csv: $(VERSION)/header.csv $(foreach x,$(boroughs),$(VERSION)/$x.csv)
	cat $^ > $@

.INTERMEDIATE: $(VERSION)/%.csv

$(VERSION)/header.csv: nyc_pluto_$(VERSION).zip
	unzip -p $< $(firstword $(boroughs)).csv | \
	head -n 1 > $@ 

$(addprefix $(VERSION)/,$(addsuffix .csv,$(boroughs))): nyc_pluto_$(VERSION).zip | $(VERSION)
	unzip -p $< $(@F) | \
	tail -n+2 > $@

$(VERSION): ; mkdir -p $@

nyc_pluto_$(VERSION).zip:
	curl -o $@ $(URL)/$(FILE)

clean: ; rm -rf $(VERSION) pluto$(VERSION).csv