Reverse-Geocoding in Time at SFO Museum

This is a blog post by aaron cope that was published on March 26, 2021 . It was tagged golang, tools, edtf, spatial and reverse-geocoding.

Have you ever wanted to be able to reverse-geocode a point (sometimes called a “point-in-polygon” operation) not just in space but also in time? Have you ever wanted to do that date-filtering with fuzzy or imprecise dates, encoded using the Extended DateTime Format (EDTF) ? Have you ever wanted to do both of these things with an arbitrary subset of location records? Have you ever wanted to be able expose these things as a web application and an API that doesn’t need to talk to a remote database? Have you ever wanted to be able to deploy those applications both locally and as “serverless” applications running on a “cloud”-provider’s infrastructure?

Now you can.

SFO Museum definitely wants to be able to do all of these things. An airport like SFO is almost always in a constant state of change and “where” something “is” can very often be a function of “when” that event happened.

To help in answering these questions we’ve been contributing to two different open-source software projects:

The go-edtf package for parsing EDTF strings, which we wrote about in the Tools for Complex and Ambiguous Dates at SFO Museum blog post.
The go-whosonfirst-spatial packages, which define an abstract (and service-agnostic) interface for performing spatial queries and date-filtering with Who’s On First-style documents, like the ones SFO Museum uses to publish its open data records.

This blog post is not going to get in to the technical details of the go-whosonfirst-spatial layer cake. 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. These are:

The wof-sqlite-index-features tool, which is part of the go-whosonfirst-sqlite-features-index package.
The server tool, which is part of the go-whosonfirst-spatial-www and go-whosonfirst-spatial-www-sqlite packages.

In addition to the screenshots included below there is a live demo for each of the spatial applications described in this blog post. These demos, and all their data, are being run as containerized Lambda functions on Amazon’s servers. We are still feeling our way around the edges of this approach. One of the things we’ve noticed is that spatial queries are not especially fast when deployed as Lambda functions. In order to reach the actual code that is executing a spatial query each request has to transit through a number of different AWS services which introduces some very noticeable latency. Being able to deploy these kinds of spatially-enabled applications without the need to configure and maintain a separate database, though, is pretty great and the speed and performance issues are an acceptable trade-off for the time being.

Architecture at SFO

The first example is a spatial application using data from the sfomuseum-data-architecture repository. It uses a SQLite database to perform spatial operations but it’s important to remember that other spatially-enabled databases are, or will be, supported too. We create the database using a local copy of the architecture data like this:

$> cd go-whosonfirst-sqlite-features-index
$> go build -mod vendor -o bin/wof-sqlite-index-features cmd/wof-sqlite-index-features/main.go

$> ./bin/wof-sqlite-index-features \
	-all \
	-timings \
	-dsn /usr/local/data/sfomuseum-architecture.db \
	/usr/local/data/sfomuseum-data-architecture/

2021/03/25 13:11:15 time to index paths (1) 2.454395807s

And we run the spatial application like this:

$> cd go-whosonfirst-spatial-www-sqlite
$> go build -mod vendor -o bin/server cmd/server/main.go

$> ./bin/server \
	-enable-www \
	-spatial-database-uri 'sqlite://?dsn=/usr/local/data/sfomuseum-architecture.db' \
	-leaflet-tile-url 'https://tile.openstreetmap.org/{z}/{x}/{y}.png'

Opening the URL http://localhost:8080/point-in-polygon in a web browser we see this:

It is also possible to query the application’s API directly. For example this is how you might reverse-geocode a particular point at SFO filtering for records from the 1970s whose placetype is “concourse”:

$> curl -s -XPOST \
	localhost:8080/api/point-in-polygon -d \
	'{"latitude":37.617699, "longitude":-122.383661,"inception_date":"197X", "placetypes":["concourse"]}'

| jq

{
  "places": [
    {
      "wof:id": "1159396257",
      "wof:parent_id": "1159396149",
      "wof:name": "Boarding Area D",
      "wof:country": "US",
      "wof:placetype": "concourse",
      "mz:latitude": 37.61725,
      "mz:longitude": -122.38287,
      "mz:min_latitude": 37.61589952095811,
      "mz:min_longitude": 37.61725,
      "mz:max_latitude": -122.38417387690471,
      "mz:max_longitude": -122.38095701026343,
      "mz:is_current": 0,
      "mz:is_deprecated": 0,
      "mz:is_ceased": 1,
      "mz:is_superseded": 1,
      "mz:is_superseding": 1,
      "edtf:inception": "1979~",
      "edtf:cessation": "1981~",
      "wof:supersedes": [
        1159396219,
        1159396265
      ],
      "wof:superseded_by": [
        1159396283
      ],
      "wof:belongsto": [
        102527513,
        102191575,
        85633793,
        85922583,
        102087579,
        1159396331,
        1159396149,
        85688637
      ],
      "wof:path": "115/939/625/7/1159396257.geojson",
      "wof:repo": "sfomuseum-data-architecture",
      "wof:lastmodified": 1547232163
    }
  ]
}

You can see a live demo of this on the Mills Field website at:

https://millsfield.sfomuseum.org/demo/architecture/point-in-polygon

Timezones

The second example is a point-in-polygon application for all the timezones in the world. To build this application we’re going to use data from the whosonfirst-data-admin-xy repository. In this example we’ll iterate over all the records but only include those whose wof:placetype property is “timezone”.

$> ./bin/wof-sqlite-index-features \
	-all \
	-timings \
	-dsn /usr/local/data/whosonfirst-timezones.db \
	-iterator-uri 'repo://?include=properties.wof:placetype=timezone' \
	/usr/local/data/whosonfirst-data-admin-xy/
	
2021/03/25 13:38:33 time to index paths (1) 13.354624823s

Just like the SFO architecture demo you can run the application in your browser using the server tool and specifying the whosonfirst-timezones.db database that’s been created.

$> ./bin/server \
	-enable-www \
	-spatial-database-uri 'sqlite://?dsn=/usr/local/data/whosonfirst-timezones.db' \
	-leaflet-tile-url 'https://tile.openstreetmap.org/{z}/{x}/{y}.png' \
	-leaflet-initial-zoom 3

You can see a live demo of this on the Mills Field website at:

https://millsfield.sfomuseum.org/demo/timezones/point-in-polygon

California

The third example is a point-in-polygon application for all the Who’s On First records that are contained by the state of California. This application is built using data from the whosonfirst-data-admin-us repository. In this example we’ll iterate over all the records but only include those whose wof:belongsto property contains the ID 85688637 (California) and whose edtf:deprecated property is empty.

$> ./bin/wof-sqlite-index-features \
	-all \
	-timings \
	-dsn /usr/local/data/whosonfirst-california.db \
	-iterator-uri 'repo://?include=properties.wof:belongsto=85688637&exclude=properties.edtf:deprecated=.*' \
	/usr/local/data/whosonfirst-data-admin-us

2021/03/25 14:00:40 time to index paths (1) 2m26.710875349s

You can see a live demo of this on the Mills Field website at:

https://millsfield.sfomuseum.org/demo/california/point-in-polygon

The past and former Yugoslavia

The final demo is built using data from seven different Who’s On First repositories. In this example we’ll iterate over all the records for the countries of Slovenia, Croatia, North Macedonia, Bosnia and Herzegovina, Serbia, Montenegro and countries that no longer exist. These are all the countries that used to make up the former Yugoslavia, when it was the Kingdom of Yugoslavia and then the Socialist Federal Republic of Yugoslavia. We’ll also fetch the records directly from the whosonfirst-data repositories on GitHub rather than local checkouts of the data.

$> ./bin/wof-sqlite-index-features \
	-timings \
	-all \
	-iterator-uri 'git://?exclude=properties.edtf:deprecated=.*' \
	-dsn /usr/local/data/whosonfirst-yugoslavia.db \
	https://github.com/whosonfirst-data/whosonfirst-data-admin-xx.git \
	https://github.com/whosonfirst-data/whosonfirst-data-admin-si.git \
	https://github.com/whosonfirst-data/whosonfirst-data-admin-hr.git \
	https://github.com/whosonfirst-data/whosonfirst-data-admin-mk.git \
	https://github.com/whosonfirst-data/whosonfirst-data-admin-ba.git \
	https://github.com/whosonfirst-data/whosonfirst-data-admin-rs.git \
	https://github.com/whosonfirst-data/whosonfirst-data-admin-me.git

2021/03/25 14:12:24 time to index paths (7) 5m10.001781563s

Here are some screenshots from this application all centered on Koševo City Stadium in the city Sarajevo. Sarajevo was the site of the Winter Olympics in 1984 and some of the worst fighting during the dissolution of Yugoslavia during the 1990s, demonstrating again that “where” something is depends on “when” it happened.

You can see a live demo of this on the Mills Field website at:

https://millsfield.sfomuseum.org/demo/yugoslavia/point-in-polygon

We’ll write more about this work, and how it will hold hands with the collection at SFO Museum, in the weeks to come.