Updating (and reverse-geocoding) GPS EXIF metadata
In the last blog post Updating EXIF metadata in JavaScript (and WebAssembly) I wrote:
Currently only EXIF tags that are stored as strings are supported [when dynamically updating EXIF information in JavaScript]. I am still trying to get familiar with the requirements of the go-exif package so this is just a first step. I am hoping to enable the writing of numeric EXIF tags soon because the other place where SFO Museum has a save-to-file camera button that would benefit from dynamically-written EXIF tags is the historical maps webpage.
I am happy to say this is now enabled on the Mills Field historical maps webpage. For example when I save an image of this view of the runways at SFO, from 2017:
The newly created image’s EXIF data contains not only the date and the permanent URL for the map but also the latitude and longitude coordinates for the map’s center point:
$> exiv2 -pa ~/Downloads/20210421_2017_17_37.61297_-122.36133.png
Exif.Image.GPSTag Long 1 76
Exif.GPSInfo.GPSLatitude Rational 3 37deg 36' 46"
Exif.GPSInfo.GPSLatitudeRef Ascii 2 North
Exif.GPSInfo.GPSLongitude Rational 3 122deg 21' 40"
Exif.GPSInfo.GPSLongitudeRef Ascii 2 West
Exif.Image.DateTime Ascii 9 20210421
Exif.Image.ExifTag Long 1 178
Exif.Photo.ImageUniqueID Ascii 66 https://millsfield.sfomuseum.org/map/#2017/17/37.61297/-122.36133
Exif.Image.DateTimeOriginal Ascii 5 2017
This new functionality to write GPS tags and other EXIF properties encoded as “rational” values is available in the v0.0.3 (or higher) release of the go-exif-update package:
In order to test that these new GPS tags were being encoded correctly I uploaded the map image to Flickr which will automatically read location information from EXIF tags and, when present, place your photo on a map.
This screenshot of our map image on Flickr shows that the EXIF data was successfully imported and the image is geotagged at the end of runway 10R/28L. Curiously, Flickr thinks the end of runway 10R/28L is in Burlingame.
Looking at their map I was reminded that in the Reverse-Geocoding in Time at SFO Museum blog post I wrote:
This blog post is meant to be a quick show-and-tell to demonstrate two of the tools used to make all of this possible, one for creating spatial databases from arbitrary Who’s On First records and the other for performing spatial queries using that data.
With that in mind, I made a set of tools, called go-flickr-pip, that call the Flickr API to retrieve geotagged photos and then calls the local reverse-geocoding tools (described in the Reverse-Geocoding in Time at SFO Museum blog post and running on my laptop) to resolve where each one of those photos is located at SFO.
The first step was to create a new SQLite database from the sfomuseum-data-architecture data using the wof-sqlite-index-features tool:
$> ./bin/wof-sqlite-index-features \
-all \
-dsn /usr/local/data/sfomuseum-architecture.db \
/usr/local/data/sfomuseum-data-architecture/
2021/04/22 12:49:41 time to index paths (1) 2.702893585s
Then I used that database as the data source for a local instance of the go-whosonfirst-spatial-www-sqlite server running on my laptop:
$> ./bin/server \
-spatial-database-uri 'sqlite://?dsn=/usr/local/datasfomuseum-architecture.db'
2021/04/22 12:50:29 time to index paths (0) 25.819µs
12:50:29.601179 [server] STATUS finished indexing in 425.886µs
The server exposes a simple web API for “reverse-geocoding” a point to one or more places which contain that point. For example, here’s what the server returns for the coordinates assigned to our image of the map from 2017:
$> curl -s -XPOST http://localhost:8080/api/point-in-polygon -d '{"latitude": 37.612777, "longitude": -122.361112}' | jq
{
"places": [
{
"wof:id": "1730008749",
"wof:parent_id": "102527513",
"wof:name": "RUNWAY 10R/28L",
"wof:country": "US",
"wof:placetype": "custom",
"mz:latitude": 37.61900555744979,
"mz:longitude": -122.37573763583393,
"mz:min_latitude": 37.6114694151077,
"mz:min_longitude": 37.61900555744979,
"mz:max_latitude": -122.39327941811621,
"mz:max_longitude": -122.35819198079813,
"mz:is_current": 1,
"mz:is_deprecated": 0,
"mz:is_ceased": 0,
"mz:is_superseded": 0,
"mz:is_superseding": 0,
"edtf:inception": "",
"edtf:cessation": "..",
"wof:supersedes": [],
"wof:superseded_by": [],
"wof:belongsto": [
102527513,
102191575,
85633793,
102087579,
85922583,
554784711,
85688637,
102085387
],
"wof:path": "173/000/874/9/1730008749.geojson",
"wof:repo": "sfomuseum-data-architecture",
"wof:lastmodified": 1619054586
}
]
}
SFO Museum’s own architecture data tells us that this point is contained by runway 10R/28L rather than Burlingame.
Finally I ran the pip tool in the go-flickr-pip package to reverse-geocode geotagged photos in Flickr using SFO Museum data. This is what the output looks like for publicly-viewable photos in the aaronofsfo Flickr account:
$> go run -mod vendor cmd/pip/main.go \
-client-uri 'oauth1://?consumer_key=...&consumer_secret=...' \
-param method=flickr.photos.search \
-param user_id=161215698@N03 \
-param has_geo=1 \
-param extras=geo
photo_id,latitude,longitude,whosonfirst_id,whosonfirst_name,whosonfirst_placetype
51130478394,37.615555,-122.388889,1729792681,International Terminal Connector,concourse
51130478394,37.615555,-122.388889,1729792579,International Terminal,wing
51130478394,37.615555,-122.388889,1729792387,SFO Terminal Complex,building
51130478394,37.615555,-122.388889,1729792679,International Terminal Main Hall,concourse
51131288670,37.612777,-122.361112,1730008749,RUNWAY 10R/28L,custom
The pip tool is designed to operate with any Flickr API method that returns a standard photo response so it will also work fetching and reverse-geocoding all the openly-licensed and geotagged photos from the airports-sfo Flickr group:
$> go run -mod vendor cmd/pip/main.go \
-client-uri 'oauth1://?consumer_key=...&consumer_secret=...' \
-param method=flickr.photos.search \
-param group_id=95693046@N00 \
-param has_geo=1 \
-param extras=geo \
-param license=1,2,3,4,5,6,7,8,9,10 \
> airports.csv
I’ve uploaded a copy of the airports.csv file if you want to see all the results but in the interest of brevity here’s the unique list of places those photos depict when we query SFO Museum’s own architecture data:
$> cat airports-sfo.csv | awk -F ',' '{ print $5 }' | sort | uniq
Boarding Area D
Boarding Area F
Boarding Area G
Central Parking Garage
D-13 Kids’ Spot
Garage A
Garage A AirTrain Station
Garage G
Garage G AirTrain Station
International Terminal
International Terminal Connector
International Terminal Departures (Post-Security)
International Terminal Main Hall
Null Terminal
RUNWAY 01L/19R
RUNWAY 10R/28L
SFO Bart Station
SFO Terminal Complex
Taxiway A/F1
Taxiway D
Taxiway G
Taxiway Q
Terminal 2
Terminal 2 Air Train Station
Terminal 2 Main Hall
Terminal Two Arrivals
Null Terminal, if you’re wondering, is a liminal space located at the intersection of the two runways where we place things that we know are “at” SFO but we’re still not exactly sure where.
As the Reverse-Geocoding in Time at SFO Museum blog post demonstrated there are actually many other “places” those photos might “be” depending on when they were taken but, for now, the tools in the go-flickr-pip package are designed to limit results to places that are considered “current”.
In the Surface Areas – Photos and Depictions on the Mills Field Website blog post I wrote about how SFO Museum has started to import openly-licensed Flickr photos of airports and airplanes relevent to SFO Museum and said:
Some of those 100,000 photos aren’t relevant or germane to our collection (and some of them aren’t even at airports because geotagging photos is hard) so the immediate concern will be developing tools and interfaces to let the museum work through the queue.
The go-flickr-pip tool is mostly a demonstration of what’s possible but it also serves as kind of “training-wheels” for how we might automate and improve the processes that we use for importing data from Flickr or any other third-party data source. It’s also very relevant to our on-going work building tools for geotagging our own collection objects.
These tools and the data that we used to do this work are not specific to Flickr or SFO Museum either. The other day we released the smk-open-to-wof package which contains “tools for building a mapping of National Gallery of Denmark (SMK) collection objects and their latitude and longitude coordinates to corresponding Who’s On First IDs”.
It uses the same wof-sqlite-index-features and go-whosonfirst-spatial-www-sqlite tools that we used to reverse-geocode Flickr photos at SFO but with location data for the country of Denmark and geotagged collections data from the National Gallery of Denmark. For example here are a few of the collection objects that are located in WOF ID 102544207 which is the Copenhagen Airport, an airport relevant to the history and interests of SFO Museum:
> git grep 102544207 smk-open-wof.csv
smk-open-wof.csv:1684,55.63568419562137,12.640782935717771,102544207,85907903,Copenhagen Airport,campus
smk-open-wof.csv:1680,55.62584400000001,12.6227171,102544207,85907903,Copenhagen Airport,campus
smk-open-wof.csv:2314,55.59660361967791,12.634810984130844,102544207,85907903,Copenhagen Airport,campus
smk-open-wof.csv:2316,55.594276030589086,12.634510576721176,102544207,85907903,Copenhagen Airport,campus
smk-open-wof.csv:2301,55.62714440000001,12.6236595,102544207,85907903,Copenhagen Airport,campus
smk-open-wof.csv:3114,55.61155381950298,12.669836623767088,102544207,85907903,Copenhagen Airport,campus
smk-open-wof.csv:3173,55.61482775161416,12.66678963432617,102544207,85907903,Copenhagen Airport,campus
smk-open-wof.csv:3177,55.613474191461094,12.668248756030271,102544207,85907903,Copenhagen Airport,campus
Coming full circle, the description for the photo of South Terminal, above, reads:
35mm photographic color negative depicting exterior view taken from upper level of Central Terminal (now Terminal 2 / T2) facing southwest of South Terminal (now Terminal 1 / T1) gates with airplanes parked, roadway and parking lot; Millbrae and Burlingame visible in background.
And it turns out that it’s one of the objects we’ve geotagged already as part of Geotagging at SFO Museum blog posts. I mentioned earlier that the go-flickr-pip tool is limited to returning records that are considered “current”. In future blog posts I’ll talk about how we plan to use these reverse-geocoding tools to help locate objects in time and how that work will be integrated with our tools for geotagging collections objects.