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




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s