Consolidate my posterior...

While I was asleep...

As I'm sure many of you are aware of by now, Apple iOS 4.x contains a database file named consolidated.db, in which your every move (or at the very least, the movements of your device) are recorded. This, according to conspiracy buffs and privacy advocates, is done to make life easier for Gil Grissom or whoever your local CSI representative is. As an international black market arms dealer security professional, I've been curious about how useful the collected data really is, especially since a lot of the comments on the subject claims that the coordinates and time stamps are wildly inaccurate. So I decided to figure this out for myself, and proceeded to crank up Google Earth...

Google Earth has this neat feature that lets you import (and export) data via an XML-based file format named KML, so I've wrote a perl script that reads the consolidated.db file and outputs a KML file. The script can be found here. What you need to run the script is perl (obviously), and the DBI and DBD::SQLite modules (the database is in sqlite3 format).

Now, the procedure for extracting the consolidated.db file from backups have been described elsewhere, so I won't get into that. For this exercise, I'll just assume you've figured out that part already.

When everything is ready, simply run the script with the database file as the single argument:

perl xonsolidated.pl consolidated.db

If everything went well, there should be no output other than a new file with the .kml extension tacked onto the end of the original database file name (consolidated.db becomes consolidated.db.kml). This file can be loaded into Google Earth, and the result should be a big list of timestamps (one point and one path for each time stamp, and each pair with a different color).

If it didn't run properly... Well, happy debugging.

So, what did the Google Earth representation of my location data show me?

Well, it certainly didn't show a complete record of all my movements. In fact, before I separated each time segment into different colors, it looked a lot like a big plate of spaghetti. It seems that the location data is only dumped to the database every now and then (sometimes several times a day, sometimes as rare as every fortnight), all the data points are given same time stamp.  From what I can tell, the coordinates corresponds more closely with which cell tower my cell phone was talking to than any actual phone location.

One of the more recent time stamps (see the picture up top) indicated my presence at dozens of locations with several square kilometers around my apartment, at a time that I'm quite certain I was sound asleep in my bed.

Another thing I found is that the collected can be vastly incomplete at times. Case in point; I spent a week in Angola last November, more precisely in the central district of the capital Luanda (which has surprisingly good cell phone coverage). The database contains tracking information all the way down to Frankfurt airport (in Germany), but after that, there's a 10 day gap where absolutely nothing is recorded. The next tracking dump occurs after I had been back home for several days.

So, are these data useful for anything at all?

It should be quite clear that it's not a reliable source of evidence; The data is written at way too irregular intervals, the coordinates are all over the place, and it is likely that vast chunks of data are missing (like my trip to Angola). In all likelihood, the cellular network providers already have much more accurate data on your device's movements.

So if it's not useful for auditing purposes, why does your device record these data at all? To be honest; I have no idea. Hopefully, someone who cares a lot more than me about these things, will figure it out, or Apple will decide to be a bit more open about what goes on under the hood of their devices (yeah, that's gonna happen).