Bill Shupp Software engineer, photographer, musician, space geek

Local Timezone Lookups by Coordinates with MariaDB

I recently wrote about performing local timezone lookups with PostGIS. While this has been working well for me for many months, it requires that I keep an instance of PostgreSQL running just for this one purpose. At the time of that post, the spatial support in MySQL was pretty broken, but this should be fixed in the recent stable release of MySQL 5.6.11 (I haven't tested it).

I've been paying more attention to MariaDB recently, which is an enhanced drop-in replacement for MySQL. (Wikipedia recently switched over to it from the facebook fork of MySQL).  It is appealing to me for a couple of reasons:  First, it uses the performance enhanced fork of the InnoDB engine, XtraDB.  Also, it has had spatial support in a couple of stable releases already (introduced in MariaDB 5.3).  So I decided to move to MariaDB 5.5.30, the current stable release as of this writing.

To switch over to MariaDB, just follow the instructions on their site.  It's pretty straight forward.  I did upgrade MySQL to 5.5 before the switch, which made the move to MariaDB more of a lateral move since it's based on MySQL 5.5.  Once the switch to MariaDB was done, I then decided to test out the spatial support.  On my Ubuntu precise system, here are the steps I took to set up the tz_world database:

First, download the shapefile from efele.net.  This is the file world/tz_world.shp in the tz_world.zip archive.  Next, you'll need the shapefile -> sql tool ogr2ogr, which you can install from the gdal-bin package:

sudo apt-get install gdal-bin

Create the tz_world database and grant permissions as needed.  Once that's done, you're ready to import the shapefile into the database.  You can do this with the following command (substituting credentials):

 

ogr2ogr -progress -lco engine=MYISAM -f MySQL \
    MySQL:tz_world,user=root,password=secret,host=localhost world/tz_world.shp

 

Once this was done, I could quickly test a lookup for latitude 37.795653 and longitude -122.392128, which is in San Francisco and should have the America/Los_Angeles timezone id:

MariaDB [tz_world]> SELECT tzid FROM tz_world WHERE ST_Contains(SHAPE, POINT(-122.392128, 37.795653));
+---------------------+
| tzid |
+---------------------+
| America/Los_Angeles |
+---------------------+
1 row in set (0.00 sec)

 

Pretty easy, huh?  I tested that all coordinates in my data set returned the same timezone id from both PostGIS and MariaDB, and am pretty confident that it's all working correctly.  So I made the switch permanent.  The really nice thing about this is that lookups in MariaDB are *much* faster than in PostGIS.  A cold lookup in MariaDB is around 7ms, a warm lookup is < 1ms.  In PostGIS, both are consistently > 200ms.  Granted, it's on a separate server in the same data center instead of MariaDB being local, but pings to that system are < 1ms, so I don't think the network latency contributes that much.  So far, I'm pretty impressed with doing timezone lookups in MariaDB.