Saturday, April 30, 2011

Visualization of my iPhone tracking data

Last week, the internets were flooded with panic about the iPhone storing location data in a sqlite DB.  The DB (called consolidated.db) contains longitude, latitude, altitude, accuracy, and timestamp information for near by wifi hotspots and GPS locations (when maps apps are used).  You can take a look at the data stored on you by your iPhone by using the iPhonetracker app (for Mac OSX) or if you've got a Windows machine you can find the conslidated.db file and load it into a database software.
I used the program to visualize the data on my recently purchased iPhone 4 (I had a 3G until March, so I've only got 1 month of data to visualize--which in a way makes it easier to see how much tracking is really going on since I can easily recall where I've traveled in the past month).  Here is the graph that iPhonetracker shows:

The advantage of this tracker app is that it visualizes the movement over time.  However, it's difficult to see the locations on the map and since it's using a heat map to display the frequency that each location was in my DB -- that is, it's hard to see some of the smaller dots on the maps.  As you can see, I spent most of my time since March in College Station and Waco, TX.  The dark purple spot in CS is near my building on campus.  
There are a couple of reasons that, after looking at this data, I am not too concerned about the tracking capability.  
First, when examining the timestamp data and my location, it is off by a few hours and even a full day in several cases -- such as the days I traveled to Waco. 
Also, notice that the tracking did not pickup any locations during the drive to Waco (I guess that's because I didn't use it for GPS directions and there probably aren't many wifi locations between College Station and Waco).  
Finally, the tracking it not very precise. There are lots of small dots on the map that show locations I've never been, including the dots far outside of Waco near Gatesville and Hico.  Also, when I zoom in on the map and examine the records in the DB, it puts in lots of places in the College Station area that I haven't traveled to in the last month. My only guess is that the blips are due to wifi hotspots or cell towers that my cell phone connected to but have either 1) a really long range or 2) somehow have inaccurate longitude/latitude entries.
  If you don't want to use or you don't have a Mac, you can extract the data yourself by first finding the "consolidated.db" file in your iPhone backups maintained by iTunes (assuming they are unencrypted or you have unencrypted them). An easy way to find this file is to run this python script with the command:
"~//" | grep "consolidated"
this will tell you the name of the file containing your tracking DB.  Open this file in your favorite sqlite software.  In Mac OSX you can open/import this from terminal with the commands:
>sqlite3 "yourfilenamehere"
>CREATE TABLE CellLocation (MCC INTEGER, MNC INTEGER, LAC INTEGER, CI INTEGER, Timestamp FLOAT, Latitude FLOAT, Longitude FLOAT, HorizontalAccuracy FLOAT, Altitude FLOAT, VerticalAccuracy FLOAT, Speed FLOAT, Course FLOAT, Confidence INTEGER, PRIMARY KEY (MCC, MNC, LAC, CI));
then export the table to csv or whatever format you'd like.  Next, I uploaded this data to google maps and created the map below:

This google map shows the erroneous entries points more clearly.  Thinking that this might have to do with the "horizontal accuracy" column in the DB, I examined the raw data for these erroneous locations, but "horizontal accuracy" was about the same as all the entries, so it wasn't an accuracy problem.  Regardless, I'm not too worried about Apple or someone else using this data for no-good.

Update is looking for donated iPhone tracking data so that they can make visualizations like this one.  They'd better hurry and gather the data they need before the next iOS update that makes this tracking file more difficult to locate/access.


  1. And you don't have to actually use their meter to create an account and use the free iPhone app. thanks

  2. Thanks for sharing the wonder article, SAS is the recent technology, you must learn this valuable course to get future in IT firm.
    SAS Training in Chennai|SAS Courses in Chennai