Time to be with your loved ones

Time to be with loved ones

by sat

submit your photo


Hall of Fame
View past winners from this year

Please participate in Meta
and help us grow.

Take the 2-minute tour ×
Photography Stack Exchange is a question and answer site for professional, enthusiast and amateur photographers. It's 100% free, no registration required.

I've been using Lightroom for a few years. At one point I kept keywords in a hierarchy, so I'd have something like:

Animals > Birds > Eagles

This worked well, I could include the option to export parent keywords and all was good. Then I got lazy and started tagging things all over the place. So whereas my "Eagles" tag is nicely under Animals and Birds, I might have a Falcons or Parrot keyword that stands on its own and doesn't have the parental structure.

Rearranging the keyword hierarchy within Lightroom is very time-consuming... dragging and dropping isn't so quick when there are thousands of items in play.

I know that Lightroom has the ability to export and import keywords, but I'm struggling with the best way to perhaps clean this up. Is there a good way to manipulate the keywords externally and then bring that back into Lightroom?

share|improve this question
    
Oh boy I feel your pain. I'm in a similar situation, after importing thousands of JPEG files I had properly (and heavily) tagged on Flickr. The keyword UI in LR is really difficult and slow to use, even with a hierarchical structure. Keywords are, however, stored just like the rest of LR's data, in its SQLite database (i.e. the LR catalog). I contemplated writing a small app in Qt to manipulate the keywords, but the issue is that I also need to see the corresponding photos when I cleanup or reorganize my keywords, say, to see if that keyword is really needed or properly applied for example. –  sebastien.b Mar 16 '11 at 5:45
    
I'm not too worried about the photo association - I just want to heirarchy-ize the keywords, leaving them associated with their respective photos. –  ahockley Mar 16 '11 at 14:16
add comment

3 Answers

up vote 6 down vote accepted

Like Sebastien mentioned, the library file is SQLite. I dug around in my own catalog and found that AgLibraryKeyword table. There are three columns you care about: id_local, name, and parent. As you might expect, parent points to the id_local of the parent keyword. In my library the "root" keyword has id_local 73.

So, assuming you had something like this:

  id_local | name    | parent
-----------+---------+--------
  12345    | animals | 73
  12346    | birds   | 73
  12347    | eagles  | 73
  12348    | parrots | 73
  12349    | falcons | 73

You could "fix" the hierarchy like so:

UPDATE AgLibraryKeyword
    SET parent = (SELECT id_local FROM AgLibraryKeyword WHERE name = 'animals')
    WHERE name = 'birds';

UPDATE AgLibraryKeyword
    SET parent = (SELECT id_local FROM AgLibraryKeyword WHERE name = 'birds')
    WHERE name IN ( 'eagles', 'parrots', 'falcons' );

This would end up as Animals > Birds > Eagles, Animals > Birds > Parrots, and so on. The table ends up looking like this:

  id_local | name    | parent
-----------+---------+--------
  12345    | animals | 73
  12346    | birds   | 12345
  12347    | eagles  | 12346
  12348    | parrots | 12346
  12349    | falcons | 12346

It's not as manual as doing it by hand through the UI, but it's still going to take some work. Should be just as functional though.

I should note that while investigating this, I made similar changes a few times that didn't seem to take. I'd suggest saving your SQL to a file so you can re-run it if it doesn't seem to work the first time. And, of course, you should back up your library ahead of time :)

share|improve this answer
add comment

I hope my answer will not be perceived as advertising.

Recently our company released a new digital photo management software named Daminion.

Daminion and Lightroom can easily interact by XMP: both tools could read/write hierarchical keywords. But Daminion is a pure photo management software, so we focused on media management tasks mainly.

The program is free (because it's in a beta stage). I suggest you to:

  1. Write your keywords to metadata from Lightroom
  2. Import your folders with photos to Daminion
  3. Reorganize your keywords as you wish in Daminion.
  4. Daminion will automatically wrote all of your keywords (as well as other 30+ tags) changes back into the XMP metadata
  5. Return to LR and re-read the file's metadata

Hope this helps.

share|improve this answer
add comment

If you want to edit the hierarchy using SQLite as suggested in bleything's answer, note that (at least in LR4; not sure about 3) the keyword hierarchy is actually represented in two different places in the AgLibraryKeyword table:

sqlite> select id_local, parent, genealogy, lc_name from AgLibraryKeyword;
[...]
1757015|989984|/240/6802186/6989984/71757015|karen people
1757016|1757015|/240/6802186/6989984/71757015/71757016|red karen
1757018|1757016|/240/6802186/6989984/71757015/71757016/71757018|kayan people
1757019|1757018|/240/6802186/6989984/71757015/71757016/71757018/71757019|kayan lahwi

The genealogy column is a full dump of the nodes of the hierarchy, with nodes delimited by forward slashes and with the first digit of each entry indicating the number of digits in the node's ID. In the excerpt above, the root node is ID 40, the top-level keyword in my hierarchy is ID 802186, etc.

You will probably want to make sure that column as well as the parent column is updated correctly if you're manually manipulating the hierarchy in SQLite.

Why they include the number of digits in the node ID is unclear to me; my best guess is it's to support LIKE queries to quickly return subtrees without risking ambiguity between IDs of different lengths, but they could just as easily have done that in less space by requiring an ending delimiter, so maybe there's another reason.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.