
Using SQL to find my best photo of a pelican according to Apple Photos - markx2
https://simonwillison.net/2020/May/21/dogsheep-photos/
======
simonw
My favourite demo is hidden pretty deep in the text - here's a SQL query that
shows the machine learning labels that were applied to each photo by Apple
Photos:

[https://dogsheep-
photos.dogsheep.net/public?sql=select%0D%0A...](https://dogsheep-
photos.dogsheep.net/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)

------
ping_pong
I didn't realize that you could search for things on Apple Photos. That's the
main reason why I use Google Photos, because its search capabilities are
amazing. When I search for "chicken" it returns both fried chicken, raw
chicken and actual chickens. I'll have to check

The thing I don't like about Apple Photos is that when you sync with your
Apple TV 4K, you can't actually play 4K 60 fps videos on your TV. I don't know
why that is, but it won't work. That's extremely disappointing to me, and I
don't understand what the reason is, unless the AppleTV 4K is underpowered.

~~~
hadrien01
The Photos app in Windows 10 has the same capability. And the Apple and
Microsoft apps are totally offline, unlike Google Photos.

~~~
giancarlostoro
Is the Windows 10 one truly offline or does it eventually phone home in some
way? Genuinely asking out of curiosity.

~~~
hashhar
The classification model runs offline. I can say this because i had it
classify a lot of pics while I was out on a trip with no internet.

But i do think it fetches newer models given the chance.

~~~
nkristoffersen
I'm pretty sure classification happens locally for Apple as well. They call it
"on-device machine learning". It doesn't mention it for MacOS photos but I can
see `photoanalysisd` running in the background on my Mac.

[https://www.apple.com/ios/photos/](https://www.apple.com/ios/photos/)

------
renewiltord
This is incredible! Thank you for sharing!

I sync my Google Photos and Apple Photos and I hope they both also run the
model on things I've uploaded from a different device. Wish the Google stuff
was also queriable like this.

~~~
simonw
Originally my plan was to upload everything to Google Photos and then export
the metadata out via their API into a SQLite database so I could query it.

Google Photos does not have a comprehensive API - and more importantly they
refuse to release geolocation information through it, which killed that option
entirely for me.
[https://issuetracker.google.com/issues/80379228](https://issuetracker.google.com/issues/80379228)

------
gok
> Try searching for “dog” and you’ll see all of the photos you’ve taken of
> dogs!

Photos also can differentiate between breeds of dogs

~~~
simonw
Oh that's brilliant, I hadn't noticed that. I just added all of my photos of
Corgis to the demo:

[https://dogsheep-
photos.dogsheep.net/public/photos_with_appl...](https://dogsheep-
photos.dogsheep.net/public/photos_with_apple_metadata?_where=uuid%20in%20\(select+uuid+from+labels+where+%22normalized_string%22+%3D+%27corgi%27\))

------
kpozin
A feature I'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 "keeper" photos,
but that's still too many to publish in an online album, and many of them are
near-duplicates anyway.

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'd easily be able to choose the best photos of the whole set,
as well as the best ones of each subject/set of duplicates. No machine
learning would be required.

Has anyone encountered a tool like this?

Edit: You wouldn't get a stable total order because the pairwise comparisons
might not hold transitively, but something like Elo ranking could work here.

~~~
mceachen
PhotoStructure has image comparison heuristics that is used for deduping, but
would enable pairwise similarity grouping. I'd tried some transfer learning in
the past to rank photos, but found it to be unusably noisy. I'll be trying
again, though, with some new approaches. Fwiw, your PhotoStructure library has
a carefully designed schema to let SQLite queries like this be trivial.

It's an interesting idea to let users pick bests out of near duplicates (and
maybe prompt the user to workthrough this UI flow on album creation). I've put
it on my to-do list!

------
ggm
How good is it, grouping duplicates with different jpeg Q and helping pick the
best, where JFIF flags have also changed date/time?

------
hackerman123469
Nice article BUT I really despise that it's so left and so small. Impossible
to read on a larger screen.

~~~
simonw
Thanks, this is useful. Made me realize that I habitually hit Shift-Command-
Plus when I'm browsing so I never notice the default text size of anything,
even my own sites!

I understand "so small" but what do you mean by "so left"? Is there a
usability enhancement in centering designs rather than left-aligning them?

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

------
wodenokoto
I don't have space for all my photos on my laptop, but I would like to use
Apple photos.

How do you guys go about that?

~~~
simonw
By default it keeps the photos in iCloud and only stores much smaller
thumbnails on your local disk.

I've turned that option off because I wanted to be able to upload the images
to S3 from the terminal, but if you just want to use it for search etc you
wouldn't need to do that.

~~~
wodenokoto
I have even less space in iCloud :)

Can I offload images to S3/GCP and still have a organize my images in Apple
Photos?

------
kccqzy
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'm
wrong.

~~~
memco
This is actually a really interesting for me because I take a lot of pictures
many are similar and I don’t actually care which ones I keep objectively
thinking but when I look at them I like one aspect of one picture and a
different aspect of a nearly identical picture. I was recently thinking about
a tool that would just take all the photos from an event and just filter out a
handful that it thinks are best and offer to delete the rest.

------
HGK1989
This is really cool -- thank you for sharing!

------
jmarcher
Anybody tried with Lightroom CC or Lightroom Classic. Classic at least (I
haven't checked CC), uses SQLite internally.

~~~
detaro
Did later versions of classic have any automatic classification? You can
indeed use the database to get your tags etc out, but at least the versions I
used didn't have any automatic tagging/labeling features.

------
toomuchtodo
Thank you for sharing!

------
annoyingnoob
Nice work, thank you.

------
hprotagonist
datasette keeps getting cooler and cooler, huh.

~~~
goldenkey
I think it reached peak coolness back in the 70s.

[https://en.wikipedia.org/wiki/Commodore_Datasette](https://en.wikipedia.org/wiki/Commodore_Datasette)

~~~
hprotagonist
[https://datasette.readthedocs.io/en/stable/](https://datasette.readthedocs.io/en/stable/)
\-- i could've been more clear.

