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

Loading the GEO Data into MySQL

CREATE TABLE timezones (
timezone_name VARCHAR(32) NOT NULL,
area MULTIPOLYGON NOT NULL
);

Note: If you want to create an index for arealater you must use the MyISAMengine.

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

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

ALTER TABLE timezones ADD SPATIAL INDEX (area);

Considering the UTC Offset

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

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

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

Written by

I’m a data nerd and TDD enthusiast originally from Sydney. Currently working for Uber in New York. My thoughts here are my own. 🤓 elliotchance@gmail.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store