Creating Convex Hulls with SQL to QGIS: Part 2

We created clusters (convex hulls) of venues making distinctions between the venues by categories. We started off by creating a mathematical algorithm that combines multiple equations:

  • We needed to determine a method of comparison for all the venues. Our method used the average distance between all the venues. We used the average distance, which we calculated by comparing the distance from one venue to all other venues and doing this for all the venues and then dividing the number of venues. In essence, it is a permutation with repetition calculation.
  • After we calculated the average distance we set a threshold divider of the average distance to determine the size of the clusters that we wanted to use.
  • Then we had to match the venue points of comparison to cluster only by category whereby venue points would only cluster when one venue category was equal to another venue category.
  • The next step was to create the shape of the cluster creating a geometry polygon to import as a PostGIS shapefile.

Step 1:

CREATE TABLE avgdistances2 AS

SELECT * FROM  (SELECT f1.gid as v1id, f1.fsid, f2.gid as v2id, st_distance(f1.geom,f2.geom) AS dist, f1.upperlevelcat as f1ul, f2.upperlevelcat as f2ul

FROM fsvenuewithupperlevelcat2 as f1

CROSS JOIN fsvenuewithupperlevelcat2 as f2  WHERE f1.checkinct > 200 and f1.gid<>f2.gid) AS q1

WHERE q1.dist < ( SELECT SUM(avgdist)/count(hack) as avgdisttotal    

FROM     (SELECT f1.gid, 1 as hack, SUM(st_distance(f1.geom,f2.geom))/COUNT(f1.gid) AS avgdist

FROM fsvenue as f1

CROSS JOIN fsvenue as f2

WHERE  f1.gid <> f2.gid     GROUP BY f1.gid) as subq    GROUP BY subq.hack)/4 AND f1ul=f2ul  ORDER BY q1.v1id;

Step 2:

CREATE TABLE fsvenue_cathulls(   gid serial NOT NULL,   fsid character varying(256),   CONSTRAINT fsvenue_hulls_pkey PRIMARY KEY (gid) ) WITH (   OIDS=FALSE );

ALTER TABLE fsvenue_hulls  OWNER TO poweruser; GRANT ALL ON TABLE hullmp3 TO poweruser;

SELECT AddGeometryColumn(‘fsvenue_hulls’,’geom’,3435,’MULTIPOINT‘,2);

INSERT INTO fsvenue_hulls (fsid,geom) SELECT ad.fsid, st_collect(v.geom)  FROM avgdistances2 AS ad Left JOIN fsvenue AS v ON ad.v2id = v.gid WHERE v1id <> 42 GROUP BY ad.fsid ORDER BY ad.fsid;

CREATE VIEW fsvenue_cathulls AS SELECT v.gid, v.fsid, v.upperlevelcat, st_convexhull(h.geom)::geometry(‘Polygon’, 3435) as geom FROM fsvenue_hulls as h LEFT JOIN fsvenuewithupperlevelcat2 as v ON h.fsid = v.fsid WHERE st_npoints(h.geom) > 3;

SELECT * FROM fsvenue_cathulls;

Step 3:

Import PostGIS file to QGIS

Result from category 1: Arts & Entertainment

Bucktown & Wicker Park




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?

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

Are you Forming Neighborhoods in Chicago?

The city of Chicago maps out all different types of boundaries, but when it comes to neighborhoods the borders are up to residents. They are constantly changing, what a neighborhood is called, neighborhoods are broken into a number of smaller neighborhoods. How does all of this affect urban design? For example, in 1920, Edgewater was considered to be Uptown on the community area map.  In 1980, the Edgewater community council convinced the city to change this and draw a line between Edgewater and Uptown. Today, people consider Edgewater to consist of six communities, Edgewater, Edgewater Glen, Magnolia Glen, Edgewater Beach, Andersonville, Lakewood, Balmoral. These are neighborhoods within neighborhoods.  How can we use data and the public, users of these neighborhoods, to help us understand the changing boundaries and needs of city? An original survey was done in 1978, asking residents what they call their own neighborhoods, listed 178 neighborhoods, what would this look like today?

The point is that neighborhoods are not homogenous boundaries, they are constantly changing.  The first map shows hard boundaries lines for neighborhoods when in reality these are blurred. The second map start to illustrate the soft edges of boundaries through race and ethnicity.

1978 survey of neighborhoodsmap 1