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

[m.Th.]

One of the main problems of the DB backend now is the metadata.

It is stored in a black-box-BLOB in the Images table which otherwise it should be the fastest table in the system.

I wrote enough time why such a BLOB is a performance drain when we discussed about Thumbs.

However, now the things are different. While the Thumbs cannot be improved internally, the Metadata sure it can in terms of space, performance, search and display and support for other subsystems (eg. searching for duplicates).

That's why, by far, is best to split the blob in fields.

Also, while we're here, we'll take a more general approach to image properties.

The initial plan is to have two tables, one for EXIF and one for IPTC - also an existing table (ImagesInfo) possibly to be expanded with new fields.

Question: Which are the most important metadata fields for you for displaying and filter / search?

Please make two lists, one for display and one for filter / search, in the order of importance.

My lists:

Filter / Search
--------------------
EXIF: ISO Value
EXIF: Aperture
EXIF: Focal Length
EXIF: Shutter Speed
EXIF: Camera Model

Display:
-----------
Same as above
EXIF: Exposure Bias


=======================================================
For your convenience, bellow are the most common fields from one of the most common programs:

XnView
File created date
File modified date
File size
File type
Comment
IPTC: Caption
IPTC: Caption Writer
IPTC: Headline
IPTC: Special Instruction
IPTC: Keywords
IPTC: Categories
IPTC: Supplemental Categories
IPTC: Copyright
IPTC: Byline
IPTC: Byline Title
IPTC: Credit
IPTC: Source
IPTC: Edit.Status
IPTC: Priority
IPTC: Object Cycle
IPTC: Job ID
IPTC: Program
IPTC: Object Name
IPTC: Date.Created
IPTC: Release.Date
IPTC: City
IPTC: Sublocation
IPTC: State Province
IPTC: Country
IPTC: Country Code
IPTC: Original Transmission
IPTC: Contact
IPTC: Location
EXIF: Make
EXIF: Model
EXIF: Date Modified
EXIF: Date Taken
EXIF: Date Digitized
EXIF: Software
EXIF: F Number
EXIF: ISO Value
EXIF: Focal Length
EXIF: Shutter speed
EXIF: Aperture
EXIF: Copyright
EXIF: User Comment
EXIF: Image Description
EXIF: Exposure Time
EXIF: Exposure Bias
EXIF: Flash

FastStone
Make
Model
Software
Date/Time
Exposure Time
Exposure Program
Exposure Bias
F Number
Max Aperture
ISO
Flash
Focal Length
35mm Equiv
Metering Mode
GPS

Lightroom

It has (at least) two tables:

AgHarvestedExifMetadata
Aperture
CameraModelRef
cameraSNRef
dateDay
dateMonth
dateYear
flashFired
focalLength
gpsLatitude
gpsLongitude
gpsSequence
hasGPS
isoSpeedRating
lensRef
shutterSpeed

AgHarvestedIptcMetadata
cityRef
copyrightState
countryRef
creatorRef
isoCountryCodeRef
jobIdentifierRef
locationDataOrigination
locationGPSSequence
locationRef
stateRef

Zoner Photo Studio

Has (at least) three tables:

CIM_ID
CIM_Path
CIM_OriginalPath
CIM_DisplayNameWithExt
CIM_Extension
CIM_Size
CIM_CaptureTime
CIM_CreationTime
CIM_LastWriteTime
CIM_ImageWidth
CIM_ImageHeight
CIM_ImageBpp
CIM_ImageDimesionsSort
CIM_ImageSizeSort
CIM_ImageDPI
CIM_ExifExposureTime
CIM_ExifAperture
CIM_ExifISO
CIM_ExifExpositionAdj
CIM_ExifFocalLength
CIM_ExifFocalLength35mm
CIM_ExifFocalRange
CIM_DataRating
CIM_DataLabel

aperture
cameraModelRef
cameraSNRef
dateDay
dateMonth
dateYear
flashFired
focalLength
gpsLatitude
gpsLongitude
gpsSequence
hasGPS
isoSpeedRating
lensRef
shutterSpeed

cityRef
copyrightState
countryRef
creatorRef
isoCountryCodeRef
jobIdentifierRef
locationDataOrigination
locationGPSSequence
locationRef
stateRef


Please answer & comment.

[m.Th.]

...sorry, I forgot the metadata fields for AfterShotPro:

ASP.JPG (56.31 KiB) Viewed 10727 times

[oops66]

...That's why, by far, is best to split the blob in fields.
Also, while we're here, we'll take a more general approach to image properties.
The initial plan is to have two tables, one for EXIF and one for IPTC - also an existing table (ImagesInfo) possibly to be expanded with new fields.

... I agree too ... good idea ... but we must be a little bit more patient ... IMHO after the holidays ...

My lists:
Filter / Search
--------------------
EXIF: ISO Value
EXIF: Aperture
EXIF: Focal Length
EXIF: Shutter Speed
EXIF: Camera Model
...
EXIF: Date Modified
EXIF: Date Taken
EXIF: Date Digitized
EXIF: Flash
EXIF: GPS
IPTC: Keywords
IPTC: Date.Created
IPTC: Release.Date
...

Display:
-----------
Same as above
EXIF: Exposure Bias
...

[m.Th.]

... I agree too ... good idea ... but we must be a little bit more patient ... IMHO after the holidays ...

Yes - that's why I started it now.

In order to discuss between us - to gather feedback from the community. And after that to implement

Btw, in your list:

EXIF: Date Modified - this is the modified date of the file?
IPTC: Keywords - this are in fact the same with the selection from the Categories tree, right?

IPTC: Date.Created - we can approximate this with the date of the creation of the file?

[oops66]

... In fact, it's for all dates in general ... and IPTC keywords in particular
( this are in fact the same with the selection from the Categories tree, right? ...Yes)

[m.Th.]

... In fact, it's for all dates in general ... and IPTC keywords in particular
( this are in fact the same with the selection from the Categories tree, right? ...Yes)

Ok, thanks. Another question. About 'Flash fired' field. I assume that you don't want to filter for this field only.

Do you expect to see from 100000 photos 50000 which are with flash (or not)?
...or do you want to combine this field with other conditions?
...or you do not want to search at all on this field? (or in very rare cases)

[oops66]

About 'Flash fired' field:
The idea is than the flash is usually used to highlight the foreground (less than ~ 5 meters), so usually the faces, so combined with IPTC keywords for a particular person it can be useful (search on rare cases)

[xnview]

Question: Which are the most important metadata fields for you for displaying and filter / search?

For displaying, all

[oops66]

...For displaying, all

LOL ...

[m.Th.]

About 'Flash fired' field:
The idea is than the flash is usually used to highlight the foreground (less than ~ 5 meters), so usually the faces, so combined with IPTC keywords for a particular person it can be useful (search on rare cases)

Yeah, sure. The 'Flash' field has very low selectivity (in a gazillion of photos you have just two values 0 and 1) and hence a filter for 'Flash' alone doesn't have any sense. However for fill flash it does. So, no index on this field.

But let's go further...

One of the main problems of metadata nowadays is that the same properties of image (the same metadata) is encoded in different ways, each encoding system having enough common parts with the others.

I think that we must introduce the concept of Unified Metadata Space which will hold only once each property of the image. The Unified Metadata Space is an in-database representation of the metadata regardless of the storage scheme (EXIF, IPTC, XMP et al.). IOW, the IPTC: Date Digitized and EXIF: Date Digitized labels in the GUI (if exist) will point to the same DB field. Of course, the advantages are obvious: speed, space and abstract driver-driven output.

Dictionary:
driver-driven output = from the same data warehouse one can extract and write in different formats (EXIF, IPTC, XMP, tab delimited etc.), according with the needs.


Something like this:

Unified Metadata Space.jpg (53.69 KiB) Viewed 10656 times

...in fact, almost all the programs have more tables in which each metadata property is stored only once (eg. look at Lr's tables above). Usually the numbers (which come mostly from EXIF) - including the date/time vars - which are most efficient to process, and most often to encounter are stored in one table and the strings (which usually come from IPTC) are stored some of them as lookup fields while others are stored inline. The most notable exception is Camera Make and Camera Model which is stored as lookup field in the first table.

Dictionary:
lookup field = A field (usually string) whose his actual value is stored in a special table, called lookup table with two columns (ID, NAME both indexed) whereas in the main table is stored only the ReferenceID (Primary Key) from the lookup table.

Beware! No completely 'null' (empty) rows! For ex. if an image doesn't have IPTC info we will not have a corresponding entry in the IPTC table! (This will be yet another important speedup)

Also, note that the display engine can use info directly from the file. In fact, Pierre uses it now because in the custom labels he have fields which aren't in the database. While this, of course, is sometimes a small performance penalty, generally speaking this is an improvement because the cases in which the display engine reaches to disk are corner cases.

Not all the info needs to be embedded into tables.

Question:

Does someone see any problems with this model?

My answer: It shouldn't ...all the programs which I listed above use this model. But perhaps something is missing...

Another question:

We need other property fields in the db except the classical metadata ones?

My answer: I'm thinking now at the compression... (uncompressed, JPEG etc.). Image size (H x W) is already in. File format is there. Also, a field called 'XMP last change date'?...

[m.Th.]

Question: Which are the most important metadata fields for you for displaying and filter / search?

For displaying, all

LOL! nice one!

[oops66]

...I think that we must introduce the concept of Unified Metadata Space which will hold only once each property of the image. The Unified Metadata Space is an in-database representation of the metadata regardless of the storage scheme (EXIF, IPTC, XMP et al.)...

+1 ... Interesting ...

... It would be also interesting than XnViewMP has the ability to calculate by itself some others "composite data/fields"
ScaleFactor35efl
FocalLengthIn35mmFormat
Field of View
SensorSize
1pixel_ccd/cmos size
...
CircleOfConfusion
FocusDistance
HyperfocalDistance
...
etc...

Like examples here from exiftool:
http://owl.phy.queensu.ca/~phil/exiftoo ... osite.html

[m.Th.]

...I think that we must introduce the concept of Unified Metadata Space which will hold only once each property of the image. The Unified Metadata Space is an in-database representation of the metadata regardless of the storage scheme (EXIF, IPTC, XMP et al.)...

+1 ... Interesting ...

... It would be also interesting than XnViewMP has the ability to calculate by itself some others "composite data/fields"
ScaleFactor35efl
FocalLengthIn35mmFormat
Field of View
SensorSize
1pixel_ccd/cmos size
...
CircleOfConfusion
FocusDistance
HyperfocalDistance
...
etc...

Like examples here from exiftool:
http://owl.phy.queensu.ca/~phil/exiftoo ... osite.html

Hmmmm... well, interesting.

1. Do you have the formulae?

2. You said: "If XnView could calculate..." well yes, if you know the formula and have the source data, in C++ you can do "anything". So for display, we're (more or less) ok.

3. However, the interesting point here is to have such calculated data pass through the filter/search engine - but SQLite supports only very simple operators: + - * and /

Most of the time is enough - for example:

Code: Select all

select ImageID, width * height/1024/1024 as MPixel from imagesinfo where MPixel > 18; -- show all the photos bigger than 18 MPixels

...but in some formulae perhaps isn't enough - I'm afraid about the lack of SQRT, for example - but let us not fear and try to find what's useful and add to the list.

3,5 - Well, if a function doesn't exist, Pierre can implement it (in fact just call the C++ counterpart) and register it to SQLite. See here: http://www.sqlite.org/c3ref/create_function.html

[m.Th.]

This post is RFC (Request For Comments) - Questions. (please comment my thoughts bellow)


Foreword:

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

- there is already the ImagesInfo table which holds mostly physical properties of the image (dimensions, format, bit depth etc.)


But let's start with my questions:
My thoughts are in blue.

(the names of the fields are taken from XnView's field list)

• Confilct between Comment - EXIF:User Comment
  One Field (Comment) - which will hold Comment or EXIF:User Comment (Comment overwrites the EXIF:User Comment) in metadata2
  
• IPTC:Copyright - EXIF:Copyright
  One field (Copyright) in metadata1
• IPTC:Caption - EXIF:Image Description
  One field (Caption) in metadata2
• IPTC:Keywords - IPTC:Categories - IPTC:Supplemental Categories
  Can someone explain why three fields for this?
• IPTC:Date.Created - EXIF:Date Taken
  One field (DateCreated) in metadata1
• File created date
  It will be taken from disk. No field for this. Because it will slow down and can change at copy / depends on OS / not relevant etc.

Comments? Thoughts?

[oops66]

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