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