Using SQL to find my best photo of a pelican according to Apple Photos

My favourite demo is hidden pretty deep in the text - here&#x27;s a SQL query that shows the machine learning labels that were applied to each photo by Apple Photos:<p><a href="https:&#x2F;&#x2F;dogsheep-photos.dogsheep.net&#x2F;public?sql=select%0D%0A++photo%2C%0D%0A++%28%0D%0A++++select%0D%0A++++++json_group_array%28%0D%0A++++++++normalized_string%0D%0A++++++%29%0D%0A++++from%0D%0A++++++labels%0D%0A++++where%0D%0A++++++labels.uuid+%3D+photos_with_apple_metadata.uuid%0D%0A++%29+as+labels%2C%0D%0A++date%2C%0D%0A++albums%2C%0D%0A++persons%2C%0D%0A++ZOVERALLAESTHETICSCORE%0D%0Afrom%0D%0A++photos_with_apple_metadata%0D%0Alimit+40" rel="nofollow">https:&#x2F;&#x2F;dogsheep-photos.dogsheep.net&#x2F;public?sql=select%0D%0A...</a>

I didn&#x27;t realize that you could search for things on Apple Photos. That&#x27;s the main reason why I use Google Photos, because its search capabilities are amazing. When I search for &quot;chicken&quot; it returns both fried chicken, raw chicken and actual chickens. I&#x27;ll have to check<p>The thing I don&#x27;t like about Apple Photos is that when you sync with your Apple TV 4K, you can&#x27;t actually play 4K 60 fps videos on your TV. I don&#x27;t know why that is, but it won&#x27;t work. That&#x27;s extremely disappointing to me, and I don&#x27;t understand what the reason is, unless the AppleTV 4K is underpowered.

This is incredible! Thank you for sharing!<p>I sync my Google Photos and Apple Photos and I hope they both also run the model on things I&#x27;ve uploaded from a different device. Wish the Google stuff was also queriable like this.

&gt; Try searching for “dog” and you’ll see all of the photos you’ve taken of dogs!<p>Photos also can differentiate between breeds of dogs

A feature I&#x27;ve long wanted to see in a photo management tool is ranking based on pairwise comparisons. Say you went on a trip and had 200 &quot;keeper&quot; photos, but that&#x27;s still too many to publish in an online album, and many of them are near-duplicates anyway.<p>If the photo manager let you compare any set of photos two at a time, you could (with n-choose-2 keypresses) find something close to a total order of the set. Then you&#x27;d easily be able to choose the best photos of the whole set, as well as the best ones of each subject&#x2F;set of duplicates. No machine learning would be required.<p>Has anyone encountered a tool like this?<p>Edit: You wouldn&#x27;t get a stable total order because the pairwise comparisons might not hold transitively, but something like Elo ranking could work here.

How good is it, grouping duplicates with different jpeg Q and helping pick the best, where JFIF flags have also changed date&#x2F;time?

Nice article BUT I really despise that it&#x27;s so left and so small. Impossible to read on a larger screen.

Very cool -- is there a version of Apple Photos where this was implemented? Knowing that this data is queryable suddenly makes it very appealing as a Photo app

I don&#x27;t have space for all my photos on my laptop, but I would like to use Apple photos.<p>How do you guys go about that?

So apparently some machine learning model is secretly judging how aesthetic your photos are. I would have thought judgements of aesthetics would somehow be the last bastion of human subjectivity and human intellect. Apparently I&#x27;m wrong.

This is really cool -- thank you for sharing!

Anybody tried with Lightroom CC or Lightroom Classic. Classic at least (I haven&#x27;t checked CC), uses SQLite internally.

Thank you for sharing!

Nice work, thank you.

datasette keeps getting cooler and cooler, huh.