Beer mapping done better

I’ve long wished for a better tool than beermapping.com for planning visits to new cities and making sure to hit a couple of the best bars, restaurants, and stores for beer. Of course, it doesn’t care about making any actual recommendations, just listing *everything*:

For recommendations that are usable for planning a trip, I’ve always used the BeerAdvocate city directories, which (if you pay close attention), list the bars, stores, etc in order of ratings. But to properly plan a trip, you need a map, and these guides fall far short.

As a first step towards fixing this, I used a series of tools to make transforming a city’s BeerAdvocate guide into a map less painful. Maybe I’m biased from working on it recently, but I put the final map into Google MapsEngine. Here’s my final beer map for Chicago:

Detail shot:

The map has 2 groups – blue markers for bars/eateries (my biggest wish would be for BeerAdvocate to separate those…) and green markers for bottle stores. Darker icons have higher scores.

Here’s the semi-manual process I used to generate this map in less than a half hour:

1. Open the BeerAdvocate city guide you want, in my case, Chicago.
2. Install the Kimono bookmarklet, which will give you a tool to automate the conversion of lists on websites into data feeds (most importantly here, CSV format).
3. After going through the Kimono tutorial, you should be able to make something like my API – both name and address for the 2 different sections (bars/eateries and bottle stores).
   
   (one caveat: I had to go into the advanced mode here and tweak the regular expressions to ignore the ” – ” prefix that the addresses have by default.)
4. Save this API to Kimono, then grab the CSV export, it should look something like mine.
5. Copy and paste the 2 different sections into 2 different CSV files on your computer, then open them as 2 spreadsheets in a tool that will let you fetch content from the web. I used Google Spreadsheets because I’ve used its ImportXml function before.
6. Taking the bottle stores list for example, this is the Google Spreadsheet that I made. Note that I added a new column “Score” that I populated with:

   =ImportXML(A2, "//span[@class='BAscore_big']") 

7. If you want to only map places with scores above a certain value, you can delete those rows now.
8. Then you can import your spreadsheet into a new Google MapsEngine map. I styled mine by Score, using ranges to automatically assign darker colors to higher numbers. Beware: “100″ sorts below “90″ because it uses alphabetic sorting – I changed my 100s into 99s in the built-in data editor to work around this. As a final note, there were some weird artifacts from the import like duplicate incomplete rows and [#] annotations, but hopefully the MapsEngine folks will fix those – they’re good people.

This is still a pretty manual process, and I might come back to this in the future and use python or javascript/node to build an all-in-one tool that anyone can go to in order to request a map for a city (and I know I’m not the only one thinking about this), but this scratches my itch in the short term. Maybe BeerAdvocate will decide to launch an API, or RateBeer will re-enable theirs. Who knows?

Hopefully this will help some other beer adventurers for now, also.