postgis_timezone_db.markdown

Create a Postgis timezone DB

Ubuntu's analog of robcowie / postgis_timezone_db.markdown

Install postgis

sudo apt-get install python-software-properties
sudo apt-add-repository ppa:ubuntugis/ppa
sudo apt-get update
sudo apt-get install postgresql-9.1-postgis

See How to Get Started with PostGIS 2.0 on Ubuntu 12.04 (precise)

Create a db user with the same name as the current system user

XXX# sudo -u postgres createuser --superuser $(whoami)

Create a GIS-enabled db

createdb timezones
psql -d timezones -c 'create extension postgis'
(optional) # psql -d timezones -c 'create extension postgis_topology'

Import timezone data from the tz_world shapefile

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

shp2pgsql -IiDS -s 4326 -g geom tz_world.shp timezone | psql timezones

See http://efele.net/maps/tz/world/

Get a timezone from (longitude, latitude)

psql timezones -c '
SELECT tzid FROM timezone
  WHERE ST_Within(ST_SetSRID(ST_Point(2.3470, 48.8742), 4326), geom);
  ' ## Paris

psql timezones -c '
SELECT tzid FROM timezone
  WHERE ST_Within(ST_SetSRID(ST_Point(-81.61027, 41.50489), 4326), geom);
  ' ## Cleveland