Local Timezone Lookups by Coordinates with PostGIS

12Aug/121

Local Timezone Lookups by Coordinates with PostGIS

For my project "Where the ISS at?", I rely on a few external services for GIS related data. For example, when a browser gets permission to determine the end user's location coordinates, a call is then made to Google to look up the local name of that location, such as San Francisco, CA, based on those coordinates. This is so that I can determine the upcoming ISS passes for the user's location. But to show that data in their local time, I need to know what timezone they are in. Unfortunately, this isn't included with the data I get back from Google. So I need to make a second call to Geonames for this data. Since this is a small, non-commercial project I do for fun, it gets expensive quickly to pay for data. And understandably the Geonames API has a lower SLA for their free service, resulting in occasional outages for timezone lookups. What would be really handy is a way to look this up locally.

I recently met up with Jaap Meijers, creator of twisst, the original and most popular twitter notification ISS site. He was kind enough to tell me about a way to do this using timezone shape files and PostGIS. Shape files are a standard format that describes polygons. PostGIS is an add on for the PostgreSQL database that "spatially enables" it for geographical information systems lookups. It's actually surprisingly easy to set up. On my Ubuntu system, I just had to install PostGIS like so:

sudo apt-get install postgresql-9.1-postgis

Once I created the database, I had to run a couple of SQL files to "spatially enable" the database I had created (called tz_world):

psql -d tz_world -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql

psql -d tz_world -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql

Then I downloaded the tz_world.zip file from efele.net, unzipped it and changed into the world directory. Once there, I had access to the shape file tz_world.shp. The next step was to use shp2pgsql to create a dump file of the tz_world table, and import it into my tz_world database:

/usr/lib/postgresql/9.1/bin/shp2pgsql -D tz_world.shp > dump.sql

psql -d tz_world -f dump.sql

And now I'm ready to test with the following sql to see if I can get the correct timezone id for San Francisco, which is at lat 37.776685 and lon -122.420706:

tz_world=# SELECT tzid FROM tz_world WHERE ST_Contains(the_geom, ST_MakePoint(-122.420706, 37.776685));
 tzid 
---------------------
 America/Los_Angeles
(1 row)

Pretty cool, huh? I found PostGIS to be very accurate, even if a little slow on my inexpensive servers. Since I was already using MySQL for this app, I thought I'd take a look at its spatial support to see if I could avoid running a second database. Unfortunately, MySQL < 5.6.1 (which is a development version), doesn't support much accuracy, as they are limited to "minium bounding rectangles" rather than precise shapes. This is faster, but produces multiple matches and often the wrong timezone id in my case. And to make things worse, versions lower than 5.6.7 (which is not even publicly available), have a bug that prevents you from using these functions with SPATIAL keys, making the results very slow (upwards of 300ms in my tests).

This was my first experience with doing my own GIS lookups, and it was a lot of fun to learn about. I figured I'd post this here, though, since I spent too much time investigating the feasibility of using MySQL for this purpose. Once their improved spatial support is stable, I'll take another look. But for now, PostGIS is the way to go.

UPDATE: Google has released support for timezones in their Maps API:

http://googlegeodevelopers.blogspot.com/2012/10/sleepy-colleagues-and-sydney-in-summer.html