<\/p>\n
Socrata is a Seattle-based company, originally founded as Blist in 2007, that has engineered the platform for a number of open government databases, including that of New York, Chicago, Baltimore, the White House, and a number of federal agencies. Socrata catalogs all its open datasets here<\/a>. Socrata platforms are the gateway to a number of government\u00a0datasets you may want to use. I also like that the platforms are well-structured, relatively well-documented, and offer an SQL-like query language that can be a good preview to using\u00a0MySQL and other database packages.<\/p>\n Not at all! Sometimes a\u00a0dataset is small enough that you can download it through the Socrata web UI. But in the case that you are dealing with massive datasets like the NYC 311 callbase, you will need to be able to use SoQL to get exactly what you want (unless you want to wait for hours and days to download massive datasets).<\/p>\n Please peruse\u00a0the\u00a0Socrata Open Data API Docs<\/a>\u00a0to understand the following\u00a0NYC Open Data<\/a>\u00a0queries. I wanted to query this month’s\u00a0311<\/a> noise complaints in in my neighborhood (zip code 11231), so I obtained the dataset’s API endpoint<\/a>, then I\u00a0modified the format extension<\/a> to output CSVs instead of JSON, and finally I added filter<\/a> and query<\/a> parameters to obtain\u00a0this month’s noise complaints in 11231.<\/p>\n Try pasting\u00a0the following queries as URLs in your browser; for each query, a small a CSV will download\u00a0to your computer.<\/p>\n <\/p>\n Expand the NYC Open Data exercise by looking at December 2014, the month of protests around Eric Garner\u2019s death. Form a query that outputs a\u00a0CSV with the following attributes:<\/p>\n If you have some visualization experience, you can use the\u00a0Socrata examples<\/a>\u00a0to help you turn your new dataset into a visualization. This will be due in two weeks. If you aren’t there yet, give it a try. I’ll post a brief\u00a0tutorial on visualizing your data next week.<\/p>\n You will probably be using Excel, Google Sheets, Numbers, or another spreadsheet tool to view your data. It can be very handy–especially when you’re annotating your vis or writing a report on it–to know how to put together basic formulas using spreadsheet functions. Since we all have access to Google via our NYU addresses, here are some basic how-tos on putting together a formula using Google Sheet functions:<\/p>\n Background What is Socrata? Socrata is a Seattle-based company, originally founded as Blist in 2007, that has engineered the platform for a number of open government databases, including that of New York, Chicago, Baltimore, the White House, and a number of federal agencies. Socrata catalogs all its open datasets here. It also hosts community […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,8],"tags":[],"class_list":["post-483","post","type-post","status-publish","format-standard","hentry","category-nyu-datavis","category-tutorials"],"_links":{"self":[{"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=\/wp\/v2\/posts\/483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=483"}],"version-history":[{"count":5,"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=\/wp\/v2\/posts\/483\/revisions"}],"predecessor-version":[{"id":564,"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=\/wp\/v2\/posts\/483\/revisions\/564"}],"wp:attachment":[{"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/arlduc.org\/senseandscale\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n<\/strong>It also hosts community hackathon sites here<\/a>.
\nSODA =\u00a0Socrata Open Data API
\nSoQL =\u00a0Socrata Query Language<\/strong><\/p>\nWhy are we learning Socrata tools?<\/h3>\n
Do I always have to use SoQL to get Socrata data?<\/h3>\n
<\/h1>\n
Getting Started: NYC Open Data<\/h1>\n
\n
https:\/\/data.cityofnewyork.us\/resource\/erm2-nwe9.csv?incident_zip=11231<\/pre>\n
\n
https:\/\/data.cityofnewyork.us\/resource\/erm2-nwe9.csv?$where=incident_zip='11231'<\/pre>\n
\n
https:\/\/data.cityofnewyork.us\/resource\/erm2-nwe9.csv?$where=incident_zip='11231'&$limit=10000<\/pre>\n
\n
https:\/\/data.cityofnewyork.us\/resource\/erm2-nwe9.csv?$where=created_date >='2015-09-01T00:00:00'<\/pre>\n
\n
https:\/\/data.cityofnewyork.us\/resource\/erm2-nwe9.csv?$where=starts_with(complaint_type,'Noise')<\/pre>\n
\n
https:\/\/data.cityofnewyork.us\/resource\/erm2-nwe9.csv?$where=starts_with(complaint_type,'Noise') AND created_date >='2015-08-01T00:00:00' AND incident_zip='11231'<\/pre>\n
Now Try It!<\/h1>\n
\n
Start Visualizing<\/h1>\n
And a Handy Thing<\/h1>\n
\n