-
Notifications
You must be signed in to change notification settings - Fork 105
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Feature: provide interface to the detailed faces information in database #21
Comments
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: |
@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. |
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. |
See also phace |
Need to add height, width, orientation to PhotoInfo. (Should add size while I'm at it) |
See this note on orientation |
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
); |
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 |
|
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 |
The "drawing" code that handles this for Photos 4. And the orientation adjustments that I validated against my personal photos lib at the time. |
@neilpa perfect--thanks! I did a lot of work on this over the weekend and have added a |
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 |
I was going to guess that this would end up in 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. |
Also, if you haven't already, you should look at the |
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 |
Thanks for the pointers, especially |
I spent some time poking around but haven't deciphired the format of the embedded blobs in the Also, In my limited testing it doesn't look like |
The 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 For now, I think I'll push the initial |
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 |
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.
Awesome that you figured this out for photos with detected faces at least. |
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 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. |
Now need to add face regions to XMP sidecars. See iphoto2xmp: |
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. |
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. |
@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. |
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). |
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. |
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. |
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. |
I tried that with Test Man and it worked. |
I think mwg regions are correct now. First photo is face region in Photos (stored by Photos as circle) and second is in XnView (XMP stores region as rectangle). Still can't get Digikam to read the XMP though. Now I'll work on confirming the MPRI regions are correct as that seems to be what Digikam uses. These use a different coordinate system. |
If both MWG-RS and MPRI face regions are in the XMP data, both XnView and Digikam default to the MPRI region. This is interesting as I would've expected the Metadata Working Group to be more standard but appears that Microsoft format is more commonly accepted. Also, if I write the XMP to the photo using exiftool, Digikam correctly displays the face. But I cannot get Digikam to actually read the .xmp sidecar. |
@martinhrpi I've added beta support for face regions in XMP in v0.39.21. Note, to use this you'll need to use the undocumented "--beta" flag when exporting. e.g.
I'd be happy for any feedback you have on whether this is working. There may be issues with different photo orientations though the ones I've tested do work. I still need to figure out why Digikam won't read the XMP sidecar. |
One known issue: the face region info in Photos is stored relative to the most recent version of the photo (e.g. if photo has adjustments/edits, face region applies to the edited photo, not the original). osxphotos currently exports the same XMP data for both edited and original version so if you resized or cropped the photo, face regions for the original will be incorrect. I still need to think about how to handle this as it will complicate the XMP sidecar logic to deal with this properly. Happy for any thoughts on how to deal with this case. |
Uff! You are a genius! Testes ist with a test-database of about 60 photos, works perfect! I can‘t thank you enough! Will test it now with a 16.000 picture data-base and will think about the edited ones, too. I scanned a lot of old pictures (yet another photos database) and edited them - so I have quite a good testing ground. thank you so much so far - you saved me months of work! |
Oh wow so Digikam even uses the not yet named faces for their "unknown" category. So you get the ones you already named in MacOS Photos AND you get the ones that you didn't name in Photos. That is even more convenient. To give you some perspective of your impact (at least on me):
Thank you again for that great help - and I will look into the edit thing :-) |
Success! New version v0.39.22 generates XMP sidecars that Digikam will read! Must still be used with --beta to enable this. As soon as I get a little more testing I'll enable this by default. |
And I have found (I think) that it‘s not a only problem with pictures edited by OS Photo but also with selfies or pictures rotated outside of photos by exif info. So I don‘t think there is a fix - but this small percentage of wrongly drawn faces is nothing against the big advantages! |
@martinhrpi if you have one of the "incorrect" images you're willing to share, send me the image and XMP file to [email protected] and I'll see if I can figure it out. I think I have an idea how to fix this (though there are some photos for which I cannot easily get the correct orientation due to how Photos stores this data). |
I think I've fixed the incorrect face regions for most photos -- tested with several different exif orientations. See release v0.39.23. Photos rotated within Apple Photos app may still be incorrect as these photos do not change the orientation data in the database. I think I can extract the correct orientation using the native PhotoKit interface but this will take more work. |
Ahhh saw your comment to late. I have just now collected 35 MB of problematic pictures. Would just Need to Upload those. I will test the new Version and give feedback. |
YES! I can confirm the images that were incorrect because the camera set the rotation or iOS switched it because of selfie are now correct! |
Great! Thanks much for the testing and feedback. I'll promote this new XMP format to "non-beta" in the next release. Now I'll need to work on --no-person (#284) option because some people want to export XMP without details on people. |
Thank you for your great work! I will now export 24.000 pictures! Hooray :-) |
One more update -- I found the face regions weren't correct for a couple of EXIF orientations. I've tested this with photos of every orientation I had in my library (only EXIF 1, 3, 6, 8) and they were all correct with this latest fix. |
Here's a quick script to get the "best" 3 photos of each possible orientation that also contain faces and print out the UUIDs in a form useable by --uuid-from-file for testing: import osxphotos
photos = osxphotos.PhotosDB().photos()
for orientation in range(1,9):
# valid EXIF orientations are 1-8
face_photos = sorted([p for p in photos if p.orientation == orientation and p.face_info], key=lambda p: p.score.overall, reverse=True)
if face_photos:
num_photos = 3 if len(face_photos) >= 3 else len(face_photos)
for x in range(num_photos):
photo = face_photos[x]
print(f"#{photo.orientation}")
print(photo.uuid) |
Fixed face regions for mirrored exif orientations in v0.39.25. All exif orientations have now been tested. |
v0.40.0 now includes face regions by default in the XMP file (no need for --beta flag). All exif orientations seem to be working. Some photos rotated in Photos.app may still be incorrect. |
@RhetTbull this is really impressive. Thanks for creating this functionality. Is there a way to get this functionality working on the iphone -- I'm not familiar enough (yet) with differences between how the photos library on macosx exists vs photos on mobile. |
@wasauce osxphotos itself is a command line tool written in python and will not run on the iPhone. You can however connect an iPhone to a Mac and copy the Photos database to the Mac (using something like iMazing) then use osxphotos to read the database and output information about your photos. This is not very practical. What specifically are you trying to do on the iPhone? |
@RhetTbull - my hope is to be apply to apply some of the same approach as what you have done here in an iOS app. Obviously not in python. Goal is to be able to in an app, with permissions granted, show the photos that have names/people identified -- and then help users create profiles with those photos for those people. Are you aware of any projects that has made this work in swift / on an iOS device? Thank you again! |
@wasauce I don't believe this will be possible on iOS due to sandboxing. On iOS, the only way to access Photos and associated metadata is via the PhotoKit API and Apple does not provide a way to access information about persons in the photos via the public API. OSXPhotos works by directly reading the Photos.sqlite database in the Photos library. The database exists on iOS but it is not possible (AFAIK) to read the database from an iOS app. There is a Mac app, Face Explorer that uses the same techniques as OSXPhotos to read face data from the Photos library but again, this requires full access to the underlying database. It would be great if Apple provided better APIs for working with Photos but so far they've not shown any interest in doing so. |
@RhetTbull - Thanks very much for your detailed reply. I greatly appreciate it. Yes, I read the PhotoKit documentation and via everything I see, I don't see Apple providing a way to access information about people in the photos. Thank you for the link to Face Explorer -- interesting. What you have built here is quite impressive. Thank you again for your replies! |
The database contains much detailed info about the people/faces in the photo
The text was updated successfully, but these errors were encountered: