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 to provide some tools and methods for answering these questions.

We know from previously published research that Apples iOS Core 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 property is an accurate representation of an approximate device speed at that moment in time.

Forensic Question:

Is the ZSPEED property 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)

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

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? It has already been published and can found on the website. Please take some time and review the posting if you are interested in learning how to determine if Location Services and what System Services 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.

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.

Figure 1 Cellebrite PA Aggregated significant locations (iOS)

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 property 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 property is listed in the ZRTCLLOCATIONMO table ZSPEED column 26.044729232788086 meters per seconds.
    • Notice the differences between the accuracy properties and speed properties, as they appear in Figure 2 (ArtEx), Figure 3 (ZRTCLLOCATIONMO table), Figure 4 (Cellebrite PA) and Figure 5 (Magnet AXIOM).
Figure 2 ArtEx
  • 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 property.
Figure 3 – ArtEx ZRTCLLOCATIONMO original properties
  • 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.
Figure 4 Cellebrite PA
Figure 5 Magnet AXIOM
  • 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 properties, 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.

Figure 6 Mile Marker 28

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

Figure 7

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.

Figure 8 12:20:01 PM Location 656 feet radius
Figure 9

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.

Figure 10 12:20:02 PM Location 228 feet 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.

Figure 11 Distance from MM28

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.

Figure 12 Cell Phone Tower location

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.

Figure 13
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.

Figure 15 Accuracy Comparison

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).

Figure 16

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 17 Nye County Mile Marker 21

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.

Figure 18

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.

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.

Figure 20

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.

Figure 21 IMG_0108.HEIC
Figure 22

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
Figure 23
Figure 24

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.

Figure 25

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.

Figure 26
Figure 27

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.

Figure 28

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.

Figure 29
Figure 30

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.

Figure 31

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 property (meters) and a converted measurement (feet)
  • Speed – I have both the original property (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 property 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.

SELECT

  DateTime(ZRTCLLOCATIONMO.ZTIMESTAMP + 978307200, ‘UNIXEPOCH’) AS ‘Timestamp in UTC’,

  DateTime(ZRTCLLOCATIONMO.ZTIMESTAMP + 978307200, ‘UNIXEPOCH’, ‘localtime’) AS ‘Timestamp Local Time’,

  ZRTCLLOCATIONMO.ZLATITUDE AS ‘Latitude’,

  ZRTCLLOCATIONMO.ZLONGITUDE AS ‘Longitude’,

  ZRTCLLOCATIONMO.ZHORIZONTALACCURACY AS ‘Accuracy in Meters’,

  ZRTCLLOCATIONMO.ZHORIZONTALACCURACY * 3.28084 AS ‘Accuracy in Feet Calculated’,

  CASE ZRTCLLOCATIONMO.ZSPEED

                WHEN -1 THEN ‘Not_Valid’

                ELSE ZRTCLLOCATIONMO.ZSPEED

  END AS ‘Speed Meters per Sec’,

  ZRTCLLOCATIONMO.ZSPEED * 2.23694 AS ‘Speed MPH Calculated’,

  CASE ZRTCLLOCATIONMO.ZCOURSE

                WHEN -1 THEN ‘Not_Valid’

                ELSE ZRTCLLOCATIONMO.ZCOURSE

  END AS ‘Course’,

  CASE ZRTCLLOCATIONMO.ZVERTICALACCURACY

                WHEN -1 THEN ‘Not_Valid’

                ELSE ZRTCLLOCATIONMO.ZVERTICALACCURACY

  END AS ‘Vertical Accuracy in Meters’,

  ZRTCLLOCATIONMO.Z_PK

FROM ZRTCLLOCATIONMO

WHERE ZHORIZONTALACCURACY < 65 AND ZRTCLLOCATIONMO.ZTIMESTAMP BETWEEN ‘#####’ AND ‘#####’

ORDER BY ZRTCLLOCATIONMO.ZTIMESTAMP

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) and 14.7.1 (18G82).

Figure 32 SQLite Query

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.   

Resources:

GPS Accuracy

June 5, 2018, Vladimir Katalov

December 23, 2018, Sarah Edwards

July 18, 2019, Krista Merry and Pete Bettinger

June 25, 2020, Ryan NHP

July 18, 2020, Ian Whiffin

December 10, 2020, Bryan Ambrose

December 21, 2020 Ian Whiffin

March 26, 2021, Ian Whiffin

Apple Developer Website

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: