This is a pretty rough explanation, with some examples, of how to setup a PostgreSQL database with PostGIS, and build your timezone table that you can query, given a latitude and longitude.

LocalTimezoneDatabase.mkd

Installing and Setting a Timezone database

We're using Rails 4, PostgreSQL 9.3.4, and PostGIS 2.1.2.

Creating a Postgis enabled Postgresql template

createdb -E UTF8 template_postgis
createlang -d template_postgis plpgsq
psql -d template_postgis -c "CREATE EXTENSION postgis WITH SCHEMA public;"
psql -d template_postgis -c "CREATE SCHEMA IF NOT EXISTS topology AUTHORIZATION name;"
psql -d template_postgis -c 'alter database template_postgis set search_path="$user",public,postgis,topology;'
psql -d template_postgis -c "CREATE EXTENSION postgis_topology WITH SCHEMA topology;"

# Postgresql RDS
# alter all tables to the rds_superuser user
# alter table topology owner to rds_superuser;

Make sure it works

psql template_postgis
SELECT * FROM pg_available_extensions;

Configure Rails' config/database.yml to use the Template

add the following line to your database yaml file:

  template: template_postgis

It should look something like this:

development:
  <<: *default
  database: my_map
  template: template_postgis

Run your create & migration tasks as usual

rake db:create
rake db:migrate

You should now have all that is necessary for a postgis enabled database. Awesome!

Creating your timezone database

Now that you have your database setup and migrated, let's install the world's timezones. You should be able to add this to an existing database that is already postgis enabled, but make sure you have the following views:

• geography_columns
• geometry_columns
• raster_columns
• raster_overviews

and the following tables:

• spatial_ref_sys
• layer
• topology

If you should have them if you've enabled the following extensions:

• postgis
• postgis_topology

but it's worth making sure. Otherwise you'll run into missing function errors when importing the timezones shapefile.

Now, run the following commands to import the timezone shape file into your database, specifying the table name you want to use to hold the timezones. I've set the table name to "timezones" in the code snippet below.

curl -O http://efele.net/maps/tz/world/tz_world.zip
open tz_world.zip
cd tz_world

/usr/local/Cellar/postgis/VERSION.NUMBER/bin/shp2pgsql -IiDS -s 4326 -g geom tz_world.shp timezones | psql MYDATABASE

If you did all of the above correctly, this last command will read in the tz_world shapefile and import the data into your rails' database.

To validate that it works, use psql and run this command on the database:

SELECT tzid FROM timezones WHERE ST_Within(ST_SetSRID(ST_Point(-3.009444, 16.775833), 4326), geom);

It should return 'Africa/Bamako'.

Create a Timezones Model

Now that you have the timezone shapefile imported, create a timezone model.

rails g timezone

Delete the migration file.

Add the following method (#search) to the timezone model so you can search for a timezone given a latitude and longitude:

class Timezone < ActiveRecord::Base

  def self.search(lat, lng)
    select("tzid").where(%{
      ST_Within(
        ST_SetSRID( ST_Point(%f, %f), 4326 ),
        geom
      )
    } % [lng, lat])
  end
  
end

Now when you do a search for a lat/lng (for Oceanside, Ca.):

Timezone.search(33.267272, -117.287580)

it'll return:

#<ActiveRecord::Relation [#<Timezone gid: nil, tzid: "America/Los_Angeles">]>

No more need to use some 3rd party service like Google's Timezone, which is has limit of 2500 requests per day, 10 per second - that was a limitation for me and what spawn this.