Using the Forensic Browser for SQLite to display maps based on data from exiftool Edit

by | Jun 29, 2016 | Forensic Browser for SQLite

I recently saw a Twitter conversation where a user wanted to see the EXIF data from some image files displayed as maps and showing a clickable URL for Google Maps. The latter part of this problem can easily be solved with the Browser – the steps are as follows:

  1. Run exiftool and export the relevant results as a csv
  2. Import the csv into an SQLite database
  3. Use the Browser to create a query displaying the lat and long as two fields
  4. Create a VIEW to represent this query
  5. Use the Browser to display a map for each row showing the location defined by the lat and longs
  6. Use the Browser to combine the lat and longs into a clickable URL

This example assumes that you want to display the locations of all the files in the path “E:\\My Pictures”

1. Run exiftool and export the relevant results as a csv

Run the following command line in exiftool

Code:

exiftool -n -gpsposition -csv "e:\\my Pictures" > "e:\\geo.csv"

The commands instruct exiftool to parse all of the data in the specified folder and pipe the output in csv format to the specified file.

-gpsposition specifies that just the GPS tags from the EXIF data will be exported
-n tells exiftool to save GPS data in numerical (floating point) form

A few of the lines from the exported geo.csv file are shown below:

Code:

e:/my pictures/hugh.jpg,
e:/my pictures/image.png,
e:/my pictures/image1.JPG,50.0867083333333 -5.31498611111111
e:/my pictures/IMG_1568.JPG,50.0888333333333 -5.10166666666667
e:/my pictures/IMG_1697.MOV,50.1567 -5.0683

We can see that for those files that have GPS information it is displayed as a lat and long. The keen-eyed among you will have noted that the lat and long is actually a single column, i.e. there is no comma separating the two – this can be resolved later with the Forensic Browser.

2. Import the csv into an SQLite database

Using the sqlite command line tool (or another tool of your choice) create a new database:

Code:

sqlite3 geo.db

Now within the command line tool create a table with two columns for the new data

Code:

CREATE TABLE files (filename TEXT, latlon TEXT);

Set SQLite to work in csv mode

Code:

.MODE CSV

import the csv file created with exif tool.

Code:

.IMPORT geo.csv

3. Use the Browser to create a query and then a VIEW displaying the lat and long as two fields

A query showing the data from the files table looks as follows:

What we need is a query that splits the lat and long from the latlon column into two separate entities, i.e. two new columns. SQLite provides an inbuilt function to extract a portion of a field SubStr and a second function InStr to find the offset of a particular element of a string.

Notice that in the latlon field above the two elements are separated by a space, the following query extracts the characters from the latlon field starting at character 1 and stopping at the character 1 before the space.

SubStr(files.latlon, 1, instr(files.latlon, ‘ ‘) – 1)

This can be combined with a similar query that extracts the part of the latlon string after the space. The combined query looks as follows:

You can see the original latlon column plus two new columns (which we have called lat and lon using the AS qualifier in the query above.

4. Create a VIEW to represent this query

A VIEW is a sort of virtual table and the VIEW can then be used in place of the query itself. The SQLite command we would use is:

Code:

CREATE VIEW geo AS (SELECT files.filename,
files.latlon,
SubStr(files.latlon, 1, instr(files.latlon, ' ') - 1) AS lat,
SubStr(files.latlon, instr(files.latlon, ' ') + 1) AS lon
FROM files)
However the browser has a menu option that allows you to simply create a VIEW on the current visual query.
Once a view has been created then the VIEW name can be used instead of the previous query. e.g.:

5. Use the Browser to display a map for each row showing the location defined by the lat and longs

The Browser has a built-in function that creates geolocated maps based on lat and long fields:

You are just prompted for the table, an ID field and the lat and long columns:

A new table is created and populated with maps for each row in the “source” table. Once the maps have been created for you a simple visual query is automatically built joining the two tables allowing you to customize your query:

6. Use the Browser to combine the lat and longs into a clickable URL

The final step is to create a URL column. This simply uses some hard-coded string values concatenated together with data from the lat and lon columns we created above.

The format for a google maps URL at zoom level 9 is as follows:

http://maps.google.com/ll=,,z9

All we need to do is replace the and elements with values from our table

The SQL for this row is below:

‘http://maps.google.com/?ll=’ || CaseDB.Geodata1.lat || ‘,’ || CaseDB.Geodata1.lon || ‘,z9 ‘ AS url

Hardcoded strings are enclosed in single quotes and the SQLite concatenation operator || is used to join successive strings and field values together, we call the column URL.

The final report is shown below