We like to use Adobe Lightroom to organize and archive photos. Lightroom uses SQLite 3 to store its internal catalog database. All the photos’ metadata are in the catalog database in one form or another. It’s not too hard to peer inside SQLite3 databases to see what information is stored and how, so I thought it would be interesting to poke around and see what I could learn.

Adobe has a Lightroom SDK available for download, but I’m not that interested in learning how to use the Lua scripting language. The SDK gives access to seemingly all the photos’ metadata, but it was enough for me just to extract information directly out of the SQLite3 database.

I learned a little about SQLite3 and started writing a Ruby script to extract some patterns about our photo habits: what kinds of cameras we’ve taken pictures from, what times of the year we take the most pictures, and how obvious are the “spikes” in photography around significant events in our lives.

I found a free tool called ImageReporter that already does a lot of this analysis, but not all. The author tallies up some results that I had not considered, but are interesting. For example, ImageReporter counts the number of pictures taken with various lenses, and for zoom lenses, what focal lengths they were set at. This could be really handy for someone who is considering purchasing a lens upgrade.

This is ImageReporter’s tally of the zoom settings on our most-used lens, a 17–85mm zoom.

	Count by Make, Model, and Lens
	Count by Focal Length (nearest 10mm)
	      8684	Canon / Canon EOS DIGITAL REBEL XT / 17.0-85.0 mm
		      1394    16%	 20mm
		      1324    15%	 30mm
		      1360    15%	 40mm
		       949    10%	 50mm
		       837     9%	 60mm
		       618     7%	 70mm
		       470     5%	 80mm
		      1732    19%	 90mm

We take the majority of our pictures in the wide-angle range of 20–50mm, so for the lens to get the most use, we should make sure we get something that covers that range.

Another interesting tally covers geocoded pictures. We have attached geographic information to a small number of our pictures, and here is an abridged list of some of the top hits. (Chesterton, Vertland, Wellington?!)

	Count by Make, Model, and Location
	       357	All Cameras /  
	       357	All Cameras /   /  /  
	     21007	All Cameras / [unknown]
	       306	All Cameras / United States
	       306	All Cameras / United States / Indiana
	         1	All Cameras / United States / Indiana / Broad Ripple
	         4	All Cameras / United States / Indiana / Carmel
	         4	All Cameras / United States / Indiana / Castleton
	        20	All Cameras / United States / Indiana / Chesterton
	         7	All Cameras / United States / Indiana / Vertland
	         6	All Cameras / United States / Indiana / Wellington
	       683	All Cameras / USA
	       678	All Cameras / USA / IN
	       241	All Cameras / USA / IN /  
	         3	All Cameras / USA / IN / Bloomington
	         1	All Cameras / USA / IN / Castleton
	        51	All Cameras / USA / IN / Fishers
	       116	All Cameras / USA / IN / Indianapolis
	       107	All Cameras / USA / IN / Muncie
	       159	All Cameras / USA / IN / Speedway
	         5	All Cameras / USA / WV
	         5	All Cameras / USA / WV / Charleston

The haphazard nature of the locations in the list expose the inconsistency in how I geocode photos and how the place names are tagged with different services.

Strangely, there is not a straightforward listing of all the camera makes and models in the catalog. The closest I could get required some massaging.

	Count by Make and Model
	       595	[unknown]
	     12690	Canon / Canon EOS DIGITAL REBEL XT
	        47	Canon / Canon PowerShot SX110 IS
	         5	FUJIFILM / FinePix 3800
	         1	FUJIFILM / MX-1700ZOOM
	       490	Motorola / ZN5
	         1	NIKON / E2200
	         6	NIKON / E990
	        13	OLYMPUS IMAGING CORP. / IR-500
	         4	OLYMPUS OPTICAL CO.,LTD / C3100Z,C3020Z
	       138	OLYMPUS OPTICAL CO.,LTD / C860L,D360L
	       433	Samsung Techwin / <VLUU L830  / Samsung L830>
	        17	SONY / CLIE
	      8113	SONY / CYBERSHOT
	         5	SONY / DCR-TRV340
	       329	SONY / FDMAVICA