Simon Willison's Weblog: photos

Breakwater Barbecue in the El Granada station for the Ocean Shore Railroad

2025-05-04T17:09:08+00:00

Our local BBQ spot here in El Granada - Breakwater Barbecue - had a soft opening this weekend in their new location.

Here's the new building. They're still working on replacing the sign from the previous restaurant occupant:

It's actually our old railway station! From 1905 to 1920 the Ocean Shore Railroad ran steam trains from San Francisco down through Half Moon Bay most of the way to Santa Cruz, though they never quite connected the two cities.

The restaurant has some photos on the wall of the old railroad. Here's what that same building looked like >100 years ago.

Tags: history, photos, half-moon-bay

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

2020-05-21T19:16:38+00:00

According to the Apple Photos internal SQLite database, this is the most aesthetically pleasing photograph I have ever taken of a pelican:

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). Or try lemur or seal.

I actually think this is my best pelican photo, but Apple Photos rated it fifth:

How this works

Apple Photos keeps photo metadata in a SQLite database. It runs machine learning models to identify the contents of every photo, and separate machine learning models to calculate quality scores for those photographs. All of this data lives in SQLite files on my laptop. The trick is knowing where to look.

I'm not running queries directly against the Apple Photos SQLite file - it's a little hard to work with, and the label metadata is stored in a separate database file. Instead, this query runs against a combined database created by my new dogsheep-photos tool.

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. In my experience most people who use it are missing some of the most valuable features. A few highlights:

It can show you ALL of your photos on a map. On iOS go to the "Albums" tab, scroll half way down and then click on "Places" (no wonder people miss this feature!) - on macOS Photos it's the "Library -> Places" sidebar item. It still baffles me that Google Photos doesn't do this (I have conspiracy theories about it). This is my most common way for finding a photo I've taken - I remember where it was, then zoom in on that area of the map.
It runs machine learning models on your phone (or laptop) to identify the subject of your photos, and makes them searchable. Try searching for "dog" and you'll see all of the photos you've taken of dogs! I love that this runs on-device: it's much less creepy than uploading your photos to the cloud in order to do this.
It has a really great faceted search implementation - particularly in the phone app. Try searching for "dog", then add "selfie" and the name of a city to see all of the selfies you've taken with dogs in that place!
It has facial recognition, again running on device, which you can use to teach it who your friends are (autocompleting against your contacts). A little bit of effort spent training this and you can see photos you've taken of specific friends in specific places and with specific animals!

As with most Apple software, Photos uses SQLite under the hood. The underlying database is undocumented and clearly not intended as a public API, but it exists. And I've wanted to gain access to what's in it for years.

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. There's a lot of detailed metadata in there!

Querying the database isn't straight-forward. Firstly it's almost always locked by some other process - the workaround for that is to create a copy of the file. Secondly, it uses some custom undocumented Apple SQLite extensions. I've not figured out a way to load these, and without them a lot of my queries ended up throwing errors.

osxphotos to the rescue! I ran a GitHub code search for one of the tables in that database (searching for RKPerson in Python code) and was delighted to stumble across the osxphotos project by Rhet Turnbull. It's a well designed and extremely actively maintained Python tool for accessing the Apple Photos database, including code to handle several iterations of the underlying database structure.

Thanks to osxphotos the first iteration of my own code for accessing the Apple Photos metadata was less than 100 lines of code. This gave me locations, people, albums and places (human names of geographical areas) almost for free!

Quality scores

Apple Photos has a fascinating database table called ZCOMPUTEDASSETATTRIBUTES, with a bewildering collection of columns. Each one is a floating point number calculated presumably by some kind of machine learning model. Here's a full list, each one linking to my public photos sorted by that score:

I'm not enormously impressed with the results I get from these. They're clearly not intended for end-user visibility, and sorting them might not even be something that makes sense.

The ZGENERICASSET table provides four more scores, which seem to provide much more useful results:

My guess is that these overall scores are derived from the ZCOMPUTEDASSETATTRIBUTES ones. I've seen the best results from ZOVERALLAESTHETICSCORE, so that's the one I used in my "show me my best photo of a pelican" query.

A note about the demo

The demo I'm running at dogsheep-photos.dogsheep.net currently only contains 496 photos. My private instance of this has over 40,000, but I decided to just publish a subset of that in the demo so I wouldn't have to carefully filter out private screenshots and photos with sensitive locations and suchlike. Details of how the demo work (using the dogsheep-photos create-subset command to create a subset database containing just photos in my Public album) can be found in this issue.

Automatic labeling of photo contents

Even more impressive than the quality scores are the machine learning labels.

Automatically labeling the content of a photo is surprisingly easy these days, thanks to convolutional neural networks. I wrote a bit about these in Automatically playing science communication games with transfer learning and fastai.

Apple download a machine learning model to your device and do the label classification there. After quite a bit of hunting (I ended up using Activity Monitor's Inspect -> Open Files and Ports option against the photoanalysisd process) I finally figured out where the results go: the ~/Pictures/Photos\ Library.photoslibrary/database/search/psi.sqlite database file.

(Inspecting photoanalysisd also lead me to the /System/Library/Frameworks/Vision.framework/Versions/A/Resources/ folder, which solved another mystery: where do Apple keep the models? There are some fascinating files in there.)

It took some work to figure out how to match those labels with their corresponding photos, mainly because the psi.sqlite database stores photo UUIDs as a pair of signed integers whereas the Photos.sqlite database stores a UUID string.

I'm now pulling the labels out into a separate labels table. You can browse that in the demo to see how it is structured. Labels belong to numeric categories - here are some of my guesses as to what those mean:

Category 2024 appears to be actual content labels - Seal, Water Body, Pelican etc.
Category 2027 is more contextual: Entertainment, Trip, Travel, Museum, Beach Activity etc.
Category 1014 is simply the month the photo was taken. 1015 is the year, and 2030 is the season.
Category 2056 is the original filename.
Category 12 is the country the photo was taken in.

Here's a query that shows the labels (from every category) next to each photo.

Geography

Photos taken on an iPhone have embedded latitudes and longitudes... which means I can display them on a map!

Apple also perform reverse-geocoding on those photos, resolving them to cities, regions and countries. This is great for faceted browse: here are my photos faceted by country, city and state/province.

Hosting and serving the images

My least favourite thing about Apple Photos is how hard it is to get images from it onto the internet. If you enable iCloud sharing your images are accessible through icloud.com - but they aren't given publicly accessible URLs, so you can't embed them in blog entries or do other webby things with them.

I also really want to "own" my images. I want them in a place that I control.

Amazon S3 is ideal for image storage. It's incredibly inexpensive and essentially infinite.

The dogsheep-photos upload command takes ANY directory as input, scans through that directory for image files and then uploads them to the configured S3 bucket.

I designed this to work independently of Apple Photos, mainly to preserve my ability to switch to alternative image solutions in the future.

I'm using the content addressable storage pattern to store the images. Their filename is the sha256 hash of the file contents. The idea is that since sensible photo management software leaves the original files unmodified I should be able to de-duplicate my photo files no matter where they are from and store everything in the one bucket.

Original image files come with privacy concerns: they embed accurate latitude and longitude data in the EXIF data, so they can be used to reconstruct your exact location history and even figure out your address. This is why systems like Google Photos make it difficult to export images with location data intact.

I've addressed this by making the content in my S3 bucket private. Access to the images takes place through s3-image-proxy - a proxy server I wrote and deployed on Vercel (previously Zeit Now). The proxy strips EXIF data and can optionally resize images based on querystring parameters. It also serves them with far-future cache expire headers, which means they sit in Vercel's CDN cache rather than being resized every time they are accessed.

iPhones default to saving photos in HEIC format, which fails to display using with the <img src=""> tag in the browsers I tested. The proxy uses pyheif to convert those into JPEGs.

Here's an example HEIC image, resized by the proxy and converted to JPEG: https://photos.simonwillison.net/i/59854a70f125154cdf8dad89a4c730e6afde06466d4a6de24689439539c2d863.heic?w=600

Next steps

This project is a little daunting in that there are so many possibilities for where to take it next!

In the short term:

Import EXIF data from the images into a table. The Apple Photos tables give me some of this already (particularly GPS data) but I want things like ISO, aperture, what lens I used.
Load the labels into SQLite full-text search.
I'd like other people to be able to play with this easily. Getting it all up and running right now is a fair amount of work - I think I can improve this with usability improvements and better documentation.
The system only handles static images at the moment. I'd like to get my movies and more importantly my live photos in there as well.

And in the longer term:

Only iPhone photos have location data at the moment - I'd like to derive approximate latitude/longitude points for my DSLR images by matching against images from my phone based on date.
Running my photos through other computer vision systems like Google's Cloud Vision APIs could be really interesting.
For better spotting of duplicate images I'm interested in exploring image content hashing.
The UI for all of this right now is just regular Datasette. Building a custom UI (running against the Datasette JSON API) could be a lot of fun.

Tags: photography, photos, projects, sql, sqlite, datasette, dogsheep, weeknotes, apple-photos

Weeknotes: Archiving coronavirus.data.gov.uk, custom pages and directory configuration in Datasette, photos-to-sqlite

2020-04-29T19:41:11+00:00

I mainly made progress on three projects this week: Datasette, photos-to-sqlite and a cleaner way of archiving data to a git repository.

Archiving coronavirus.data.gov.uk

The UK goverment have a new portal website sharing detailed Coronavirus data for regions around the country, at coronavirus.data.gov.uk.

As with everything else built in 2020, it's a big single-page JavaScript app. Matthew Somerville investigated what it would take to build a much lighter (and faster loading) site displaying the same information by moving much of the rendering to the server.

One of the best things about the SPA craze is that it strongly encourages structured data to be published as JSON files. Matthew's article inspired me to take a look, and sure enough the government figures are available in an extremely comprehensive (and 3.3MB in size) JSON file, available from https://c19downloads.azureedge.net/downloads/data/data_latest.json.

Any time I see a file like this my first questions are how often does it change - and what kind of changes are being made to it?

I've written about scraping to a git repository (see my new gitscraping tag) a bunch in the past:

Scraping hurricane Irma - September 2017
Changelogs to help understand the fires in the North Bay - October 2017
Generating a commit log for San Francisco’s official list of trees - March 2019
Tracking PG&E outages by scraping to a git repo - October 2019
Deploying a data API using GitHub Actions and Cloud Run - January 2020

Now that I've figured out a really clean way to Commit a file if it changed in a GitHub Action knocking out new versions of this pattern is really quick.

simonw/coronavirus-data-gov-archive is my new repo that does exactly that: it periodically fetches the latest versions of the JSON data files powering that site and commits them if they have changed. The aim is to build a commit history of changes made to the underlying data.

The first implementation was extremely simple - here's the entire action:

name: Fetch latest data

on:
push:
repository_dispatch:
schedule:
    - cron:  '25 * * * *'

jobs:
scheduled:
    runs-on: ubuntu-latest
    steps:
    - name: Check out this repo
    uses: actions/checkout@v2
    - name: Fetch latest data
    run: |-
        curl https://c19downloads.azureedge.net/downloads/data/data_latest.json | jq . > data_latest.json
        curl https://c19pub.azureedge.net/utlas.geojson | gunzip | jq . > utlas.geojson
        curl https://c19pub.azureedge.net/countries.geojson | gunzip | jq . > countries.geojson
        curl https://c19pub.azureedge.net/regions.geojson | gunzip | jq . > regions.geojson
    - name: Commit and push if it changed
    run: |-
        git config user.name "Automated"
        git config user.email "actions@users.noreply.github.com"
        git add -A
        timestamp=$(date -u)
        git commit -m "Latest data: ${timestamp}" || exit 0
        git push

It uses a combination of curl and jq (both available in the default worker environment) to pull down the data and pretty-print it (better for readable diffs), then commits the result.

Matthew Somerville pointed out that inefficient polling sets a bad precedent. Here I'm hitting azureedge.net, the Azure CDN, so that didn't particularly worry me - but since I want this pattern to be used widely it's good to provide a best-practice example.

Figuring out the best way to make conditional get requests in a GitHub Action lead me down something of a rabbit hole. I wanted to use curl's new ETag support but I ran into a curl bug, so I ended up rolling a simple Python CLI tool called conditional-get to solve my problem. In the time it took me to release that tool (just a few hours) a new curl release came out with a fix for that bug!

Here's the workflow using my conditional-get tool. See the issue thread for all of the other potential solutions, including a really neat Action shell-script solution by Alf Eaton.

To my absolute delight, the project has already been forked once by Daniel Langer to capture Canadian Covid-19 cases!

New Datasette features

I pushed two new features to Datasette master, ready for release in 0.41.

Configuration directory mode

This is an idea I had while building datasette-publish-now. Datasette instances can be run with custom metadata, custom plugins and custom templates. I'm increasingly finding myself working on projects that run using something like this:

$ datasette data1.db data2.db data3.db \
    --metadata=metadata.json
    --template-dir=templates \
    --plugins-dir=plugins

Directory configuration mode introduces the idea that Datasette can configure itself based on a directory layout. The above example can instead by handled by creating the following layout:

my-project/data1.db
my-project/data2.db
my-project/data3.db
my-project/metadatata.json
my-project/templates/index.html
my-project/plugins/custom_plugin.py

Then run Datasette directly targetting that directory:

$ datasette my-project/

See issue #731 for more details. Directory configuration mode is documented here.

Define custom pages using templates/pages

In niche-museums.com, powered by Datasette I described how I built the www.niche-museums.com website as a heavily customized Datasette instance.

That site has /about and /map pages which are served by custom templates - but I had to do some gnarly hacks with empty about.db and map.db files to get them to work.

Issue #648 introduces a new mechanism for creating this kind of page: create a templates/pages/map.html template file and custom 404 handling code will ensure that any hits to /map serve the rendered contents of that template.

This could work really well with the datasette-template-sql plugin, which allows templates to execute abritrary SQL queries (ala PHP or ColdFusion).

Here's the new documentation on custom pages, including details of how to use the new custom_status(), custom_header() and custom_redirect() template functions to go beyond just returning HTML.

photos-to-sqlite

My Dogsheep personal analytics project brings my tweets, GitHub activity, Swarm checkins and more together in one place. But the big missing feature is my photos.

As-of yesterday, I have 39,000 photos from Apple Photos uploaded to an S3 bucket using my new photos-to-sqlite tool. I can run the following SQL query and get back ten random photos!

select
  json_object(
    'img_src',
    'https://photos.simonwillison.net/i/' || 
    sha256 || '.' || ext || '?w=400'
  ),
  filepath,
  ext
from
  photos
where
  ext in ('jpeg', 'jpg', 'heic')
order by
  random()
limit
  10

photos.simonwillison.net is running a modified version of my heic-to-jpeg image converting and resizing proxy, which I'll release at some point soon.

There's still plenty of work to do - I still need to import EXIF data (including locations) into SQLite, and I plan to use osxphotos to export additional metadata from my Apple Photos library. But this week it went from a pure research project to something I can actually start using, which is exciting.

TIL this week

Generated using this query.

Tags: git, http, matthew-somerville, photos, projects, datasette, weeknotes, covid19, git-scraping

Photos from our tour of the amazing bone collection of Ray Bandar

2018-02-21T04:58:35+00:00

Photos from our tour of the amazing bone collection of Ray Bandar

Ray Bandar (1927-2017) was an artist, scientist, naturalist and an incredibly prolific collector of bones. His collection is in the process of moving to the California Academy of Sciences but Natalie managed to land us a private tour lead by his great nephew. The collection is truly awe-inspiring, and a testament to an extraordinary life lived following a very particular passion.

Tags: natalie-downe, photos

PostgreSQL: How can I store images in a database? What existing products makes it easy for a user to upload photos into a general database?

2013-11-19T15:06:00+00:00

My answer to PostgreSQL: How can I store images in a database? What existing products makes it easy for a user to upload photos into a general database? on Quora

As a general rule, it's a bad idea to store images in a database. This is due to the large amount of space they take up, which can affect database read performance and will greatly increases the size of your backups, making them both take longer and cost more to store.

Instead, it's best to store the images elsewhere and then store a reference to them in your database. These days the easiest way of doing this is generally to use Amazon S3, which can cheaply and reliably store an unlimited number of images. Save them to S3, then store the S3 URL (or the bucket + key combination) in a string in your database row.

If you're determined to store them in a database you can do so using a BLOB field, or by base64 encoding them and storing them in a large text field (which will even further inflate the size of your tables).

Tags: databases, photos, postgresql, webapps, web-development, quora

For a Django application, deployed on Heroku, what are my options for storing user-uploaded media files?

2013-10-21T18:28:00+00:00

My answer to For a Django application, deployed on Heroku, what are my options for storing user-uploaded media files? on Quora

S3 is really a no-brainer for this, it's extremely inexpensive, very easy to integrate with and unbelievably reliable. It's so cheap that it will be practically free for testing purposes (expect to spend pennies a month on it).

You could store uploaded files in the Heroku database, but that will explode the size of your backups and will be much more expensive than paying for S3.

Tags: aws, django, photos, programming, python, s3, web-development, quora, heroku

Help pick the best photos, but watch out, it's addictive!

2010-01-25T00:36:35+00:00

Help pick the best photos, but watch out, it's addictive!

My favourite WildlifeNearYou feature yet—our new tool asks you to pick the best from two photos, then uses the results to rank all of the photos for each species. It’s surprisingly addictive—we had over 5,000 votes in the first two hours, peaking at 4 or 5 votes a second. The feature seems to be staying nice and speedy thanks to Redis under the hood. Photos in the top three for any given species display a medal on their photo page.

Tags: crowdsourcing, photos, projects, redis, wildlifenearyou

PhotoSketch turns a rough sketch in to a photo montage

2009-10-06T07:59:20+00:00

PhotoSketch turns a rough sketch in to a photo montage

Computer vision is really exciting at the moment—Photosketch is an application which takes a rough labeled sketch, finds images matching the labels, filters them by the sketched shapes and composes them in to a not-too-bad photo montage. As wmf on Hacker News points out, “this technology has epic potential in the LOLcat market”.

Via Hacker News

Tags: computer-vision, photos, photosketch

Building Rome in a Day

2009-07-29T15:41:03+00:00

Building Rome in a Day

“The ﬁrst system capable of city-scale reconstruction from unstructured photo collections”—computer vision techniques used to construct 3D models of cities using 10s of thousands of photos from Flickr. Reminiscent of Microsoft PhotoSynth.

Via Digital Urban

Tags: 3d, computer-vision, flickr, photos, photosynth, research, rome

Slouching towards Bethlehem

2009-07-15T10:19:25+00:00

Slouching towards Bethlehem

Photos of the various installations that contributed to the construction of the first atom bomb.

Tags: atombomb, history, nuclear, photos

Large Hadron Collider nearly ready - The Big Picture

2008-08-01T19:46:47+00:00

Large Hadron Collider nearly ready - The Big Picture

Stunningly beautiful set of photographs of the LHC. I love Big Science.

Tags: bigscience, cern, lhc, photos, thebigpicture

A Look at the Presidential Candidates

2008-07-04T21:09:10+00:00

A Look at the Presidential Candidates

The Big Picture (the Boston Globe’s fantastic photojournalism blog) presents a fascinating collection of historical photos of Senators Barack Obama and John McCain.

Tags: barack-obama, john-mccain, photography, photos, politics, thebigpicture

Video on Flickr!

2008-04-09T13:16:29+00:00

Video on Flickr!

There’s a 90 second length limit, because “... Flickr is all about sharing photos that you yourself have taken. Video will be no different and so what quickly bubbled up was the idea of long photos, of capturing slices of life to share.”

Tags: flickr, photos, video

Flickr: The Commons

2008-01-16T21:38:57+00:00

Flickr: The Commons

Exciting pilot collaboration with the Library of Congress to release images with “no known copyright restrictions”. The header photo (of a bench) is one of my favourite spots in the world, in Mission Dolores Park, San Francisco.

Tags: flickr, libraryofcongress, missiondolores, photos, publicdomain, san-francisco, the-commons

Photos taken in Brighton on Flickr!

2007-11-21T08:28:38+00:00

Photos taken in Brighton on Flickr!

The new Flickr Places feature has finally launched, and it’s absolutely beautiful.

Via Flickr: Places

Tags: brighton, flickr, photos, places

My Washington D.C. photos

2005-04-04T01:43:30+00:00

My Washington D.C. photos

Touristy photos. Includes squirrels... and ENIAC!

Tags: eniac, flickr, photos, squirrels

My photos of squirrels

2004-08-30T16:29:46+00:00

My photos of squirrels

I like squirrels, and I’ve started playing with Flickr.

Tags: flickr, photos, squirrels