PostGIS « Bill Shupp

30Apr/130

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.

Filed under: MariaDB, MySQL, PostGIS, Technology Continue reading

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.

Filed under: MySQL, PostGIS, Technology Continue reading