iPhone consolidated.db location tracking notes

I’m trying to understand the consolidated.db iPhone location database, which means understanding how iOS stores and managing data. Here’s some notes.

Resources

  1. @aallan’s original announcement
  2. iPhone Tracker (Objective C code)
  3. Technical instructions from howto.wired
  4. nphonetrack (.NET code)
  5. iPhoneLs.py
  6. iphone-tracker.py
  7. Quick start for sqlite3
Notes
  • When you make a backup of an iPhone, it creates a big directory full of files with random 160 bit filenames. These are named files in the iPhone that iphonels.py can display. My 3 biggest files are named
    HomeDomain::Library/Caches/com.apple.WebAppCache/ApplicationCache.db
    AppDomain-com.foreflight.ForeFlightMobile::Documents/Procedures.zip
    RootDomain::Library/Caches/locationd/consolidated.db
  • consolidated.db is the file with the location data in it. It’s a sqlite3 database.
  • The juicy tables are (apparently) CellLocation and WifiLocation. The *Counts tables seem to contain just a row count. Presumably the Boxes tables are some sort of spatial index. I can find no record of how often I was at a specific location other than what’s implicit in the timestamps and density of cell/wifi locations.
  • I have 32,013 rows in CellLocation, 171,040 rows in WifiLocation
  • Timestamps are like 309803342: I believe this is NSDate seconds since 1 Jan 2001. Add 978307200 to get it in seconds since Unix 1970 epoch.
  • HorizontalAccuracy varies from 50-300 for WifiLocation, and up to 80,000 for CellLocation.
  • Confidence is 0, 50, 60, 65, 68, or 70
  • Speed and Course are always -1 (no surprise).
Some quicky sqlite3 shell code
.mode csv
.output wifilocation.csv
select Timestamp, Latitude, Longitude from WifiLocation order by Timestamp;
.output CellLocation.csv
select Timestamp, Latitude, Longitude from CellLocation order by Timestamp;
Some pasted info from sqlite3

sqlite> .tables
CdmaCellLocation                   CellLocationCounts
CdmaCellLocationBoxes              CellLocationHarvest
CdmaCellLocationBoxes_node         CellLocationHarvestCounts
CdmaCellLocationBoxes_parent       CellLocationLocal
CdmaCellLocationBoxes_rowid        CellLocationLocalBoxes
CdmaCellLocationCounts             CellLocationLocalBoxes_node
CdmaCellLocationHarvest            CellLocationLocalBoxes_parent
CdmaCellLocationHarvestCounts      CellLocationLocalBoxes_rowid
CdmaCellLocationLocal              CellLocationLocalCounts
CdmaCellLocationLocalBoxes         CompassCalibration
CdmaCellLocationLocalBoxes_node    Fences
CdmaCellLocationLocalBoxes_parent  Location
CdmaCellLocationLocalBoxes_rowid   LocationHarvest
CdmaCellLocationLocalCounts        LocationHarvestCounts
Cell                               TableInfo
CellLocation                       Wifi
CellLocationBoxes                  WifiLocation
CellLocationBoxes_node             WifiLocationCounts
CellLocationBoxes_parent           WifiLocationHarvest
CellLocationBoxes_rowid            WifiLocationHarvestCounts

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));

CREATE TABLE WifiLocation (
  MAC TEXT,
  Timestamp FLOAT,
  Latitude FLOAT,
  Longitude FLOAT,
  HorizontalAccuracy FLOAT,
  Altitude FLOAT,
  VerticalAccuracy FLOAT,
  Speed FLOAT,
  Course FLOAT,
  Confidence INTEGER,
  PRIMARY KEY (MAC));

About these ads

3 thoughts on “iPhone consolidated.db location tracking notes

  1. Nelson, what do you think of the idea of getting people to upload their consolidated.db files to a central service where we can anonymize them and create an open database of Cell and Wifi locations?

  2. Hmm, I like that idea. It may not be necessary; it used to be the Skyhook-like web service Apple used was unprotected, I haven’t checked lately.

  3. You say: “I can find no record of how often I was at a specific location” — Well obviously not, as each cell (the (MCC, MNC, LAC, CI) column combination) is stored only once in the CellLocation table; as the CREATE TABLE statement shows it is a primary key, and there is no “count” column. Ditto for the MAC column of the WifLocation table.

Comments are closed.