↓Skip to main content
  1. Posts/

Using-SQL-to-find-my-best-photo-of-a-pelican-accor

278 words·2 mins

Using-SQL-to-find-my-best-photo-of-a-pelican-accor #

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

Created: May 22, 2020 8:02 PM URL: https://simonwillison.net/2020/May/21/dogsheep-photos/ According to the Apple Photos internal SQLite database, this is the most aesthetically pleasing photograph I have ever taken of a pelican: ! Using%20SQL%20to%20find%20my%20best%20photo%20of%20a%20pelican%20accor%202abe187079964d1284b0f800b448a8e2/cbfe463f1a67e37a1d36c5db44f0159ef6f86a0d64a987b129b63b52e555f1af.jpeg Here’s the SQL query that found me my best ten pelican photos:

select
sha256,
ext,
uuid,
date,
ZOVERALLAESTHETICSCORE
from
photos_with_apple_metadata
where
uuid in (
select
uuid
from
labels
where
normalized_string = 'pelican'
)
order by
ZOVERALLAESTHETICSCORE desc
limit
10

You can try it out here (with some extra datasette-json-html magic to display the actual photos). Using%20SQL%20to%20find%20my%20best%20photo%20of%20a%20pelican%20accor%202abe187079964d1284b0f800b448a8e2/a444857c4ac71ceae6af5192c8acc5ac35934ed589259136df0ed11295dbb085.jpeg

How this works #

Apple Photos keeps photo metadata in a SQLite database.

An aside: Why I love Apple Photos #

The Apple Photos app—on both macOS and iOS—is in my opinion Apple’s most underappreciated piece of software.

Querying the Apple Photos SQLite database #

If you run Apple Photos on a Mac (which will synchronize with your phone via iCloud) then most of your photo metadata can be found in a database file that lives here:

~/Pictures/Photos\ Library.photoslibrary/database/Photos.sqlite

Mine is 752MB, for aroud 40,000 photos. Here’s a full list, each one linking to my public photos sorted by that score:

↑