top of page

iPhone Cache.sqlite, KnowedgeC and InFocus data used in criminal traffic case

Updated: 3 days ago

I served as a cell phone expert in a case where the defendant drove his car into a construction area and killed a construction worker. This was a tragedy for all involved. There was no intent on the part of the driver and the families and friends of the victim and defendant all suffered.

In this case databases were extracted from deep within the defendant's iPhone. The three databases that were key to this investigation were Cache.sqlite, KnowedgeC.db and InFocus.db.

First I'll take a look a Cache.sqlite. In this phone this database can be found in \private\var\mobile\Library\Caches\\Cache.sqlite. Using DB Browser I opened the database and viewed the table "ZRTCLLOCATIONMO". This table is shown below.

The timestamp is in iOS time and needs to be converted by using the following Excel formula (iOS Time/86400) + DATE(2001,1,1) - TIME(5,0,0). The last function converts UTC time to Eastern Standard Time by subtracting 5 hours. iOS time would be the value in the Excel ZTIMESTAMP column. ZSPEED is in meters per second. Accuracy is in meters.

I filtered the data to only view the time up to and including the accident and converted the date/times and resulted in the following Excel sheet.

Based on the location data obtained from the phone and the police reports we know the accident occured between 8:25:03 and 8:26:13 AM. The following map shows the points from 8:25:08 to 8:26:13 AM. The locations were pulled from the sheet above.

The vehicle was traveling northbound. The last known speed prior to the accident is at 8:24:55 AM at 54.2 mph. The next point at 8:25:03 AM has no speed data (e.g. -1) but has an accuracy of 50 meters. The next point at 8:25:08 AM is south of the first point which doesn't seem to make sense except the accuracy is 253.3 meters so theoretically his actual position may be north of the second point. The next point is north of the two construction warning signs at 8:25:56 AM again with a poor accuracy of 575 meters. The last point is when the vehicle stopped at 8:26:13 with a 20 meters accuracy which doesn't match with the actual crash site so the accuracy figures should be taken with a grain of salt and not to be expected to be completely 'accurate'.

The final part of this analysis has to do with what the person in the vehicle was doing on the phone. Or more specifically, what apps were active and what was physically displayed on the phone screen during this time period. For this information we accessed the KnowledgeC and InFocus databases. These were found in folders  /private/var/mobile/Library/CoreDuet/Knowledge/knowledgeC.db and  /private/var/db/biome/streams/restricted/_DKEvent.App.InFocus.

I opened both databases using DB Browser then combined them into Excel using the date and time. I filtered out all the records except those between the times of 8:24:55 AM and 8:26:13 AM. The result is the following sheet.

The highlighted rows are those with a duration greater than zero. The records with "InFocus" in column 'K' indicate the app was being displayed on the phone screen. Notes in column 'M' are my decoding in plain language of which app was on the screen. Column 'E' shows the duration of the app activity. Using this data along with the location data we can approximate the location of the car when each app was launched. We don't have speed data after 8:24:55 AM but we can make some assumptions such as constant speed or constant deceleration and estimate location ranges.

The first three items Snapchat, Apple Mail and Message notification were not necessarily deemed to be obstructing the driver but it is believed the jury had an issue with the TikTok video. While the defense argued that this could have been launched and not viewed. In other words the driver could have been listening to it but keeping his eyes on the road, the jury ultimately decided this was the crucial factor in the cause of the accident. There were three visible warning signs starting at 750 feet from the crash site that the driver should have seen and would have had enough time to slow down.

A classical accident construction analysis was also performed and used at trial. The cell phone data was key in providing additional detail to the reconstruction of this tragic accident.

10 views0 comments

Recent Posts

See All


bottom of page