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 then clicking the "Show" box. This will show you the location of your catalogue file - which is really a database. You're looking for the file that ends in .lrcat and not the .previews one.
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 SQLiteBrowser. It's free. If you don't want to use this, Google search for "SQLite Browser" or similar.
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 install the program.
Once it's open, you can then do File>Open Database and navigate to the COPY of your Lightroom database. You may need to look for "All files" rather than just database files, as the Lightroom catalogue has an .lrcat extension. 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 AgHarvestedExifMetadata. Here you can see some things that begin to make sense..
Every photograph is listed here and you can see in each column that there's a header corresponding to a bit of metadata.
There's also AgInternedExifCameraModel which allows you to 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, screenshots, as well as images taken by other people in my Lightroom Catalogue, so there are a lot of shots from different cameras.
AgInternedExifLens shows 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. By making a note of the local ID of the lens, you can now query the database for that too.
But it's AgHarvestedExifMetadata that's the important table. 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.
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.
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 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 or similar spreadsheet program.
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 do the same for aperture & shutter speed.
While we're on this subject, 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 (lost the link), 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 (you can copy/paste straight into a cell. Just replace s/a with the shuuter speed/aperture cell):
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
- You could look at the Date columns to determine when you shoot more photographs.
