- Firefox (FF 4 friendly)
- The SQL Lite Plug In
- GPSBabel (optional,used to create KML file)
- A Google account to view Map
C:\Users\*
Now in this folder there may be several folders with long numbered names (i only had one), if you check the details you should navigate into the newest one. Then open info.plist with a text editor, it's reasonably readable and you should find an entry near the top which gives the Device Name for your iPhone. (this is just to confirm it's the folder for your device, to look at anyone elses data would just be plain rude).
Now in the original Mac write up there's a way to determine which of the many obscurely numbered files in that folder is the (location) consolidated.db file, with Windows this doesn't appear to be forthcoming so some trial and error is in order. First i listed the files by size, biggest first (click on size on top line), Then open up Firefox and the SQL Lite plug in (it should be in options menu).
Now attempt to open the files one by one using 'Connect Database' (the little folder icon), make sure you have 'File Names' drop down menu set to 'all files' in explorer bar to see them.You'll probably get a Error message for the first few....
*Update* i've been led to believe the relevant file begins with the numbers 4096... (hat tip Owen)
but keep going (i think mine was the fourth file down the list ) and hopefully...
The good stuff is under 'CellLocation' (in the Browse & Search' Tab)
(I blacked my stuff out but stalkers can still find me on FourSquare).This can be exported to an XML file, the 'to do' now being to convert it to an KML file and view the numbers as a map (i'm not bleedin' Keanu Reeves you know!!)
Midge
*Update* re' viewing on Google Maps: There's probably easier/better ways to do this but if you go the 'Execute SQL' Tab in SQL Lite and enter...
SELECT Latitude,longitude FROM CellLocation
followed by 'Run SQL' you'll get a Table of just the Latitude/Longitude readings, which can then be saved as an .csv file (manually add a row with lat/lon column headings in a spreadsheet (eg. Google Docs))
Using the ('free') GPSBabel program you can then then convert this into a KML 'Google Earth Keyhole Markup Language' file (it's a relatively intuitive program)...
*Errata* Use 'Universal csv with field structure in first line' instead of the plain 'Comma seperated values' shown in picture below
Then it's just a quick wonder over to Google maps, create new map and import the KML file to view the locations.
*UPDATE* If your familiar with Google Fusion tables you can import the KML file into that to view as a map as well (hatip Ian)
Now be prepared to be very disappointed, the locations appear to be very vague and certainly don't have the Big Brotherish accuracy which everyones banging on about.
6 comments:
How do you add a timestamp to the lat/longs?
This is great and worked perfectly. Thanks very much.
(You could mention that kml is found under "Google Earth (Keyhole) Markup Language" in GPSBabel for the ignorant like me.
Thanks, it worked great! I had 23000+ coords so vanilla google maps failed to render most of the points - Google Fusion tables came to the rescue.
@Michael You would use
SELECT Latitude, Longitude, Timestamp FROM CellLocation
in SQLLite 'Execute SQL' tab (and add 'name' heading in the .csv file).
The slight problem is it uses a Unix style Timestamp (seconds elapsed from January 1st 2001).I guessing there's a method to change this to a regular dd/mm/yy time format in a spreadsheet (made from .csv file) but that is a bit beyond me atm.
@Michael i've had a quick Google and here's an article that shows how to convert Unix style Timestamps to dd/mm/yy in a spreadsheet.
http://spreadsheetpage.com/index.php/tip/converting_unix_timestamps/
note: the formula needs tweaking to read..
=(((A1/60)/60)/24)+DATE(2001,1,1)
Use this sql to convert the unix timestamp to a local timeformat:
SELECT datetime(Timestamp,'unixepoch','localtime'), latitude, longitude FROM celllocation
Post a Comment