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):
Note: If you want to create an index for area later you must use the MyISAM engine.
We need to convert the JSON into SQL polygons so they can be loaded into the table. jq is very powerful and perfect for this:
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):
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:
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:
Considering the UTC Offset
There are more timezone names than timezone offsets, and many of them have the same UTC offset. For example:
Will return all of the timezones that have an offset of UTC+11:00:
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:
Putting It All Together
Now we can locate the sites that may have selected the wrong timezone: