Turn your picurl thumbnail folder into a powerful photo database with ImageQuery

You want to know which images you took in summer 2004 with your old OLYMPUS cam? When you need to run complex queries on your photo collection, picurl and ImageQuery are your dreamteam. This tutorial reveils the powerful search algorithms of ImageQuery and expects some knowledge on SQL, EXIF and IPTC.

Prerequisites

For this tutorial, we not only assume a fully functional picurl installation, you must also install the freeware !ImageQuery 1.4.4. Unfortunately, ImageQuery only runs under Windows and we don't know (yet) about similar tools for Linux and Mac OS X. But eventually we will integrate similar functionality to picurl.

Step 1: Thumbnails for the Thumbnails

The first step sounds a bit ridiculous: we need to insert EXIF thumbnails to our placeholder thumbnail files. This is because ImageQuery generates its preview images by extracting the EXIF thumbnails from the JPEG file header. If it doesn't find the desired data, no preview will be available. So, the following batch file does some pickapack processing: each placeholder thumbnail will be inserted into its own JPEG header. We use the great metadata processor exiv2 for this, which is already on your system as it is required by picurl.

Open up your text editor, copy-paste the text box below and save it as insert_thumbnails.bat in your picurl program folder.

rem @echo off

rem copy <image>.jpg to <image>-thumb.jpg
for %%I in (*.jpg) do copy %%I %%~nI-thumb.jpg

rem let exiv2 insert the thumbnails
exiv2 -it *.jpg

rem cleanup temporary thumbnails
del *-thumb.jpg

Open a DOS Box, cd to you picurl placeholder thumbnail directory and type insert_thumbnails. Now the thumbnails are fixed.

Step 2: Building an Image Query

Now you can launch ImageQuery, where the friendly Query Wizard already waits for you (if not, it is hidden under Tools -> Query Wizard). Each Query consists of three sections, which are represented by the Wizard Screens:

1. File Selection: Here you specify the image files to run queries on. In our case it's the picurl thumbnail folder.
2. Data Columns in the result set: This allows you to specify which metadata tags you want to have in the result set. As EXIF and IPTC each contain hundreds of tags, you may want to reduce your query to the popular ones like the EXIF tags Make,Model, DateTime?`.
3. Select a filter: just like in a normal database, you can build conditions here to restrict your image results. Your query can consist of multiple conditions that are joined by AND / OR. The ImageQuery help tells you more.

After finishing the assistant, your query is shown in textual form (upper window). You can directly edit the query in this window or copy/paste one from below.

When you are ready, just click the leftmost "Play"-Icon or hit Ctrl+E. Now ImageQuery executes your query and displays the results in the lower window.

Step 3: Working with the Results

After some searching time, ImageQuery will display the results as thumbnails. You can also switch to the list view (just filenames) or the detail view (all metadata tags that you selected in one list).

But now? You can also download the source images of your placeholder thumbnails with picurl. To do so, you need to create a new Result Action for picurl-download.bat.

Open Tools -> Settings in ImageQuery and go to the Actions tab.

Click the Add Button to create a new action.

In the appearing Dialog, type picurl Download as Menu text, then select picurl-download.bat as program and - most important - set $filename as Parameter. You can also assign a hotkey to your action by placing the cursor in the Hotkey field and pressing the desired keys.

To download your desired source images, select the corresponding placeholder thumbnails, right-click and choose Result actions -> picurl Download.

Inspiring Query Examples

Here are some inspiring Query examples, that you can directly copy'n paste to your ImageQuery Window. Don't forget to adapt the paths!

-- Select all photos taken by an OLYMPUS camera whose IPTC Keywords contain Barcelona.
SELECT Copyright,Keywords,Make,MakerNote
FROM "C:\picurl\placeholder-thumbs\*.jpg"
WHERE Keywords ~ 'Barcelona' AND Make ~ 'OLYMPUS'

-- Select all photos taken between april and june.
SELECT Name, Size, Caption, ByLine
FROM "C:\picurl\placeholder-thumbs\*.jpg"
WHERE (DatePart('month’, DateTimeOriginal) > 3) and (DatePart('month’, DateTimeOriginal) < 7)

Caveats & Known Issues

The powerful syntax of ImageQuery and the wealth of EXIF/IPTC tags also causes some caveats that you will likely stumble upon:

• Not every photo contains every metadata field: some cameras only write the most essential EXIF tags, others are more verbose. If a photo doesn't contain a DateTimeDigitized tag and you filter on this tag, it isn't included in the resultset. It's a good idea to inspect a sample image from your camera in e.g. IrfanView to see what tags are provided.
• First select, then filter: When you want to use a tag in your WHERE clause, you have to include it in the SELECT clause first.
• Don't forget quotes for strings: ...WHERE Keywords ~ Barcelona will raise an error, because you haven't quoted the string Barcelona. ...WHERE Keywords ~ 'Barcelona' is correct.
• Queries using file size or resolution lead to bogus results: You won't get any hits if you are looking for photos bigger than 10 MB or larger than 1024x768 pixels. This is because ImageQuery only looks at the placeholder thumbnails and determines their file information. So the filesize is always some kilobytes and the resolution a few hundred pixels. In future versions, picurl will insert the file information of the source image to certain EXIF fields of the placeholder thumbnail. This allows you to use criteria like source image resolution in your queries.
• ImageQuery is ***-lame: Probably, you/the Query Wizard included all EXIF/IPTC tags in the SELECT clause. For the sake of performance and usability, just SELECT the tags you really need.