Creating Convex Hulls with SQL to QGIS: Part 1

In order to create clusters based on foursquare categories, we first made a table in PostgreSQL called upperlevelcat and assigned a gid for each of the nine categories.

Foursquare Categories:

upperlevel cat table

To link these categories to venues, we did a double join : fscategory table, fsvenue, and fsvenue_fscategory tables.

fscategory table


fsvenue table

fsvenue table



SQL statement for double join:

SELECT v.herenow, v.checkinct, v.userct, join1.upperlevelcat, as catname, as venuename,,v.lon,v.geom

FROM (SELECT * from fsvenue_fscategory

LEFT JOIN fscategory as fsc ON fsc.fsid=fsvenue_fscategory.cid) AS join1

LEFT JOIN fsvenue as v ON  v.fsid=join1.vid




Dynamic Boundaries

Hypothesis Statement:

The boundaries of a city are not fixed, they are dynamic. The hard edge boundary lines illustrated in traditional municipal maps do not reflect the lively nature of a city. By querying Foursquare “here/now” data and employing clustering algorithms, our prototype groups Foursquare venues together based on location and activity levels. By identifying and mapping these clusters through time, this tool helps to reveal the dynamic patterns and processes of boundary formation, mapping out opportunities for effective intervention.


Process Diagram

Mapping of Foursquare Here Now Check-Ins and Business Licenses

Business Licenses Description view 2new business licenses in ward 32Business License descriptions with foursquare checkins bucktown ward 31

First Map: Business license distribution throughout Chicago from 2006-present, including renewals and newly issued.

Mapping people checking into Foursquare venues:

Second Map: Foursquare venue here now check-ins Bucktown are denoted in white, new business licenses in ward 32 issued since 2006 are shown in purple.

The third map illustrates business license descriptions. A high amount of business licenses are in yellow, limited business licenses.

How do business licenses relate to the vibrancy of an area?

The Livehoods Project: Utilizing Social Media to Understand the Dynamics of a City

Studying the social dynamics of a city on a large scale has tra- ditionally been a challenging endeavor, requiring long hours of observation and interviews, usually resulting in only a par- tial depiction of reality. At the same time, the boundaries of municipal organizational units, such as neighborhoods and districts, are largely statically defined by the city government and do not always reflect the character of life in these ar- eas. To address both difficulties, we introduce a clustering model and research methodology for studying the structure and composition of a city based on the social media its res- idents generate. We use data from approximately 18 million check-ins collected from users of a location-based online so- cial network. The resulting clusters, which we call Livehoods, are representations of the dynamic urban areas that comprise the city. We take an interdisciplinary approach to validating these clusters, interviewing 27 residents of Pittsburgh, PA, to see how their perceptions of the city project onto our findings there. Our results provide strong support for the discovered clusters, showing how Livehoods reveal the distinctly charac- terized areas of the city and the forces that shape them.

Mapping Foursquare Check-Ins using QGIS

Here are some simple steps I used to map the Foursquare check-ins:

1) Doing  simple Foursquare search using the venue explore API, I selected coffee shop check-ins, using coordinates for The Loop within a 5000 meter distance.  These are the response results Apigee Snapshot: Foursquare The Loop Coffee Shop Check-Ins.

2) In order to geolocate these check-ins in QGIS, I had to import the JSON into Excel, filter and parse the information to show, name of coffee shop, latitude, and longitude.

3) Import the CSV file to QGIS

These maps are showing coffee shop check-ins in The Loop with purple dots.


Coffee check-ins, The Loop 09.23.13 830 amCoffee check-ins, The Loop 09.23.13 830 am 2

Neighborhood boundaries based on social media activity

Researchers at the School of Computer Science at Carnegie Mellon University investigate the structure of cities in Livehoods, using foursquare check-ins.

The hypothesis underlying our work is that the character of an urban area is defined not just by the the types of places found there, but also by the people who make the area part of their daily routine. To explore this hypothesis, given data from over 18 million foursquare check-ins, we introduce a model that groups nearby venues into areas based on patterns in the set of people who check-in to them. By examining patterns in these check-ins, we can learn about the different areas that comprise the city, allowing us to study the social dynamics, structure, and character of cities on a large scale.

It’s most interesting when you click on location dots. A Livehood is highlighted and a panel on the top right tells you what the neighborhood is like, related neighborhoods, and provides stats like hourly and daily pulse and a breakdown location categories (for example, food and nightlife). Does foursquare have anything like this tied into their system? They should if they don’t.

There’s only maps for San Francisco, New York City, and Pittsburgh right now, but I’m sure there are more to come.

Want more on the clustering behind the maps? Here’s the paper [pdf].livehoods_icwsm12livehoods