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:


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)


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.


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


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!


One of the most efficient aspects of Lightroom is to be able to export your image for different uses.

Do you have a particularly prized photograph that you want to show on the web? Perhaps put on your website? Or maybe stick it in a book, or send it off to the printers?

With one click, you can do these things, and organise your images so that you don’t get confused.

So let’s start by looking at the different formats?

An image has two properties of importance. It’s size, and its resolution.

An image, for example that was 6×4 inches in size, but had a resolution of 1 pixel per inch, would result in a terrible quality image. So therefore, a rule of thumb might be to imagine that “more ppi” (pixels per inch) would be reasonable. However…

A computer screen cannot display more than 72ppi, so there is no point in uploading an image to your favourite image hosting site at 320ppi. What’s worse, is that if you did do this, you’d be putting your image up on the web in a quality that could be downloaded and printed in a magazine.

The filesize of an image is also important. A full size image could be anywhere upwards of 8-10Mb which would not only take a while to load on the page, but would also eat up any “limited bandwidth” options your image hoster may have. Likewise, if you own your own website, most hosting companies have limits on your “bandwidth” (the amount of data the server will “download” to browsers, usually on a per/month scale). An image set to 72ppi (or dpi – “dots” per inch from the days if ink printing) with a limit of 800 pixels on its widest side could weigh in at 200k, which is around 50x lighter than the full size image. It would also be useless to anyone wanting to copy it and put it on anything other than a postage stamp, or rebrand it on the web.

However, if you want to print an image out to paper (or in a book), you’d want anywhere from 320 – 400dpi, and want the size to be reasonable (at least look good on a 6x4in print).

I’m not going to go into optimum sizes right now, but you can see that there may be multiple ways to display your image, and each method involves resizing the picture.

Adobe Lightroom does it for you!

All you need to do, is set up a few presets, and save them, and you’re done.

Here’s how I do it. I’m not suggesting this is the best method, but it’s fast, and it works well for me.

Most image printers/hosts ask you for a folder when it comes to uploading your images for the web, or to print. So I have set up (on my desktop) five folders for the different Exports I need to do.

1. To Print. These images need to be full size, and at 320dpi in jpeg format

2. For Web. These images need to be max width 800pixels, and 72dpi in jpeg format

3. For a book. These images need to be full size, 320dpi and in tiff format (better quality than compressed jpeg)

4. For Photoshop. When I work on an image in photoshop, I export it in there. This allows me to keep the original completely safe, and in Photoshop, I’m only working from one folder.

5. For Photomatix. I’ve already discussed HDR in my HDR tutorial, but my HDR software asks for a particular folder when importing images, so by exporting from Lightroom into one location, I never have to keep searching for the right photo. Again, my original is untouched.

Now, when I need to send an image to print, I “Export to Print”. Exporting leaves your original image untouched, and sends a “copy” to the relevant folder. When I go to my printer, and they ask for the folder where my prints are, I don’t need to hunt through “My Pictures”, I can just direct it to the “To Print” folder and we’re done.

To set these export functions up, select an image and right click. You’ll see a menu come up and within that is an “Export” choice.

This should open the following screen:

Lightroom export window

Each section of the export function is well laid out. It’s just a matter of making your choice, then moving to the next section.

Export Location

This is where you specify the location you want to export your images to. This could be a folder on your desktop (for printing, or further work) or it could be a remotely mapped location. For example, Dropbox or online backup location. As you can see, you can rename your exported image and/or put it into a subfolder.

File Settings

This is a key entry that requests the type of file you want to export (export as a jpeg, tiff, raw file etc) as well as any quality settings (such as limiting the file size).

Image Sizing

This section allows you to specify the size of the image. It’s more useful when you’re exporting for a web display, as some sites have size restrictions. It’s important to note, that Lightroom will size to the shortest side. In the example above, 800px is set as the width & height limit. What this means, is that 1a 1600×400 panorama (for example) would be resized to 800×200. The original dimensions are kept safely bound. A 1600 x 3200 image would see the 3200 size reduced to 800, and the 1600 reduced accordingly (making the image 400 x 800 when exported)

Other Settings

The other settings, File naming, sharpening, metadata, watermarking, post processing etc, can all be altered to taste. I generally leave everything default. I’m not precious about hiding the metadata, or adding a watermark, and all my renaming occurs usually once everything is exported.

Once you’ve finished entering your settings, click “add” (bottom left). This allows you to rename your Export template to something memorable (see my list of examples above). These appear not only in this box, but you’ll also see them appear under the right click menu in Lightroom. So multiple image selection and export can be done very very quickly. Creating variants of an Export Template are extremely easy. Just select the one you want to create a variant of, make the changes, then click “add” and give it a new name. Setting up different web sized images as in the example above (600, 800 & 1000 pixel size) is a very swift job.

So that’t that! And if you don’t use Lightroom because you think it’s too expensive, then you may qualify for a Student edition. Have a read of my “Buying Lightroom or Photoshop as a Student” post, or pay a visit to the Adobe UK Education Store.

Posted by: In: Full Versions, Software 20 Jan 2011 1 comment Tags: , , ,

So, you want to purchase Adobe Photoshop electronically, yet you’re astounded by the costs involved?

It’s a lot of money, but there is one way to purchase it without a remortgage as long as you meet their education criteria.

The Adobe Education store allows all their products to be purchased as “student” editions, which are severely reduced in price. The only disadvantage with the purchase is that you can’t upgrade to the next version, but as a user of CS3 for the last 3 years, and Lightroom 2 for the last 2 years, I can say that by the time you’re ready for the next version, the cost won’t be so prohibitive if you continue to need this software.

You may be thinking that you don’t qualify for the Education discount. Think again…

Do you have a child in full time education?

Have you been thinking about attending a night school or class in Photography?

If you can answer yes to the above questions, you may be eligible for the Education discount.

If you have a child (16 or under, 18 if they’re doing A levels) in full-time education, then that child is entitled to a copy of an Education version of Adobe software. All you need is to get the school to send you a letter confirming the child is enrolled at that school.

My preferred option though, is to enroll at an evening class at the local college. I have taken three courses so far now, and you may be wondering why so many? Am I really that bad?

Possibly! But for me, the social aspect of the course, is just as much fun. You’re not only learning how to use your camera, but you’re spending time with people with like-minded interests. That can only be a good thing surely. No need to bore your Significant Other with talk of aperture, shutter speed, and all that techy stuff they hate listening to. Get yourself enrolled!

Now college courses range from tens of pounds to hundreds. I have just (2011) started an AS level (half an A level) for £250 across the academic year. However the first course I did was £90 (An NCFE Photography course, part 1!). When you add that to the cost of the Education version of Photoshop or Lightroom, you’re still paying well under the odds.

I’ll admit I was nervous on that first evening when I sat in a room with a bunch of strangers, but after time, we got to know each other, and I was hooked. I can’t imagine not doing a course in the evening once a week now. Other people on the course were the same. Some were vastly over-qualified, and yet they still went along purely for the social aspect.

So if you’re thinking that Adobe products are out of your reach, they’re probably not as far away as you thought.

In terms of actually purchasing the software, the process is extremely simple. Just visit the Adobe UK site, go to the “Education” store, and order the software. You then submit a copy of your student ID, or the letter from the school electronically, and a follow up email is sent with the activation codes. Or you can follow this link directly to the Adobe UK Education Store

If you don’t qualify, don’t panic. Adobe will refund your money, so you have lost nothing.