Pages: [1] 2   Go Down

Author Topic: Can the LR database be queried directly?  (Read 2820 times)

dreed

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1619
Can the LR database be queried directly?
« on: August 04, 2014, 05:53:20 AM »

I'm thinking about buying a new lens but I'm not sure which lens I should buy.
Why don't I know which lens I should buy? Because I don't know which focal lengths I use most of the time - I figure if I'm smart about buying equipment I should know what's important to me. So how does LR fit into that question, you ask.

What I'd like to do is query the LR database and find out which focal lengths I use the most. If I think about it a bit more, I could probably do some more interesting projections such as frequency of IS/VR being on at slow shutter speeds and so on. In the LR database is a tale of how I shoot and that information would seem to me to be the perfect way to help me choose where I will get the best value for $ spent.

Are there any tools or methods to query the LR database directly without using LR?
Logged

francois

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 8139
Re: Can the LR database be queried directly?
« Reply #1 on: August 04, 2014, 06:49:00 AM »

You might find something useful on Jeff Friedl's website, here. If you're technically inclined, then you can try to browse and extract data directly from [a copy of your] .lrcat Lightroom catalog file using either the terminal or SQLite manipulations tools.
Logged
Francois

elied

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 333
Re: Can the LR database be queried directly?
« Reply #2 on: August 04, 2014, 07:10:24 AM »

Why outside LR when it is so easy to do inside LR?

Make a collection of, say, a year's worth of shots. Select all. Open the Metadata filter and in one of the four cells make the parameter Focal Length. Finished. Go buy that new lens.
Logged
Roll over Ed Weston,
Tell Ansel Adams the news

Simon Garrett

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 688
Re: Can the LR database be queried directly?
« Reply #3 on: August 04, 2014, 07:26:46 AM »

I agree that you can probably do it within LR, but if you need plugins, as well as Jeff Friedl's site, another source of techy plugins is Rob Cole: http://www.robcole.com/Rob/ProductsAndServices/
Logged

dreed

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1619
Re: Can the LR database be queried directly?
« Reply #4 on: August 04, 2014, 07:44:57 AM »

Why outside LR when it is so easy to do inside LR?

Make a collection of, say, a year's worth of shots. Select all. Open the Metadata filter and in one of the four cells make the parameter Focal Length. Finished. Go buy that new lens.

That's the long way around and requires me to effectively do 284 catalogues for every value between 16 and 300 (think zoom lenses.)

I suppose I could split it up and do ranges such as 24-70, etc, but that seems like a very primitive and arduous way to construct a query.
Logged

john beardsworth

  • Sr. Member
  • ****
  • Online Online
  • Posts: 3736
    • http://www.beardsworth.co.uk
Re: Can the LR database be queried directly?
« Reply #5 on: August 04, 2014, 07:49:16 AM »

Do you know SQL? If so you can use something like the SQLite Database Browser utility or there's an open source ODBC driver so you could query LR's catalogue from any database or spreadsheet.

Alternatively, try my ListView plugin which exports the data to Excel where you can easily analyse it.

John

john beardsworth

  • Sr. Member
  • ****
  • Online Online
  • Posts: 3736
    • http://www.beardsworth.co.uk
Re: Can the LR database be queried directly?
« Reply #6 on: August 04, 2014, 07:54:32 AM »

"What I'd like to do is query the LR database and find out which focal lengths I use the most."

And re-reading your post, exactly as Elied said- why not use LR's Library Filter? Focal length is one of the parameters.

John

Hans Kruse

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1389
    • Hans Kruse Photography
Re: Can the LR database be queried directly?
« Reply #7 on: August 04, 2014, 07:57:43 AM »

I'm thinking about buying a new lens but I'm not sure which lens I should buy.
Why don't I know which lens I should buy? Because I don't know which focal lengths I use most of the time - I figure if I'm smart about buying equipment I should know what's important to me. So how does LR fit into that question, you ask.

What I'd like to do is query the LR database and find out which focal lengths I use the most. If I think about it a bit more, I could probably do some more interesting projections such as frequency of IS/VR being on at slow shutter speeds and so on. In the LR database is a tale of how I shoot and that information would seem to me to be the perfect way to help me choose where I will get the best value for $ spent.

Are there any tools or methods to query the LR database directly without using LR?


I think you will get a pretty good idea using just LR to do the search. IS/VR is not recorded in EXIF as fa as I know. You could use a query tool that works with SQLite, e.g. http://razorsql.com/download_mac.html byt you would then need to find out what the values in the various tables and columns mean. Good luck, but I think a simple search in LR as suggested is the easy way to find out. Besides that is the criteria only what you have used?

dreed

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1619
Re: Can the LR database be queried directly?
« Reply #8 on: August 04, 2014, 09:14:12 AM »

So this is what I did..

- downloaded and installed sqlitebrowser 3.2.0
- copied the .lrcat file to a new location, set it to read-only
- started up sqlitebrowser
- opened the Lightroom lrcat file with sqlitebrowser
- looked through the tables, found AgHarvestedExitMetadata
... don't need to export data, sqlitebrowser can do them natively ...
- run a query:  select count(focal) as mycount,focal from exif group by focal order by mycount;
- top 10 focal lengths used by me are:

    1228 |       70+/++
    1321 |       35*
    1405 |       28+
    1589 |      105++
    1722 |       24+
    1725 |       50*
    1864 |       85*
    1971 |       40++
    2229 |      300++
    4638 |       17+

* - prime lens
+ - widest focal length
++ - narrowest focal length

Interesting!
« Last Edit: August 04, 2014, 10:36:21 AM by dreed »
Logged

PeterAit

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 2561
    • Peter Aitken Photographs
Re: Can the LR database be queried directly?
« Reply #9 on: August 04, 2014, 10:13:49 AM »

That's the long way around and requires me to effectively do 284 catalogues for every value between 16 and 300 (think zoom lenses.)

I suppose I could split it up and do ranges such as 24-70, etc, but that seems like a very primitive and arduous way to construct a query.

Not arduous at all. In less than 30 sec I got LR to display a list of all focal lengths I used, down to the precise mm for zooms, and the # of photos taken at each. Just display the metadata tab in Catalog view and select camera info from the filter list. A few minutes scanning this list tells me, for example, that I took lots of photos at 200 (the max for my favorite zoom), lots between 24 and 40, not so many between 40 and 60, and so on. Why make life more complicated with some special database access?

I will suggest that you limit your analysis to photos you like. You might have taken tons of photos at 35mm, but if very few of those "made the cut" that tells you something.
Logged
Peter
"Photographic technique should always be a means to an end and never the end itself."

dreed

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1619
Re: Can the LR database be queried directly?
« Reply #10 on: August 04, 2014, 10:15:06 AM »

I think you will get a pretty good idea using just LR to do the search. IS/VR is not recorded in EXIF as fa as I know. .... Good luck, but I think a simple search in LR as suggested is the easy way to find out. Besides that is the criteria only what you have used?

The Library Filter is a good tool for this except that it doesn't allow me to sort the columns based on frequency of a value appearing (it sorts by focal length but not how many images match a focal length nor can I make it sort on that count.)
« Last Edit: August 04, 2014, 10:35:07 AM by dreed »
Logged

Hans Kruse

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1389
    • Hans Kruse Photography
Re: Can the LR database be queried directly?
« Reply #11 on: August 04, 2014, 12:14:31 PM »

The Library Filter is a good tool for this except that it doesn't allow me to sort the columns based on frequency of a value appearing (it sorts by focal length but not how many images match a focal length nor can I make it sort on that count.)

See the screen shot I did where you see how many pictures were shot at a given focal length. And true, you can't sort on the count. I also would suggest to do the query on the highest rated of your pictures. Also it would make sense to take the crop factor the camera into consideration. In my screen shot the cameras were limited to my own full frame cameras.

Hans Kruse

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1389
    • Hans Kruse Photography
Re: Can the LR database be queried directly?
« Reply #12 on: August 04, 2014, 12:21:33 PM »

That's the long way around and requires me to effectively do 284 catalogues for every value between 16 and 300 (think zoom lenses.)

I suppose I could split it up and do ranges such as 24-70, etc, but that seems like a very primitive and arduous way to construct a query.

Sorry to ask, but why do you have 284 catalogues? Why not a single catalogue?

FranciscoDisilvestro

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 988
    • Frank Disilvestro
Re: Can the LR database be queried directly?
« Reply #13 on: August 05, 2014, 07:02:30 AM »

There is a lot of information in the LR database and the EXIF information from the image (including VR info, at least in Nikon cameras) and it is possible to create interesting queries beyond what is possible with LR.

It should be even possible to create an XMP file for a raw image querying the LR database. You can also get an ODBC driver for SQLite and query the LR database from any ODBC compatible database and even in MS excel, where you can perform additional analysis on the data.

E.G. you could analyse if there exists any correlation between edit parameters and camera / lens combinations and create presets based on that info, something like any time you use camera x with lens y at ISO z you find out that you use the same clarity, sharpening, noise reduction, etc.

dreed

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1619
Re: Can the LR database be queried directly?
« Reply #14 on: August 05, 2014, 09:21:17 AM »

Sorry to ask, but why do you have 284 catalogues? Why not a single catalogue?

I think I meant to say "collection", not "catalogue".
Logged

PeterAit

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 2561
    • Peter Aitken Photographs
Re: Can the LR database be queried directly?
« Reply #15 on: August 05, 2014, 11:27:05 AM »

Dear dreed,

With all due respect, I think you are making way more out of this than it deserves. In the time it has taken you to write all your posts, you could have used LR to determine which focal lengths you use most, using the steps that I and others have posted, and you would currently be shopping for that new lens.
Logged
Peter
"Photographic technique should always be a means to an end and never the end itself."

dreed

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1619
Re: Can the LR database be queried directly?
« Reply #16 on: August 06, 2014, 05:41:16 AM »

With all due respect, I think you are making way more out of this than it deserves. In the time it has taken you to write all your posts, you could have used LR to determine which focal lengths you use most, using the steps that I and others have posted, and you would currently be shopping for that new lens.

Owing to my IT background, when I see a database, I see data that is waiting to be manipulated and used. Where you see a precise list of lens focal lengths sorted on mm with a usage count, I also see a usage count that's unsorted associated with focal length.

I also see the potential to export that data into graphs that represent (focal length, aperture, shutter speed) or (iso, aperture, shutter speed) that allow me to visualise how I shoot. I suspect that there's untapped potential for data mining in that LR database.
Logged

Simon Garrett

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 688
Re: Can the LR database be queried directly?
« Reply #17 on: August 06, 2014, 05:58:46 AM »

Owing to my IT background, when I see a database, I see data that is waiting to be manipulated and used. Where you see a precise list of lens focal lengths sorted on mm with a usage count, I also see a usage count that's unsorted associated with focal length.

I also see the potential to export that data into graphs that represent (focal length, aperture, shutter speed) or (iso, aperture, shutter speed) that allow me to visualise how I shoot. I suspect that there's untapped potential for data mining in that LR database.


Never get in the way of a techy and his or her goal (I speak as a techy)!

You may be able to get almost 100% of what you need from LR directly, but if there's a way of querying the database (preferably using low level, geeky APIs) then the techy will be in there!

I've written a number of Android apps for my phone.  None of them do anything not done by other apps, but they do it my way, and more importantly I did it!
Logged

john beardsworth

  • Sr. Member
  • ****
  • Online Online
  • Posts: 3736
    • http://www.beardsworth.co.uk
Re: Can the LR database be queried directly?
« Reply #18 on: August 06, 2014, 06:06:17 AM »

I suspect that there's untapped potential for data mining in that LR database.

No kidding! Lightroom only exposes a fraction of the information that could be useful, while in Aperture so much more of the information in the catalogue could be searched without resort to plugins or SQL. Why shouldn't we be able to search for images with a certain lens which don't have a CA or other lens correction, find which high ISO images haven't received a noise reduction adjustment, or all the pictures in a job which haven't been printed, all the pictures which look black and white? The data's all in there, even manufacturers' proprietary "maker notes" if you look hard enough.

Hans Kruse

  • Sr. Member
  • ****
  • Offline Offline
  • Posts: 1389
    • Hans Kruse Photography
Re: Can the LR database be queried directly?
« Reply #19 on: August 06, 2014, 09:33:33 AM »

I would appreciate more options for smart collections to add to my workflow, but it is not a huge need. Regarding running queries against the Lightroom database, I don't see a great need beyond what Lightroom already provides. back to the question about focal lengths used, I personally would not buy a new lens based on previous patterns in used focal lengths. I would know precisely what kind of lens I would be after based on what I know without any query to the database. I would also know if the is a lens that I would need to explore new ways of shooting what I like and a LR query would help me in that.

I have seen too many databases in my former IT life to really wanting to dig into one more  ;D
Pages: [1] 2   Go Up