Monday, February 24, 2014

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

FourSquare activity - actual size

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:

Pushpin history

Or a Heat map of my travels to Seattle

Heat Map

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 KML 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 ( 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) Create / Fusion Table
  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 Choose File
  4. On the import new table row, I just click Next again and leave the columns as is Column Headers
  5. On the final page, I give it a good friendly Table Name, and click Finish Import 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:

Map of Geometry

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!


Sean Daniel said...

What is weird, is just about a week later, I tried again and now the Fusion table is taking all of my check-ins. So you might not need to break it down by year. It is "experimental" so maybe I just got lucky?

Unknown said...

Great tutorial Sean. Do you know how to add numbers and lines, tracking a trip across the United States would be the goal.


Sean Daniel said...

Glad you enjoyed it Luiz, thanks for all the tweets as well. I'm not sure how to add lines. something to explore for sure. based on time, that would be pretty cool. Might have to look into that one.

Unknown said...

Sean, I've been uploading it to MapBox and creating the lines manually.

Would be so swell to have it automated. I need to download kml data and reupload/customize every time I have new data.

Still comes out very clean and the best solution I've found yet. -

Matt Peloquin said...

Thank you so much for the tutorial. I was quite sad when Google Maps ended the convenient setup of being able to paste the KML URL into the map "search" form to instantly create a map.

I used your walk through and was able to successfully create a map.

I did have a question though:

Using the Fusion Tables, I did the KML file upload to then populate the map. But my question is:

Is it possible to create a Google Spreadsheet where there is an "ImportXML" formula that populates a spreadsheet automatically based on the Foursquare KML URL?

I ask because I've realized that the map will ONLY be updated manually when the KML file is uploaded to a new Fusion table, and then having the copy copy/pasted onto my own site.

But if the data source for the Fusion Table came from a Google spreadsheet that was auto-populating via an "ImportXML" formula as being used, the data would be updated on the map automatically.

I did a test of the formula in a Google spreadsheet and it does automatically update with the most recent foursquare data. But I am not skilled enough to format the sheet to work with a Fusion table.

Any thoughts?

Sean Daniel said...

I have the same problem, haven't found a work around yet. Moor automation!!

O n T Schrock said...

This is awesome and worked seamlessly for me! Thanks!

Ingrid said...

Thanks a lot for this writeup, in 2016 this still works :-) Just managed to make a map with all my Swarm checkins for 2016.