iPhone Device Speeds via Cache.sqlite > ZRTCLLOCATIONMO table

Have you ever wanted to know how fast a vehicle or person was traveling at a particular time? Have you considered acquiring iPhone data to answer that question? The material in this blog will help provide some tools and methods for answering these questions.

We know from previously published research that Apples iOS location data in Cache.sqlite > ZRTCLLOCATIONMO table is a great artifact and can be used to determine a device’s location within a few meters, but what about determining a device’s speed?

Before discussing my research and testing, I would like to take this time to point out the resources section at the end of the blog. These resources contain lots of information that I will not be discussing in this blog. If you are analyzing any iOS location data, I strongly encourage you to review all the resources.  

Based on previous research performed and publications by others, in conjunction with my research and testing, I believe, if data exists within the Cache.sqlite > ZRTCLLOCATIONMO table and the record contains a horizontal accuracy which is 65 meters or less, its highly likely the ZSPEED value is an accurate representation of an approximate device speed at that moment in time.

Forensic Question:

Is the ZSPEED value located within the com.apple.routined > Cache.sqlite > ZRTCLLOCATIONMO table accurate? Should we, as digital forensic examiners/analysts, use it as a representation of the device speed at a particular time and date?

Test Devices:

• Apple iPhone 6s Plus [iPhone8,2 N66AP] – No Sim Card and no Mobile data
• Apple iPhone XS [iPhone11,2 D321AP] – Has SIM card and mobile data

OS Versions:

• iOS: 14.4.2 (18D70)
• iOS: 14.6 (18F72)
• iOS: 14.7.1 (18G82)
• iOS: 15.0.2 (19A404)

Tools:

• Cellebrite UFED 4PC 7.47.0.247
• Cellebrite Physical Analyzer 7.47.0.58, 7.48.0.49 & 7.48.1.3
• Magnet AXIOM 5.4.0.26185
• ArtEx 1.6.0.0 & 2.0.0.4

Artifact Location:

• \private\var\mobile\Library\Caches\com.apple.routined\Cache.sqlite > ZRTCLLOCATIONMO table

Extraction Methods that contained \com.apple.routined\Cache.sqlite:

• Cellebrite Advance Logical Full File System UFED 4PC – Checkra1n
• Magnet AXIOM Full Acquisition – Checkra1n
• ArtEx ArtExtraction – Full Extraction – Checkra1n
• ArtEx ArtExtraction – Live Connection – Checkra1n
• Consent Full File System Extraction

Extraction Methods that did not contain \com.apple.routined\Cache.sqlite:

• Cellebrite Advance Logical Extraction – UFED 4PC
• Magnet AXIOM Quick Extraction

Device Settings:

This was written as a two-part blog. The first part is titled iOS Location Services and System Services ON or OFF? Please take some time and review the posting if you are interested in learning how to determine if Location Services, System Services, and Significate Locations were ON or OFF when iPhone data was acquired.

ZRTCLLOCATIONMO Table Data:

This section is a review of the data stored in the ZRTCLLOCATIONMO table. I hope to answer some questions you might have about the data stored and point out some resources for additional research.

Note: Forensic tools title the locations parsed from the ZRTCLLOCATIONMO table differently. For example: In Cellebrite Physical Analyzer, they are titled Native Locations. In Magnet AXIOM and in ArtEx, they are titled Cached Locations.

This is where the device stores very granular location data and based on my testing and the testing of others, these are locations where the physical device has been. These locations are not predictive, nor does the device have to visit the location multiple times before it gets stored as a record. Just another reminder, please read all the resource material at the end of this blog for additional details.

The locations being stored in this table have a direct correlation to whether or not the device Significate Locations was On or Off and if Significate Locations cache has been cleared or not.

ZHORIZONTALACCURACY:

According to Apple Developer website, https://developer.apple.com/documentation/corelocation/cllocation/1423599-horizontalaccuracy, horizontal accuracy is “the radius of uncertainty for the location, measured in meters” and “the location’s latitude and longitude identify the center of the circle, and this value indicates the radius of that circle. A negative value indicates that the latitude and longitude are invalid.”

ZVERTICALACCURACY:

According to Apple Developer website, https://developer.apple.com/documentation/corelocation/cllocation/1423550-verticalaccuracy, vertical accuracy is “the accuracy of the altitude value, measured in meters” and “when this property contains 0 or a positive number, the value in the altitude property is plus or minus the specified number of meters. When this property contains a negative number, the value in the altitude property is invalid. Determining the vertical accuracy requires a device with GPS capabilities. Thus, on some devices, this property always contains a negative value.”

ZSPEED:

According to Apple Developer website, https://developer.apple.com/documentation/corelocation/cllocation/1423798-speed, speed is “the instantaneous speed of the device, measured in meters per second” and “this value reflects the instantaneous speed of the device as it moves in the direction of its current heading. A negative value indicates an invalid speed. Because the actual speed can change many times between the delivery of location events, use this property for informational purposes only.”

ZALTITUDE:

According to Apple Developer website, https://developer.apple.com/documentation/corelocation/cllocation/1423820-altitude, altitude is “the altitude, measured in meters” and “positive values indicate altitudes above sea level. Negative values indicate altitudes below sea level.”

Note: It’s important to know your tools when analyzing location data. My primary tools are Cellebrite Physical Analyzer (PA) and Magnet AXIOM. In Figure 1, the setting highlighted within Cellebrite PA is labeled Aggregated significant locations (iOS). This setting is used to group locations that Cellebrite PA has determined to be similar. If this setting is checked and you are doing location analysis, it’s possible to miss/overlook locations that are at similar coordinates and at a similar date and time, because they will be grouped together. Another note about this setting, is when you update Cellebrite PA to the most recent version, this setting will revert to its default, which is checked/enabled.

Device Location and Speed Testing:

Now that we have covered what is contained in the table, let’s look at some data from a test device.

During testing, I had two devices with me in a vehicle. I traveled around at different speeds and in different environments while the devices recorded locations. I made notes of where I was located and the speed I was traveling. In this blog I will compare device data to my notes.      

On 8/30/2021 at approximately 12:18:33 PM, I was on Nevada State Road 160 also known as Pahrump Valley Highway. I was between Mile Marker 25 and Mile Marker 26. At that time, I was traveling approximately 80 Miles Per Hour. I sent a text message to the iPhone XS test phone to document that information.

On 8/30/2021 at approximately 12:18:33 PM, I was traveling on Nevada State Road 160 also known as Pahrump Valley Highway. I was traveling between Mile Marker 25 and Mile Marker 26. At that time, I was traveling at approximately 80 Miles Per Hour. I sent a text message to the iPhone XS test phone to document that information.

Things to notice in Figure’s 2, 3, 4, and 5:

• At 12:13:47 PM, a location was recorded within ZRTCLLOCATIONMO
  • The location is recorded in latitude and longitude coordinates
  • In ArtEx the accuracy is displayed as 18 meters, this is a rounded number. This raw value listed in the ZRTCLLOCATIONMO table ZHORIZONTALACCURACY column is 17.7919010188834.
  • In ArtEx notice the speed is listed as ~26.04 meters per second (~94 kilometers per hour). These numbers are also rounded. The raw value is listed in the ZRTCLLOCATIONMO table ZSPEED column 26.044729232788086 meters per seconds.
  • Notice the differences between the accuracy values and speed values, as they appear in Figure 2 (ArtEx), Figure 3 (ZRTCLLOCATIONMO table), Figure 4 (Cellebrite PA) and Figure 5 (Magnet AXIOM).

• In Figure 2 and Figure 3 we can see that several locations were recorded between 12:13:47 PM though 12:19:57 PM. Most of the locations had inaccurate/low accuracy.

• In Figure 3 notice the locations with inaccurate/low accuracy also have invalid ZSPEED and ZCOURSE, as marked with -1 value.

• At 12:18:33 PM, a message was received. This message was my notes which included my location and my speed.

• Figure 3 is a screenshot of the ZRTCLLOCATIONMO table which depicts the data as it resides in the database. Later, I will discuss and provide a SQLite query that might assist in your analysis of this location data.

• In Figure 4 and Figure 5, we can see screenshots from Cellebrite PA and Magnet AXIOM. Notice in the Cellebrite PA screenshot the latitude and longitude are rounded and not the original data, as listed in the database. In the Magnet AXIOM screenshot, we can see they use the original data, there is no rounding of values, which I believe is critical to understand if an examiner/analyst is using this data.

• In Figure 4 Cellebrite PA is not decoding the ZSPEED from the ZRTCLLOCATIONMO table in the Analyzed Data view, but it is listed in the Hex view.

Based on the number of locations that were recorded with invalid speeds and with inaccurate/low accuracies, I would suggest that there is not enough data to determine a reliable approximate device speed in this instance. We will discuss some additional examples where I believe we can come to a different conclusion.

At 12:19:49 PM, I sent a message to the test device documenting my location and vehicle speed. I was near the Clark County Mile Marker 28 and was traveling approximately 70 – 71 miles per hour. Mile Marker 28 is located near latitude and longitude coordinates (36.005275, -115.6253591), as seen in Figure 6.

In Figure 7, we can see two locations recorded in the ZRTCLLOCATIONMO table. Let’s review these locations.

At 12:20:01 PM, a location was recorded:

• Latitude 36.004863373124124
• Longitude -115.63749102569237
• Horizontal Accuracy 200 meters/656.168 feet
• Speed 28.78927685224199 meters per second/64.399778212781825459 miles per hour

That seems close to how fast I was traveling, but if we analyze the GPS coordinates in comparison to the coordinates for mile marker 28, we notice they are different. In Figure 8 and Figure 9, I was traveling right to left on the screenshots, this makes sense because I sent the message as I passed the mile marker. But what about the accuracy? Does this location point serve as an accurate representation of where the device was at that time? Sure, the device was in the area, but I would prefer to analyze locations that have better horizontal accuracy. You will also notice; I highlighted a vehicle on the roadway to give you a representation of an approximate vehicle size compared to the accuracy radius.

At 12:20:02 PM, another location was recorded, which was only one second after the previous location. As I previously stated, Cellebrite PA has a setting called Aggregated significant locations (iOS). If this setting is checked/enabled, these locations would have been aggregated and listed as one artifact and could have been missed.

• Latitude 36.004863373124124
• Longitude -115.63749102569237
• Horizontal accuracy 69.574525425611355 meters/228.26287869295063615 feet
• Speed 31.403461456298828 meters per second/70.247542627726446085 miles per hour

That seems to be a match for how fast I was traveling based on my notes, but it has an accuracy of 228 feet. Is that accurate enough for a get a reliable speed? Look at how big the accuracy radius looks like on a map in Figure 10. You will also notice; I again highlighted a vehicle on the roadway to give you a representation of an approximate vehicle size compared to the accuracy radius.

In Figure 11, we can see the distance between the 12:20:01 PM and the 12:20:02 PM location are the same distance from mile marker 28, approximately 1.0947 kilometers or 0.680215044 miles.

There is an accuracy difference between the two locations, which are only one second apart. This made me believe the speed for the 12:20:02 PM location, should be more accurate to the speed I as traveling, which it was. After plotting this information onto a map and doing some research as to the accuracy of GPS and the degree of error, it led me to ask myself, does this have anything to do with the accuracy of the speed recorded in the database? Before we answer that question, let’s look at more examples.

At 12:27:03 PM, I sent a message to the test device reminding me what took place. I accelerated to 70 MPH when I was near the cellphone tower, then accelerated to 80 MPH. I noted the speeds and my location in relation to the cell phone tower. When this test started, I was on the roadway in relation to the cell phone tower as depicted in Figure 12. Using Google Maps, I was able get the approximate latitude and longitude coordinates (36.004997, -115.631162) for my position at that time.

In Figure 13, we can see the message that was sent, and some device locations were recorded between 12:27:39 PM though 12:27:44 PM. The locations were recorded with different accuracies. I used a SQLite query to filter and decode the ZRTCLLOCATIONMO table, seen in Figure 14.

Note: At this point in testing, I was traveling through a rural part of Clark County, Nevada. There are very few businesses, residences, and cell phone towers but the terrain was flat and wide open. Additionally, from my experience, I believe there should be some locations with greater accuracy (four and five meters) but have not experienced them yet during testing.

I then started using the Apple Maps application for navigation. The Maps Location Services settings were set as:

• Allow Location Access as While Using the App
• Precise Location was ON

The next 41 locations, as seen in Figure 16, were recorded within 40 seconds. During this time, I accelerated to 90 miles per hour, then slowed down and documented the location and the speed. Here are the locations and what the device recorded.

At 4:16:50 PM, a location was recorded:

• Latitude 36.3596452261051
• Longitude -116.056892698589
• Horizontal accuracy 4.54071510183259 meters/14.8973597346964 feet
• Speed of 40.1525808445653 meters per second/89.8189141944419 miles per hour

In the 4:16:50 PM location, the accuracy is approximately 15 feet. In Figure 15, we can see how an accuracy of 4 meters/15 feet looks compared to 69 meters/228 feet accuracy.

In Figure 16, the ZHORIZONTALACCURACY is listed in meters and all the accuracies are approximately 4.5 meters or 14-15 feet. That is a very accurate location for mobile devices, even while the device is traveling approximately 90 miles per hour. The speeds are accurate for how fast I was traveling at that time (please don’t tell on me).

At 4:17:47 PM, after recording the times and the speed, I sent a message to the test device documenting my location. The location documented was Nye County Mile Marker 21, which is located at the approximate coordinates of latitude of 36.3520179 and longitude of -116.0512627, as seen in Figure 17. 

Figure 18 is a video I created demonstrating the device location between 4:00 PM though 4:30 PM. The path traveled is based on the recorded locations and each location is labeled with the speed, in miles per hour.

Note: This video was created with Google Maps and Google Earth, but I know Ian Whiffin is working on a new release of ArtEx, which will enhance our abilities to make better maps and videos of device locations that can be used as demonstrative evidence.

At this point, I was confident the data stored in ZRTCLLOCATIONMO table contained very similar, location coordinates, times, accuracy, and speeds as to what took place during testing.

On Thursday, September 16, at 4:51:33 PM, I unlocked the test device and at 4:52:27 PM, I accessed the device (iPhone XS) settings and documented that Location Services was ON and that the Maps app Location Services > Allow Location Access was set to While Using the App and the Precise Location was ON, seen in Figure 19.

At 4:45:35 PM, I opened the Calendar application (com.apple.mobilecal) and checked the settings. When I opened the application, I was prompted to Allow “Calendar” to use your location? In Figure 20, we can see that Precise Location is ON and I am given an option to change/set the Allow Location Access to one of the three options. In this case, I chose Allow While Using the App. I then moved the applications so that they were running in the background, no application was in focus.

At 4:54:51 PM, I took a photo, IMG_108.HEIC, to document my location. Figure 21 is the photo I captured. Figure 22 is a screenshot documenting what was recorded by the device, both the photo EXIF location information and the location recorded in the ZRTCLLOCATIONMO table.

Reminder, the speed displayed in ArtEx is a rounded number, but we can see that the data is indicating I was traveling approximately 10 miles per hour. Also notice the accuracy difference between the Cache.sqlite > ZRTCLLOCATIONMO table and the Photos.sqlite > ZASSET table.

Between 4:55:47 PM though 4:55:52 PM, I took several photos documenting the speedometer, which indicated I was traveling 44 and 43 miles per hour, seen in Figure 23. In Figure 24, we can see the locations and device speeds recorded during this time.

In Figure 24, I have highlighted a ZRTCLLOCATIONMO entry which was recorded at the same time of a photo (IMG_0111.HEIC) that captured of the speedometer. Both the speedometer and the database indicated the device was traveling approximately 44 miles per hour.

• ZRTCLLOCATIONMO primary key entry 129640 has a latitude and longitude coordinates of 36.1447322386637, -115.1643824135
• IMG_0111.HEIC has EXIF location data with latitude and longitude coordinates of 36.14505833, -115.16418333

In Figure 25, I have plotted the two points on a map to show, even though they were recorded at the same time, the location coordinates were different. Remember to review the listed resources to read up on the past research and publications about GPS error rate.

Even though there are some differences between the latitude and longitude coordinates, the device speed and vehicle speed are matching, in this example.

Between 4:58:04 PM through 4:58:34 PM, I took several photos to document my location and vehicle speed. Figure 26 is a slideshow of those photos and the times that they were captured. Figure 27 is a screenshot of the ZRTCLLOCATIONMO data between the same time.

As noted in Figure 27, the vehicle was accelerating during this time. This could be the reason for the difference between the speeds recorded and the speeds displayed on the speedometer. In the previous example the vehicle was traveling at a steady speed and was not accelerating or breaking.

Between 4:58:47 PM through 5:04:36 PM, I entered each of the running applications (Maps, Calendar and Camera) Location Services settings menu and turned OFF the Precise Location. These were the only applications that were running in the background on the device. After turning OFF the Precise Location setting for all running applications, I noticed a significant decrease in the number of accurate locations recorded in both the ZRTCLLOCATIONMO table and in the photos EXIF information.

There appears to be a direct correlation between applications running in the background that have Precise Locations enabled and getting accurate locations for recorded artifacts. Additional testing will be needed, but this seems logical given Apple’s explanation of Precise Location and Reduce Accuracy. Figure 28 is a video that demonstrates the correlation.

Between 5:07:34 PM through 5:07:37 PM, I took several photos to document my location and vehicle speed. Figure 29 is a slideshow of those photos and the times that they were captured. Figure 30 is a screenshot of the ZRTCLLOCATIONMO data between the same time.

At 5:08:07 PM, I again entered the Privacy > Location Services setting and this time I turned OFF all location services. See Figure 31. When I made the change, I noticed the ZRTCLLOCATIONMO table stopped recording location information and did not start again until I turned Location Services back ON the next day.

SQLite Query:

I wrote a SQLite query that helped me extract the coordinates I wanted to analyze. A few things I would like to point out about the query:

• I have the date and time listed in UTC.
• I have included the date and time converted to local time.
• Horizontal Accuracy – I have both the original value (meters) and a converted measurement (feet)
• Speed – I have both the original value (meters per second) and a converted measurement (miles per hour)
• This query will only pull locations that have a horizontal accuracy that is less than 65 meters
• This query will only pull locations between user specified dates. The start and end dates must be the original raw value as listed in the database. Example: start date and time: 652057200.000424 and end date and time: 652059000.998956
• Lastly, the exported data will be sorted by the time stamp.

This query will be stored and updated via my GitHub when needed. Figure 32 is an example of the query output. This query has been tested and works with iOS versions 13.5.1, 14.0.1, 14.4.2 (18D70), 14.6 (18F72), 14.7.1 (18G82) and 15.0.2 (19A404).

Conclusion:

I believe based on research and testing we can use the ZSPEED in the ZRTCLLLOCATIONMO table as reliable data of an approximate device speed at a particular time and place. I believe the more accurate the ZHORIZONTALACCURACY is the more reliable the ZSPEED data is.

I believe this should be used in conjunction with vehicle collision reconstruction reports, crash data recorder reports and/or vehicle forensics data acquisition reports. You might not always have those types of reports, and this might be the only evidence you have as to a possible speed.

Future Considerations:

I plan on contacting a vehicle forensics company and inquiring if they would be interested in a joint research project where the vehicle data and device data can be acquired together then we could compare the data sets. I am hoping this would provide more accurate representation of the location and the device/vehicle speeds.   

An update to this write-up can be located here. It discusses the comparison of three devices (vehicle, iPhone 7 and iPhone X) location data and speed data.

Resources:

GPS Accuracy

• https://www.gps.gov/systems/gps/performance/accuracy/

June 5, 2018, Vladimir Katalov

• https://blog.elcomsoft.com/2018/06/apple-probably-knows-what-you-did-last-summer/

December 23, 2018, Sarah Edwards

• https://sarah-edwards-xzkc.squarespace.com/?offset=1545656400308

July 18, 2019, Krista Merry and Pete Bettinger

• https://journals.plos.org/plosone/article?id=10.1371/journal.pone.0219890

June 25, 2020, Ryan NHP

• https://levelup.gitconnected.com/whats-new-with-corelocation-in-ios-14-bd28421c95c4

July 18, 2020, Ian Whiffin

• http://www.doubleblak.com/blogPosts.php?id=14

December 10, 2020, Bryan Ambrose

• https://www.datadigitally.com/2020/12/apple-pattern-of-life-lazy-outputer.html?m=1

December 21, 2020 Ian Whiffin

• http://cellebrite.com/en/ios-location-artifacts-explained/

March 26, 2021, Ian Whiffin

• https://www.doubleblak.com/blogPosts.php?id=16

Apple Developer Website

• https://developer.apple.com/search/?q=cllocation
• https://developer.apple.com/documentation/corelocation/cllocationspeed/
• https://developer.apple.com/videos/play/wwdc2020/10660/