Skip navigation.


Click any image to expand it

1) iPhone/iPod Touch Backup Extractor




2) Extracted iPhone files




3) SQLite Manager - Accessed from the Firefox tools menu




4) SQLite Manager - Selecting the consolidated.db file




5) SQLite Manager - Accessing WifiLocation Table Right click on table to export to csv




6) Excel Formula for Timestamp




7) Batchgeo.com - Pasting Data




8) Batchgeo.com - Mapping Data




6) Excel Formula for Timestamp




9) Google Fusion Table - Creating Table




10) Google Fusion Table - The Table




11) Google Fusion Table - The Table with Photos (enable photo view by Modifying Columns to be "Text" Type: 8 line image)




12) Google Fusion Table - Map with Photos




13) Google Fusion Table - Map of All Locations




14) Google Fusion Table - Map of All Locations above 1000 meters




15) Google Fusion Table - Map of All Locations above 1900 meters




First, as requested, here is a link to

A Google Fusion Table Map of Historical Moscow Images

(Note: the locations and time information on these images is approximate in most cases; if you note any problems, please contact Devin Becker at dbecker@uidaho.edu.


Hack Your Phone | Map Your Phone

by Devin Becker

Disclaimer:The following steps are in no way endorsed by anyone at the University of Idaho, the University of Idaho Library, or the University of Idaho Library Digital Initiatives Department. Download and access the software and websites listed here at your own risk.

Hack Your Phone

Extract Your data

iPhone Backup Extractor (http://www.iphonebackupextractor.com/) - this works on both windows and Mac computers. Requires upgrade for all features to work.

iPhone/iPod Touch Backup Extractor (http://supercrazyawesome.com/) - This is what I used (See figure 1). Works only on Macs. Simple interface.

Both of these applications will extract data (See figure 2)from the backup folder of your iPhone and extract that data into a new folder. This data will be in a variety of formats. Some of it you may have deleted (indeed, one use for these applications is to find deleted photos, contacts, etc.

Once you open one of these extractors, extract your "ios" data to a folder (on your desktop or elsewhere) so you'll be able to find it later.

Read Your Data

Many of the files that you extract will be SQLite database files. To read these, you should download some sort of SQL browser. This plugin works well with Firefox: https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/. Download and install it, then go to the tools menu to open the browser in firefox (See figure 3). Once opened, you can browse to your extracted files folder, then open: iOS Files/Library/Caches/locationd/consolidated.db (See figure 4). This is a SQL database file.

The SQL browser will then give you a list of tables, views, indexes, and triggers. We're interested in the tables. On my iPhone, I found there were several tables that recorded lat/long. The biggest by far was the WifiLocation Table (See figure 5), followed by the CdmaCellLocation table and then the CdmaCellLocationLocal table, which I found to be the most manageable. These tables provided large datasets, with data not only on latitude and longitude, but also on time, speed, and other measurements recorded by the iPhone

Export Your Data

After you click on one of the tables, you can either execute an SQL query (sounds scary, but it's really just a request for certain rows from a certain table) or just right click on the table to export the whole thing into a csv file using the browsers "Export Wizard" (be sure to click the "First row contains column names" button, so you don't have to manually enter them).

You can then open the exported csv in Excel or another spreadsheet application. Save that file (or the exported csv you created with the SQL query) somewhere you can find it, then open it up in Excel. You'll want to delete the columns you don't need, or that don't give you any decent information (for instance, I deleted the "confidence" column because each cell had the same number in it), then insert a row at the top to name the columns if they don't have names yet.

Map Your Phone

Preparing Your Data

Before you map your data, you'll want to reformat the timestamp row into a row that you can read (See figure 6). The big number recorded in the time stamp is the amount of time, in seconds, since January 1, 2001. To format this in Excel, you'll need to enter the formula "=(((COLUMN_ID_HERE/60)/60)/24)+DATE(1970,1,1)"* in a cell (I just used the one next to the timestamp) and then format that cell so that it is a date in Excel. This should display the timestamp as a real date and time.

Exporting/Mapping Your Data - BatchGeo.com (Easiest Way)

Now that you have a spreadsheet with latitude and longitude information, you can map it using a variety of tools. The easiest I've found is at batchgeo.com. There you can simply copy and paste (See figure 7) your spreadsheet to see the latitude and longitude coordinates mapped(See figure 8). Note: BatchGeo will only let you map 2500 points (unless you upgrade), which is much less than you'll have, so you may want to just map a selection to start, or map one of the smaller csv files you've made.

BatchGeo is especially good for making maps that you'd like to access on your phone. So if you want to look at where you've been with your phone on your phone, go here.

Importing Your Data - Google Fusion Tables (Most Controlled Way)

Google Fusion Tables allow you to visualize and display your latitude and longitude coordinates (and other readable addresses, if you would like to). You simply need to import the csv files (See figure 9) you made above into a fusion table. Once you've imported your data, go to the "Edit" menu at the top and select "Modify Columns." Make sure your latitude is set as a "two column" location -- the two-column will then automatically select the longitude column. Set up your other columns as well to be either "numbers," "text," or "date/time."

Finally, in the "Visualize" menu, select "Map." Your coordinates should pop up on a Google map showing you all the places where your phone has collected your geographical information.

Mapping Your Photos

There are a couple of ways to map your photos. The easiest way I've found is to use Locr.com. You can create an account on this website and then upload your photos. Any lat/long info on the photos will be pulled into their system and the photos will display on those locations. The site is pretty easy to use and helpful if you want a quick map.

Advanced Option: Another, more difficult way is to extract, treat, and import your data into 'oogle Fusion Tables, much the same way you did above. The database file for your photo information can be found here: "iOS Files/Media/PhotoData/PhotosAux.sqlite": using the SQLite manager, you can extract this information and map it in the same way as above

If you would like to have the photos up in the pop up bubble on a Google map, you can use aDropbox.com account. Store all your phone pics in the public folder, then make sure the image file names in dropbox, match the list of file names in the csv file you import into 'oogle Fusion Tables(See figure 11) . Then get a link to your dropbox images and change the code in the "Configure Info Window" so that it matches up(See figure 12). (If you have questions about how to do this, just email me at dbecker {at} uidaho.edu)

References

Thank you to Pete Warden for making me aware of the iPhone files, and demonstrating how to visualize them. I did not have success with using his iPhone Tracker, but it was definitely my main inspiration for figuring out how to map my phone.

(Thank you to Brooke Bryan for this formula).