Switch photo gallery to photoswipe

[simonw]

I used it for this and really liked it: https://simonwillison.net/2022/May/16/weeknotes/

[simonw]

One catch: photoswipe needs the image height and width, which I don't currently store anywhere.

I can get that from the JSON API for imgix using https://niche-museums.imgix.net/misalignment-museum-3.jpeg?fm=json

[simonw]

I'm going to cache that JSON in full in photos-metadata/ - then I'll build a photos table in my SQLite database.

[simonw]

Next step: load the photos-metadata/*.json data into a SQLite table.

for json_file in *.json; do
  echo $json_file
  sqlite-utils insert ../browse.db photos "$json_file" --flatten --convert "def convert(row):
    row['filename'] = '$json_file'
    return row
  " --pk filename --alter --replace
done

[simonw]

The neatest way to do this would be to load that detailed metadata into a raw_photos table, then create a photos table with the filenames and widths and heights in it and use that to replace the existing photos JSON column.

But I don't want to rewrite my SQL queries that generate the RSS feed just yet, so I'm going to do a cheaper version that just creates a photos table with a url column that I can join against on the museum display page.

[simonw]

This worked, but is really slow:

for json_file in photos-metadata/*.json; do
  sqlite-utils insert browse.db raw_photos \
    --pk=filename \
    --replace \
    --alter \
    --silent \
    --convert "def convert(row):
      row['filename'] = '$(basename $json_file .json)'
      return row
    " \
    $json_file
done

I'll write it as a Python script instead.

[simonw]

SQL to get photos for a museum:

select
  json_extract(j.value, '$.url')
from
  museums,
  json_each(photos) j
where
  museums.id = 111

Demo.

[simonw]

And to get the width/height from that new photos view:

with urls as (select
  json_extract(j.value, '$.url') as url
from
  museums,
  json_each(photos) j
where
  museums.id = 111)
select url, width, height from photos where url in (select url from urls)
``

[simonw]

Confusing: you have to take Orientation into account to figure out which of PixelWidth and PixelHeight are the actual width and height.

[simonw]

GPT-4 says:

The values you mentioned (1, 3, 6, and 8) represent the most common orientations:

1: Normal (0° rotation)
3: Upside-down (180° rotation)
6: Rotated 90° counterclockwise (270° clockwise)
8: Rotated 90° clockwise (270° counterclockwise)

[simonw]

So if it's 6 or 8 I should swap width and height.

[simonw]

Here's a fun query against raw_photos:

select
  json_object(
    'image', 'https://niche-museums.imgix.net/' || filename || '?w=600',
    'title', filename
  ) as popup,
  case
    when json_extract(GPS, '$.LatitudeRef') = 'S'
      then -1 * json_extract(GPS, '$.Latitude')
    else json_extract(GPS, '$.Latitude')
  end as latitude,
  case
    when json_extract(GPS, '$.LongitudeRef') = 'W'
      then -1 * json_extract(GPS, '$.Longitude')
    else json_extract(GPS, '$.Longitude')
  end as longitude
from
  raw_photos
where json_extract(GPS, '$.Latitude') is not null

Had to take the LatitudeRef and LongitudeRef into account.

https://www.niche-museums.com/browse?sql=select%0D%0A++json_object%28%0D%0A++++%27image%27%2C+%27https%3A%2F%2Fniche-museums.imgix.net%2F%27+%7C%7C+filename+%7C%7C+%27%3Fw%3D600%27%2C%0D%0A++++%27title%27%2C+filename%0D%0A++%29+as+popup%2C%0D%0A++case%0D%0A++++when+json_extract%28GPS%2C+%27%24.LatitudeRef%27%29+%3D+%27S%27%0D%0A++++++then+-1+%2A+json_extract%28GPS%2C+%27%24.Latitude%27%29%0D%0A++++else+json_extract%28GPS%2C+%27%24.Latitude%27%29%0D%0A++end+as+latitude%2C%0D%0A++case%0D%0A++++when+json_extract%28GPS%2C+%27%24.LongitudeRef%27%29+%3D+%27W%27%0D%0A++++++then+-1+%2A+json_extract%28GPS%2C+%27%24.Longitude%27%29%0D%0A++++else+json_extract%28GPS%2C+%27%24.Longitude%27%29%0D%0A++end+as+longitude%0D%0Afrom%0D%0A++raw_photos%0D%0Awhere+json_extract%28GPS%2C+%27%24.Latitude%27%29+is+not+null&_hide_sql=1

[simonw]

Deployed to https://www.niche-museums.com/

[simonw]

Bonus query - this one shows my Niche Museums photographic history in terms of the different lenses I used to take the photos:

https://www.niche-museums.com/browse?sql=with+t+as+%28%0D%0A++select%0D%0A++++json_extract%28TIFF%2C+%27%24.Model%27%29+as+model%2C%0D%0A++++json_extract%28Exif%2C+%27%24.LensModel%27%29+as+lens%2C%0D%0A++++json_extract%28Exif%2C+%27%24.DateTimeOriginal%27%29+as+dt%2C%0D%0A++++filename%0D%0A++from%0D%0A++++raw_photos%0D%0A%29%0D%0Aselect%0D%0A++lens%2C%0D%0A++count%28*%29+as+c%2C%0D%0A++min%28dt%29+as+start%2C%0D%0A++max%28dt%29+as+end%2C%0D%0A++json_group_array%28filename%29%0D%0Afrom%0D%0A++t%0D%0Awhere%0D%0A++lens+is+not+null%0D%0Agroup+by%0D%0A++lens%0D%0Aorder+by%0D%0A++end

[simonw]

Mentioned in my weeknotes: https://simonwillison.net/2023/Apr/23/weeknotes/