Feature: provide interface to the detailed faces information in database

[RhetTbull]

The database contains much detailed info about the people/faces in the photo

[AaronVanGeffen]

I would love an export option for this information as well. Perhaps they could be added as EXIF keywords in (the XMP and JSON sidecar files for) the exported photos?

Query to retrieve faces by uuid, from an old (unpythonic) Python script of mine:

https://github.com/AaronVanGeffen/ExportPhotosLibrary/blob/0fa41d75ea115614792cc0526acf1646c9df69dd/export_photos.py#L175-L182

[RhetTbull]

@AaronVanGeffen the names of the people in the images are already included in the export for both JSON and XMP (stored in XMP:Subject and IPTC:PersonInImage). I am also working on an ExifTool interface to directly write this and other metadata to the image upon export. This issue (though written very vaguely!) is referring to the detailed information such as the actual face regions and characteristics (e.g. left eye closed, right eye closed, has smile, etc.) that Photos stores about the faces. (In Photos 5, much of this information is in ZDETECTEDFACE table) It would be useful to have access to this info, for example, for experimenting with face detection or machine learning.

[RhetTbull]

SELECT 
ZGENERICASSET.ZUUID, 
ZPERSON.ZFULLNAME, 
ZDETECTEDFACE.ZCENTERX, 
ZDETECTEDFACE.ZCENTERY, 
ZDETECTEDFACE.ZSIZE
FROM 
ZGENERICASSET
JOIN ZDETECTEDFACE ON ZDETECTEDFACE.ZASSET = ZGENERICASSET.Z_PK
JOIN ZPERSON on ZDETECTEDFACE.ZPERSON = ZPERSON.Z_PK

Coordinates seem to be percentage of photo size measured from bottom left corner.

[RhetTbull]

See also phace

[RhetTbull]

Need to add height, width, orientation to PhotoInfo. (Should add size while I'm at it)

[RhetTbull]

See this note on orientation

[RhetTbull]

Photos 4

CREATE TABLE RKFace (modelId integer primary key autoincrement, uuid varchar, isInTrash integer, personId integer, 
hasBeenSynced integer, adjustmentUuid varchar, imageModelId integer, sourceWidth integer, sourceHeight integer, centerX 
decimal, centerY decimal, size decimal, leftEyeX decimal, leftEyeY decimal, rightEyeX decimal, rightEyeY decimal, mouthX 
decimal, mouthY decimal, hidden integer, manual integer, hasSmile integer, blurScore decimal, isLeftEyeClosed integer, 
isRightEyeClosed integer, nameSource integer, poseRoll decimal, poseYaw decimal, posePitch decimal, faceAlgorithmVersion 
integer, expressionConfidence decimal, expressionType1 integer, expressionType2 integer, expressionType3 integer, 
expressionScore1 decimal, expressionScore2 decimal, expressionScore3 decimal, qualityMeasure integer, 
clusterSequenceNumber integer, syncPropertyModifiedDate timestamp, faceGroupId integer, 
confirmedFaceCropGenerationState integer, faceType integer, trainingType integer, cloudNameSource integer, personUuid 
varchar);

Photos 5

CREATE TABLE ZDETECTEDFACE (
  Z_PK INTEGER PRIMARY KEY, Z_ENT INTEGER, 
  Z_OPT INTEGER, ZAGETYPE INTEGER, ZASSETVISIBLE INTEGER, 
  ZBALDTYPE INTEGER, ZCLOUDLOCALSTATE INTEGER, 
  ZCLOUDNAMESOURCE INTEGER, ZCLUSTERSEQUENCENUMBER INTEGER, 
  ZCONFIRMEDFACECROPGENERATIONSTATE INTEGER, 
  ZEYEMAKEUPTYPE INTEGER, ZEYESSTATE INTEGER, 
  ZFACEALGORITHMVERSION INTEGER, ZFACIALHAIRTYPE INTEGER, 
  ZGENDERTYPE INTEGER, ZGLASSESTYPE INTEGER, 
  ZHAIRCOLORTYPE INTEGER, ZHASSMILE INTEGER, 
  ZHIDDEN INTEGER, ZISINTRASH INTEGER, 
  ZISLEFTEYECLOSED INTEGER, ZISRIGHTEYECLOSED INTEGER, 
  ZLIPMAKEUPTYPE INTEGER, ZMANUAL INTEGER, 
  ZNAMESOURCE INTEGER, ZQUALITYMEASURE INTEGER, 
  ZSMILETYPE INTEGER, ZSOURCEHEIGHT INTEGER, 
  ZSOURCEWIDTH INTEGER, ZTRAININGTYPE INTEGER, 
  ZASSET INTEGER, Z34_ASSET INTEGER, 
  ZFACECROP INTEGER, ZFACEGROUP INTEGER, 
  ZFACEGROUPBEINGKEYFACE INTEGER, 
  ZFACEPRINT INTEGER, ZPERSON INTEGER, 
  ZPERSONBEINGKEYFACE INTEGER, ZADJUSTMENTVERSION TIMESTAMP, 
  ZBLURSCORE FLOAT, ZCENTERX FLOAT, 
  ZCENTERY FLOAT, ZLEFTEYEX FLOAT, ZLEFTEYEY FLOAT, 
  ZMOUTHX FLOAT, ZMOUTHY FLOAT, ZPOSEYAW FLOAT, 
  ZQUALITY FLOAT, ZRIGHTEYEX FLOAT, 
  ZRIGHTEYEY FLOAT, ZROLL FLOAT, ZSIZE FLOAT, 
  ZYAW FLOAT, ZGROUPINGIDENTIFIER VARCHAR, 
  ZMASTERIDENTIFIER VARCHAR, ZUUID VARCHAR
);

[RhetTbull]

Working with this query on Photos 5

SELECT
ZGENERICASSET.ZUUID,
ZDETECTEDFACE.ZUUID,
ZDETECTEDFACE.ZPERSON,
ZPERSON.ZFULLNAME,
ZDETECTEDFACE.ZAGETYPE,
ZDETECTEDFACE.ZBALDTYPE,
ZDETECTEDFACE.ZEYEMAKEUPTYPE,
ZDETECTEDFACE.ZEYESSTATE,
ZDETECTEDFACE.ZFACIALHAIRTYPE,
ZDETECTEDFACE.ZGENDERTYPE,
ZDETECTEDFACE.ZGLASSESTYPE,
ZDETECTEDFACE.ZHAIRCOLORTYPE,
ZDETECTEDFACE.ZHASSMILE,
ZDETECTEDFACE.ZHIDDEN,
ZDETECTEDFACE.ZISINTRASH,
ZDETECTEDFACE.ZISLEFTEYECLOSED,
ZDETECTEDFACE.ZISRIGHTEYECLOSED,
ZDETECTEDFACE.ZLIPMAKEUPTYPE,
ZDETECTEDFACE.ZMANUAL,
ZDETECTEDFACE.ZQUALITYMEASURE,
ZDETECTEDFACE.ZSMILETYPE,
ZDETECTEDFACE.ZSOURCEHEIGHT,
ZDETECTEDFACE.ZSOURCEWIDTH,
ZDETECTEDFACE.ZBLURSCORE,
ZDETECTEDFACE.ZCENTERX,
ZDETECTEDFACE.ZCENTERY,
ZDETECTEDFACE.ZLEFTEYEX,
ZDETECTEDFACE.ZLEFTEYEY,
ZDETECTEDFACE.ZMOUTHX,
ZDETECTEDFACE.ZMOUTHY,
ZDETECTEDFACE.ZPOSEYAW,
ZDETECTEDFACE.ZQUALITY,
ZDETECTEDFACE.ZRIGHTEYEX,
ZDETECTEDFACE.ZRIGHTEYEY,
ZDETECTEDFACE.ZROLL,
ZDETECTEDFACE.ZSIZE,
ZDETECTEDFACE.ZYAW,
ZDETECTEDFACE.ZMASTERIDENTIFIER
FROM ZDETECTEDFACE
JOIN ZGENERICASSET ON ZGENERICASSET.Z_PK = ZDETECTEDFACE.ZASSET
JOIN ZPERSON ON ZPERSON.Z_PK = ZDETECTEDFACE.ZPERSON
ORDER BY ZGENERICASSET.ZUUID

[RhetTbull]

ZAGETYPE: 0 = ?? (have seen children and adults; perhaps "unknown" or "not yet processed")? 1 = infant 2 = child 3 = older child 4 = ?? 5 = adult

ZBALDTYPE: 0 = ?? 1 = not seen in database 2 = not much hair 3 = has hair

ZEYEMAKEUPTYPE: 0 = all faces in database

ZEYESSTATE: 0 = ?? 1 = closed 2 = open

ZFACIALHAIRTYPE: 0 = ?? 1 = no hair 2 = ?? 3 = beard 4 = mustache 5 = ??

[RhetTbull]

For Photos 4:

SELECT
RKFace.modelId,
RKFace.uuid,
RKVersion.uuid, 
RKPerson.name,
RKFace.isInTrash,
RKFace.personId,
RKFace.imageModelId, 
RKFace.sourceWidth,
RKFace.sourceHeight, 
RKFace.centerX,
RKFace.centerY,
RKFace.size,
RKFace.leftEyeX, 
RKFace.leftEyeY,
RKFace.rightEyeX, 
RKFace.rightEyeY, 
RKFace.mouthX,
RKFace.mouthY, 
RKFace.hidden, 
RKFace.manual, 
RKFace.hasSmile, 
RKFace.isLeftEyeClosed, 
RKFace.isRightEyeClosed, 
RKFace.poseRoll, 
RKFace.poseYaw, 
RKFace.posePitch, 
RKFace.faceType,
RKFace.personUuid
FROM
RKFace
JOIN RKPerson on RKPerson.modelId = RKFace.personId
JOIN RKVersion on RKVersion.modelId = RKFace.imageModelId

[neilpa]

See also phace

The "drawing" code that handles this for Photos 4. And the orientation adjustments that I validated against my personal photos lib at the time.

[RhetTbull]

@neilpa perfect--thanks! I did a lot of work on this over the weekend and have added a FaceInfo object with all the relevant details (not yet committed to GitHub). I was able to get the bounding box correct for all exif cases but hadn't yet figured out the transforms for the other points. (I ended up with upside down faces for example on photos that had been rotated).

[RhetTbull]

Thanks to @neilpa I've got the bounding boxes and face points working for all EXIF orientations....except for pictures rotated inside Photos. Photos rotated using Image | Rotate don't change the value for ZORIENTATION and I've not yet been able to decipher how Photos is storing the orientation for these.

[neilpa]

Photos rotated using Image | Rotate don't change the value for ZORIENTATION

I was going to guess that this would end up in ZADJUSTMENT or ZUNMANAGEDADJUSTMENT but that doesn't appear to be the case from looking at the records I have for my photos lib.

I've got a few different hacks which can partially "diff" two different snapshots of the photos DB. It's come in handy for reverse engineering stuff but I mostly use it to detect and backup newly imported assets. I'll see if I can use this trick to figure out how that rotation is represented.

[neilpa]

Also, if you haven't already, you should look at the ACHANGE, ATRANSACTION, and ATRANSACTIONSTRING tables. Those have helped me decipher how things fit together. Especially when trying to trace the meanings of numeric identifiers across tables. (In that case I usually just create a SQL dump and grep the output text file).

[RhetTbull]

Thanks. I've been using sqldiff a lot to compare the database between changes. It looks like the Photos adjustments, including rotation, are in a .plist file in the same file as the edited images (resources/renders/X/ where X is first character of UUID) but the adjustment data is stored in a binary format I've not yet tried to decipher. All adjustments produce an entry in ZINTERNALRESOURCE and there's a ZDATASTOREKEYDATA column that I suspect may map to data in the .plist file which has a DataStore field.

[neilpa]

Thanks for the pointers, especially sqldiff. Can't believe I haven't seen that before.

[neilpa]

I spent some time poking around but haven't deciphired the format of the embedded blobs in the :adjustmentData field of the .plist files you mention. I did a little hex dump slicing, dicing, and diffing but nothing obvious jumped out. Will likely need a more methodical approach that I didn't have time for at the moment.

Also, In my limited testing it doesn't look like ZDATASTOREKEYDATA can be used to detect/differentiate those rotations. It's always the same small binary value for all three rotations (90, 180, 270). I was hoping to maybe find some other clues with sqldiff for a non-plist option but no luck so far either.

[RhetTbull]

The .plist file in the renders folder is a serialized PHAdjustmentData object. Unfortunately, the specification for the adjustmentData property is not specified -- it's free form data that any application that edits photos via PhotoKit can set. So in this case, we need to look at the Photos specific format which Apple doesn't document. This also means though that if user edited photo in some other app or plugin that used PhotoKit the rotation data might be stored in a different format (not sure how Photos would then know how to apply face regions...)

I've spent a lot of time with a hex editor trying to identify how the rotation data is stored by Photos and so far have come up empty handed. The data field appears to be a series of binary records of variable length delimited by 0x0A09 but I've not yet found any common bytes across these for different sequences of edits and rotations that would identify the rotation.

For now, I think I'll push the initial FaceInfo implementation with a caveat that it'll be wrong for images rotated in Photos.

[RhetTbull]

I think I found it! Was looking in the wrong place. The rotation information for images edited in Photos doesn't appear to be stored in the database but the rotation of the face itself is stored in ZDETECTEDFACE.ZROLL and ZDETECTEDFACE.ZYAW. These appear to be angles in radians so I should be able to rotate the face points accordingly. It appears that ZROLL is the angle in radians the face is rotated about an axis perpendicular to the screen where clockwise is negative. The rotation appears to apply to the eye and mouth points. The center point of the face seems to be in the correct coordinates relative the to the image.

[neilpa]

I've spent a lot of time with a hex editor trying to identify how the rotation data is stored by Photos and so far have come up empty handed. The data field appears to be a series of binary records of variable length delimited by 0x0A09 but I've not yet found any common bytes across these for different sequences of edits and rotations that would identify the rotation.

Same for me. I spent a couple hours with Hex Fiend yesterday evening looking at various diffs and have yet to make any real progress. Surprising since it's only ~150 bytes of data.

I think I found it!

Awesome that you figured this out for photos with detected faces at least.

[RhetTbull]

v0.31.0 has initial support for face regions. See docs. Note: The bounding box for the face region appears to be correct for all possible rotations and flips of the image but for the left_eye, right_eye, and mouth properties which return coordinates (in the image coordinate frame) for the eyes and mouth are not always correct if the face is tilted off normal. The angles to describe the necessary rotation are stored in roll, pitch, and yaw but I've not yet figured out how to apply the transformation (except for roll which is updated when an image is itself rotated). See issue #196.

There's an example in examples/export_faces.py showing how to use Pillow to draw face regions on exported images. This is useful for verifying the eye and mouth coordinates.

[RhetTbull]

Now need to add face regions to XMP sidecars. See iphoto2xmp:

https://github.com/jensb/iphoto2xmp/blob/2e47eacb45f385f9447fa674ef0bbd5a362c14d8/iphoto2xmp_template.xmp.erb#L141-L160

[RhetTbull]

I think the XMP sidecar is now properly encoding the face regions...however, I've not been able to find a way to test this. I've tried both Digikam and Lightroom, both of which I'm unfamiliar with, and can't figure out how to get them to import face regions from an XMP file despite hours of googling. I'm going to put this issue on hold until I can find an app or some code that will show me the face regions in an exported image so I can verify they're being encoded properly.

[neilpa]

I've not been able to find a way to test this. I've tried both Digikam and Lightroom, both of which I'm unfamiliar with, and can't figure out how to get them to import face regions from an XMP file despite hours of googling

I hit this exact same issue when working on this in phace. I could not find an app that actually used the encoded face regions. IIRC the docs for DigiKam claimed to but in all my experimenting I couldn't find an example of it working. Best I did was find some sample images with the XMP face regions already encoded and made sure I could round-trip the data based on my understanding of the format.

[RhetTbull]

@neilpa I've figured out how to make DigiKam at least write the XMP files with embedded face regions. My plan is to mark face regions in DigiKam then export the XMP files for a bunch of photos in every possible EXIF orientation then write a program that reads the XMP files and draws the face regions with Pillow. Assuming DigiKam is properly encoding the face regions and I can visually verify that I can interpret the encodings correctly, that will be give me a way to then verify the regions I'm encoding are correct. But it does lead to the question of what the value is in face regions if apps don't actually read this information. I like the idea of exporting it to future proof the export but it is frustrating the state of this so bad given how much effort it takes to properly tag a library with thousands of photos! In researching this I learned that even though there's a Metadata Working Group standard for face regions, Microsoft developed their own which is completely different (but best as I can tell, encodes no additional information). DigiKam does encode the Microsoft format as well so perhaps I can add that to osxphotos too.

[d0m1n1qu3]

Hi, for example pigallery2 use the metadata of the image files to show the persons/faces so it will be cool to keep the faces, store them in the metadata and a presentation tool like pigallery2 can use them instead of refined all faces again ..

from pigallery2 website ... Faces (persons) support reads Adobe's XMP Face region metadata. (It is defined by the Metadata Working Group (MWG).)

https://github.com/bpatrik/pigallery2

best regards Dominique

[RhetTbull]

Thanks @d0m1n1qu3 I'll take a look at pigallery2. Currently, the face information is written to the XMP sidecar if you use osxphotos export /path/to/export --sidecar xmp option but I've not been able to properly test this as I can't find an app that actually uses this information. If pigallery2 will read the sidecars, that would help confirm I'm exporting the data correctly.

[martinhrpi]

Would love to See xmp faceregion support. Supported by Digikam, Lightroom and others like Photostation on a Synology NAS. Tell me, if I could help.

I exported the data with the PersonInImage Tag but I see no way to convert it to Faceregion via Exiftool.

[RhetTbull]

osxphotos currently exports the personInImage info (name of person) but not the face region. It does have access to the face regions info but I've not found a way to validate the XMP nor found any program that will actually read this info. Lightroom writes it but I couldn't figure out how to make Lightroom read it. Happy for some help here: 1) an example of a valid XMP with Face Regions so I can copy the format, 2) ability to test the creature against some app that will actually read it from XMP.

[martinhrpi]

Sorry for the delay. So here is a true xmp, generated from Digikam. The first is normal orientation with People named left to right (LTR1, LTR 2, etc) and the second Data is the same picture with the naming - only rotated left. Had to change the type from xmp to txt. Does this help? Do you need anything else? Will be happy to test the new creature :-)

digikam_test_normal.txt digikam_test_rotate.txt

[RhetTbull]

That's helpful -- thanks! I'll be working on XMP this weekend -- #349, #350 -- so will take a look at this as well.

[martinhrpi]

Than thank you very much in advance!

[RhetTbull]

The DigiKam XMP files contain face regions in two formats:

The Metadata Working Group Regions (mwg-rs), see after page 51 here.

Microsoft MPRI format ("Microsoft Photo Region Info"?)

People Rectangles People's names however, are only part of the people-tagging feature. In addition to storing people's names in the metadata, the schema also supports region information that identifies the specific area (a rectangle) the person is shown in the image. The rectangle information is represented by four comma-delimited decimal values, such as "0.25, 0.25, 0.25, 0.25". The first two values specify the top-left coordinate; the final two specify the height and width of the rectangle. The dimensions of the image for the purposes of defining people rectangles are normalized to 1, which means that in the "0.25, 0.25, 0.25, 0.25" example, the rectangle starts 1/4 of the distance from the top and 1/4 of the distance from the left of the image. Both the height and width of the rectangle are 1/4 of the size of their respective image dimensions.

[martinhrpi]

Yes I think for broad compatability. But I think mwg is the best way

[RhetTbull]

@martinhrpi do you know how to make Digikam actually read the face tags from XMP? I've tried and not been able to make Digikam display the tagged faces in an XMP file. I worked on this issue several months ago and gave up because I could not find a way to test that the output is correct. E.g. I need a way to write an XMP file then have some program read the XMP file and display the tagged regions to see if they're correct. Without that, I'm shooting in the dark.

[RhetTbull]

exiftool region tags:

https://exiftool.org/TagNames/MWG.html#Regions

exiftool -xmp-mwg-rs:RegionAppliedToDimensionsH=4000 -xmp-mwg-rs:RegionAppliedToDimensionsUnit="pixel" -xmp-mwg-rs:RegionAppliedToDimensionsW=6000 -xmp-mwg-rs:RegionAreaX=0.319270833 -xmp-mwg-rs:RegionAreaY=0.21015625 -xmp-mwg-rs:RegionAreaW=0.165104167 -xmp-mwg-rs:RegionAreaH=0.30390625 -xmp-mwg-rs:RegionName=John -xmp-mwg-rs:RegionRotation=0 -xmp-mwg-rs:RegionType="Face" myfile.xmp

[martinhrpi]

So Digikam reads the mwg in xmp I provided to you. I copied the image and XMP, deleted the original and re-imported it and it worked.

Did you tell Digikam to read/write xmp in the settings?

[martinhrpi]

Btw I use the Beta. So Digikam 7.2 Beta 2.

and concerning exiftool: there is no way to convert your exported Data to mwg via exiftool as far as I found.

[RhetTbull]

Did you tell Digikam to read/write xmp in the settings?

I did. But I can't figure out how to make Digikam actually show the face regions. (I don't use Digikam so not familiar with it -- just need something for testing)

I'll download the Beta. Not sure which version I have

[martinhrpi]

So let me make something for you :)

[martinhrpi]

So here is a Test-Picture from Google (Creative Commons). I imported the picture to Digikam, named the man "Test Man". Then I copied the picture away from Digitkam and deleted it in Digikam. No data left in Digikam.

Then, with the help of exiftool

exiftool -overwrite_original -all=

I wiped the data from the picture file - so only the data from sidecar xmp is left.

Then I re-Imported the picture and voila: after reading from the Sidecar: there was the face again with the same rectangle (I think) and the correct name.

test.txt

[RhetTbull]

Thanks!

[RhetTbull]

After wasting many hours in following loop: 1) modify XMP, 2) load it in digikam, 3) curse, 4) delete digikam database 5) repeat, I've figured out that Digikam is reading the Microsoft MPRI region info and not the standard mwg region info.

Given this knowledge, I think I can write this info to the XMP. I would still like to include mwg regions and I think osxphotos is correctly writing these but I have no way to test. Lack of ability to test this feature is why I abandoned it months ago. I wish a simple app existed that would read an XMP, validate it, and then draw/label face regions on the jpeg which I could use for testing. I've searched Google and Github to no avail. I'd write it except of course, it would only validate that I'm interpreting the spec correctly, not that I actually got it right. While the standard case is fairly easy, handling all the different rotations / orientations for an image is tricky and that's what I need to test.

Edit: exiftool can of course do the validation (and osxphotos test suite uses exiftool for this now) but it cannot tell me if I got the coordinate transforms for the face regions correct.

[martinhrpi]

I would love to use what you have built so far. As a Workaround, since Digikam seems to use microsoft but also writes mwg I could use osxphotos to write the face and get it out of Photos, and then use Digikam to make it "more available" aka transform it to the other Face-Standards.

[martinhrpi]

So XnView (https://www.xnview.com/de/xnviewmp/#downloads) can read mwg. But not in the sidecar it seems, only in the Image-File. Tested with this file (delete the Microsoft Tags first).

[RhetTbull]

Thanks -- I had previous found & tried xnview but as you noted, I couldn't get it to read the sidecar. I'll post a beta release as soon as I can get digikam to read the xmp generated by osxphotos. Currently, exiftool says the XMP is valid but I cannot get digikam to read any XMP file it did not actually generate.

[martinhrpi]

So thank you very very much for the beta - happy to test it. As for sidecar-support on XnView: in the current Version it supports Sidecar files as I found out minutes ago. Testet it with the emptied image file and amp sidecar where I changed a view things via a text-editor. It worked so I think you could test the data with XnView!

edit: But it seems like XnView doesn't draw the face if the data is in the sidecar only.

[RhetTbull]

edit: But it seems like XnView doesn't draw the face if the data is in the sidecar only.

This is the main issue. I know the sidecar is valid because exiftool accepts it (and I consider exiftool the gold standard) but because I need to apply coordinate transformations for the face region I really need to see what some other app thinks the bounding box is. I will try to apply the XMP, via exiftool, to the image file then see if XnView will show it.

[martinhrpi]

I will try to apply the XMP, via exiftool, to the image file then see if XnView will show it.

I tried that with Test Man and it worked.