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

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


    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

    Code:
    exiftool -n -gpsposition -csv "e:\my Pictures" > "e:\geo.csv"
    The commans instructs exiftool to parse all of the data in the specified folder and pipe the ouput 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 amongst 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

    Uisng 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 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:

    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 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:

    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

    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