MySQL tool for geotagging old images on the Mac

[Jan.Steinman]

Many photo tools now know about GPS metadata. I wanted to make use of those capabilities with my old scanned film images. It was fairly tedious to enter essentially identical exiftool commands for widely dispersed images!

So, I came up with this four-part solution:

• Look up a place using the Apple Maps application. Unlike Google maps, it is straightforward to obtain coordinates for either a pin-drop location, or for one looked up by name. (I did not investigate automating this part of the process via an API to Apple's Maps, if such a thing exists.)
• The coordinates and other location information is then entered into a MariaDB (MySQL clone) database.
• A bash shell script is run that sucks out the proper database info and then embeds that in any number of images.
• A new MacOS Service then makes this script available via a menu selection. COMING SOON!

With this in place, I can select a bunch of images that have the same general location (which has previously been recorded in the database), use the mouse button menu to pick "Geotag Images...", fill in the "handle" to select the location from the database, and Bob's your uncle!

First things first, here is the database schema. You can put it wherever you want, then change the database table criteria in the script. It makes extensive use of MariaDB's "VIRTUAL" fields for computed columns. I figured out all the columns to fill in by finding the difference between an un-altered photo, and one that had been geo-tagged by the great GPS-tagging tool, "myTracks."

I put the non-computed columns all at the beginning, for ease of entry. Most computed columns are merely duplications of these.

Code Select Expand

CREATE TABLE `GPS_Locations` (
  `Handle` varchar(510) NOT NULL DEFAULT 'FIX ME',
  `City` varchar(510) DEFAULT NULL,
  `Country` enum('Canada','Switzerland','Cyprus','Germany','England','Italy','South Korea','United States') DEFAULT 'Canada',
  `GPSAltitude` float unsigned DEFAULT NULL COMMENT 'in metres',
  `GPSLatitude` double NOT NULL,
  `GPSLongitude` double NOT NULL,
  `Location` varchar(510) DEFAULT NULL,
  `State` varchar(510) DEFAULT NULL,
  `note` varchar(2046) DEFAULT NULL,
  `CodedCharacterSet` char(4) GENERATED ALWAYS AS ('UTF8') VIRTUAL,
  `Country-PrimaryLocationName` varchar(128) GENERATED ALWAYS AS (`Country`) VIRTUAL,
  `Country-PrimaryLocationCode` char(2) GENERATED ALWAYS AS (case `Country` when 'Canada' then 'CA' when 'Cyprus' then 'CY' when 'England' then 'GB' when 'Germany' then 'DE' when 'Italy' then 'IT' when 'South Korea' then 'KR' when 'Switzerland' then 'CH' when 'United States' then 'US' else '  ' end) VIRTUAL,
  `CountryCode` char(2) CHARACTER SET latin1 COLLATE latin1_swedish_ci GENERATED ALWAYS AS (case `Country` when 'Canada' then 'CA' when 'Cyprus' then 'CY' when 'England' then 'GB' when 'Germany' then 'DE' when 'Italy' then 'IT' when 'South Korea' then 'KR' when 'Switzerland' then 'CH' when 'United States' then 'US' else '  ' end) VIRTUAL,
  `LocationCreatedCountryCode` char(2) GENERATED ALWAYS AS (case `Country` when 'Canada' then 'CA' when 'Cyprus' then 'CY' when 'England' then 'GB' when 'Germany' then 'DE' when 'Italy' then 'IT' when 'South Korea' then 'KR' when 'Switzerland' then 'CH' when 'United States' then 'US' else '  ' end) VIRTUAL,
  `LocationShownCountryCode` char(2) GENERATED ALWAYS AS (case `Country` when 'Canada' then 'CA' when 'Cyprus' then 'CY' when 'England' then 'GB' when 'Germany' then 'DE' when 'Italy' then 'IT' when 'South Korea' then 'KR' when 'Switzerland' then 'CH' when 'United States' then 'US' else '  ' end) VIRTUAL,
  `EnvelopeRecordVersion` tinyint(1) unsigned GENERATED ALWAYS AS (4) VIRTUAL,
  `GPSAltitudeRef` char(15) GENERATED ALWAYS AS ('Above Sea Level') VIRTUAL,
  `GPSLatitudeRef` char(5) GENERATED ALWAYS AS (if(`GPSLatitude` >= 0,'North','South')) VIRTUAL,
  `GPSLongitudeRef` char(4) GENERATED ALWAYS AS (if(`GPSLongitude` >= 0,'East','West')) VIRTUAL,
  `GPSPosition` varchar(128) GENERATED ALWAYS AS (concat(`GPSLatitude`,', ',`GPSLongitude`)) VIRTUAL,
  `Keywords` varchar(2046) GENERATED ALWAYS AS (concat(convert(`City` using utf8mb3),', ',convert(`State` using utf8mb3),', ',convert(`Country` using utf8mb3),', ',convert(`Location` using utf8mb3))) VIRTUAL,
  `LocationCreatedCity` varchar(510) GENERATED ALWAYS AS (`City`) VIRTUAL,
  `LocationCreatedCountryName` varchar(510) GENERATED ALWAYS AS (`Country`) VIRTUAL,
  `LocationCreatedProvinceState` varchar(510) GENERATED ALWAYS AS (`State`) VIRTUAL,
  `LocationCreatedSublocation` varchar(510) GENERATED ALWAYS AS (`Location`) VIRTUAL,
  `LocationShownCity` varchar(510) GENERATED ALWAYS AS (`City`) VIRTUAL,
  `LocationShownCountryName` varchar(510) GENERATED ALWAYS AS (`Country`) VIRTUAL,
  `LocationShownProvinceState` varchar(510) GENERATED ALWAYS AS (`State`) VIRTUAL,
  `LocationShownSublocation` varchar(510) GENERATED ALWAYS AS (`Location`) VIRTUAL,
  `Province-State` varchar(510) GENERATED ALWAYS AS (`State`) VIRTUAL,
  `SourceFile` char(1) GENERATED ALWAYS AS ('*') VIRTUAL,
  `Sub-Location` varchar(510) GENERATED ALWAYS AS (`Location`) VIRTUAL,
  `Subject` varchar(510) GENERATED ALWAYS AS (concat(convert(`City` using utf8mb3),', ',convert(`State` using utf8mb3),', ',convert(`Country` using utf8mb3),', ',convert(`Location` using utf8mb3))) VIRTUAL,
  PRIMARY KEY (`Handle`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

Next, the bash script sucks the data out of the database for the requested location and feeds it to exiftool. Save this as "geotag" somewhere in your $PATH.

Code Select Expand

#!/bin/bash
# $Id: gpstag,v 1.3 2024/02/20 22:14:20 jan Exp jan $

typeset myname=`/usr/bin/basename $0`
typeset tmp="/tmp/${myname}-${$}.txt"

if [ ${1%%${1#-}} ]
then
cat <<-HeLpInFoRmAtIoN
usage: $myname [-] handle file|directory [file|directory...]

This command puts GPS information from a database into compatible image files.

Look up "handle" in the MariaDB table Personal:GPS_Locations and store all the
information found within the EXIF-compatible image "file" or files found, in a
way that preserves hard links.

(In the table Personal:GPS_Locations, column names must be valid EXIF tag names
in order to be embedded in specified files. Column names that are not valid
EXIF tag names are silently ignored. There must be a primary key column named
"Handle" that contains a unique identifer that is searched with the "handle"
command-line parameter.)

If a "directory" is found, recursively process all files in that directory and
its subdirectories.

If "handle" is not found in the database, exit with an error message and code.

If the first argument begins with a dash ("-"), ignore any other arguments and
display this information and exit.
HeLpInFoRmAtIoN
exit
fi

# Look up the handle and store the one-record result.
/usr/local/bin/mysql \
--user=GPS_User \
--password=ReSu_SpG \
--host=localhost \
--execute="select * from GPS_Locations WHERE handle=\"$1\"" \
Personal \
>$tmp

# If an empty database result is produced, exit with an error code for "no such address".
if ! test -s $tmp
then
rm $tmp
echo "$myname: handle \"$1\" not found. Type \"${myname} -\" for help."
exit 6
fi

shift
/usr/local/bin/exiftool \
-ignoreMinorErrors \
-overwrite_original_in_place \
-preserve \
-recurse \
-csvDelim '\t' \
-csv=${tmp} \
-XMPToolkit="Image::ExifTool `/usr/local/bin/exiftool -ver`" \
"$@"

rm $tmp

Finally, tie that script to your contextual menu... not so quick...

Ugh. This has been a PITA! The above is all working fine from bash, but in their infinite wisdom, Apple has made their GUI scripting tool (Automator) harder to use than shell scripting... I had it working with a manually-entered "handle", but then, I thought, "I can suck those out of the database and select one from a list!" Well, THAT is only working for the default list choice... seems like some variable type mismatch somewhere...

I'll put the contextual menu up when I get it working, which I thought was only going to be a few minutes... a few hours later...