Birds Summary Collages

May 18, 2022

I've created new birds galleries! They are summary collage-style pages showing all the birds I've photographed in a calendar year either specifically at home, or everywhere.

The pages are sorted by the date each bird was first photographed, so for example for the yearly yard pages, you can see winter birds at the top of each page, then spring migrants, then generally more uncommon birds toward the end of each year.

Here are pages you can now view:

The above photo is a male Rufous Hummingbird in my backyard in 2022, which can be seen on the "All Birds" page, the "All Yard Birds" page, the "2022 Birds" page, and the "2022 Yard Birds" one as well.

Design

To create the pages, and to make updating them easy, I had to design and populate a database of all my photos’ metadata. A SQLite database works perfectly for this application. The database is written to and queried with Python, which has a great built-in SQLite library, and to handle reading the photo files’ metadata I’m using PyExifTool.

I’ve got a Python script to read a bunch of photos and write their metadata to the database (source code here). Then I have another Python script that runs a big SQL query to find the first, last, and best (highest-rated) photos for each "species" (source code here).

The Python script that generates the pages outputs markdown pages, which are saved to my blog git repository like the other static markdown pages. The regular blog build script writes the markdown out as HTML.

Dealing with Sexes and Subspecies

I had "species" in quotes above because, for completeness, the big SQL query finds any subspecies and/or sexes and treats them as different "species" for each of which the first, last, and best photos will be found. For example, the following are all each treated as a "species”:

To show images for all these possible permutations without the page becoming enormous, the collage pages are interactive. Each species shown on the page is a collapsible section that can be expanded to show these related images.

I currently have over 1500 keeper images of birds in the database, and running the Python scripts to run the big query and generate the pages, all six of them, takes less than a second in total. I was surprised at how fast it is.

Tagging Photos

I've always been tagging my photos with species names. I use a keyword of the form species:<species-name>. For this project, I needed to add an additional keyword for each sex for each species, for example species-sex:Northern Flicker (Red-shafted):male.

This will allow the database ingest script to easily create rows in the database for each species and sex tagged in every photo.

Overrides

I have more than a thousand old photos without species-sex: tags, and unfortunately I can't just write tags to the old photos because I cryptographically sign each photo for copyright purposes. It seemed like the best solution was to create a separate table in the database to override any given photo's species/sex records. This is useful for old photos without sexes tagged, and also for making corrections at a later date.

As part of the SQL query, overrides are done with a common table expressions (WITH <query> AS a SELECT <something> FROM a) ahead of the final query. Since SQLite doesn't support FULL OUTER JOIN I have to perform the equivalent with a LEFT OUTER JOIN and then another query with UNION — this is needed because some tagged species have no associated override row, some do have an override row, and some override rows have no associated original tag (where the override is adding a new previously untagged species).

Since overrides for species/sex were so useful, I added overrides tables to correct photo tags for star ratings and for geolocation for a few photos.

Manual Fixes

Once I had the overrides system working, I could begin fixing all my photos. Luckily there was only about a year and a half of old photos to deal with. After going through all of them and fixing species, adding subspecies information, adding sex information, adding missing species, and identifying which species are "incidental" (in the photo but out of focus or only partially visible) I had over 700 rows of species/sex overrides.

Outlook

Now that I am tagging all photos with both species and sex, I won't have to manually add many override rows to the database anymore. To keep the summary collage pages updated, I just have to run one script to ensure the latest photos are written to the database, then another script to generate the pages.

I'd like to somehow include all my favorite photos of each species on the page (not just the last highest-rated one), so that'll probably happen at some point.