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

arts

 

Advertisements

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

fscategory2

fsvenue table

fsvenue table

fsvenue_fscategory

fsvenue_fscategory

SQL statement for double join:

SELECT v.herenow, v.checkinct, v.userct, join1.upperlevelcat, join1.name as catname, v.name as venuename,v.lat,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

Result

result

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?

Tag the place

We did a exercise about letting people tag on place in main campus, a panorama that simulate augmented reality app that people can actually tag on. This exercise help us create the first generation data base.

IMG_1845

IMG_1526

IMG_1534

IMG_1536

3

IMG_1543`