13 ms·
Using SQL to find my best photo of a pelican according to Apple Photos
- hprotagonist 6y agodatasette keeps getting cooler and cooler, huh.
- goldenkey 6y agoI think it reached peak coolness back in the 70s. https://en.wikipedia.org/wiki/Commodore_Datasette https://en.wikipedia.org/wiki/Commodore_Datasette
- hprotagonist 6y agohttps://datasette.readthedocs.io/en/stable/ https://datasette.readthedocs.io/en/stable/ -- i could've been more clear.
- renewiltord 6y agoThis 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 6y agoOriginally 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
- ping_pong 6y agoI 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.
- sp332 6y agoLots of details about Apple TV 4k video playback at https://www.theverge.com/2017/9/21/16341876/new-apple-tv-4k-review-2017 https://www.theverge.com/2017/9/21/16341876/new-apple-tv-4k-...
- ping_pong 6y agoThanks. I just read the article and it's interesting but I think it's old. I do believe that you can match the frame rate with the original source in AppleTV now, and it also supports Dolby Atmos. Unfortunately my Sonos doesn't which is a real bummer. The biggest issue for me is that I can upload a 4K video at 60 fps on Photos but it comes out terribly, with stuttering and looks terrible. It defeats the purpose of using 4K60 on the iPhone is you can't actually view it on a TV.
- hadrien01 6y agoThe Photos app in Windows 10 has the same capability. And the Apple and Microsoft apps are totally offline, unlike Google Photos.
- giancarlostoro 6y agoIs the Windows 10 one truly offline or does it eventually phone home in some way? Genuinely asking out of curiosity.
- deleted 6y ago
- annoyingnoob 6y agoNice work, thank you.
- simonw 6y agoMy 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++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 https://dogsheep-photos.dogsheep.net/public?sql=select%0D%0A...
- HGK1989 6y agoThis is really cool -- thank you for sharing!
- jmarcher 6y agoAnybody tried with Lightroom CC or Lightroom Classic. Classic at least (I haven't checked CC), uses SQLite internally.
- detaro 6y agoDid 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 6y agoThank you for sharing!
- ggm 6y agoHow good is it, grouping duplicates with different jpeg Q and helping pick the best, where JFIF flags have also changed date/time?
- gok 6y ago> 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 6y agoOh 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_apple_metadata?_where=uuid%20in%20(select+uuid+from+labels+where+%22normalized_string%22+%3D+%27corgi%27) https://dogsheep-photos.dogsheep.net/public/photos_with_appl...
- kccqzy 6y agoSo 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 6y agoThis 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.
- kpozin 6y agoA 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.
- Amatewasu 6y agoI created an Android app to rank your photos (up to 30 at a time) using an AI (it is a new feature, I did not update the play store page yet). https://play.google.com/store/apps/details?id=me.delrieu.bestphoto https://play.google.com/store/apps/details?id=me.delrieu.bes...
- mceachen 6y agoPhotoStructure 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!
- forgingahead 6y agoVery 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 6y agoI 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?
- sytelus 6y agoI have never managed to get iCloud Photos work on Windows. That means I can't even actually download all the photos I have taken. It's one horrible app.
- mceachen 6y agoI've used photos on an external drive, but it must be afs+, and don't expect it to keep working past ~100-200k images. My frustration with photos helped motivate me to write PhotoStructure.
- simonw 6y agoBy 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 6y agoI have even less space in iCloud :) Can I offload images to S3/GCP and still have a organize my images in Apple Photos?
- hackerman123469 6y agoNice article BUT I really despise that it's so left and so small. Impossible to read on a larger screen.
- simonw 6y agoThanks, 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?