Extracting and mapping iPhone iOS4 location data in Windows 7

Okay, so it's been quite *awhile* since I used this thing, but I've actually got something to say that is longer than 140 chars (or multiple annoying tweets).

After reading this article about a recent finding that the iPhone iOS4 records all your GPS locations in a database on your phone, I decided to figure out what my own track has been since owning my iPhone 4. I don't have the same fears about the use of this data that others have been freaking out about. I guess it would be nice if Apple was more explicit about the fact that this data is being stored, but aside from that, I kind of look on it as browser cookies (which, I seem to remember, everyone freaked out about when those were "discovered"), where a track of where you've been surfing is stored on your own machine. Similarly, tracking your own location may help with app development (you might want an app that remembers where you've been), more focused ad content (which, lets face it - ads are just going to be a part of cheap/free apps), and probably a host of other things I haven't thought about (which, admittedly, could be evil - but hey, the world's a dangerous place). This article lays out the case pretty clearly that this isn't something to be worried about, and it has been known for quite some time. I'm also willing to bet that other devices (Android, etc) do the same thing. At any rate, my point isn't to discuss the merits of all this, I just think it's pretty interesting data. But I found it wasn't very straightforward on how to extract it if you don't own a Mac (btw, if you do own a Mac, you can probably skip the rest of this post and use this iPhone tracker application).

So this is how I extracted my data on my Windows 7 machine. Oh, and results are not guaranteed, and if you screw up your phone or computer (not likely, but still, I need to say it), I absolve myself of responsibility. But, you don't have to jailbreak your phone or anything like that, so don't worry. Oh, and I also have no relationship with Apple - I just own one of their phones. And an iPad.

To use the method I used, you'll need:

- a free program called iBackupBot (I'll discuss below)

- MS Access

- MS Excel

- a SQLite ODBC driver (I'll describe below)

- a GIS program (ArcExplorer is free and what I'll use here - you may be able to use Google Earth).

The quick step through:
1) download iBackupBot and extract the consolidated.db file
2) import into MS Access using a SQLite ODBC driver
3) export to MS Excel and calculate the timestamp into a real date
4) export to a delimited text file
5) display in ArcGIS Explorer as a text file with locations. Or as an event theme in ArcMap.

The long and detailed step through:

I should have included more screen captures and that sort of thing, but it took long enough to write this up without doing all that. So, just use your best mind-visualization skills.

iBackupBot:
First, the data file that stores all this data is called "consolidated.db", which is a SQLite database that stores all your location info. The idea is that you need to find it in your iPhone backup. The iPhone backup folder is normally located in "C:\Users\<User Name>\Application Data\Apple Computer\SyncServices\Local", but if you go looking there, you won't find a file named "consolidated.db". I think this is due to the Apple ways of doing things and Unix and stuff I don't know a whole lot about, but I found a 3rd party app called iBackupBot which will drill through the muck and get you to the data file you need. I downloaded this app from cnet here:
http://download.cnet.com/iBackupBot/3000-18551_4-10969873.html. It has a good user rating, and worked well for me. You can also use it for free for 7 days, which is what I did.

Once you install iBackupBot, open it up and you'll automatically see your devices listed in the left-hand menu under "iTunes Backups". In that pane, click on your iPhone with iOS4 and you'll see the list of system files populate the right-hand pane. You'll need to do a little bit of navigating, but in that long list, locate the file: "Library/Caches/locationd/consolidated.db". Here's a screenshot with all my important stuff blacked out:

Once you locate the file, double-click on it.

If you didn't pay for the application, you'll get a "Register" page - click "Cancel" and you'll be able to move on. Once you do this, a new window with 2 panes will open. This window shows all the tables within this "consolidated.db" database file. You can click on each table name and in the right-hand pane, some data will appear. Some of it is interesting (WifiLocation) and some isn't and some contain no data. The data table I was interested in is "CellLocation" (but now I may go back and look at WifiLocation - I didn't see that before this writeup). At any rate, you will be downloading the entire database and can access any of these tables later on.

Download the entire database: click the icon in the upper left-hand corner (a page with an arrow). Save it somewhere on your machine.

Open in MSAccess using SQLite ODBC Driver
Before doing this, I didn't know anything about SQLite, but this ".db" file is a SQLite file, so you'll need to find a way to open it. I used MSAccess. To do this, I followed the instructions given here, and I'll describe what I did:

1. Download and install the free SQLite ODBC driver here: http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe.
2. In Windows, Go to Start Menu --> Administrative Tools --> Data Sources (ODBC) (window opens)
3. Under the panel "User DSN" click "Add..."
4. Select the driver: "SQLite3 ODBC Driver" - hit "Finish"
5. Enter this info, then click "OK":
   
   - Data Source Name: "consolidated" (can be anything)
   - Database Name: browse for the "consolidated.db" file
   - Just leave the other stuff blank
   
6. Click "OK" to get out of the ODBC Administrator
7. Open MS Access - create a new database or use an existing one.
8. Click "External Data" tab at the top
9. Click "ODBC Database" --> Select either import or link to data source - doesn't matter. I chose "import"
10. You'll see a "Select Data Source" menu - click the "Machine Data Source" tab and highlight the "consolidated" (or whatever you called it) data source --> click "OK"
11. You'll now see a long list of tables - the same list you saw in iBackupBot - select both "CellLocation" and "WifiLocation" - these are the 2 tables that contain the latitude/longitude that you need. I assume CellLocation contain the data from using the GPS part of the phone (or from triangulating towers), and the WifiLocation contain the location data from getting a location via a Wifi connection. If anyone knows differently, let me know in the comments. For the purpose of brevity here (this is already quite long), I'll only use the CellLocation file - but the same will work with the WifiLocation.
12. Now you will want to export the file to Excel (to do a little processing on the date) - so, in Access, right-click the "CellLocation" file --> Export --> Excel...
13. On the export menu: fill out the file name and format (or use the default), click "OK" - Do not select "Export data with formatting and layout" - it's already unselected.
14. You are now done with Access.

Calculate the timestamp in Excel - and export as a tab-delimited file:
This step is because you may want to change the timestamp into a real date and time - so you'll need to calculate a new data field on your exported file in Excel. If you don't want to do this, export the file out as a tab-delimited file and move on to displaying in a GIS:

1. Open your exported file in Excel
2. Going to assume you know how to calculate a new field, so I'm just going to give the formula here using the first occurrence of data for the Timestamp (E2) field. From my experience, usually timestamps are the number of seconds (either positive or negative) from 1/1/1970. But, when I tried to formula for using 1/1/1970, I ended up with dates in the 70's and early 80's (still not sure if the Apple 2e tracked your location). So, I think the iPhone uses 1/1/2001 - which would makes sense because all timestamps will be after that date, and you get lower numbers, etc...
   
   Set the column cells to "Date" format, and label the field "Timestamp2" or something different from "Timestamp"
   
   Then calculate the cells in the column using this formula:
   
   =(((E2/60)/60)/24)+DATE(2001,1,1)+(-6/24)
   
   Where:
   E2 = the Timestamp data field (of course, this will change down the column)
   -6 is for Central US Time Zone, since the timestamp is in UTC - so you'll need use -5 for Eastern US Time Zone, and 1 if you live in England (I think? right?).


3. Now export the datasheet with the new column as a delimited text file. Save as...--> select "Text (tab delimited). You are doing this so you can use the file to display the locations in a GIS program.

Use the text file to make an event theme (x,y) in a GIS program:

This step will vary based on what GIS program you use. I'm use ESRI, so I'll explain this using a free software called ArcGIS Explorer Desktop, which you can download here: http://www.esri.com/software/arcgis/explorer/download.html. It's a pretty big install file - about 100 MB. The online version doesn't allow you to display data from a file like this.

1. Once you install ArcGIS Explorer, open it up - you'll see the globe.
2. On the right-hand side of the toolbar pulldown "Map" --> "Add Content" --> "Text Files"
3. Navigate to your text file (or actually, you can also use an Excel file I guess), click "open"
4. You'll probably get a warning about a large number of records - it will actually take awhile, so once you do this, go do something else for a bit while it processes everything.
5. On the selection screen, select "Tab" for the separation, and "First line contains field names". Click "Next"
6. On the next screen, most everything you need will be filled out - double-check the "Longitude and Latitude" fields are pointing to the correct fields in your file. You should be able to use the selected coordinate system. There is actually a field for elevation - but there wasn't any data in my file. Yours may be different.
7. Click "Finish", and go grab lunch, a beer, some coffee, or write a blog post on how to do this, and come back in awhile.
8. And when you come back - all your locations should be displayed! Have fun...

If you use Google Earth, I think you can simply add the text file with locations with the Pro version, otherwise, you'll need to convert that text file into a KML file...I think.

If you use ArcMap, just add the text file to the map, and then display it as an event theme using the latitude/longitude coords - which are Geographic Coordinate System (decimal degree), and I assumed NAD 83. For a basemap, I used basemaps available via ArcGIS.com: http://www.arcgis.com/home/item.html?id=d228563f8ed14323a0f1c28bea1f8f86

An advantage of ArcMap 10 is you can use the timestamp and make the layer "time aware" - then make a movie of your movements through time. Kinda cool if you want to relive the past a little.

If anyone knows of other options - maybe there's a Google maps application that allows this - please mention in the comments. Probably the best route is to somehow convert the data file to KML format and use the Google.

Finally...
There are probably a multitude of other ways to do this, because this is a lot of steps. I could see using some Python coding along with use of Google Maps or ArcGIS Server application to develop and app which will extract this data file. Again, if you know of a better way, let me know. The advantage of this method is that everything is done on your machine. So if you are concerned about privacy, my method (or similar) is the way to go.