Interpreting photo orientation and locations in EXIF data#

Figuring out the width and height#

The PhotoSwipe gallery code needs to have the width and heights of the images embedded in custom data- attributes like this:

1
<a
2
  data-pswp-height="3024"
3
  data-pswp-width="4032"
4
  href="https://niche-museums.imgix.net/pioneer-history-19.jpeg?w=1200&auto=compress"
5
  class="gallery-photo">
6
  <img
7
    src="https://niche-museums.imgix.net/pioneer-history-19.jpeg?w=400&blur=200&px=16&auto=format">
8
</a>

My database was only storing the URL to those images, not their widths and heights. In order to generate the correct HTML I needed that extra information.

Niche Museums hosts photos in an S3 bucket behind Imgix, which means I can resize the photos on demand using the ?w= and ?h= query parameters.

It also means I can get back JSON for the EXIF data of the images by appending ?fm=json.

https://niche-museums.imgix.net/pioneer-history-18.jpeg?fm=json

There’s a lot of information in there, but the most important bits for solving this problem are:

1
{
2
  "Orientation": 3,
3
  "PixelWidth": 4032,
4
  "PixelHeight": 3024
5
}

For my first attempt, I assumed that PixelWidth and PixelHeight represented the width and height of the image, respectively.

This almost worked… except for some photos which were displayed in portrait mode, for which the two values appeared to be swapped.

The answer turned out to be in the Orientation parameter. GPT-4 helped me understand the following:

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)

So I need to flip the width and height values if the orientation is 6 or 8.

I ended up doing that using this SQL query:

1
select
2
    'https://niche-museums.imgix.net/' || filename as url,
3
    case
4
        when Orientation in (6, 8) then PixelHeight
5
        else PixelWidth
6
    end as width,
7
    case
8
        when Orientation in (6, 8) then PixelWidth
9
        else PixelHeight
10
    end as height
11
from raw_photos;

That raw_photos table#

I skipped a step here: how did I load the data into that raw_photos table in the first place?

First I wrote a Python script to retrieve the JSON metadata for every photo. This dumped them into a photos-metadata/ directory, each one with a name like IMG_0859.jpeg.json.

That script now runs in GitHub Actions to catch metadata for newly added photos - a form of Git scraping.

Next, I wrote a sqlite-utils Python script to load all of those JSON files into my SQLite database. That script also creates a photos view that implements the above case SQL logic.

I initially wrote this as a shell script, but it was a LOT slower than doing it in Python:

1
for json_file in photos-metadata/*.json; do
2
  sqlite-utils insert browse.db raw_photos \
3
    --pk=filename \
4
    --replace \
5
    --alter \
6
    --silent \
7
    --convert "def convert(row):
8
      row['filename'] = '$(basename $json_file .json)'
9
      return row
10
    " \
11
    $json_file
12
done

The latitude/longitude problem#

Having loaded the JSON data into my SQLite table, some of my rows now had a GPS column value that looked like this:

1
{
2
    "HPositioningError": 26.4704,
3
    "Longitude": 118.301,
4
    "Latitude": 34.1191,
5
    "Speed": 0.919278,
6
    "SpeedRef": "K",
7
    "ImgDirection": 167.075,
8
    "LatitudeRef": "N",
9
    "AltitudeRef": 0,
10
    "ImgDirectionRef": "T",
11
    "LongitudeRef": "W",
12
    "Altitude": 341.598,
13
    "DestBearingRef": "T",
14
    "DestBearing": 167.075
15
}

I tried plotting that on a map using datasette-cluster-map and the following query:

1
select
2
  json_object(
3
    'image', 'https://niche-museums.imgix.net/' || filename || '?w=600',
4
    'title', filename
5
  ) as popup,
6
  json_extract(GPS, '$.Latitude') as latitude,
7
  json_extract(GPS, '$.Longitude') as longitude
8
from
9
  raw_photos
10
where json_extract(GPS, '$.Latitude') is not null

Here’s the map that produced:

I haven’t been to most of those countries, so those locations are showing in the wrong place!

I said to GPT-4:

1
{
2
    "HPositioningError": 128,
3
    "Longitude": 122.449,
4
    "Latitude": 37.7422,
5
    "Speed": 0,
6
    "SpeedRef": "K",
7
    "ImgDirectionRef": "T",
8
    "ImgDirection": 320.522,
9
    "LongitudeRef": "W",
10
    "LatitudeRef": "N",
11
    "AltitudeRef": 0,
12
    "DateStamp": "2018:02:20",
13
    "Altitude": 167,
14
    "TimeStamp": "22:44:21",
15
    "DestBearingRef": "T",
16
    "DestBearing": 320.522
17
}

How do I turn that into a latitude/longitude with correct - or +

It replied:

To convert the given JSON data into a proper latitude/longitude format with correct positive or negative signs, you’ll need to account for the LatitudeRef and LongitudeRef values. If the LatitudeRef is “S” (South), the latitude should be negative. If the LongitudeRef is “W” (West), the longitude should be negative.

I pasted in my SQL query and said “Rewrite this SQL query to apply those rules” - and it gave me the following:

1
select
2
  json_object(
3
    'image', 'https://niche-museums.imgix.net/' || filename || '?w=600',
4
    'title', filename
5
  ) as popup,
6
  case
7
    when json_extract(GPS, '$.LatitudeRef') = 'S'
8
      then -1 * json_extract(GPS, '$.Latitude')
9
    else json_extract(GPS, '$.Latitude')
10
  end as latitude,
11
  case
12
    when json_extract(GPS, '$.LongitudeRef') = 'W'
13
      then -1 * json_extract(GPS, '$.Longitude')
14
    else json_extract(GPS, '$.Longitude')
15
  end as longitude
16
from
17
  raw_photos
18
where json_extract(GPS, '$.Latitude') is not null

Try that here - it gave me the map I was looking for.