Create a postgis-compatible timezone database

postgis_timezone_db.markdown

Create a Postgis timezone DB

See https://gist.github.com/3449216

Create a timezone db from shapefiles

$ curl -O http://efele.net/maps/tz/world/tz_world.zip
$ wget http://efele.net/maps/tz/us/tz_us.zip
$ open tz_world.zip
$ cd world
$ createdb -T template_postgis timezones
$ /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -IiDS -s 4326 -g geom tz_world.shp timezone | psql timezones

Querying

SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(-3.009444, 16.775833), 4326), geom); ## Timbuktu
SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(2.3470, 48.8742), 4326), geom); ## Paris
SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(-92.988281, 38.023862), 4326), geom); ## Missouri (US)

SELECT tzid FROM timezone WHERE ST_Within(ST_SetSRID(ST_Point(-149.818904068722 61.1467316858577), 4326), geom);

SRID 4326 is the grid system used in the shapefiles. It is possible to convert it so we don't need to convert on query.

See

• http://linfiniti.com/2010/03/batch-importing-shapefiles-into-postgis/
• http://postgis.refractions.net/documentation/manual-1.4/ch02.html#id2754935
• http://www.gisnotes.com/wordpress/2011/11/homebrew-postgresql9-0-4-postgis-1-5-3/
• http://efele.net/maps/tz/world/
• http://blog.shupp.org/2012/08/12/local-timezone-lookups-by-coordinates-with-postgis/