The iPhone "CellLocation" table, 6/23/10 to 6/25/10
[UPDATE: Check out the latest installment, “iPhone/SpyPhone?—The Music Video!“, with a soundtrack by David Byrne. You can also download a higher-res version of the video, my code changes for Pete Warden’s iPhoneTracker, a prebuilt version of iPhoneTracker like the one I used to make the video, and a complete copy of The Wired CD, with a bunch of great, Creative Commons-licensed tunes from David Byrne, Spoon, Gilberto Gil, the Beastie Boys and many others for you to rip, reuse and remix! Read this post now, or weep tears the size of October cabbages later!]
So, I went through the exercise of pulling an old consolidated.db table out of a backup—this seems to be the very first one from my iPhone 4. What you see above is (most of) the data plotted onto Google maps—there are a couple of outliers to the south that fell off the bottom, but they’re included in the detail maps below. This represents 3 days worth of data, which I’m nominally referring to as 6/23/10 through 6/25/10 (I’m not sure about these time stamps; more on this in a bit).
Altogether, there are 302 data points. On the map above, all the locations collected the first day are yellow, the second day’s are purple, and the third day’s are cyan.
I’ve provided a number of detail maps below, one corresponding to each unique timestamp in the table. In two cases, the locations with a given time covered an extremely wide area; in both instances, I’ve provided a detail map to show the main cluster of locations in better detail. The locations were apparently collected, or added, or something, in several batches, all with exactly the same time stamp, as follows:
6/23/10 03:55—8 locations
Eighteen hours, thirty minutes later…
6/23/10 22:25—21 locations
Sixteen hours, twenty minutes later…
6/24/10 14:45—15 locations
Eight minutes later….
6/24/10 14:53—21 locations
Seven minutes later…
6/24/10 15:00—42 locations
Five minutes later….
6/24/10 15:05—19 locations
One minute later…
6/24/10 15:06—24 locations
Nine minutes later…
6/24/10 15:15—43 locations
Five minutes later…
6/24/10 15:20—7 locations
Eight hours, fifty-five minutes later…
6/25/10 00:15—21 locations
Four hours, thirty-seven minutes later…
6/25/10 04:52—19 locations
One minute later…
6/25/10 04:53—21 locations
Four minutes later…
6/25/10 04:57—41 locations
Now, about the timestamps. I’ve used the same conversion that Peter Warden uses in his iPhoneTracker application, however, the first set of dates comes out as June 23rd. The iPhone 4 was released June 24th, and I picked mine up that evening, so it seems that something is amiss here.
The way the locations are clustered by time stamp seem bizarre, at least if they’re meant to indicate the time the entry was created. No multiday gaps in here, but multi-hour ones and an extremely wide variance in both the number of locations with a given time stamp and the geographical range encompassing those locations. In particular, the locations with the time stamp of 15:06 on the 24th and 04:57 on the 25th cover a great deal of ground. Why locations would be added in this fashion, and why those locations at any given moment is a mystery.
The fact that anywhere from 7 to 43 locations have the same time stamp, that those locations can demonstrably be spread over an area of ninety or a hundred miles (as in the case of the latest-dated set of locations) and the wide variance in the time between subsequent time stamps makes the idea that the iPhone is “tracking your every move” or even very many of them, pretty questionable.
“Tracking your every move” is what my geo-tagger does: once it gets a fix from GPS, it makes a note of where I am every five seconds, if at all possible. That allowed me to put together tracks like the ones I show on the web pages on my pilgrimage in Japan in 2009, reproduced below. This is not what the iPhone is doing.
I’d like to try to verify at some point whether the locations found in consolidated.db are, indeed, cell towers, as I suspect. In some cases, it seems as though there might be too many of them in too small an area, but there may be some other explanation for that.
How I Did This
First, I had to find the consolidated.db file. Anyone who tells you this is easy has never done it. First, you have to find the appropriate backup folder in ~/Library/ApplicationSupport/MobileSync/Backups/ folder. The folder in the Backups directory have names like “8a0bf15905f9cb6e3e6df6fc551467676f3873dc”, and there may be several for every device you sync to the desktop system. To determine the correct one, you have to go into the folder and examine the file “Info.plist” and look for the device name corresponding to your iPhone.
Once you’ve accomplished that, you have to determine which of the files in there is actually the consolidated.db database file, since all the files also have names like “fe5632d8f9e0966173c59da3d92af864bb8cfdc6”. To do this, you’re going to need to decipher the manifest, which is in the files Manifest.dmdb and Manifest.dmdx. A Python script developed by the user “galloglass” in response to a question on StackOverflow accomplishes this. (You can download a copy of this script from my website.) Copy the two Manifest files to the same directory as the script, and the command
./ParseManifest.py | grep consolidated
should produce some output along the lines of
-rw-r--r-- 00000000 00000000 528384 1277443751 1277443751 1277166641
The file name is the long, incomprehensible string in the parentheses. Copy the file with that name from the backup folder to your work directory, and rename it “consolidated.db”.
Next, you’ll need to dump the “CellLocations” table from the file. You can use any SQLite-capable tool you like for this. I used RazorSQL, which is a decent enough OS X application to manage this, and lets you dump an entire table to an Excel spreadsheet, or a variety of other formats easily. (It’s a free trial for 30 days.) Once you’ve done that, you’re good to go.
(I’ve uploaded the Excel spreadsheet I used to do this analysis, so you can download a copy to play with as well. The only addition I’ve made is to add a column to the right with the date corresponding (according to Peter’s kind of suspect conversion) to the time stamp. You can get a copy from here.)
Given a spreadsheet, putting the data onto a Google Map takes a little work, but nothing insurmountable. I used the Google Earth Spreadsheet Mapper, but I’m not proposing to provide a tutorial here.
If people come up with any exciting discoveries playing around with this, I’d be interested in hearing about them. My guess is still that this is most likely cell tower data for use as a cache for triangulation. Where it comes from, exactly, I have no actual idea, but I’d guess that the iPhone makes a note of any cell tower it becomes aware of, regardless of the signal strength, and obtains its geographical location from Apple by some means. I’ll likewise be interested in hearing what kind of response Apple comes up with to the questions raised by Sen. Franken and others.
But me, I’m not especially worried about the existence of this data on my phone.