Very important: Metadata V2 for 0.70 (EXIF, XMP, IPTC etc.)

[m.Th.]

...1. Do you have the formulae? ...

Hello,
Some are already here (a link into the previous exiftool link- ~/.ExifTool_config ) :
http://owl.phy.queensu.ca/~phil/exiftool/config.html

...But about composite data/fields ... it will be IMHO for the future (After the v1.0 MP release)

I think that we'll have two+one tables:
- metadata1 for most used and most often - high-performance table - mainly the EXIF info
- metadata2 which will act like a L2 cache, mainly for big texts (mainly IPTC info) which aren't so often entered and sought

+1, why not, it's a good idea.

I checked some formulas - it isn't a problem to have composite fields - in fact is the same thing. We don't need the SQRT. The much bigger problem is to delay a base field which will be needed for calculations, because now it is easy to do the conversion from Meta BLOB (assuming that it has all the data) or even to delete the all uncatalogged files from Images table (the 'Clean files' option from 'Optimize' button) and refill the metadata tables from the disk.

But when this metadata will become important for search / statistical purposes then isn't so nice to delete (and/or upgrade) 10000 photos just because you left out one field.

Istm that one of the most important parameters is Circle of Confusion which is calculated as d/1500 where "d" is the sensor's diagonal in millimeters. Yes, this implies a SQRT but we'll calculate it in C++ and store it in the DB but it is very efficient in storage because we will store it next to camera model (and not for each photo).

Do you know how to calculate it using only EXIF data?

Hint: I think that's something related to FocalPlane values (but I might be wrong).

PS: About the v1.0 - I would see it ASAP. But we must finish the on-disk file management, fix keywords/categories engine and some "Ugly, Cover page & Unfinished" bugs - like
http://newsgroup.xnview.com/viewtopic.php?f=60&t=28030
http://newsgroup.xnview.com/viewtopic.php?f=60&t=28276
http://newsgroup.xnview.com/viewtopic.php?f=74&t=28013

...and others. The ones who will take the 1.0 to not feel 'unsafe' because they have an 'unfinished' / 'in-construction' product.

[oops66]

...
Do you know how to calculate it using only EXIF data?...

... From exiftool I had this (~/.ExifTool_config):

Code: Select all

...
    ScaleFactor35efl => {
        Description => 'Scale Factor To 35mm Equivalent',
        Notes => 'this value and any derived values may be incorrect if image has been resized',
        Groups => { 2 => 'Camera' },
        Desire => {
            0 => 'FocalLength',
            1 => 'FocalLengthIn35mmFormat',
            2 => 'Composite:DigitalZoom',
            3 => 'FocalPlaneDiagonal',
            4 => 'FocalPlaneXSize',
            5 => 'FocalPlaneYSize',
            6 => 'FocalPlaneResolutionUnit',
            7 => 'FocalPlaneXResolution',
            8 => 'FocalPlaneYResolution',
            9 => 'CanonImageWidth',
           10 => 'CanonImageHeight',
           11 => 'ExifImageWidth',
           12 => 'ExifImageLength',
           13 => 'ImageWidth',
           14 => 'ImageHeight',
        },
        ValueConv => 'Image::ExifTool::Exif::CalcScaleFactor35efl(@val)',
        PrintConv => 'sprintf("%.6f", $val)',
    },
    CircleOfConfusion => {
        Notes => q{
            this value may be incorrect if the image has been resized.  Calculated as
            D/1440, where D is the focal plane diagonal in mm
        },
        Groups => { 2 => 'Camera' },
        Require => 'ScaleFactor35efl',
        ValueConv => 'sqrt(24*24+36*36) / ($val * 1440)',
        PrintConv => 'sprintf("%.6f mm",$val)',
    },
....
# ---- diagonale en 24 x 36 = sqrt(24*24+36*36) = CircleOfConfusion / 1440)  (or 1730)-----------

A good explanation here (but in French):
http://www.la-photo-en-faits.com/2012/0 ... usion.html

... It's Interesting to calculate after: the DOF (Depth of Field) , etc...

Code: Select all

...
HyperfocalDistance => {
        Notes => 'this value may be incorrect if the image has been resized',
        Groups => { 2 => 'Camera' },
        Require => {
            0 => 'FocalLength',
            1 => 'Aperture',
            2 => 'CircleOfConfusion',
        },
        ValueConv => q{
            return undef unless $val[1] and $val[2];
            return $val[0] * $val[0] / ($val[1] * $val[2] * 1000);
        },
        PrintConv => 'sprintf("%.3f m", $val)',
    },
    DOF => {
        Description => 'Depth of Field',
        Notes => 'this value may be incorrect if the image has been resized',
        Require => {
            0 => 'FocusDistance', # FocusDistance in meters, 0 means 'inf'
            1 => 'FocalLength',
            2 => 'Aperture',
            3 => 'CircleOfConfusion',
        },
        ValueConv => q{
            return undef unless $val[1] and $val[3];
            $val[0] or $val[0] = 1e10;  # use a large number for 'inf'
            my ($s, $f) = ($val[0], $val[1]);
            my $t = $val[2] * $val[3] * ($s * 1000 - $f) / ($f * $f);
            my @v = ($s / (1 + $t), $s / (1 - $t));
            $v[1] < 0 and $v[1] = 0; # 0 means 'inf'
            return join(' ',@v);
        },
        PrintConv => q{
            $val =~ tr/,/./;    # in case locale is whacky
            my @v = split ' ', $val;
            $v[1] or return sprintf("inf (%.2f m - inf)", $v[0]);
            return sprintf("%.3f m (%.3f - %.3f)",$v[1]-$v[0],$v[0],$v[1]);
        },
    },
...

[m.Th.]

...
Do you know how to calculate it using only EXIF data?...

... From exiftool I had this (~/.ExifTool_config):

Code: Select all

...
    ScaleFactor35efl => {
        Description => 'Scale Factor To 35mm Equivalent',
        Notes => 'this value and any derived values may be incorrect if image has been resized',
        Groups => { 2 => 'Camera' },
        Desire => {
            0 => 'FocalLength',
            1 => 'FocalLengthIn35mmFormat',
            2 => 'Composite:DigitalZoom',
            3 => 'FocalPlaneDiagonal',
            4 => 'FocalPlaneXSize',
            5 => 'FocalPlaneYSize',
            6 => 'FocalPlaneResolutionUnit',
            7 => 'FocalPlaneXResolution',
            8 => 'FocalPlaneYResolution',
            9 => 'CanonImageWidth',
           10 => 'CanonImageHeight',
           11 => 'ExifImageWidth',
           12 => 'ExifImageLength',
           13 => 'ImageWidth',
           14 => 'ImageHeight',
        },
        ValueConv => 'Image::ExifTool::Exif::CalcScaleFactor35efl(@val)',
        PrintConv => 'sprintf("%.6f", $val)',
    },
    CircleOfConfusion => {
        Notes => q{
            this value may be incorrect if the image has been resized.  Calculated as
            D/1440, where D is the focal plane diagonal in mm
        },
        Groups => { 2 => 'Camera' },
        Require => 'ScaleFactor35efl',
        ValueConv => 'sqrt(24*24+36*36) / ($val * 1440)',
        PrintConv => 'sprintf("%.6f mm",$val)',
    },
....
# ---- diagonale en 24 x 36 = sqrt(24*24+36*36) = CircleOfConfusion / 1440)  (or 1730)-----------

A good explanation here (but in French):
http://www.la-photo-en-faits.com/2012/0 ... usion.html

... It's Interesting to calculate after: the DOF (Depth of Field) , etc...

Code: Select all

...
HyperfocalDistance => {
        Notes => 'this value may be incorrect if the image has been resized',
        Groups => { 2 => 'Camera' },
        Require => {
            0 => 'FocalLength',
            1 => 'Aperture',
            2 => 'CircleOfConfusion',
        },
        ValueConv => q{
            return undef unless $val[1] and $val[2];
            return $val[0] * $val[0] / ($val[1] * $val[2] * 1000);
        },
        PrintConv => 'sprintf("%.3f m", $val)',
    },
    DOF => {
        Description => 'Depth of Field',
        Notes => 'this value may be incorrect if the image has been resized',
        Require => {
            0 => 'FocusDistance', # FocusDistance in meters, 0 means 'inf'
            1 => 'FocalLength',
            2 => 'Aperture',
            3 => 'CircleOfConfusion',
        },
        ValueConv => q{
            return undef unless $val[1] and $val[3];
            $val[0] or $val[0] = 1e10;  # use a large number for 'inf'
            my ($s, $f) = ($val[0], $val[1]);
            my $t = $val[2] * $val[3] * ($s * 1000 - $f) / ($f * $f);
            my @v = ($s / (1 + $t), $s / (1 - $t));
            $v[1] < 0 and $v[1] = 0; # 0 means 'inf'
            return join(' ',@v);
        },
        PrintConv => q{
            $val =~ tr/,/./;    # in case locale is whacky
            my @v = split ' ', $val;
            $v[1] or return sprintf("inf (%.2f m - inf)", $v[0]);
            return sprintf("%.3f m (%.3f - %.3f)",$v[1]-$v[0],$v[0],$v[1]);
        },
    },
...

Thanks a lot for the input!

What I was searching was the following: (copy/pasted from a friend):

Using the EXIF data, you should be able to use the following formula:
(Resolution in pixels/Focal plane resolution in dpi) X 25.4(mm/in)=size in mm

For my Canon 5DII, the horizintal and vertical numbers figure out to:
(5616p/3849.21ppi) X 25.4mm/in = 37.058mm
(3744p/3908.14ppi) X 25.4mm/in = 24.33mm

Once you have the horizontal and vertical measurements, you can calculate the diagonal measurement using the simple formula a² + b² = c² or c = √(a² + b²)

For the Canon 5DII, this figures out to: √(37.058² + 24.33²) = 44.33mm

As you can see, the result is slightly larger than the expected 36X24mm that would produce a 43.267mm diagonal. Canon publishes the sensor size as 36X24mm in sales literature, but specifies 35.8X23.9mm in technical literature for the 5DII. I got similar results for every camera I tried this with. Since the resolution reported is that of the image after demosaicing, the discrepancy can't be explained because of the extra edge pixels that are only used to interpolate the RGB values of the "effective" pixels used in the demosaiced image.

Rather than use the EXIF data, you could also consult a database such as can be found at DxO Mark. The sensor dimensions, in mm, of every camera they test is included in the specifications tab for each camera.

In general, FF cameras use sensors that are 36X24mm, APS-C cameras (other than Canon) use sensors that are 23.5X15.6mm, Canon APS-C sensors are 22.3X14.9mm. For a full list of sensor sizes used by almost every current manufacturer, see the chart near the end of http://en.wikipedia.org/wiki/Image_sensor_format

...now, I'm thinking to pre-fill the (future) CameraModels table with some names and their sensor sizes / CoC and add the calculated ones only when a match isn't found. But where we could find such a table ready to import?

[mahikeulbody]

Let the user to choice which fields he want to store in the DB.

[m.Th.]

Let the user to choice which fields he want to store in the DB.

There are some headaches tied to this approach - some of them which I remember now:

- in which form one should store the fields (for ex. lookup fields like the EXIF:CameraModel or the Paths field which already exists in DB, precomputed fields like eg. the above CoC)
- which fields we should index
- calculated fields - some of them with not-so-easy to remember formulas (eg. Hyperfocal Distance, Luminosity etc.) - how will user enter them?
- there are small fields which most of the time are filled with data and sought often (eg. the EXIF core). Also there are big fields which rarely have data and seldom searched (mainly fields from IPTC) how to make that the 2nd group to not drag the performance of the 1st group down? Will user have the ability to optimize by hand his DB?
- "Later"-binding. For ex. today we have in DB 10 fields. Tomorrow the user will add another EXIF field. Should the program re-scan all 12345678 photos in catalog in order to get the value from all photos for the newly added field in order to have consistent searches?
- How do you'll design the GUI for the Add/Edit/Remove fields? (I'm interested especially for the calculated fields - beware that the calculated fields must work - hence, it is very indicated to add some form of syntax checking or fallback in order to not "crash to death" the program and, also, besides that, sometimes you must handle some errors like "division by 0").


I have genuine interest in finding out:

- which are the advantages of your proposal in order to worth the implementation?
- how do you think to overcome the above problems?

[m.Th.]

The table metatadata1 is more or less finished:

https://docs.google.com/spreadsheet/pub ... utput=html

Perhaps someone can give us an opinion?

About the Lookup fields which are present in the table above:

'Lookup' field type for the field 'foo' means that:

a) In the main table will exist the field fooID of type INTEGER, indexed with the index tableName_fooID_idx
Example: for the Lens lookup we'll have in the metadata1 table the field LensID of type INTEGER NOT NULL and an index with the name Metadata1_LensID_idx will be created on this field.

b) A table with the name Look_foo will be created with two fields: fooID Integer PK AutoInc and fooName Text not null Unique. The text field will be indexed with a case insensitive index called Look_foo_fooName_idx
Example: For the Lens lookup we'll have...

Code: Select all

CREATE TABLE Look_Lens (LensID Integer Primary Key Autoincrement, LensName Text Not Null COLLATE NOCASE);
CREATE UNIQUE INDEX Look_Lens_LensName_idx ON Look-Lens(LensName);

Exception: For the table Look-CameraModel besides CameraModelID and CameraModeName fields we'll have also another field: CoC of type REAL.

-----

Besides of your opinions, I'm waiting for an answer to the following question: the index on the lookup value (the index on the LensName in the above example) should really be case insensitive?

Edit: Added the COLLATE NOCASE in the CREATE TABLE command. It is better.

[JohnFredC]

I think camera make and model should be indexed.

[JohnFredC]

Also a value for WB (white balance) is missing (important to me).

...and DPI? I know this is an arbitrary value chosen by the user that has no intrinsic meaning for the image, but I set it for my images very frequently and XnView displays it.

[m.Th.]

I think camera make and model should be indexed.

Yes, they are. Any lookup field is indexed (case insensitively) in the lookup table as well as its FK values, hence the query engine will use it. See closely my post about the lookup fields.

Thanks for the hint tough.

[m.Th.]

Also a value for WB (white balance) is missing (important to me).

...and DPI? I know this is an arbitrary value chosen by the user that has no intrinsic meaning for the image, but I set it for my images very frequently and XnView displays it.

Oops, updated.

WB is indeed an important value.

DPI I think that's not relevant the one from the EXIF but the one from the file that's why I put it in the ImagesInfo table. See the link again.

[JohnFredC]

OK, I missed the DPI.

But shouldn't the lookups be indexed in the primary table, too? Isn't the primary key value of the linked record in the secondary table stored there?

[m.Th.]

OK, I missed the DPI.

But shouldn't the lookups be indexed in the primary table, too? Isn't the primary key value of the linked record in the secondary table stored there?

Sure. It is. Reread the point a) from my post about Lookup fields.

Or, to be more clearer:

We'll have the table metadata1 with the following spec (till now):

Code: Select all

Field        Type	   Index
-------------------------------------------------------
ImageID	   INTEGER	pk
DateTaken	 DATE	   y
...
CameraModel  Lookup	
...		

This translates in the real world DB in two tables:

Metadata1:

Code: Select all

Field	     Type	   Index
-------------------------------------------------------
ImageID	   INTEGER	pk
DateTaken	 DATE	   y
...
CameraModelID  INTEGER y	
...		

and Look_CameraModel:

Code: Select all

Field	           Type	   Index
-------------------------------------------------------
CameraModelID	   INTEGER	pk
CameraModelName	 TEXT	   y (NOCASE)

This is, in fact, a foreign-key constraint, which perhaps is better to explicitly define - IOW for each lookup field fooID we'll have

...CONSTRAINT fooFK REFERENCES Look_foo(fooID)

As an example for this, taking the above case with CameraModel we'll have:

Code: Select all

CREATE TABLE Metadata1 (ImageID PRIMARY KEY, ... {some fields here}..., CameraModelID INTEGER CONSTRAINT CameraModelFK REFERENCES Look_CameraModel(CameraModelID) ON DELETE RESTRICT, ...{other fields here}

The advantage is that the engine will ensure our data integrity.

The disadvantage is that it is a little slower (very little because the lookup table is very small)

Also, I'm not very sure in our case if ON DELETE RESTRICT is the most appropriate... (or ON DELETE CASCADE?? Too dangerous imho).

[JohnFredC]

I was taught to treat a lookup as if it was a virtual join and to index both sides (that is, the linked fields in each table). Somewhere "down below" the db engine still has to retrieve the value from each record in the primary table to compare to the corresponding retrieved value in the secondary table.

...it certainly makes a difference to performance in Access, so I imagine it might make a difference in other DBs, too.

[m.Th.]

I was taught to treat a lookup as if it was a virtual join and to index both sides (that is, the linked fields in each table). Somewhere "down below" the db engine still has to retrieve the value from each record in the primary table to compare to the corresponding retrieved value in the secondary table.

...it certainly makes a difference to performance in Access, so I imagine it might make a difference in other DBs, too.

Sure. And more: Usually the SQL engines doesn't allow you to declare a Foreign Key constraint if you do not index both sides. And even more: It is better to index also the 3rd field (the CameraModelName in our example) because you don't want to see only the cameras for a certain search criteria eg. Select m1.*, (select cm.CameraModelName from Look_CameraModel cm where cm.CameraModeID=m1.CameraModelID) from metadata1 m1 where m1.blah = foo but also one want to search on this field:

Select m1.*, cm.CameraModelName from metadata m1 join Look_CameraModel cm on (m1.CameraModelID = cm.CameraModelID) where cm.CameraModelName like 'CANON 1D%'

The latter will return all the metadata records from photos which were shoot with Canon's 1D series (1D, 1D Mk II, 1Ds Mk II, 1D Mk II N, 1D Mk III, 1Ds Mk IV, 1DX etc.) by using the case insensitive index on CameraModelName.

But let's go further:

About IPTC.

Does anyone have experience what IPTC fields are usually filled by the users?

[m.Th.]

Much updated version including the mapping for the XnView's fields, Metadata2 table and other changes at

https://docs.google.com/spreadsheet/pub ... utput=html

Enjoy & ask!