Thousands of Photos written 8 years ago

Camera Lens

A short ten minute wait for the hourglass to stop spinning reveals that I’ve over 19,000 photos on my computer.

Being the hoarding type Yes I keep the boxes that my small technological things come in I don’t like to delete things Much to the girl’s annoyance. Disk is cheap, and by the time you’ve filled up a drive, storage has quartered in price and now has four times the capacity. So how to organise this mass of blurry pictures of my feet?

Well it’s all very web two point oh.

My stick of choice to do the organising of the troops is Photoshop Elements Organiser (Long time readers will recall how enamoured I am with PSE). And the crux of it all is the import photos doodad:

All photos imported have at least one tag attached to them, are stowed in an appropriately dated and named folder and get added to an album. The magical automaton doesn’t stop here though, as each photo will then be scanned for red-eye and fixed (saved as a TIFF for maximum quality), and then the photos automatically “stacked”. Stacking groups like photos together, so that instead of 6 dubious photos of a swan appearing in the organiser you have only 1 (presumably the best one).

Once we’ve got all the badgers imported, you add as many tags as you like, and do all your fixing jobs. Spiffing.

Now when it comes to looking at the photos; you’ve still got sixteen squillion pictures. And even though you took sixteen photos of the swan, it’s still not something you’d want to show an easily excited toddler, for fear of it’s mum reporting you to the police.

You need to work out which are your “good” photos.

PSE helps once again, by providing star ratings allowing you to weed down your pics by gradually applying more stars to the better photos until you’ve highlighted the key two picts. What I do now is add a “Pick” tag to the photo, so I know it’s a goodun. Then when I’m doing a slideshow for little timmy, I just search for all photos tagged with Pick, and away we go.

We’re not done quite yet though.

It turns out that the database used internally by PSE is just a regular old SQLite database, and such can be queried at the command line with an arbitrary string of SQL. This means that with a well crafted piece of SQL we can extract the filenames of all our Pick tagged photos, and then do with them what we please. I’ve cunning plans for a script that might one day add them to a wirelessly connected photo frame, but there’s much selotaping of laptops inside photo frames before that becomes a reality.

← previous entry | next entry → Fri 17th Apr 2009 - 18:51 | 1 comment | tagged with Photography

 Comments 1 comment made

For extra credit, here’s the SQL to extract photos tagged with “Pick”:


    select drive_path_if_builtin || full_filepath 
    from volume_table v, media_table m, tag_table t, tag_to_media_table tmt, version_stack_to_media_table vs
    where t.name='Pick' and 
          tmt.tag_id = t.id and
          m.id=tmt.media_id and
          v.id=m.volume_id and
          vs.media_index=0 and
          vs.media_id=m.id
    UNION ALL
    select drive_path_if_builtin || full_filepath 
    from volume_table v, media_table m, tag_table t, tag_to_media_table tmt, media_stack_to_media_table ms
    where t.name='Pick' and 
          tmt.tag_id = t.id and
          m.id=tmt.media_id and
          v.id=m.volume_id and
          ms.media_index=0 and
          ms.media_id=m.id
    UNION ALL
    select drive_path_if_builtin || full_filepath 
    from volume_table v, media_table m, tag_table t, tag_to_media_table tmt
    where t.name='Pick' and 
          tmt.tag_id = t.id and
          m.id=tmt.media_id and
          v.id=m.volume_id and
          m.id not in (select media_id from media_stack_to_media_table 
                        UNION ALL
                       select media_id from  version_stack_to_media_table);

It’s pretty terrible, but seemingly functional :)

Leave a reply

Name: displayed next to your comment
Email: will not be displayed
URL: to link back to your site
Comments: Markdown syntax allowed

Notes on leaving replies: