How do I clean up keyword hierarchies in Lightroom?

Question

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?

Answer 1

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 :)

Answer 2

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.

Answer 3

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.