zed/postgis_timezone_db.markdown

Forked from robcowie/postgis_timezone_db.markdown

Created May 13, 2013 08:33

Star (4) You must be signed in to star a gist
Fork (1) You must be signed in to fork a gist

Select an option

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/zed/5566939.js"></script>
Save zed/5566939 to your computer and use it in GitHub Desktop.

Download ZIP

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