I was updating my about page and wanted to add a timeline of places I’ve travelled to. I’ve got thousands of geotagged photos in my Photos library spanning over a decade, and manually going through them would’ve been painful. Here’s how I extracted the data using Python and SQLite.

The Challenge Link to heading

Photos.app doesn’t have a built-in way to export a list of countries from geotagged photos. I needed to:

  1. Find all photos with location data
  2. Extract the country name from each
  3. Group by year to build a timeline

What Didn’t Work Link to heading

Before finding the solution, I tried a few approaches that didn’t pan out:

osxphotos CLI tool - This is a brilliant tool for querying Photos, but it’s designed for comprehensive metadata extraction. Running osxphotos query --json on my 24,000+ photo library was taking forever, and I only needed country-level location data.

AppleScript - I tried querying Photos via AppleScript to iterate through media items and extract location data. It was slow and unreliable, timing out on larger queries.

After banging my head against these approaches, I decided to go straight to the source: the Photos SQLite database.

The Working Solution Link to heading

Photos stores its data in ~/Pictures/Photos Library.photoslibrary/database/Photos.sqlite. The location data lives in the ZADDITIONALASSETATTRIBUTES table in a field called ZREVERSELOCATIONDATA.

The tricky bit? This field stores location data as a binary plist using Apple’s NSKeyedArchiver format. You can’t just decode it as a simple plist - you need to parse the object graph.

Here’s the Python script that worked:

import sqlite3
import plistlib
from collections import defaultdict
from datetime import datetime

def extract_country_from_plist(plist_data):
    """Extract country from the complex NSKeyedArchiver plist structure"""
    try:
        objects = plist_data.get('$objects', [])

        # Find place names with their place types
        # Place type 1 = country, 2 = state/province, 4 = city, etc.
        for obj in objects:
            if isinstance(obj, dict) and 'placeType' in obj and 'name' in obj:
                place_type = obj.get('placeType')

                # Resolve UID reference
                if hasattr(place_type, 'data'):
                    type_idx = int.from_bytes(place_type.data, 'big') if isinstance(place_type.data, bytes) else place_type.data
                    place_type_val = objects[type_idx] if type_idx < len(objects) else None
                else:
                    place_type_val = place_type

                # Check if this is country level (type 1)
                if place_type_val == 1:
                    name_ref = obj.get('name')
                    if hasattr(name_ref, 'data'):
                        name_idx = int.from_bytes(name_ref.data, 'big') if isinstance(name_ref.data, bytes) else name_ref.data
                        country_name = objects[name_idx] if name_idx < len(objects) else None
                        if isinstance(country_name, str):
                            return country_name

        # Fallback: look for _country field in postal address
        for obj in objects:
            if isinstance(obj, dict) and '_country' in obj:
                country_ref = obj.get('_country')
                if hasattr(country_ref, 'data'):
                    country_idx = int.from_bytes(country_ref.data, 'big') if isinstance(country_ref.data, bytes) else country_ref.data
                    country_name = objects[country_idx] if country_idx < len(objects) else None
                    if isinstance(country_name, str):
                        return country_name
                elif isinstance(country_ref, str):
                    return country_ref

    except Exception as e:
        pass
    return None

# Connect to Photos database
db_path = "/Users/brtkwr/Pictures/Photos Library.photoslibrary/database/Photos.sqlite"
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# Query for assets with location data and dates
query = """
SELECT
    datetime(ZASSET.ZDATECREATED + 978307200, 'unixepoch', 'localtime') as photo_date,
    ZADDITIONALASSETATTRIBUTES.ZREVERSELOCATIONDATA as location_data
FROM ZASSET
JOIN ZADDITIONALASSETATTRIBUTES ON ZASSET.Z_PK = ZADDITIONALASSETATTRIBUTES.ZASSET
WHERE ZADDITIONALASSETATTRIBUTES.ZREVERSELOCATIONDATA IS NOT NULL
ORDER BY ZASSET.ZDATECREATED
"""

cursor.execute(query)
country_dates = defaultdict(set)

for row in cursor.fetchall():
    photo_date, location_data = row

    if location_data:
        try:
            plist = plistlib.loads(bytes(location_data))
            country = extract_country_from_plist(plist)

            if country and photo_date:
                dt = datetime.strptime(photo_date, '%Y-%m-%d %H:%M:%S')
                year_month = dt.strftime('%Y-%m')
                country_dates[country].add(year_month)
        except Exception as e:
            continue

# Print results grouped by year
for country in sorted(country_dates.keys()):
    dates = sorted(country_dates[country])
    print(f"\n{country}:")

    years = defaultdict(list)
    for date in dates:
        year, month = date.split('-')
        years[year].append(month)

    for year in sorted(years.keys()):
        months = sorted(set(years[year]), key=lambda x: int(x))
        month_names = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
                       'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
        month_labels = [month_names[int(m)-1] for m in months]
        print(f"  {year}: {', '.join(month_labels)}")

conn.close()

Key Insights Link to heading

NSKeyedArchiver gotcha: The location data isn’t a simple dictionary. It’s an object graph where values are often stored as UID references pointing to other objects in the $objects array. You need to follow these references to get the actual string values.

Place types: The plist contains multiple place entries with different placeType values (country=1, state/province=2, city=4, etc.). Filtering for placeType=1 gives you country-level data.

Photos timestamp: The ZDATECREATED field uses Apple’s reference date (2001-01-01 00:00:00 UTC). Add 978307200 seconds to convert to Unix epoch.

Performance: This approach processed 17,000+ geotagged photos in about 30 seconds. Much faster than the alternatives.

Data coverage: The earliest geotagged photos in my library are from 2013. I travelled before then, but those photos either weren’t taken on a smartphone or weren’t automatically geotagged.

Pre-2013 trips: For earlier travel, I supplemented the data by scanning DSLR photo folder names. Folder names like “France Spain Portugal” or “New York 2008” gave me country-level data. Where possible, I extracted months from EXIF creation dates using sips -g creation on sample photos. This approach worked well but required manual verification - some folders were named after family visits rather than my own trips, so I had to cross-check each one.

Credit Where It’s Due Link to heading

I figured this out working with Claude Code, iterating through different approaches until we found one that worked. AI-assisted development is brilliant for this kind of exploratory work - trying osxphotos, AppleScript, and finally the SQLite approach without having to research each one from scratch.

Further Reading Link to heading