- 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
|For corporate or law enforcement investigators a fully functional licence for the Forensic Toolkit for SQLite can be obtained by clicking here and providing your official email address, full name and position within your organisation|
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"
-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/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
2. Import the csv into an SQLite database
Uisng the sqlite command line tool (or another tool of your choice) create a new database:
CREATE TABLE files (filename TEXT, latlon TEXT);
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 offfset of 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:
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)
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 teh 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 customise 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
Hard coded 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