search

dogsheep / dogsheep-photos

Upload your photos to S3 and import metadata about them into a SQLite database
Apache License 2.0
171 stars 15 forks source link

Expose scores from ZCOMPUTEDASSETATTRIBUTES #15

Closed simonw closed 4 years ago

simonw commented 4 years ago

The Apple Photos database has a ZCOMPUTEDASSETATTRIBUTES that looks absurdly interesting... it has calculated scores for every photo:

Photos__ZCOMPUTEDASSETATTRIBUTES
simonw commented 4 years ago

I'm going to put these in a table called apple_photos_scores - I'll also pull in the following columns from the ZGENERICASSET table:

simonw commented 4 years ago

It looks like I can map the photos I'm importing to these tables using the ZUUID column on ZGENERICASSET to get a Z_PK which then maps to the rows in ZGENERICASSET.

simonw commented 4 years ago

Here's the query to create the new table:

create table apple_photos_scores as select
    ZGENERICASSET.ZUUID,
    ZGENERICASSET.ZOVERALLAESTHETICSCORE,
    ZGENERICASSET.ZCURATIONSCORE,
    ZGENERICASSET.ZPROMOTIONSCORE,
    ZGENERICASSET.ZHIGHLIGHTVISIBILITYSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZBEHAVIORALSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZFAILURESCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZHARMONIOUSCOLORSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZIMMERSIVENESSSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZINTERACTIONSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZINTERESTINGSUBJECTSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZINTRUSIVEOBJECTPRESENCESCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZLIVELYCOLORSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZLOWLIGHT,
    ZCOMPUTEDASSETATTRIBUTES.ZNOISESCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTCAMERATILTSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTCOMPOSITIONSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTLIGHTINGSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTPATTERNSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTPERSPECTIVESCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTPOSTPROCESSINGSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTREFLECTIONSSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZPLEASANTSYMMETRYSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZSHARPLYFOCUSEDSUBJECTSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZTASTEFULLYBLURREDSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZWELLCHOSENSUBJECTSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZWELLFRAMEDSUBJECTSCORE,
    ZCOMPUTEDASSETATTRIBUTES.ZWELLTIMEDSHOTSCORE
from
    attached.ZGENERICASSET
      join attached.ZCOMPUTEDASSETATTRIBUTES on
          attached.ZGENERICASSET.Z_PK = attached.ZCOMPUTEDASSETATTRIBUTES.Z_PK;
simonw commented 4 years ago

Twitter thread with some examples of photos that are coming up from queries against these scores: https://twitter.com/simonw/status/1257434670750408705

nickvazz commented 3 years ago

Hey Simon! I really enjoy datasette so far, just started trying it out today following your iPhone photos example.

I am not sure if you had run into this or not, but it seems like they might have changed one of the column names from ZGENERICASSET to ZASSET. Should I open a PR?

Would change:

RhetTbull commented 3 years ago

@nickvazz ZGENERICASSET changed to ZASSET in Big Sur. Here's a list of other changes to the schema in Big Sur: https://github.com/RhetTbull/osxphotos/wiki/Changes-in-Photos-6---Big-Sur

RhetTbull commented 3 years ago

@nickvazz @simonw I opened a PR that replaces the SQL for ZCOMPUTEDASSETATTRIBUTES to use osxphotos which now exposes all this data and has been updated for Big Sur. I did regression tests to confirm the extracted data is identical, with one exception which should not affect operation: the old code pulled data from ZCOMPUTEDASSETATTRIBUTES for missing photos while the main loop ignores missing photos and does not add them to apple_photos. The new code does not add rows to the apple_photos_scores table for missing photos.