Skip to content

Instantly share code, notes, and snippets.

@robcowie

robcowie/postgis_timezone_db.markdown

Created June 8, 2012 15:35
Show Gist options
  • Save robcowie/2896200 to your computer and use it in GitHub Desktop.
Save robcowie/2896200 to your computer and use it in GitHub Desktop.
Download ZIP
Create a postgis-compatible timezone database
Raw
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
$ 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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment