Following up on my previous post, Creating your own reverse geocoder with OSM and PostGIS, I’ve found myself needing to translate a pair of GPS coordinates into a timezone. Thankfully this operation was quite a bit simpler than building a country/city/neighborhood reverse geocoder, but still worth documenting to help others going down a similar path.

Let’s get started! In order to correctly calculate the timezone containing a given coordinate we’ll need a nice map that defines the shape of all of the world’s timezones. Thankfully Evan Siroky’s timezone-boundary-builder has done much of this heavy lifting for us. In fact, we won’t even need to use his boundary builder—the releases page has the exact shapefile exports that we’ll need for our purposes.

More specifically, we’ll need to download the timezones.shapefile archive, which contains the polygons of each timezone and an associated name, formatted as America/New_York or Europe/Brussels.

We’ll also need to create a place in our database to store all of the polygons; I created a new table:

CREATE TABLE timezone_geometries (
    id SERIAL PRIMARY KEY,
    name character varying,
    geometry geometry,
    created_at timestamp without time zone
);

Technically you don’t need the created_at field, but I wanted to keep track of when I was importing timezone data just in case.

Once we’ve got the data and a place to store it, we’ll need to create a bit of glue to combine the two; that is– we’ll need an import script that sucks up our shapefile data and shoves it into our timezone_geometries table. I wrote a simple Ruby script using the rgeo-shapefile gem:

require 'rgeo/shapefile'
require 'sequel'
require 'datetime'

# I'm using sequel here but feel free to replace this with whatever database
# wrapper you feel comfortable with
connection = Sequel.connect('postgres://[...]')

RGeo::Shapefile::Reader.open('path/to/shapefile.shp') do |file|
  file.each do |record|
    puts record.attributes.inspect
    tz = connection[:timezone_geometries].insert(
      name: record.attributes['tzid'],
      geometry: record.geometry.as_text,
            created_at: DateTime.now,
    )
  end
end

Nothing too complicated here; the script opens up our shapefile, loops through each record (timezone polygon), and inserts its geospacial definition and name into the database.

One gotcha to be aware of: when you specify the path/to/shapefile.shp, make sure the directory also includes all other files contained in the archive you downloaded. That is, the directory should contain the .shp file, a .shx file, a .prj and a .dbf file. If these files aren’t present you’ll get an error while trying to open the original .shp file.

At this point we should have a populated timezone_geometries table that we can work with. Now comes the fun part: joining a bunch of lat/lons with our fresh timezone data!

In my case I had a table, locations, with the following schema:

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    lat numeric,
    lon numeric,
);

With timezone_geometries, we can add in timezone data with this query:

SELECT
    locations.id,
    timezone_geometries.name
FROM locations
JOIN timezone_geometries ON
    ST_Contains(timezone_geometries.geometry, ST_Point(locations.lon, locations.lat))

Which yields the result we’d expect:

Hopefully this guide was useful and/or saves you some time on your own geospacial timezone adventure. If you run into any issues or questions feel free to ping me on twitter or reach out to me via email.

Update, Sept 3rd, 2019:

As /u/tsimionescu kindly pointed out in their comment, you should be a bit careful about using this method when working with historical data. It’s important to note that timezones are ever-evolving artifacts of human civilization and may change unexpectedly under certain circumstances (ie a regime or policy change at a given lat/long). As an example, Spain is (incorrectly) on Central European Time, even though they are farther West than the UK. This is the result of their dictator, Francisco Franco, changing the timezone to match German-occupied Europe back in 1942.

While most modern timezone libraries should be able to handle the oddities of daylight savings time (and how it, as a policy, also evolves over time), they likely won’t be able to save you from a timezone string changing at some point in time because a dictator wanted to impress his friends. This is generally a minor concern, but something to note nonetheless!