Posted on:
Categories: SharePoint;PowerShell
Description:

We're finding that an increasing number of JPEG photos uploaded to SharePoint contain GPS information, due to built-in GPS devices. 

In some industries, such information has real value. Rather than force people to manually tag mountains of photos, we could automatically extract the information and unlock a range of geo-oriented value-adds.

​Here is a PowerShell script we wrote to take just the first step. It analyzes the images across a SharePoint landscape and so establishes what proportion of them contain GPS information.

The output is a CSV listing of all JPEGs including, when found, latitude, longitude and altitude.

Some warnings

  1. This reads the content of every JPEG and could affect your farm performance. It would be wise to run it outside of peak hours.
  2. The finding of the images is done via search and so is security trimmed. Run it under a suitably-privileged account.
  3. The script can be run remotely but you probably want to do that only for small tests (and scope the query down). For the full run you'll get better performance and spare the network by running it from one of the SharePoint servers.

Script

param(
    [Parameter(mandatory = $false)][switch]$promptForCreds
)
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client")
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Client.Search")
[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")

$url = "http://yoursite.whereva" # Any site - just to get at the search service
$queryText = "(fileextension:.jpg OR fileextension:.jpeg) -size>5000000" # get all non-huge JPEGs
$outputCsvPath = "results.csv"

# EXIF standard property identifiers

$exifLatitudeId = 2
$exifLongitudeId = 4
$exifAltitudeId = 6

#------------------------------------------------------------------------------------
#  Functions
#------------------------------------------------------------------------------------

function GetDecimalGPSValue($src)
{
    # Convert raw lat/long bytes into a useful decimal
    # Code acknowledgement: https://code.google.com/p/exifbitmap

    [double] $deg = [System.BitConverter]::ToUInt32($src, 0)
    [uint32] $deg_div = [System.BitConverter]::ToUInt32($src, 4)

    [double] $min = [System.BitConverter]::ToUInt32($src, 8)
    [uint32] $min_div = [System.BitConverter]::ToUInt32($src, 12)

    [double] $mmm = [System.BitConverter]::ToUInt32($src, 16)
    [uint32] $mmm_div = [System.BitConverter]::ToUInt32($src, 20)

    [double] $m = 0
    if ($deg_div -ne 0 -or $deg -ne 0)    
    {
        $m = ($deg / $deg_div)
    }

    if ($min_div -ne 0 -or $min -ne 0)
    {
        $m = $m + ($min / $min_div) / 60
    }

    if ($mmm_div -ne 0 -or $mmm -ne 0)
    {
        $m = $m + ($mmm / $mmm_div / 3600)
    }

    return $m.ToString()
}

function GetDecimalGPSAltitudeValue($src)
{
    # Convert altitude bytes into a useful decimal

    [double] $alt = [System.BitConverter]::ToUInt32($src, 0)
    [uint32] $alt_div = [System.BitConverter]::ToUInt32($src, 4)

    [double] $m = 0
    if ($alt_div -ne 0 -or $alt -ne 0)    
    {
        $m = ($alt / $alt_div)
    }

    return $m.ToString()
}

#------------------------------------------------------------------------------------
#  Main Script
#------------------------------------------------------------------------------------

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
$creds = $null

if ($promptForCreds)
{ 
    $creds = Get-Credential
    $ctx.Credentials = $creds
}

# Start new CSV

if (Test-Path -Path $outputCsvPath)
{
    Remove-Item $outputCsvPath
}

# Execute CSOM search query a page-at-a-time in order to avoid hitting limits

$findingResults = $true
$startRow = 0
$pageSize = 500
$happyCount = 0
$totalCount = 0

while ($findingResults)
{
    $keywordQuery = New-Object Microsoft.SharePoint.Client.Search.Query.KeywordQuery($ctx)
    $keywordQuery.RowLimit = $pageSize
    $keywordQuery.StartRow = $startRow
    $keywordQuery.RowsPerPage = $pageSize
    $keywordQuery.QueryText = $queryText
    $keywordQuery.EnableSorting = $true
    $keywordQuery.SortList.Add("LastModifiedTime", 
        [Microsoft.SharePoint.Client.Search.Query.SortDirection]::Descending)
    $keywordQuery.SourceId = [guid]"8413cd39-2156-4e00-b54d-11efd9abdb89" # Local SP Results
    $searchExecutor = New-Object Microsoft.SharePoint.Client.Search.Query.SearchExecutor($ctx)
    $resultTables = $searchExecutor.ExecuteQuery($keywordQuery);
    $ctx.ExecuteQuery()

    if ($resultTables.Value.ResultRows.Count -eq 0)
    {
        $findingResults = $false
        break
    }

    foreach($resultRow in $resultTables.Value.ResultRows)
    {
        $status = $("Files with GPS info: {0} of {1}" -f $happyCount, $totalCount)
        Write-Progress -Activity "Analyzing" -Status $status

        # Retrieve the image file

        $request = $null

        if ($creds -eq $null)
        {
            $request = Invoke-WebRequest -Uri $resultRow.Path -UseDefaultCredentials
        }
        else
        {
            $request = Invoke-WebRequest -Uri $resultRow.Path -Credential $creds
        }
     
        try
        {
            # Read image data and attempt to extract GPS values

            $img = [System.Drawing.Image]::FromStream($request.RawContentStream)

            $exifLatitude = $img.GetPropertyItem($exifLatitudeId)
            $latitude = GetDecimalGPSValue -src  $exifLatitude.Value

            $exifLongitude = $img.GetPropertyItem($exifLongitudeId)
            $longitude = GetDecimalGPSValue -src $exifLongitude.Value

            $altitude = $null

            try
            {
                $exifAltitude = $img.GetPropertyItem($exifAltitudeId)
                $altitude = GetDecimalGPSAltitudeValue -src $exifAltitude.Value
            }
            catch
            {
                # Ignore error - no altitude found
            }

            $fileInfo = @{ 
                Url=$resultRow.Path; 
                FileSize=$resultRow.Size;
                Modified=$resultRow.LastModifiedTime.ToString("yyyy-MM-dd");
                InfoFound=$true; 
                Lat=$latitude; 
                Long=$longitude; 
                Alt=$altitude 
            }

            $happyCount++
        }
        catch
        {
            $fileInfo = @{ 
                Url=$resultRow.Path; 
                FileSize=$resultRow.Size; 
                Modified=$resultRow.LastModifiedTime.ToString("yyyy-MM-dd");
                InfoFound=$false; 
                Lat=$null; 
                Long=$null; 
                Alt=$null 
            }

        }

        [PSCustomObject]$fileInfo | 
            SELECT Url, Modified, FileSize, InfoFound, Lat, Long, Alt | 
            Export-Csv -Path $outputCsvPath -Append -Force -NoTypeInformation

        $totalCount++
    }

    $startRow += $pageSize
}

You might want to tailor the search query to operate on a different set.

If you provide the switch  -promptForCreds when you run the script, you will be prompted for credentials. This is helpful when the account you're running PowerShell under is not the one that has best access to the content.