iPhone location log to Google Earth, all with shell commands
If you're a privacy geek, then you've no doubt heard people talking about a file on the iPhone called consolidated.db. This file stores all the places your phone has been.
I don't know why everybody is making such a fuss about it. I expect my own computers (including my phone) to store logs of what I'm doing. It's good to be able to reset this information before I give a device to somebody else, and hey, the iPhone has a comprehensive reset function!
I think the concern is that the phone is sending this to Apple periodically. That would be a very serious concern. But until somebody gives any sort of evidence to that effect, I think it's just a badly-managed log file.
Anyhow.
Yesterday, I saw that I had apparently bought a song on my iPhone that I didn't recognize. I listened to it, and it didn't sound familiar. It also didn't sound like something I'd buy. After a couple of blind alleys, I checked the purchase date in iTunes. I then correlated that to Google Latitude's history. Ah, it was a mall in Santa Cruz. Now I remembered: the reason I went into the mall was to get a lemonade at Starbucks, and while I was there my friend gave me one of the vouchers that Starbucks has for a free iTunes song.
While I was relaying this story to another friend, he asked if I'd used consolidated.db to read it. Nope, but not a bad idea. I started wondering what was actually in there. Most stuff I saw to read that requires me to download some stranger's code, and I wasn't too thrilled about that.
OS X and iOS tend to use property lists and SQLite for all kinds of storage, so I'd guessed it was SQLite. That meant that it could be read from the command line using the sqlite3 utility, which is part of OS X.
So, without further ado, here's a quick rundown on how to look at all this stuff straight from the command line. There's one bit of Perl to convert it to Google Earth's KML format, but the Perl really isn't necessary to view the data in lat/long format. Note that making use of the following requires a certain degree of technical savvy; knowing SQL is going to be very useful to fiddle with the data. However, you don't need to know anything about OS X (except how to start the terminal), iPhone development, etc.
# Get the current time in the format used by the database (Apple's CFAbsoluteTime, which is the number of seconds since Jan 1, 2001 at midnight GMT; we use UTC instead of GMT here, but it's close enough for these purposes):
$ perl -MTime::Local -e 'print time() + timegm(0,0,0,1,0,70) - timegm(0,0,0,1,0,101), "\n"'
325464777
# Go to the backups
$ cd ~/Library/Application\ Support/MobileSync/Backup/
# Go to the most recent backup
$ cd $( ls -t | head -1 )
# The files in here are named (almost) randomly.
# Get the database's filename by looking at table lists of all of the SQLite databases and finding one that has CellLocation
# (This prints the filename and leaves it in $db)
$ for db in $( file * | sed -e '/SQLite/ !d' -e 's/:.*//' ) ; do sqlite3 $db .tables | grep -q CellLocation && echo $db && break ; done
4096c9ec676f2847dc283405900e284a7c815836
# Your filename will be different; it's based on a hash of the data.
# View the file
$ sqlite3 $db
-- Take a look at the schema of CellLocation
sqlite> schema CellLocation
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));
-- (That will also print a few other tables that you can ignore.)
-- View the contents of the past week (this uses the time computed at the beginning of this walkthrough; 604800 is the number of seconds in a typical week)
sqlite> select latitude,longitude,timestamp from CellLocation where timestamp > 325464777 - 604800 order by timestamp;
-- Back to the shell
sqlite> .exit
# Put the data through Perl to convert to KML
$ sqlite3 $db "select longitude,latitude,timestamp from CellLocation where timestamp > 325464777 - 1209600 order by timestamp, horizontalAccuracy" | perl -nw -MPOSIX -MTime::Local -e 'BEGIN { our $LTS=0; print "\n";} next if /^[0.]+\|[0.]+\|/; my ($long, $lat, $tstamp) = split /\|/; next if $tstamp==$LTS; $LTS=$tstamp; my @time = gmtime($tstamp - timegm(0,0,0,1,0,70) + timegm(0,0,0,1,0,101)); my $tstr = strftime("%Y-%m-%dT%H:%M:%SZ", @time); print "$tstr $long $lat 0 \n"; END { print "
# Open it in Google Earth
$ open ~/Desktop/locations.kml
If you're trying to make sense of the Perl (particularly the $LTS bit), note that each timestamp tends to have several locations with varying precisions. I'm not sure whether that's a triangulation of the user's position, or of the cells the user was able to communicating with, but I suspect it's increasingly precise triangulations of the position that are recorded with the same timestamp. This code will use the first recorded position for each timestamp, which (because of the ORDER BY clause) is going to be the one with the smallest horizontalAccuracy. I'm currently supposing that the horizontalAccuracy column is actually the precision radius.
I also played with using the WifiLocation table (which is similar), but it seems to be pretty unreliable data; it seems to include data based on one database that's about 10 miles too far north. (At least, in my case, it kept showing me jumping abruptly between Santa Clara / Sunnyvale and Fremont. I haven't been on the Fremont side of the bay in over a month.) I read online that Verizon customers need to use CdmaCellLocation instead of CellLocation.
I don't know why everybody is making such a fuss about it. I expect my own computers (including my phone) to store logs of what I'm doing. It's good to be able to reset this information before I give a device to somebody else, and hey, the iPhone has a comprehensive reset function!
I think the concern is that the phone is sending this to Apple periodically. That would be a very serious concern. But until somebody gives any sort of evidence to that effect, I think it's just a badly-managed log file.
Anyhow.
Yesterday, I saw that I had apparently bought a song on my iPhone that I didn't recognize. I listened to it, and it didn't sound familiar. It also didn't sound like something I'd buy. After a couple of blind alleys, I checked the purchase date in iTunes. I then correlated that to Google Latitude's history. Ah, it was a mall in Santa Cruz. Now I remembered: the reason I went into the mall was to get a lemonade at Starbucks, and while I was there my friend gave me one of the vouchers that Starbucks has for a free iTunes song.
While I was relaying this story to another friend, he asked if I'd used consolidated.db to read it. Nope, but not a bad idea. I started wondering what was actually in there. Most stuff I saw to read that requires me to download some stranger's code, and I wasn't too thrilled about that.
OS X and iOS tend to use property lists and SQLite for all kinds of storage, so I'd guessed it was SQLite. That meant that it could be read from the command line using the sqlite3 utility, which is part of OS X.
So, without further ado, here's a quick rundown on how to look at all this stuff straight from the command line. There's one bit of Perl to convert it to Google Earth's KML format, but the Perl really isn't necessary to view the data in lat/long format. Note that making use of the following requires a certain degree of technical savvy; knowing SQL is going to be very useful to fiddle with the data. However, you don't need to know anything about OS X (except how to start the terminal), iPhone development, etc.
# Get the current time in the format used by the database (Apple's CFAbsoluteTime, which is the number of seconds since Jan 1, 2001 at midnight GMT; we use UTC instead of GMT here, but it's close enough for these purposes):
$ perl -MTime::Local -e 'print time() + timegm(0,0,0,1,0,70) - timegm(0,0,0,1,0,101), "\n"'
325464777
# Go to the backups
$ cd ~/Library/Application\ Support/MobileSync/Backup/
# Go to the most recent backup
$ cd $( ls -t | head -1 )
# The files in here are named (almost) randomly.
# Get the database's filename by looking at table lists of all of the SQLite databases and finding one that has CellLocation
# (This prints the filename and leaves it in $db)
$ for db in $( file * | sed -e '/SQLite/ !d' -e 's/:.*//' ) ; do sqlite3 $db .tables | grep -q CellLocation && echo $db && break ; done
4096c9ec676f2847dc283405900e284a7c815836
# Your filename will be different; it's based on a hash of the data.
# View the file
$ sqlite3 $db
-- Take a look at the schema of CellLocation
sqlite> schema CellLocation
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));
-- (That will also print a few other tables that you can ignore.)
-- View the contents of the past week (this uses the time computed at the beginning of this walkthrough; 604800 is the number of seconds in a typical week)
sqlite> select latitude,longitude,timestamp from CellLocation where timestamp > 325464777 - 604800 order by timestamp;
-- Back to the shell
sqlite> .exit
# Put the data through Perl to convert to KML
$ sqlite3 $db "select longitude,latitude,timestamp from CellLocation where timestamp > 325464777 - 1209600 order by timestamp, horizontalAccuracy" | perl -nw -MPOSIX -MTime::Local -e 'BEGIN { our $LTS=0; print "
# Open it in Google Earth
$ open ~/Desktop/locations.kml
If you're trying to make sense of the Perl (particularly the $LTS bit), note that each timestamp tends to have several locations with varying precisions. I'm not sure whether that's a triangulation of the user's position, or of the cells the user was able to communicating with, but I suspect it's increasingly precise triangulations of the position that are recorded with the same timestamp. This code will use the first recorded position for each timestamp, which (because of the ORDER BY clause) is going to be the one with the smallest horizontalAccuracy. I'm currently supposing that the horizontalAccuracy column is actually the precision radius.
I also played with using the WifiLocation table (which is similar), but it seems to be pretty unreliable data; it seems to include data based on one database that's about 10 miles too far north. (At least, in my case, it kept showing me jumping abruptly between Santa Clara / Sunnyvale and Fremont. I haven't been on the Fremont side of the bay in over a month.) I read online that Verizon customers need to use CdmaCellLocation instead of CellLocation.
If you're a potential employer, please note that the Perl above was thrown together for my own experimenting. I just put it here on the blog in case somebody else wanted to fiddle with it, and it's not at all what I'd put into production.
Well, that's about all the time I felt like putting into decoding that file. Feel free to post improvements in the comments section below. Share and enjoy!
(Disclaimer: I release the above code to the public domain, if you care. It's for education, and is not intended to run in any particular environment. I provide no warranty, expressed or implied; if it breaks, you get to keep both pieces.)
Comments