A Tableau-Based Photo Viewer

Introduction

I have had an idea for about the past 20 years that I finally decided to complete using Tableau. What took me so long, you ask? Well, software technology had to catch up with my idea. It took me 20 years to collect the data and about a day to finish the first rendition of the idea.

Jett Black in his early days.

My idea is simple in design. I want to have a tool that can quickly and easily allow me to visualize the lifetime of my kids. I remember creating this idea in 1996 or so, which is when I got my first Kodak digital camera. I began building the data needed to fulfill my idea by taking and collecting the pictures and videos over many years. It is this collection of digital information coupled with Tableau that allowed me to bring my idea to fruition.

Background on the Idea

Many of us take thousands of digital pictures and videos through the years and store them in directories on hard drives. Some of these pictures and videos will become our favorites. The vision I had 20 years ago was that one day I would be able to pull together the best content into one place to visualize the lifetime of my kids. I always believed that this would become possible. The problem has been that I have never found a software platform that would allow me to do this in a reasonable time.

The Process

You can use a program like Picasa from Google to organize your digital content and to quickly pick your favorites. You can then export the files to a directory so that they are all in one place.

Once you have done that, you have to capture metadata information about the files such as the date and time the photo was taken and some other information about the photos themselves, especially if you want to do some analytics on the files. That seems like it should be easy to do. Well, it isn’t necessarily that easy and that is one of the reasons I’ve decided to write this article.

Fair warning: There is some technical stuff included in the videos that form this article. It took me a few attempts to get the steps right on how to do this. Since the level of detail is high, I decided to make some videos showing how it is done.

I have broken the process down into three videos to explain what you have to do. The first video explains the problem with getting the right date and time stamps from your files when you export them from Picasa. The second video shows you how to use Powershell to extract photo metadata and the correct date and times. The third video shows you my first rendition of my “lifetime photo viewer”.

Considering I did all the work in Tableau in a couple of hours, I’m pretty happy with my progress towards my final goal. I can now see the finish line on this project and the final dashboards for Sarah, Colton, and Jett are going to be totally awesome. I still have more work to do with respect to determining location data of the files and adding some additional content, but right now I’m satisfied with this burst of creativity. Tableau rocks.

Step 1 – A Statement of the Problem

Step 2 – Using Powershell to Extract Photo Metadata

Step 3 – The Tableau-Based Photo Viewer

Download the Tableau Public example shown in this article.

Technical Notes

Youtube rendering – You might find that your Youtube video links no longer render in a web page object in Tableau. I have no idea why. However, I found a way to make them work by changing the word “embed” to the letter “v”. I have no idea why this works, it just does. Here is an example:

https://www.youtube.com/embed/6Mary-q_upg <does not play in Tableau web page>

https://www.youtube.com/v/6Mary-q_upg <will play in Tableau web page>

WordPress rendering – The videos will not render at this time, although you can see the video by moving the time cursor from left to right. I’ll update this if and when a solution is found.

Where are the pictures stored? – In my example, I uploaded the photos to my WordPress blog site and used those links. I could have also just used my Google photo links if I wanted to. All the files you want to render must be on-line. Additionally, here is one note on how the file names are handled by WordPress when they are imported. If you have a space in the file name, the space will be replaced by a dash automatically by WordPress.

Powershell sources and example– totally great guys but they live deep in the community called Nerdville.

Getting started with Powershell.
Extracting metadata from photograph files.
The Jett Black metadata script. Make sure you create a subdirectory to hold your photo files within the directory you specify to process. In my case, I used the directory d:\wordpress\jett_black, but the files were stored within the subdirectory titled d:\wordpress\jett_black\images. The two items shown in red below are all that you need to change to make the script run.

Function Get-FileMetaData
{
Param([string[]]$folder)
foreach($sFolder in $folder)
{
$a = 0
$objShell = New-Object -ComObject Shell.Application
$objFolder = $objShell.namespace($sFolder)

foreach ($File in $objFolder.items())
{
$FileMetaData = New-Object PSOBJECT
for ($a ; $a -le 266; $a++)
{
if($objFolder.getDetailsOf($File, $a))
{
$hash += @{$($objFolder.getDetailsOf($objFolder.items, $a)) =
$($objFolder.getDetailsOf($File, $a)) }
$FileMetaData | Add-Member $hash
$hash.clear()
} #end if
} #end for
$a=0
$FileMetaData
} #end foreach $file
} #end foreach $sfolder
} #end Get-FileMetaData

$picMetadata = Get-FileMetaData -folder (Get-childitem d:\wordpress\jett_black -Recurse -Directory).FullName

$picMetadata |
Select ‘Name’,’Date taken’,’Date modified’,size,path,width,height,’camera model’,f-stop,’flash mode’,’iso speed’,’exposure time’,’focal length’ |
Export-CSV -Path d:\scripts\jett_black_Metadata.csv -NoTypeInformation

The Picture Collage

WordPress has come a long way in its content management system. You can throw a group of pictures together and have them rendered into one big collage as shown below. Can you find baby Kenny in the collage? If not, just click on the collage and it will be rendered into a slide show. All this is done automatically, with no effort on my part. Great job WordPress developers. This is getting to be fun again!