ziggy aepyornis

A day in SQL HELL: or how I learned to like (or at least appreciate) SQL by ziggy

In short: the secret to who owns nyc will probably be found in a really complicated sql statement

Here's how this all started. I was working on my project to analyze heat/hot water complaints. I downloaded all the complaints for this heat season (there's over 200,00), geocoded them to obtain the bbls, and then imported them into postgres. I wanted to match complaints with their owners in order to find out whose responsible for not providing tenants' with essential services.

So I fired up PSQL and started typing out some queries.

SELECT bbl,

Keybase proof

I hereby claim:

I am aepyornis on github.
I am zy (https://keybase.io/zy) on keybase.
I have a public key whose fingerprint is FFE6 48C8 E335 C52A BB57 1EA7 A40D 8B63 5065 8609

To claim this, I am signing this object:

A tour of clojure(script) syntax.

Perhaps you will or won't find this interesting, but I think clojure provides some compelling form to work with. I've essentially just going over the bits that are different from languages we are used to, like javascript. Anyways, it's just help for me to write this all down.

data structures: There are are immutable, which is part of the big selling point of clojuire.

Maps:

	function writeCSV(columnNames, fileName, query, callback){
	var db = mongojs('mongodb://localhost:27017/test', ['jobs']);
	//array of columnNames
	var columns = [];
	//ensure that columnNames is array
	if (typeof columnNames === 'string') {
	columns.push(columnNames);
	} else if (columnNames.constructor === Array) {
	columns = columnNames;
	} else {

	var fs = require('fs');
	var should = require('should');
	var util = require('../util');
	// var db = mongojs('mongodb://localhost:27017/test', ['jobs']);

	describe('writeCSV', function(){
	this.timeout(20000);

	before(function(done){
	util.writeCSV(['BuildingType', 'ExistingStories', 'OwnerName'], 'test3', {'CB': '304'}, function(){

	var fs = require('fs');
	var mongojs = require('mongojs');
	var util = require('./util');

	util.writeCSV(['BuildingType', 'ExistingStories', 'OwnerName'], 'test3', {'CB': '304'}, function(){
	console.log('done');
	});

	# get bbls
	import glob
	import csv

	def get_bbl(row):
	return row[69] + ','

	# create file to store bbls
	bbl_file = open('./all_bbls.csv', 'w')

	function join () {
	return _.map(total_complaints(complaints), function(val, i, list) {
	var complaint = val;
	var bbl = complaint[1];
	complaint = complaint.concat(recentJob(bbl).slice(0, 3))
	complaint = complaint.concat(get_pluto_data(bbl));
	return complaint;
	})
	}

	var _ = require('underscore');

	var arr = [1,2,3,4]

	arr.someProperty = "blah blah blah";

	// this prints out only 1,2,3,4
	_.each(arr, function(x){
	console.log(x);
	})


	function doSomething() {
	var someValue = "this is an important value";
	anotherFuctionDefinedElsewhere();
	}

	function anotherFuctionDefinedElsewhere () {
	// this function can't access someValue
	console.log(someValue);
	}