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:
- Find a quality source of GEO data for the timezones.
- Load them into the database.
- Throw some SQL at it.
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 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")
| map("point(\(.),\(.))") | join(","))
| "INSERT INTO timezones VALUES(\"\(.)\", polygon(linestring(\(.))));"
' combined.json > timezones.sql# Append the remaining MultiPolygons.
jq -r '.features
| select(.geometry.type == "MultiPolygon")
| map("polygon(linestring(\(map("point(\(.),\(.))") | join(","))))")
] | join(","))
| "INSERT INTO timezones VALUES(\"\(.)\", multipolygon(\(.)));"
' 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:
where mbrcontains(area, point(151.2095611, -33.8862892));
I receive one row back that is
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:
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
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
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:
WHERE timezone_name = timezone) AS existing_offset,
WHERE mbrcontains(area, point(longitude, latitude))
LIMIT 1) AS new_offset
HAVING existing_offset <> new_offset
ORDER BY createdtime DESC
Originally published at http://elliot.land on January 3, 2018.