Ranking the Indiana Breweries by Population

In my last post I used PostGIS to calculate the number of Hoosiers that live within 5 miles of an Indiana microbrewery.  Now I want to rank each brewery by population.  The total is going to be a little more than the 2.5 million-ish I came up in the last post due to the lucky duckies who live with 5 miles of more than one brewery.

Flat 12 of Indianapolis, with over 243,000 people within 5 miles of their location is our winner.  No big surprise, but the top 11, and 12 of the top 13 are all Indy breweries.

A few breweries, most notably Three Floyds in Munster are lower than they should be because I only used Indiana census data.  Three Floyds is only a half mile from the Indiana / Illinois border.  If I included Illinois data the nearby Chicago population would have pushed them up the list.

The full list and the SQL used to create it are below the jump.

Rank

Brewery Name

Address

Approx. Population

1 Flat12 Bierwerks 414 N. Dorman St., Indianapolis, IN 46202 243,637
2 Sun King Brewing Company 135 N. College Ave. Indianapolis, IN 46202 237,615
3 Rock Bottom Restaurant and Brewery – Downtown Indy 10 West Washington St. Indianapolis, IN 46204 232,710
4 RAM – Indianapolis 140 South Illinois Street Indianapolis, IN 46225 231,048
5 Black Acre Brewing Co. 5632 E. Washington Street Indianapolis, IN 46219 224,575
6 Upland Brewing Company – TAPROOM INDY 4842 N. College Ave, Indianapolis, IN 46205 223,779
7 Fountain Square Brewery 1301 Barth Ave, Indianapolis, IN 46203 223,193
8 Three Wise Men Brewery 1021 Broad Ripple Ave, Indianapolis, IN 46220 207,280
9 Brugge Brasserie 1011 E. Westfield Blvd Indianapolis, IN 46220 205,849
10 Broad Ripple Brewpub 842 E. 65th St. Indianapolis, IN 46220 202,090
11 Bier Brewery 5133 East 65th Street, Indianapolis, IN 46220 186,577
12 Granite City Food & Brewery 3809 Coldwater Rd Fort Wayne, IN 46805 172,589
13 Triton Brewing Company 5764 Wheeler Road, Indianapolis, IN 46216 171,743
14 Four Horsemen Brewing Co. 710 Fellows St. South Bend, IN 46601 170,287
15 Mad Anthony Brewing Company 2002 Broadway Fort Wayne, IN 46802 159,205
16 Granite City Food & Brewery 150 W 96th St Indianapolis, IN 46290 157,295
17 BJ’s Restaurant & Brewhouse 1251 US 31 N Greenwood , IN 46142 143,292
18 Lafayette Brewing Company 622 Main St. Lafayette, IN 47901 138,677
19 Rock Bottom Restaurant and Brewery – College Park 2801 Lake Circle Drive Indianapolis, IN 46260 138,578
20 Granite City Food & Brewery 6501 Grape Road Mishawaka, IN 46545 136,428
21 RAM – Fishers 12750 Parkside Drive Fishers, IN 46038 134,446
22 People’s Brewing Company 2006 N. 9th St. Road Lafayette, IN 47904 132,954
23 Oaken Barrel Brewing Co 50 North Airport Parkway Suite L Greenwood, IN 46143 132,929
24 Main Street Brewery at Turoni’s Pizza 408 N. Main St. Evansville. IN 47711 130,967
25 Three Floyds Brewing Company 9750 Indiana Parkway Munster, Indiana 46321 129,115
26 Tin Man Brewing 1430 W. Franklin St, Evansville, IN 47710 116,914
27 Upland Brewing Company 350 W. 11th Street Bloomington, IN 47404 106,808
28 Bloomington Brewing Company 1795 E. 10th St. Bloomington, IN 47408 102,247
29 Cutter’s Brewing Company 1927 S. Curry Pike, Unit #1, Bloomington, IN 47403 95,893
30 New Albanian Brewing Company – Public House 3312 Plaza Drive New Albany, IN 47150 88,634
31 Barley Island – Noblesville 639 Conner Street, Noblesville IN 83,990
32 Crown Brewing Co 211 S. East St. Crown Point, IN 46307 74,243
33 New Albanian Brewing Company – Bank Street Brewhouse 415 Bank Street. New Albany, IN 73,072
34 Half Moon Restaurant & Brewery 4051 S. Lafountain Kokomo, IN 46902 62,618
35 Figure 8 Brewing 1555 W. Lincolnway, #105 Valparaiso, IN 46385 58,737
36 Power House Brewing Co – At the Columbus Bar 322 4th St. Columbus, IN 47201 50,596
37 New Boswell Brewing Company 923 N E Street Richmond, IN 47374 45,010
38 Shoreline Brewery & Restaurant 208 Wabash Michigan City, IN 46360 38,998
39 Lake City Tap House 113 E. Center St. Warsaw, IN 46992 33,869
40 Back Road Brewery 308 Perry St. LaPorte, IN 46350 33,072
41 Mad Anthony Brewing Company TAPROOM 114 N. Main St. Auburn, IN 46706 25,241
42 Great Crescent Brewery 315 Importing St. Aurora, IN 47001 16,608
43 Wilbur Brewhaus 4130 Forest Lake Rd Martinsville, IN 46151 11,363
44 Lil’ Charlie’s Restaurant and Brewery 504 E. Pearl St. Batesville, IN 47006 11,278
45 Hawcreek Brewing Company 11420 N State Road 9 Hope, IN 47246 5,171
46 Big Woods Brewing Company 60 Molly Lane, Nashville, IN 47448 4,475

To start I created a table to hold the area of a census block under a buffer, the total area of the census block, the block’s total population, and the associated brewery’s ID number.  I used this query to populate the table:

INSERT INTO brew_pop (buffer_area, totarea, pop10, brewery_id)
SELECT ST_AREA(ST_Intersection(c.geom, b.geom)), c.totarea, c.pop10, b.id FROM
block_pop c, buffer_5mile b
WHERE ST_Intersects(c.geom, b.geom);

Now to calculate the populations I used:

SELECT rank() OVER (ORDER BY x.approx_pop DESC) As "Rank", x.brewery_id, x.name As "Brewery Name", 
 x.address As "Address", x.approx_pop As "Approx. Population"
FROM (
SELECT a.brewery_id, b.name, b.addy As address, 
 round(sum(a.pop10*(a.buffer_area / a.totarea))) As approx_pop
FROM brew_pop a, brewery b
WHERE a.brewery_id = b.id
GROUP BY a.brewery_id, b.name, b.addy) x;

Rank() is a Postgres window function, window functions are cool.  Check out that tutorial I just linked for a sample of their goodness.

  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

%d bloggers like this: