Find Out More About Your Photography: The Lightroom Database

Posted by: In: Computers 29 Sep 2011 Comments: 2 Tags: , , ,

I will get around to writing my article about WordPress – honest! It’s just that this week I’ve been obsessing a bit about my photography. And when I say obsessing, I mean it.

Looking back over time, I’ve bought lenses on a whim, because people said they were the best, because I thought it would improve my photography, and various other reasons. However, when it comes to my next purchase, I want to buy something I need rather than want.  So I began to wonder whether there was a way to look at my images and see what focal lengths I was shooting at. After all, no point in getting a 400mm f4 if I never shoot over 35mm.

After a fair bit of Googling, I found out how to do it. And I discovered that out of the 2,200 images I’ve shot since January, 39% have been with my 50mm/85mm primes, 22% with my 10-20 wide angle, 14% with the 70-200 & 25% with my 24-105. This is interesting in itself, and I find myself wondering whether the 70-200 f2.8L – as the most expensive lens in my bag – would be better off traded against a better lens in the shorter focal length ranges.

Further analysis based on this shows that 85% of my shots are taken at 105mm and below – and with my 10-20mm having just come back from three months away at Sigma, I can only see this rising. Maybe the 16-35 f2.8 would have been a better choice!

I pulled this data from the Lightroom database. Every shot you take and import into Lightroom is catalogued in an SQLite database. That might not mean much to most photographers, and to be honest, it doesn’t need to mean much. But if you get curious, it means you can extract useful information from that database.

All you really need is a working knowledge of Excel (or some similar program) and half a brain. Best of all, it’s free!

Here’s how to go about it.

The Lightroom Database

First up, you really don’t want to mess with your live database. Unless you know what you’re doing I STRONGLY urge you to copy it to a safe location.

Lightroom stores all your photo information in a Catalogue. You can find out where this is by opening Lightroom, and going to Edit>Catalogue Settings>General tab. This will show you the location of your catalogue file – which is really a database.

The Lightroom Catalog Settings window

Open windows explorer and navigate to that location. Right click and COPY your catalogue then paste it to another folder somewhere safe. Don’t drag it, as it may just move the location rather than copy it.

You then need to download a program that can read SQLite databases. I use this one. It’s free. If you don’t want to use this, Google search for “SQLite Browser” or somesuch.

Extract the downloaded zip file to the same safe folder you’ve got your copied database. Within the extracted files, you’ll find an executable (.exe) file. Click this to run the program.

Once it’s open, you can then do File>Open Database and navigate to the COPY of your Lightroom database. Click “Open”

Reading the SQLite Browser for Lightroom

So now you’ve got a window of what appears to be nonsense. And this is what a database looks like. The good news is that the browser makes navigating through the database quite easy.

Every photograph is catalogued with a series of numbers representing various things. Understanding this matrix allows you to extract an awful lot of information about your photographs. Go to the “Browse Data” tab and look at the dropdown menu next to “Table”. Pick AgLibraryCollection. Here you can see some things that begin to make sense.

SQLite view of the Lightroom Library Collection Window

Any image you have tagged with “5 Stars” for example has a local id of 6. So you could query your database to see how many images you’ve 5 starred.

So how does this help?

Well – if you export a table to a .csv (comma separated variable) file, it can be opened in programs such as Excel, where you can then filter, count, analyse and graph your photographic habits.

The tables I’ve found of use are:

AgInternedExifCameraModel

Here you can see the local id assigned to every camera that has taken a photo that is in your database. I have imported stock images from magazine cds, as well as images taken by other people in my Lightroom Catalogue, so there are a lot of shots from different cameras. As you can also see, there are two entries for a Canon 50D in there. One of them is mine, one of them is someone elses. So this is a good place to start looking at your database, as each photograph will be embedded with the local id of the camera. My Canon 50D has a local ID of 1950. (I know this because 2,158 of the images in my database are taken with it)

AgInternedExifLens

This is the lens data. So your own lenses will be on here, as well as any lenses you’ve tried in a shop (as long as the image was imported into Lightroom) and also any lenses used to take stock images from magazine cds. As you can see from this list, a 300-800 lens is in there! It was used to take a stock image of the moon for a photoshop tutorial illustrated in a magazine. By making a note of the local ID of the lens, you can now query the database for that too.

AgHarvestedExifMetadata

So this is what it’s all about (click the thumbnail for a larger image). This lists all your images along with all the EXIF data stored alongside it. This includes things like focal length, shutter speed, lens used, camera body used etc. As you can see, under CameraModelRef and LensRef, only a number is presented. But that number correlates to the local id of the camera and lens in the above two tables. You can see now that by exporting this table to a program like Excel. You can filter against a particular body and/or lens to see which is more popular. You can also graph all the focal lengths to discover which focal length you shoot at most.

You may have noticed that the Aperture and Shutter Speed refer to something other than the actual shutter speed and aperture in a way you understand it. This is because the EXIF data is represented using APEX values rather than real ones. I invite anyone with a stronger grasp of mathematics than I to read the Wikipedia article on this here or have a browse through this pdf by Doug Kerr. If you want to skip all that stuff though, these can be calculated back to real numbers in Excel using the following formulae:

Real Shutter Speed = 1/(2^s) where “s” is the shutter speed given in the EXIF data

Real Aperture = (SQRT(2))^a where “a” is the Aperture value in the EXIF data

Adobe_imageDevelopSettings

This last table is mainly for a bit more fun if you like this kind of thing. It will show you how many images you’ve cropped (in Lightroom). On it’s own, it’s of limited use, but if you tie the local id of the image, to the EXIF data above, you could begin to see how you shoot. For example, if you’re cropping a lot of shots with a 50mm lens, maybe you want to take an 85mm out with you more often. You can even work out the percentage reduction to get a more in depth analysis of your own shooting habits. In this example, rows 743 & 744 have cropped images. You can see the original size as well as the cropped size.

Analysing the Lightroom Database

So all of this is very interesting, but how do you go about analysing it? If you know SQL, the browser program allows you to write a query, but I don’t so I export it to play around with in Excel.

Initially, you need to export the AgHarvestedExifMetadata to a csv file. To do this, in the SQLite Browser program, simply choose File>Export>Table as csv. You then get a pop up asking which table you want to export (chose the Exif metadata one), give it a filename (not forgetting to add the .csv extension!) and save it to a folder. You can then open it in Excel.

Once it’s in Excel, depending on your knowledge of the program there are several things you could do.

– You could simply Find & Replace instances of the CameraModelRef with the real camera name (for example, replace all instances of 1950 with “Canon 50D). Same goes for the lens.

– You could do “CountIf” statements to count how many images (and subsequently what percentage) were shot with a particular lens.

– You could just select the whole “focalLength” column and put it in a chart to see what focal lengths you shoot at. (If you’re anything like me – with a zoom lens, you’ll find the majority of your shots are at either end of the zoom rather than in between)

– You could look at the Date columns to determine when you shoot more photographs.

I am currently working on a macro driven Excel sheet (above) to pull data based on a series of option boxes. It will probably have graphs, charts and a lot of analysis. But that’s just my idea of fun. This may not be for everyone! I can now turn out graphs like this!

 

Mildly exciting!

If you are interested in a copy of the Excel workbook (and macros) when it’s finished, please pop along to the Facebook page and leave a message. If I get enough thumbs, I’ll post it up for download.

Have a good weekend!

-Sc

Comments: 2

  1. Posted by David Naylor 18 Aug 2013 at 7:05 am Reply

    Hi! I’m surprised you haven’t had any comments on this post – a great read! Just like you I’m both a camera and statistics geek. I came across your post while searching for ways to analyse or graph the Lightroom catalog. I also came across this, which may interest you: http://lightroomanalytics.com

    Sadly it claims to only cope with ~2500 images at a time.

    • Posted by Ian 24 Aug 2013 at 10:19 pm Reply

      That’s an interesting link for sure! Thanks for taking the time to add it. I guess the only downside is that the analysis isn’t a standalone program and one is reliant on the lranalytics team keeping their servers up!

      Ian.

Leave a Comment!

Your email address will not be published. Required fields are marked *

Related Posts