Ad
Ad
Ad
Pages: [1] 2 »   Bottom of Page
Print
Author Topic: Can the LR database be queried directly?  (Read 1638 times)
dreed
Sr. Member
****
Offline Offline

Posts: 1260


« on: August 04, 2014, 04:53:20 AM »
ReplyReply

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


« Reply #1 on: August 04, 2014, 05:49:00 AM »
ReplyReply

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


« Reply #2 on: August 04, 2014, 06:10:24 AM »
ReplyReply

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


« Reply #3 on: August 04, 2014, 06:26:46 AM »
ReplyReply

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


« Reply #4 on: August 04, 2014, 06:44:57 AM »
ReplyReply

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
****
Offline Offline

Posts: 2832



WWW
« Reply #5 on: August 04, 2014, 06:49:16 AM »
ReplyReply

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
Logged

john beardsworth
Sr. Member
****
Offline Offline

Posts: 2832



WWW
« Reply #6 on: August 04, 2014, 06:54:32 AM »
ReplyReply

"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
Logged

Hans Kruse
Sr. Member
****
Offline Offline

Posts: 760



WWW
« Reply #7 on: August 04, 2014, 06:57:43 AM »
ReplyReply

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?

Logged

dreed
Sr. Member
****
Offline Offline

Posts: 1260


« Reply #8 on: August 04, 2014, 08:14:12 AM »
ReplyReply

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, 09:36:21 AM by dreed » Logged
PeterAit
Sr. Member
****
Offline Offline

Posts: 1952



WWW
« Reply #9 on: August 04, 2014, 09:13:49 AM »
ReplyReply

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 is a means to an end, never the end itself."
View my photos at http://www.peteraitken.com
dreed
Sr. Member
****
Offline Offline

Posts: 1260


« Reply #10 on: August 04, 2014, 09:15:06 AM »
ReplyReply

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, 09:35:07 AM by dreed » Logged
Hans Kruse
Sr. Member
****
Offline Offline

Posts: 760



WWW
« Reply #11 on: August 04, 2014, 11:14:31 AM »
ReplyReply

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.
Logged

Hans Kruse
Sr. Member
****
Offline Offline

Posts: 760



WWW
« Reply #12 on: August 04, 2014, 11:21:33 AM »
ReplyReply

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?
Logged

FranciscoDisilvestro
Sr. Member
****
Offline Offline

Posts: 551


WWW
« Reply #13 on: August 05, 2014, 06:02:30 AM »
ReplyReply

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.
Logged

dreed
Sr. Member
****
Offline Offline

Posts: 1260


« Reply #14 on: August 05, 2014, 08:21:17 AM »
ReplyReply

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



WWW
« Reply #15 on: August 05, 2014, 10:27:05 AM »
ReplyReply

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 is a means to an end, never the end itself."
View my photos at http://www.peteraitken.com
dreed
Sr. Member
****
Offline Offline

Posts: 1260


« Reply #16 on: August 06, 2014, 04:41:16 AM »
ReplyReply

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


« Reply #17 on: August 06, 2014, 04:58:46 AM »
ReplyReply

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
****
Offline Offline

Posts: 2832



WWW
« Reply #18 on: August 06, 2014, 05:06:17 AM »
ReplyReply

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.
Logged

Hans Kruse
Sr. Member
****
Offline Offline

Posts: 760



WWW
« Reply #19 on: August 06, 2014, 08:33:33 AM »
ReplyReply

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  Grin
Logged

Pages: [1] 2 »   Top of Page
Print
Jump to:  

Ad
Ad
Ad