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:
- Run exiftool and export the relevant results as a csv
- Import the csv into an SQLite database
- Use the Browser to create a query displaying the lat and long as two fields
- Create a VIEW to represent this query
- Use the Browser to display a map for each row showing the location defined by the lat and longs
- 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
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:
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:
Now within the command line tool create a table with two columns for the new data
CREATE TABLE files (filename TEXT, latlon TEXT);
Set SQLite to work in csv mode
import the csv file created with exif tool.
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:
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:
CREATE VIEW geo AS (SELECT files.filename,
SubStr(files.latlon, 1, instr(files.latlon, ' ') - 1) AS lat,
SubStr(files.latlon, instr(files.latlon, ' ') + 1) AS lon
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:
All we need to do is replace the
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