Skip to content

Instantly share code, notes, and snippets.

@zed

zed/postgis_timezone_db.markdown

Forked from robcowie/postgis_timezone_db.markdown
Created May 13, 2013 08:33
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save zed/5566939 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save zed/5566939 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
postgis_timezone_db.markdown

Create a Postgis timezone DB

Create postgis database template

$ createdb -E utf8 template_postgis
$ psql -d template_postgis -f /usr/local/Cellar/postgis/1.5.3/share/postgis/postgis.sql
$ psql -d template_postgis -f /usr/local/Cellar/postgis/1.5.3/share/postgis/spatial_ref_sys.sql

Create a new spatially-aware db

$ createdb -T template_postgis <mydb>

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