How to map your FourSquare data

I use FourSquare, not for the mayor-ships, or the points (although they are fun) but as a bit of history in my travels.  It’s easier to check in somewhere, then write it down the location and map it later.  My goal has always been to get some form of map with push pins in it.  Sure FourSquare provides a bit of history, but the map is SUPER tiny, and very hard to use

I’ve tried a bunch of solutions, usually provided by LifeHacker (one of my favourite websites), and they’ve all been just ok.  I think I’ve finally found a solution, that based on my check-in frequency, works for me.  LifeHacker posted a link to Billy Burle’s blog (follow BillyBurle here).  Billy had a blog post on How to Create a Heat Map of your FourSquare check-ins using Google Fusion Tables.  It’s quite frankly, awesome, but needed some adjustments.  I can get push-pins, or heat maps with this approach!  For example, here is my push-pin history for 2013:

Or a Heat map of my travels to Seattle

Are you a map geek and want to do this?  So here is how…

Step 1: Obtain your check-in history from FourSquare

First you need to get your FourSquare check in history.  For this I browsed to my FourSquare Feeds, and got the URL for my feed.  It has a unique character set in it, because that’s your private feed.

Clicking the link will give you the last 50 or so check-ins.  what I did was right click on the link and say “Copy” url, pasted it into the address bar, then at the end of the URL I added the text “?count=1000” to get the last 1000 items (1000 is a key number in a lower step).  This will download a .kml file with the file name patching that crazy string character set in the URL.  I renamed mine to “Foursquare Data 2013”, just to make it easier to find.  Since I stored the .kml files for use if a better solution comes up down the road.

Step 2: Prepare the KML file to have only the years data

It’s pretty unlikely that you checked in exactly 1000 times in a given year, but in Step 1 we downloaded exactly 1000 historical check-ins.  So let’s make that exactly the year.  What I did (and I’m trying to figure out how to automated some of this) is to open the .kml file I downloaded in Notepad (I also turned on “word wrap”, since the file is one line, that’s 1000s of characters long).  I searched for “31 Dec” (or “30 Dec” or the last day you checked in on the previous year).  Then I scanned backwards in the file from that last check-in from the previous year until I found the “</Placemark><Placemark>”.  This is the end of the first check in of the current year, and the start of the last check in of the previous year.  I put my cursor between “><” characters and scrolled to the bottom of the document.  I then held shift and clicked between the “><” of the “</Placemark></Folder>”.  I then hit delete.  The end of the file should still look like “</Placemark></Folder></kml>”, but now everthing from the beginning of the document to the end of the document is exactly the current year you wish to display. 

The reason you need to do this, is because the Fusion Tables (at least as far as I can tell) have a hard coded limit of 1000 objects in them. So by limiting it to the year, I noticed in 2013, I had 783 check-ins, which is well under the 1000 limit.  But if I included 2010-2014 (my entire life of FourSquare check ins (obtained by adding to the URL: ?Count=10000”), I had 1723 check ins, and I was missing a bunch of data, hence my decision to break it up by year.  You may have to break it up further if you’re a FourSquare fanatic.

Step 3: Prepare Google Drive if you haven’t already

This was my first time using Google Drive, and as such, this is a one-time step I did for each year I imported.  I have OneDrive, where most of my online data is stored and I have a Home Server where my local data is stored, so I haven’t needed it. So I logged into my Google Drive account, and needed to add Fusion tables.  I clicked the down arrow on the settings gear ( ) and chose Manage Apps, then clicked on Connect more apps in the pop-up window.  In the search box I typed “fusion table”, and it was the only one I could install.  I installed it.  At the time of writing it is an experimental app…. which I figure is just typical of Google.  Is anything ever really out of Beta with them? ;o)

Step 4: Importing the data into a Fusion Table

This is where Billy Burle’s blog post really shines (I had to figure out all the above), and even helped in the comment section of Billy’s post with the data.  So here is the fun part

1. Click on the Create Button
2. Choose to create a new Fusion Table (experimental)
3. Choose to upload a file from your computer by clicking Choose File and select the file that you created and edited in step 1 and 2, then click Next
4. On the import new table row, I just click Next again and leave the columns as is
5. On the final page, I give it a good friendly Table Name, and click Finish

That’s it, now you have a new Fusion table.  Ready to see your check-in history?  Check out the Map of Geometry page, and now you can play around with the map, and choose the Feature Map (default) which is the push-pin view, or the Heatmap view:

Last but not least, if you ever want to revisit your data, just hit up your Google Drive and select the fusion table and it’s all saved there for you for that year.  So you can quickly pull it up at a map-tastic party.  What’s also awesome, is if you have a Windows 8.1 tablet (Like a Surface 2) then you can also just get the Windows Google Drive Client, and show it there instantly.  Works like it does on the web!

Enjoy and have fun with your data!