Calculating the Timezone From the Latitude/Longitude in SQL

I needed the ability to lookup a timezone based on a GPS coordinate to verify that the timezones people selected when signing up were correct for the address they had entered. Since this was a once off I decided against building a script that uses an external API, rather to just do it in MySQL.

The process is fairly straight forward:

1. Find a quality source of GEO data for the timezones.
2. Load them into the database.
3. Throw some SQL at it.

Preparation

First, download the latest GeoJSON data from the releases page (timezones.geojson.zip) over at the evansiroky/timezone-boundary-builder project. After uncompressing it you should have a large file called combined.json. This contains polygon information for each of the timezones.

Loading the GEO Data into MySQL

Here is the table we will be loading into (you will need to create it now):

CREATE TABLE timezones (
    timezone_name VARCHAR(32) NOT NULL,
    area GEOMETRY SRID 0 NOT NULL,
    PRIMARY KEY (timezone_name),
    SPATIAL INDEX (area)
) ENGINE=InnoDB;

We need to convert the JSON into SQL polygons so they can be loaded into the table. jqis very powerful and perfect for this:

# Process the Polygons first.
jq -r '.features[]
    | select(.geometry.type == "Polygon")
    | [
        (.properties.tzid),
        (.geometry.coordinates[0]
            | map("point(\(.[0]),\(.[1]))") | join(","))
    ]
    | "INSERT INTO timezones VALUES(\"\(.[0])\", polygon(linestring(\(.[1]))));"
' combined.json > timezones.sql
# Append the remaining MultiPolygons.
jq -r '.features[]
    | select(.geometry.type == "MultiPolygon")
    | [
        (.properties.tzid),
        ([
            (.geometry.coordinates[]
                | map("polygon(linestring(\(map("point(\(.[0]),\(.[1]))") | join(","))))")
                | .[0]
            )
        ] | join(","))
    ]
    | "INSERT INTO timezones VALUES(\"\(.[0])\", multipolygon(\(.[1])));"
' combined.json >> timezones.sql

It may take some time to produce this table, but you should end up with a large (133 mb) SQL file.

Now we can load the timezones and polygons in (fill in your appropriate mysql CLI options):

mysql mydb < timezones.sql

If all goes well you should see no output.

Verify A Single Timezone

You can find the coordinates for any address easily here: https://www.gps-coordinates.net

When creating the SQL POINT from the GPS coordinates it will need to be in the form of (longitude, latitude). Here is an example that finds which timezone our office is located in:

SELECT timezone_name
FROM timezones
WHERE mbrcontains(area, point(151.2095611, -33.8862892));

I receive one row back that is Australia/Sydney.

Speeding It Up

On my modest laptop the query above took around 0.04 seconds. This speed is fine for me in testing, however, if you were to use this in production (or just needed/wanted it to be faster) you can also create an index to significantly speed it up:

ALTER TABLE timezones ADD SPATIAL INDEX (area);

Considering the UTC Offset

There are more timezone names than timezone offsets, and many of them have the same UTC offset. For example:

SELECT timezone_name
FROM timezones
WHERE TIME_FORMAT(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), timezone_name, 'UTC')), '%H:%i') = '11:00';

Will return all of the timezones that have an offset of UTC+11:00:

Antarctica/Macquarie
Asia/Magadan
Australia/Currie
Australia/Hobart
Australia/Lord_Howe
Australia/Melbourne
Australia/Sydney
Pacific/Efate
Pacific/Kosrae
Pacific/Norfolk

This means that we cannot simply compare the timezone name to verify the correct timezone was selected. This is exactly what I needed to do so I prepopulated all of these offsets in to the existing timezones table:

ALTER TABLE timezones ADD offset CHAR(6);
UPDATE timezones
SET offset = TIME_FORMAT(TIMEDIFF(NOW(), CONVERT_TZ(NOW(), timezone_name, 'UTC')), '%H:%i');

Putting It All Together

Now we can locate the sites that may have selected the wrong timezone:

SELECT
  createdtime,
  sitename,
  (SELECT offset
   FROM timezones
   WHERE timezone_name = timezone) AS existing_offset,
  (SELECT offset
   FROM timezones
   WHERE mbrcontains(area, point(longitude, latitude))
   LIMIT 1)                        AS new_offset
FROM site
HAVING existing_offset <> new_offset
ORDER BY createdtime DESC

Thanks to Cyrille B. Delavenne for submitting corrections for MySQL 8.

Originally published at http://elliot.land on January 3, 2018.