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

Creative hauling

Who would have thought that you can still take home a dozen shrubs from the nursery if you’re already towing a pair of jetskis? Proof!


Bizarre Bluetooth data corruption

Here’s a bizarre example of Bluetooth data corruption that I came across when downloading a picture from my phone last week. It looks like a few packets must have been dropped or had undetected errors, causing misalignment of the rest of the data.


Here’s a close-up of the beginning of the corrupted area.


My second attempt to copy the picture from the phone was successful. I just found it surprising that the error was undetected, and that the JPEG still was decodable.

I found a couple of resources on Bluetooth data transport that might be interesting. I’d like to find some sort of characterization on the error rates in Bluetooth communication.

This is really strong!

To the person who put this post-it on the coffee maker at work: don’t be sorry, no problem here. I like it when my coffee actually tastes like something!


Panorama stitching with Photoshop

photoshop-iconPhotoshop CS4 includes a well-hidden tool (File → Automate → Photomerge…) that automatically stitches panoramic photos together. It’s surprisingly good! Things to keep in mind when you’re shooting by hand: keep the lens horizontal (don’t angle it up or down), lock the exposure and white balance settings, and don’t move! I violated my own guideline by angling the camera down toward the floor, so I had to do some manual work to de-warp the result, which added some other distortion. This is why Adobe prices Photoshop the way they do: to keep it out of the hands of amateurs like me.


Respect the Prius: The two dressers, hidden nightstand, and bed frame all fit inside the Prius, thanks to the wizards at Ikea, and the mattress set fit on top.

Big snow


Aftereffects of yesterday’s snow day in the parking lot at work!

Update: Bitter irony, in retrospect.

Photo statistics

Playing around more with the Google Charts API, I found some interesting insights into our photo albums.

  1. Cameras
    Still lots more pictures taken on the two Sony cameras we’ve owned: a CyberShot DSC-S70, and a CyberShot DSC-F707. The Canon Digital Rebel XT is next by volume, and it is our primary camera. We also use a Samsung L830 and a Motorola Motozine.
  2. Years
    We started dating in 2001 and were married in 2002. It was new and exciting to have a digital camera, but then our camera use settled down for the next couple years. David was born at the end of 2005, and surprise, the number of pictures doubled!
  3. Months
    I think this chart speaks a bit to our seasonal happiness levels: February is the depth of the wintertime experience, and there is less to be excited about, or to take pictures of. As spring emerges, the number of photo-worthy events increasesand keeps going through the summer. Fall then arrives, and we start to withdraw into our hibernation routine. December brings a spike because of the holidays, and there is some inertia that carries through to January. And then our cycle repeats.
  4. Weekdays
    Google Chart
    This one isn’t too surprising, either. Naturally, we take more pictures on the weekends!

So there are some fun insights into our picture-taking habits.

Camera cleaning for cave men

I took a picture of a pretty sunrise last week with our Samsung point-and-shoot, and when I pulled it into Lightroom off the camera, it had a big nasty black glob just to the left of the sun. It was easy enough to fix in Photoshop, but I needed to fix the cause of the glob. 


No problem, I thought, I’ll just clean the lens. Well, the lens was clean. The glob was on the interior of the lens assembly. Getting foreign objects into a lens is always a possibility with lenses that extend and retract, since air gets sucked into the assembly when the lens moves. Without cleaning the lens assembly, this glob was going to be on every picture the camera would take until the end of time. 

I could have taken it in to a local camera repair shop to get professionally cleaned, but I have to guess that I wouldn’t get it back for less than $50, which is nearly what I paid for it three months ago. We got this camera for a steal, but I still wanted it to work well. I checked the warranty information, but I’d have to send it to Skokie to a place I’ve never heard of before, and the warranty says it doesn’t cover routine cleaning. I’m pretty sure foreign objects inside the lens assembly does not count as routine, but would I have to pay postage both directions and then fight that fight? Not worth it. It’s sad when you have to assume a valid warranty won’t be worth the hassle.

I like fixing things and taking things apart, but I’ve acknowledged that there are limits to what I can re-assemble. I could go in with a miniature screwdriver, dismantle the camera, and then clean it myself, but there’s a good chance I’d make it worse. Cameras are assembled in very clean environments for the exact reason I am trying to get rid of.

On a whim, I thought that maybe I could jostle the particle out of the way. So I banged the camera on a desk a couple times, but no success. I turned the camera on an angle and then banged quite a bit harder once or twice, and voila, clean images again! There’s still a mystery dingleberry inside the lens somewhere, but it’s not in front of the sensor anymore!

Moral of the story: to clean a delicate optical instrument assembled in a clean room, bang it on a table hard enough to make you nervous.