6
\$\begingroup\$

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?

\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$ Mar 16, 2011 at 5:45
  • \$\begingroup\$ I'm not too worried about the photo association - I just want to heirarchy-ize the keywords, leaving them associated with their respective photos. \$\endgroup\$
    – ahockley
    Mar 16, 2011 at 14:16

3 Answers 3

8
\$\begingroup\$

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

\$\endgroup\$
3
\$\begingroup\$

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.

\$\endgroup\$
0
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ I don't think that solves the issue - because it'll just impact the keywords on individual images, rather than fixing the internal Lightroom keyword hierarchy. \$\endgroup\$ Jul 8, 2016 at 11:35
  • \$\begingroup\$ If the file metadata and the LR catalog metadata differ what happens? \$\endgroup\$ Oct 31, 2017 at 19:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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