THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Peter DeBetta's SQL Programming Blog

Peter DeBetta's blog about programming in SQL Server using technologies such as T-SQL, .NET, CLR, C#, VB, Visual Studio, and SQL Server Management Studio.

Extracting GPS Data from JPG files

I have been very remiss in posting lately. Unfortunately, much of what I do now involves client work that I cannot post. Fortunately, someone asked me how he could get a formatted list (e.g. tab-delimited) of files with GPS data from those files. He also added the constraint that this could not be a new piece of software (company security) and had to be scriptable.

I did some searching around, and found some techniques for extracting GPS data, but was unable to find a complete solution. So, I did a little PowerShell programming this weekend and came up with this script (below). It is not the most elegant of solutions, but it worked as expected.

Something of note: I was first lead down the path of searching for GPS data in the EXIF by looking for the property with an ID of 34853, but none of my files that appeared to have GPS data (according to file properties in Windows 7) contained an EXIF property with this ID. So I did a little spelunking and found that the following IDs are also used for GPS data:

Image Property ID Size (bytes) Description
1 2 Latitude North or South value
Contains the literal “N” or “S”
2 24 Latitude degrees, minutes, and seconds
2 - 4 bytes values expressed as rational
3 2 Longitude East or West value
Contains the literal “E” or “W”
4 24 Longitude degrees, minutes, and seconds
2 - 4 bytes values expressed as rational
5 2 Altitude Reference:
0 Above Sea Level, 1 Below Sea Level
6 8 Altitude in meters
2 - 4 bytes values expressed as rational

Update:

  • I found out that the Property IDs that are being used are not the EXIF ones, but the ones from the System.Drawing.Imaging.PropertyItem that encapsulate the metadata from an image file (System.Drawing.Bitmap in this case). You can find a reference for these IDs at PropertyItem.Id Property (System.Drawing.Imaging).
  • Added in the ability to read the altitude data
  • Added some additional exception handling for those files that don’t play nice and an error log file for record of those image files
  • Added the ability to change the status update counter (after 100 rows by default)

Once I sorted this out, it was time to start writing some PowerShell script. Once I got the core functionality working, I added some additional features (recurse folders, include/exclude files with no GPS data, and so on) to the script and cleaned up the code and comments. Enjoy…

PARAM([String]$FolderPath = "", 
    [String]$OutputFileName = "FileList.txt", 
    [String]$IncludeFilesWithNoGPSInOutput = "N",
    [String]$RecurseFolders = "N",
    [String]$Delimiter = "`t", 
    [Int32]$UpdateCount = 100)
# These are the parameters for the command line
# Syntax: 
#    GetImageGPS2.ps1 
#        -FolderPath "<Enter your folder path here>" 
#        -OutputFileName "<output filename here>" (Default "FileList.txt") 
#        -IncludeFilesWithNoGPSInOutput "<Y or N>" (Default "N")
#        -RecurseFolders "<Y or N>" (Default "N")
#        -Delimiter "<Single Character>" (Default [tab])
#        -UpdateCount <Show status after number of rows> (Default 100)
# Example: 
#     GetImageGPS2.ps1 
#        -FolderPath "c:\Pictures" 
#        -OutputFileName "MyGSPFiles.txt" (Default "FileList.txt")
#        -IncludeFilesWithNoGPSInOutput "N" (Default "N")
#        -RecurseFolders "Y" (Default "N")
#        -Delimiter "," (Default [tab])
#        -UpdateCount 50

# The extension of the files we will be searching
$jpg = "*.jpg";
# So we can see how long this is taking
$startTime = [DateTime]::Now
# Captilize $IncludeFilesWithNoGPSInOutput parameter
$IncludeFilesWithNoGPSInOutput = $IncludeFilesWithNoGPSInOutput.ToUpper();

# Check for 'N' or 'Y' in $IncludeFilesWithNoGPSInOutput - if not, assign 'N'
if (($IncludeFilesWithNoGPSInOutput -ne 'Y') `
    -and ($IncludeFilesWithNoGPSInOutput -ne 'N'))
{
    $IncludeFilesWithNoGPSInOutput = 'N';
}

# Captilize $RecurseFolders parameter
$RecurseFolders = $RecurseFolders.ToUpper();
# Check for 'N' or 'Y' in $RecurseFolders - if not, assign 'N'
if (($RecurseFolders -ne 'Y') `
    -and ($RecurseFolders -ne 'N'))
{
    $RecurseFolders = 'N';
}

# Check to see if a filename has been supplied for the output file
# If not, use a default name 'FileList.txt'
if ($OutputFileName -eq '')
{
    $OutputFileName = 'FileList.txt';
}

# Check to see if a folder path has been supplied
if($FolderPath -ne '') 
{
    # Check to see if a valid folder path has been supplied
    if((Test-Path($FolderPath)) -eq $true)
    {
        # Trim the folder path
        $FolderPath = $FolderPath.Trim();
        # Check to see if the trailing backslash (\) is present, if not, add it
        if($FolderPath.Substring($FolderPath.Length-1, 1) -ne '\')
        {
            $FolderPath += '\';
        }
        # Tab delimiter
        if (($Delimiter -eq '') -or ($Delimiter.Length -ne 1))
        {
            $d = "`t";
        }
        else 
        {
            $d = $Delimiter;
        }
        
        $ErrorCount = 0;
        
        # Create output file variable with full path of folder being searched
        $OutputFilePath =($FolderPath + $OutputFileName);
        $OutputFilePathErrors =($FolderPath + $OutputFileName + '.ERRORS.txt');

        # Create output file in folder being searched
        New-Item $OutputFilePath  -ItemType 'File' -Force;
        New-Item $OutputFilePathErrors  -ItemType 'File' -Force;

        # Create file header row and write to output file
        $FileHeader = '"FileName"' + $d + '"LonEW"' + $d + '"LatNS"' + $d `
            + '"LonDegrees"' + $d + '"LonMinutes"' + $d + '"LonSeconds"' `
            + $d + '"LatDegrees"' + $d + '"LatMinutes"' + $d + '"LatSeconds"' `
            + $d + '"AboveSeaLevel"' + $d + '"Altitude(meters)"';
        $FileHeader | Out-File -FilePath $OutputFilePath;
        
        'FILES NOT PROCESSED DUE TO ERROR' | Out-File -FilePath $OutputFilePathErrors;

        # Get the files in the directory
        if ($RecurseFolders -eq 'Y')
        {
            $files = Get-ChildItem ($FolderPath + '*') -Include $jpg -Recurse;
        }
        else
        {
            $files = Get-ChildItem ($FolderPath + '*') -Include $jpg;
        }
        
        # Set initialize file counter variables
        [Int32] $fileCount = 1;
        [Int32] $fileCountWithGPS = 0;
        [Int32] $fileCountWithPartialGPS = 0;

        Write-Host 'Processing has begun...';
        # Iterate through the files
        foreach ($file in $files)
        {
            # Resolve the path to the image file
            $filename = `
            [String](Resolve-Path ($file.DirectoryName + '\' `
                + $file.Name.Replace("[", "``[").Replace("]", "``]")));
            
            # Create new image object from image file
            # Write filename to error log if image create fails
            try
            {
                $img = New-Object `
                    -TypeName system.drawing.bitmap `
                    -ArgumentList $filename;
            }
            catch
            {
                $filename | Out-File -Append -FilePath $OutputFilePathErrors;
                $ErrorCount += 1;
                continue;
            }

            # Set variables for processing
            $out = '';
            [Boolean]$GPSData = $true;
            
            # Set ASCII encoding for extracting string data from property
            $Encode = new-object System.Text.ASCIIEncoding;
            
            # Check to see if image file has GPS data by checking 
            # PropertyID 1: Latitude N or S value
            try
            {
                $LatNS = $Encode.GetString($img.GetPropertyItem(1).Value)
            } 
            catch
            {
                $GPSData = $false
            };
            # If image file has GPS data, process remaining data
            if ($GPSData -eq $true)
            {
                #Increment file with GPS data counter
                $fileCountWithPartialGPS += 1;
                
                # Get GPS data from image file. 
                # PropertyID 1 = Latitude N or S
                # PropertyID 2 = Latitude degress, minutes, and seconds
                # PropertyID 3 = Longitude E or W
                # PropertyID 4 = Longitude degress, minutes, and seconds
                
                $LatDegrees = (([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(2).Value, 0)) `
                            / ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(2).Value, 4)));
                $LatMinutes = ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(2).Value, 8)) `
                            / ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(2).Value, 12));
                $LatSeconds = ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(2).Value, 16)) `
                            / ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(2).Value, 20));
                
                # Set ASCII encoding for extracting string data from property
                $Encode = new-object System.Text.ASCIIEncoding;
                $LonEW = $Encode.GetString($img.GetPropertyItem(3).Value); 
                
                $LonDegrees = (([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(4).Value, 0)) `
                            / ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(4).Value, 4)));
                $LonMinutes = ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(4).Value, 8)) `
                            / ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(4).Value, 12));
                $LonSeconds = ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(4).Value, 16)) `
                            / ([Decimal][System.BitConverter]::ToInt32( `
                                $img.GetPropertyItem(4).Value, 20));

                try
                {
                    $AboveSeaLevel = 1 - ([System.BitConverter]::ToInt32( `
                                    $img.GetPropertyItem(6).Value, 0))
                                    
                    $Altitude = (([Decimal][System.BitConverter]::ToInt32( `
                                    $img.GetPropertyItem(6).Value, 0)) `
                                / ([Decimal][System.BitConverter]::ToInt32( `
                                    $img.GetPropertyItem(6).Value, 4)));
                }
                catch
                {
                    $AboveSeaLevel = "0";
                    $Altitude = 0;
                }
                
                
                # Create outpfile row from GPS data
                if ((($LatDegrees + $LatMinutes + $LatSeconds + `
                    $LonDegrees + $LonMinutes + $LonSeconds) -ne 0) `
                    -or ($IncludeFilesWithNoGPSInOutput -eq 'Y'))
                {
                    $out = '"' + $file + '"' + $d + '"' + $LonEW + '"' + $d `
                        + '"' + $LatNS + '"' + $d + $LonDegrees + $d `
                        +  $LonMinutes  + $d +  $LonSeconds  + $d `
                        +  $LatDegrees  + $d +  $LatMinutes  + $d `
                        +  $LatSeconds  + $d +  $AboveSeaLevel  + $d `
                        + $Altitude;
                }
                if (($LatDegrees + $LatMinutes + $LatSeconds + `
                    $LonDegrees + $LonMinutes + $LonSeconds) -ne 0)
                {
                    $fileCountWithGPS += 1;
                }
            }
            elseif ($IncludeFilesWithNoGPSInOutput -eq 'Y')
            {    
                # Create blank output row if it should be included in results
                $out = '"' + $file + '"' +  $d + '""' +  $d + '""' + $d `
                    + '0' +  $d + '0' +  $d + '0' +  $d + '0' +  $d `
                    + '0' +  $d + '0' + $d + '0' +  $d + '0';
            }
            # If image file has GPS data or image file should be recorded 
            # with blank GPS data, add data row to output file
            if ($out -ne '')
            {
                $out | Out-File -Append -FilePath $OutputFilePath;
            }
            # Write update to console every 100 files that are processed
            if (($fileCount % $UpdateCount) -eq 0)
            {
                [Int32]$startTimeDiff = `
                    ([TimeSpan]([DateTime]::Now - $startTime)).TotalSeconds;
                $hostout = "Processed $fileCount files so far. ";
                $hostout += `
                    "$fileCountWithGPS file(s) contain complete GPS data. ";
                $hostout += `
                "$fileCountWithPartialGPS file(s) contain partial GPS data...";
                Write-Host $hostout;
                Write-Host "$startTimeDiff seconds processing so far.";
                Write-Host "";
            }
            # Increment file counter
            $fileCount += 1;
        }
        [Int32]$startTimeDiff = `
            ([TimeSpan]([DateTime]::Now - $startTime)).TotalSeconds;
        $hostout = "Processing complete. Processed $fileCount total files. ";
        $hostout += "$fileCountWithGPS file(s) contained complete GPS data. ";
        $hostout += `
            "$fileCountWithPartialGPS file(s) contained partial GPS data...";
        Write-Host $hostout;
        Write-Host "$startTimeDiff seconds total processing.";
        $img.Dispose();
    }
    # An invalid folder path has been supplied, so return without processing
    else
    {
        Write-Host 'You must supply a valid folder path';
    }
}
# No folder path has been supplied, so return without processing
else
{
    Write-Host 'You must supply a folder path';
}
Published Monday, May 21, 2012 3:14 PM by Peter W. DeBetta

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Peter W. DeBetta

Peter DeBetta works for Microsoft as an Application Platform Technology Strategist, specializing in strategies, design, implementation, and deployment of Microsoft SQL Server and .NET solutions. Peter writes courseware, articles, and books – most recently the title Introducing SQL Server 2008 from Microsoft Press. Peter speaks at conferences around the world, including TechEd, SQL PASS Community Summit, DevTeach, SQL Connections, DevWeek, and VSLive!

When Peter isn’t working, you can find him singing and playing guitar (click here to hear an original song by Peter), taking pictures, or simply enjoying life with his wife, son, and daughter.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement