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



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?

facebook and mapping



Facebook intern Paul Butler has made a courageous attempt in revealing the electronic social connections worldwide. Turns out we do not need geographers to help us decide where to draw country borders, some clever social scientists with Facebook data should do.

Instead of taking the whole (secret?) Facebook social graph consisting of about 500 million people, the data is based on a sample of “about ten million pairs” of friends, which is combined with their home location. From this information, Paul was able to calculate the relative strength between pairs of cities, which was then normalized by their relative distance. By drawing lines between these pairs on top of each other, and fine-tuning their brightness, a map of visually distinguishable countries and continents naturally appeared. In fact, the map might not be that different from a view on the Earth at night, one commenter at Mashable remarked.

I began by taking a sample of about ten million pairs of friends from Apache Hive, our data warehouse. I combined that data with each user’s current city and summed the number of friends between each pair of cities. Then I merged the data with the longitude and latitude of each city.


At that point, I began exploring it in R, an open-source statistics environment. As a sanity check, I plotted points at some of the latitude and longitude coordinates. To my relief, what I saw was roughly an outline of the world. Next I erased the dots and plotted lines between the points. After a few minutes of rendering, a big white blob appeared in the center of the map. Some of the outer edges of the blob vaguely resembled the continents, but it was clear that I had too much data to get interesting results just by drawing lines. I thought that making the lines semi-transparent would do the trick, but I quickly realized that my graphing environment couldn’t handle enough shades of color for it to work the way I wanted.


Instead I found a way to simulate the effect I wanted. I defined weights for each pair of cities as a function of the Euclidean distance between them and the number of friends between them. Then I plotted lines between the pairs by weight, so that pairs of cities with the most friendships between them were drawn on top of the others. I used a color ramp from black to blue to white, with each line’s color depending on its weight. I also transformed some of the lines to wrap around the image, rather than spanning more than halfway around the world.”




“One of the most interesting / unexpected finds was seeing how closely tied Brazil is to Japan,” Facebook’s project lead Mandy Zibart says. “Brazilians are the third largest immigrant group to Japan… There’s a lot of buried treasure inside here.” When you click one of the multi-colored circles representing countries, related countries burst to life and enlarge based on how many friendships the two countries share. In many cases the results seem strange, so Facebook worked in coordination with an international relations researcher to develop “Closer Looks” for many of these relationships — hypotheses about why Nepal might have a very high number of friendships with Australia, for instance. In that case, Facebook says, the number of friendships could be due to the tens of thousands of student visas Australia has issued to Nepalese students within the last few years.


Facebook offers a source linked below each hypothesis that substantiates the company’s claim. The default view for examining friendships is “by continent,” but you can also click “by language” to view the world color-coded by the primary language each country speaks. It’s easy to explore French colonization in this view, since you can see at a glance which countries speak primarily French.



things might affecting neighborhood definition

Discussions about the traits of strong downtowns and what makes them succeed usually focus on larger cities such as Vancouver, BC, Portland, OR, New York, NY or Charleston, SC. However, a lot can also be learned by looking at things on a smaller scale. This happened to the authors, when we recently looked at downtowns in two small Wisconsin communities. What we learned from them is applicable to many other communities of comparable size.

Our experiences in these two communities certainly confirmed that two basic and broadly held revitalization tenets are just as applicable to small communities as they are to large ones: the need for a comprehensive approach to downtown revitalization and the need to focus on leveraging existing assets. The focus here will be on three other topics that evidence these tenets and deserve our attention:

  • The surprisingly complex economic development challenges that many small downtowns typically face
  • Providing jobs, especially in more rural areas, is a chronic and seemingly intractable problem
  • These small communities too often lack the resources and full range of professionals to initiate and manage broad economic changes.

we again found an economy with numerous economic components and related markets that would have to be analyzed:

  •  Retail and restaurants
  •  Personal services
  •  Educational facilities
  •  A medical clinic
  •  A seniors’ home
  •  A high tech manufacturer

Complex Land Use and Transportation Issues. Even more surprising than the number of markets we had to investigate in Sherwood and the depth of the analyses they required were the complex land use and transportation issues that were hurting the downtown:

  • A high degree of dispersion that might be more readily expected in a larger, more urban community. Even with its small population, Sherwood has four commercial nodes including a growing highway node that intercepts a lot of residents before they reach the downtown and where significant new businesses want to locate, e.g. a supermarket, a childcare center, restaurants. There is really poor economic agglomeration, and in a small economy economic assets benefit even more from agglomeration
  • The downtown is “unfriendly” to pedestrians – it lacks “walkability.” It has significant traffic with lots of trucks. It lacks a solid building wall front and adequate parking spaces. Many of its businesses are closed to shoppers during the day
  • An inability to benefit from a nearby “captive market.” Access to an abutting popular state park was changed so visitors no longer had to drive through the downtown – or Sherwood
  • An underdeveloped local roadway system that does not bring residents in newer parts of town naturally to the downtown. Also, the State recently proposed a highway expansion through the heart of downtown, which would have demolished several businesses and undermined what little pedestrian activity currently exists.

our team found a number of complex land use and transportation issues to address. However, unlike Sherwood, which faces growing pains associated with exurban growth, Village X is facing strong, complex and seemingly intractable challenges, characteristic of other small, often more rural communities and their downtowns:

  • Its region is sparsely populated and has little or no growth
  • The regional economy has long been problematic
  •  Attracting or creating firms that can provide new jobs is tough.

Characteristics and Guidelines of Great Neighborhoods

Characteristics and Guidelines of Great Neighborhoods

A neighborhood can be based on a specific plan or the result of a more organic process.

Neighborhoods of different kinds are eligible — downtown, urban, suburban, exurban, town, small village — but should have a definable sense of boundary.

Neighborhoods selected for a Great Neighborhood designation must be at least 10 years old.

Description of the Neighborhood

It is important to identify the geographic, demographic, and social characteristics of the neighborhood. Tell us about its location (i.e. urban, suburban, rural, etc.), density (i.e. dwelling units per acre), or street layout and connectivity; economic, social, and ethnic diversity; and functionality (i.e. residential, commercial, retail, etc.). We also want to know whether a plan or specific planning efforts contributed to or sustained the character of the neighborhood, or if the neighborhood formed more organically and not through a formal planning process.

Neighborhood Form and Composition

How does the neighborhood …

  • Capitalize on building design, scale, architecture, and proportionality to create interesting visual experiences, vistas, or other qualities?
  • Accommodate multiple users and provide access (via walking, bicycling, or public transit) to multiple destinations that serve its residents?
  • Foster social interaction and create a sense of community and neighborliness?
  • Promote security from crime is made safe for children and other users (i.e. traffic calming, other measures)?
  • Use, protect, and enhance the environment and natural features?

Neighborhood Character and Personality

How does the neighborhood …

  • Reflect the community’s local character and set itself apart from other neighborhoods?
  • Retain, interpret, and use local history to help create a sense of place?

Neighborhood Environment and Sustainable Practices

How does the neighborhood …

  • Promote or protect air and water quality, protect groundwater resources, and respond to the growing threat of climate change? What forms of “green infrastructure” are used (e.g., local tree cover mitigating heat gain)?
  • Utilize measures or practices to protect or enhance local biodiversity or the local environment?

Great Neighborhoods – Characteristics and Guidelines for Designation

A neighborhood can be based on a specific plan or the result of a more organic process. Neighborhoods of different kinds are eligible — downtown, urban, suburban, exurban, town, small village — but should have a definable sense of boundary. Neighborhoods selected for a Great Neighborhood designation must be at least 10 years old.

Characteristics of a Great Neighborhood include:

  1. Has a variety of functional attributes that contribute to a resident’s day-to-day living (i.e. residential, commercial, or mixed-uses).
  1. Accommodates multi-modal transportation (i.e. pedestrians, bicyclists, drivers).
  1. Has design and architectural features that are visually interesting.
  1. Encourages human contact and social activities.
  1. Promotes community involvement and maintains a secure environment.
  1. Promotes sustainability and responds to climatic demands.
  1. Has a memorable character.

Description of the Neighborhood

  1. When was the neighborhood first settled?
  1. Where is the neighborhood located: in a downtown, urban area, suburb, exurban area (i.e., on the fringes of a metropolitan area), village, or small town? What is the neighborhood’s approximate density (e.g., in dwelling units per acre, or other)?
  1. What is the neighborhood’s location, its physical extent, and layout?  What are the boundaries of the neighborhood? Are these boundaries formal, defined by an institution or jurisdiction (i.e., wards or other political boundaries, neighborhood associations, other entities) or is the neighborhood defined informally?
  1. How large a geographic area does the neighborhood encompass (number of blocks, acres, or other measurement)?
  1. What is the layout (e.g., grid, curvilinear) of the streets? Is there street connectivity; is it easy to get from one place to another by car, foot, or bike within or beyond the neighborhood without going far out of one’s way?
  1. What is the mix of residential, commercial, retail and other uses?
  1. What activities and facilities support everyday life (e.g., housing, schools, stores, parks, green space, businesses, churches, public or private facilities, common streets, transit, etc.)?
  1. Is there diversity amongst the residents, including economic, social, ethnic, and demographic? Describe the neighborhood’s homogeneity or heterogeneity in those terms.
  1. How has a plan or planning contributed to or sustained the character of the neighborhood? Or did the neighborhood form more organically and not through a formal planning process?

Guidelines for Great Neighborhoods

1.0 Neighborhood Form and Composition

1.1 Does the neighborhood have an easily discernable locale? What are its borders?

1.2 How is the neighborhood fitted to its natural setting and the surrounding environs?

1.3 What is the proximity between different places in the neighborhood? Are these places within walking or biking distances? Does walking or bicycling within the neighborhood serve multiple purposes? Describe (access to transit, parks, public spaces, shopping, schools, etc.). How are pedestrians and bicyclists accommodated (sidewalks, paths or trails, designated bike lanes, share-the-road signage, etc.).

1.4 How does the neighborhood foster social interaction and promote human contact? How is a sense of community and neighborliness created?

1.5 Does the neighborhood promote security from crime, and is it perceived as safe? How are streets made safe for children and other users (e.g., traffic calming, other measures)?

1.6 Is there consistency of scale between buildings (i.e., are buildings proportional to one another)?

2.0 Neighborhood Character and Personality

2.1 What makes the neighborhood stand out? What makes it extraordinary or memorable? What elements, features, and details reflect the community’s local character and set the neighborhood apart from other neighborhoods?

2.2 Does the neighborhood provide interesting visual experiences, vistas, natural features, or other qualities?

2.3 How does the architecture of houses and other buildings create visual interest? Are the houses and buildings designed and scaled for pedestrians?

2.4 How is local history retained, interpreted, and used to help create a sense of place?

2.5 How has the neighborhood adapted to change? Include specific examples.

3.0  Neighborhood Environment and Sustainable Practices

3.1 How does the neighborhood respond to the growing threat of climate change? (e.g., local tree cover mitigating heat gain)?

3.2 How does the neighborhood promote or protect air and water quality, protect groundwater resources if present, and minimize or manage stormwater runoff? Is there any form of “green infrastructure”?

3.3 What measures or practices exist to protect or enhance local biodiversity or the local environment?