Andrew Coles
Posted by Andrew Coles,

The consolidated.db file and storing location data

It has been quite common knowledge -- at least here at the iPBE office -- that Apple and their iDevices store a lot of personal data. I was surprised to wake up today to find people blogging heavily about the iPhone logging users' locations. We wonder, is this really news? We have known for a while (at least since the launch of iOS 4) that the iPhone and iPad store location data on their devices and in the backup files. It would also seem quite obvious baring in mind GPS is a feature that most apps have available and most people turn on. I suppose the fright was that in that the data is stored and more importantly the fact that this and other sensitive data on any iDevice is not encrypted by default, either on the iPhone or in its backups.

What location data is actually being stored by Apple on your iPhone or iPad?

I have been surprised by the sudden realisation (by experts!) about the ‘consolidated.db’ file that stores your Cell/Phone, GPS and Wifi location data and by the panic it has caused. Knowing you might have been in the supermarket last week isn’t to much to worry about unless you were involved in criminal activity or do something you shouldn’t there. One legitimate concern is possibly the location of your home, but as there isn’t any reference to this stored on the device here so this shouldn’t be an issue. Having said this other data stored, like contacts may well be just as revealing if not more when including family and friends details. This would be applicable to any phone or device, but allowing users to know what is being stored on a device isn't such a bad idea. Bringing this to light, albeit a year late, is no bad thing.

 

Apple stores a bit more than just the latitude-longitude coordinates along with a time-stamp on the device and backup. Below is a quick summary of what the file contains, these are tables in the database and key fields included:

  • CellLocation (or CdmaCellLocation) - Time-stamp(time and date), Latitude, Longitude, Horizontal accuracy, Altitude(not set), Vertical accurancy(not set), Speed(not set)
  • CellLocationHarvest - Phone Operator(Orange, T-Mobile, O2, Vodafone), Time-stamp(time and date), Latitude, Longitude, Horizontal accuracy, Altitude, Vertical accuracy, Speed, Course(direction), confidence (accuracy percentage?)
  • LocationHarvest - TripID, Time-stamp(time and date), Latitude, Longitude, Horizontal accuracy, Altitude, Vertical accuracy, Speed, Course(direction), confidence (accuracy percentage?)
  • WifiLocation - MacAddress of wifi, Time/Date, Latitude, Longitude, Horizontal accuracy, Altitude, Vertical accuracy, Speed (not set), Direction (not set), confidence (accuracy percentage?)

There is some interesting stuff in this database, and not just the phone locations are stored. The accuracy of this data is only as good as the triangulation from the phone masts and GPS sources can predict depending on what’s been logged at the time. Looking at the data stored on my iPhone I would say the triangulation of my iPhone's location on the move isn’t very accurate, but still within a mile or so of my location. Although any GPS locations stored seem to be within meters if not spot on, so it can be a bit hit and miss unless you decipher the differences in data. Accuracy will always make a big difference depending on how remote the location, the more built up an area is the concentration of masts is greater therefore making these readings far more accurate.

The data stored isn’t specifically related to the phone and signal either, 3G and wifi locations are prevalent everywhere and using GPS also logs location data. For example using 3G, BTOPENZONE or a similar wifi source on your travels will active GPS usage on application if switched and therefore store your location. Details about the wireless hub you connected to are also stored in the consolidation.db file.

Where is the 'consolidated.db' that stores location date and how can I view it’s contents?

Finding the consolidation.db file can be carried out in several ways on your iDevice or iTunes back-up, below is an example using our iPBE application.

Extracting consolidation file with iPhone Backup Extractor

The consolidation.db database file is stored in ‘/Library/Caches/locationd/’ folder, on the computer and the iPhone. Apple use hex-encoded SHA1 hashes to store backup files after synchronising with iTunes, so the easiest way to access the file is using ‘Expert mode’. This can be done by:

  • Open the iPhone backup Extractor application
  • Click the ‘Expert mode’ button
  • Navigate to the following folder ‘Library’ > ‘Caches’ > ‘locationd’
  • tick the box next to the ‘consolidation.db’ file
  • Click the ‘Extract selected’ button and choose a location to save the file
  • The consolidation.db file will be save in this location in the following folder ‘\Library\Caches\locationd\’

If you wish to view the database file you will need to use one of the following applications. Both applications are free and using the tables mentioned above you should be able to see location data with little technical knowledge.

iPhone consolidation file in sqlite manager

Once you find any location reference points in the database, you can use Google Maps to plot the locations. Search using ‘Latitude, Longitude’ and hey presto a location you have once been is shown! You may have problems deciphering the data and time field, if you export the data to Excel, you can just convert using =1/1/2001 + A1/60/60/24 where A1 is your time data.

If you have any comments on this subject or would like to add to the debate further, please reply to this blog

We've helped over 3,000,000
people recover their lost iPhone data.
Let us help you too.

DOWNLOAD THE FREE VERSION »
Windows

v7.3.7.1428 - 11th Nov, 2016

OS X

v7.3.7.1428 - 11th Nov, 2016

Works with Windows and Mac