Posts Tagged ‘ Beer ’

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.

Continue reading

How many Hoosiers live with 5 miles of an Indiana brewery?

2,539,798.  Approximately.  I think.  Maybe.  Here’s what I did to get there.

First, the Census department has put together some nice pre-digested block layers, they include the total population for the blocks so you don’t have to compile that data yourself.

Second, create a 5 mile (acutally I used 8046.72 meters because my data in a UTM projection) buffer around my brewery points.  Shp2pgsql, use it to shove the buffers into a PostGIS database and call the layer buffer_5mile.  Fun!

Third, load the block data into QGIS and add a numeric field (I used totarea) to store the area of the blocks.  Use shp2pgsql to reproject the census data to match your buffers and load the results into PostGIS.  I called my layer block_pop.

Fourth, update that new area field with

UPDATE block_pop SET totarea = st_area(geom);

Fifth, to get an estimate of the population within the buffers use:

SELECT 
round(sum(c.pop10*(ST_Area(ST_Intersection(c.geom, b.the_geom))) / c.totarea)) 
FROM
(SELECT ST_Union(geom) as the_geom FROM buffer_5mile) as b, block_pop c
WHERE ST_Intersects(the_geom, c.geom);

Bam.  You done.

Updated Indiana Breweries Map

A few months ago I followed Darren Cope’s Geography of Tim Horton’s blog post to create a similar map that showed breweries in Indiana.  A new brewery opened up down south so I decided to update the map.

To get the location I looked up the location on Google Maps then used the QGIS plugin OpenLayers to find the brewery on Google Maps’ Hybrid Map.  I added the point to my breweries PostGIS layer and re-imported the layer into GRASS along with a shapefile of the state.  From there I used v.to.rast.constant to convert those layers to rasters.

With the vectors converted to rasters I used r.mask to use the Indiana raster as a mask for the next step which is to use r.grow.distance to create the final map.  The result was exported with r.out.png and brought into GIMP to make the no data areas transparent.

To create the transparencies use Layer -> Transparency -> Add Alpha Channel then use the Select Region by Color tool to select the no data areas, then press the delete key to zap them away.  The image below is the result.

 

Location of breweries in Indiana

Indiana breweries

How far away are you from beer on a Sunday in Indiana?

Answer:  At most 50 miles.

Inspired by GIS blogger Darren Cope’s analysis of Tim Horton’s locations in Canada I decided to figure out how far you are at any given time from a microbrewery in Indiana.   Instead of Canadian coffee and donuts I mapped brewery locations.  I pulled the Brewer’s Guild of Indiana’s membership list (and took out Bee Creek, I’m pretty sure they’re closed).  This is a vital map because Indiana’s blue laws prohibit alcohol sales on Sundays except at microbreweries and vineyards.   Two things I don’t like on Sundays, wine and crossing state boundaries so this was a vital project for me.  I found that I really don’t want to be in northeast Dubois County on a Sunday. Seriously, 50 miles.  Dang.  At least people in Vincennes and Terre Haute can jump over to Illinois for a 6 pack.

Distances to Indiana breweries

I followed Cope’s steps except to get the lat / long for the breweries I had to geocode them against the TIGER street centerlines and instead of using GRASS directly I used QGIS’s GRASS plugin.  This article was a big help on getting a GRASS workspace set up to create the raster.