On the Tenth Day of APOLLO, My True Love Gave to Me – An Oddly Detailed Map of My Recent Travels – iOS Location Analysis

I saved one of my favorite topics for (nearly) last. There is no question that location can play a major role in many investigations. 

iOS location data as changed drastically with iOS 11 from previous iOS versions. I published research on these locations in the past and parsing scripts.

It is my goal to update these scripts with this new research soon(ish).

Powerlog Metadata

The CurrentPowerlog.PLSQL contains some useful metadata associated with the primary locations data I’ll discuss a bit later in this article.

The powerlog_location_tech_status module contains a log of how location was determined. Was the location determined by Wi-Fi or GPS technologies? This information can contribute to how accurate the location data may be.

The powerlog_location_client_status module appears to keep track of which applications and services are requesting location data. Some app examples below include Waze, Weather Underground, The Weather Channel, and the AUDI app. The services can be seen in the second screenshot (the data contained in this table was too long for only one!). 

The type of location is also recorded along with accuracy figures. I’ve seen the following types.

  • Location

  •  Significant (Likely has something to do Significant Locations)

  • Fence (Geofencing?)

  •  Visit

Finally, we have a small log of providing time zone context to the data. The powerlog_timezone module will extract this information.

Significant Locations & Routined Databases

As I’ve mentioned above, the storage of the routined process locations and Significant Locations has changed dramatically in iOS11 from previous versions.

These databases are stored in the following path and are only accessible in full file system dumps.

  •  /private/var/mobile/Library/Caches/com.apple.routined/

    • Cache.sqlite

    • Cloud.sqlite

    • Local.sqlite

Cache.sqlite - routined Locations 

The first database, Cache.sqlite, contains an extremely detailed history of coordinates where the device was. In my own data I had over 40,000 (!) data points. This data is stored for just over a week. This can provide a very accurate map of where I was during that last week.

The routined_cache_zrtcllocationmo module can extract these coordinates along with a timestamp, altitude, course, speed (meters/second), and vertical/horizontal accuracy figures.

Also kept for about a week is data extracted with the routined_cache_zrthintmo module. It has fewer data points, but the timestamp and coordinates appear to be accurate.

Cloud.sqlite - Significant Locations - Visits

The next few modules all use basically the same query but are keyed off of different timestamps. The example shown is of the first module.

Again, this screenshot was split into two because of the amount of data provided by this table. Each significant location visit contains various timestamps (visit entry/exit, visit creation/expiration, learned place creation/expiration). Each visit has coordinates along with a Place ID (an identifier for a specific location), data points collected for that place, uncertainty and confidence figures, and device logging information. 

In the second screenshot there are two odd looking columns, Place Name BLOB and Place Geo BLOB. Each of these columns is storing BLOB data in hex format. I chose this format as it is easy (relatively) to copy/paste into a hex editor for viewing. Examples below.

The first column “Place Name BLOB” contains a smaller amount of binary data (than the Place Geo BLOB), and as you can see you can fairly easily determine where I was at this time – Dulles Airport (I’m a frequent visitor there as you can imagine.) You get address, city, state, business name information in this blob.

The second column “Place Geo BLOB” contains much more binary data, but you can still pick out some similarities in the strings.

So here is the kicker – for YEARS I just accepted these BLOBS, tried to parse them and was unsuccessful but didn’t care too much as I could always see the contents of them. While putting this article together I finally discovered their format – it’s a Christmas miracle!

I do lots of mobile work, that includes Android devices for a good portion of the time (Android, eww – I know, but it provides a paycheck. 😉) When I see random BLOBs in anything Android related my first inclination is to say it’s a Protobuf – a very Googly format. 90% of the time I’m right, I can usually spot them pretty easily. For some reason while looking at them now, my usual protobuf triggers just slapped me in the face so I gave the protoc a try (see the usage below.) 

protoc --decode_raw < binary_BLOB

It worked! I finally have a parsed data structure, granted some of the protobuf pieces I still need to determine but its certainly further than I’ve gotten before. Below is the protobuf output for the “Place Name BLOB”. Many of the strings are obvious, however the highlighted section is the coordinates for Dulles Airport. These are 8-byte floats stored in big-endian as shown in the hex editor below.

The “Place Geo BLOB” is quite a bit lengthier, too long in fact to create a decent screenshot of but still contains the string data and coordinates as expected.

Fun Fact – These protobuf data BLOBs are scattered throughout macOS and iOS systems, you can also see them in Maps data if you are looking for examples to play with. Those also appear to contain timestamps! I promise I’ll do a nice protobuf location blog in the near future. I love me some protobufs! (I’m weird, I know.)

Local.sqlite - Significant Locations - Locations of Interest

Ok back to Significant Location databases. There is one left, the Local.sqlite database. This contains similar data to what we’ve seen before. Timestamps, coordinates, confidence, uncertainty, data point count, and (protobuf!) BLOBS. As with the other modules, they query is basically the same but is using different timestamps to key off from. The example is from the first module. These screenshots are also from the same query, too long to post as a single screenshot.

The last piece of location data I’m extracting from the Local.sqlite database is parking history. I connect my iPhone to my vehicle via CarPlay so I’m not sure if this is required to populate this data. The first module routined_local_vehicle_parked shows the last location of my parked car. 

The routined_local_vehicle_parked_history module shows a parking history.

These modules should give you a pretty good idea where a certain user’s device was a given moment. It is worth mentioning the data does expire after a certain period of time, the sooner you have the data the better the data will be for you. Historically it appears to be pretty accurate but significant locations, but it doesn’t have every location. Dump that phone ASAP! 

Santa can easily find where you live. Creepy Santa.

Grab APOLLO Here!

Start with Day 1: On the First Day of APOLLO, My True Love Gave to Me - A Python Script – An Introduction to the Apple Pattern of Life Lazy Output’er (APOLLO) Blog Series