In Class SQL Workshop

On Monday we wrote a SQL for determining the most culturally diverse neighborhoods in Chicago as an exercise to demonstrate the potential and functionality of SQL in PGAdmin.

Continue reading


Naming Conventions

For the database it is important to set up conventions for naming our files so everyone knows what files are and it will be more organized. A few of us met today to define major categories and this is what we came up with. We can discuss this tomorrow further, so it is more suitable for everyones projects, but until then use this naming for the new database. Just a reminder… do not use spaces or upper case letters in your table names.

City Geom:
– ex. Sidewalks, City Limits, Streets, Building Footprints
– should ideally just be an “id” and “geom” column as this will be used mainly for visualizing in QGIS
– start layers in this category with cty_

– ex. Bus Routes, CTA Stops
– start layers in this category with trn_

– ex. Census by Block 2010, Income by Block 2000
– start layers in this category with dmo_

– ex. Parks, Trees
– start layers in this category with lnd_

– ex. Police Districts, TIF Districts, Neighborhoods, Wards
– start layers in this category with jur_

– ex. Crime 2013, 311 Requests, Permits, Business Licenses
– start layers in this category with rpt_

– ex. Facebook API, Yelp API
– start layers in this category with soc_

– ex. Customer, Alderman, Dogs
– start layers in this category with agt_


Another place to get data:
(worldwide, but has all Chicago Data Portal data and then some)


If you’ve ever used craigslist before then you know, it’s just not very good. That’s not to say you can’t find what you need on there. The site is full of amazing deals and goods and services of all kinds, but navigating it involves opening new browser tab after browser tab, going back and forth and generally losing your way.


For those of you who are tired of the craigslist user experience from circa 1996, head on over to craiggers, the site that lets you interact with Craigslist the way you ought to.

As the craiggers’ tagline says, the site is simply “craigslist data, better than craigslist!” It allows users a number of simple functions you’ve likely unconsciously wished for for years but didn’t even realize you were desperately missing. For example, the site separates navigation into a number of columns, so you don’t need to open listings in new tabs or hit the back and forward buttons all the time. Click on a result and it loads in the same page. Hit the down arrow or click on a different entry and it loads in the right most column without ever leaving the page.

Beyond navigation – which is quite an improvement already – craiggers adds on a new layer of functionality when it comes to searching. No longer do you have to search simply within a single geographic area. As the site points out, “there are cases when searching outside your immediate community benefits both seekers and providers,” giving the example of searching for a stolen bike or adopting a dog. When you search on craiggers, you can specify that you want to see results from neighboring locations and it will show you those as well.

Furthermore, if you wanted to search craigslist repeatedly, say for a job or an apartment, craiggers will not only let you save the search to repeat later, but it will also send you an email notification twice a day of results.

craiggers: An Example for Developers

For those of you out there interested in more than simply craiglist searches, there’s another interesting aspect to craiggers – it was built using the 3taps API. We firstwrote about 3taps last month when the company launched at the Data 2.0 conference, explaining how the company wanted to “democratize the exchange of data.”

Through the 3taps API, data from craigslist, eBay, Indeed, Etsy, Amazon and a host of other services is available in real-time, making mash-ups like this possible. Craiggers was built by the 3taps team as an example of the potential of its offering and we think it makes quite an argument.

Examples of Visualizing Data using various media


Attached are two links to relevant projects for those dealing with social media and GIS in general. The first website is an explanation of two projects from the Columbia GSAPP Spatial Information Design Lab. The first is a map showing all the foursquare check-ins during a one week span in New York City. Within this they highlight another project about the incarceration rate amongst public housing blocks in NYC. Both are not only relevant to Chicago but also show great visual ways of representing the data extracted


The second is the SIDL lab website. They have other projects on their website as well and it is a great example of visualizing the data collections