Setting up PostGIS for Timezone Lookup

gistfile1.md

Forked from: corbanb/gist:76d0658493e93830af48 which borrowed heavily from http://blog.shupp.org/2012/08/12/local-timezone-lookups-by-coordinates-with-postgis/ to make this geared towards mac users, which has been updated.

Step 1: Install Postgresql & Postgis + dependencies

$ psql -U <user> -d template_postgis  
$ template_postgis=> select postgis_lib_version(); should return installed version!  
$ template_postgis=> \d+tz_world  

And you'll see that the template does exist

NOW ACTUALLY CREATE DB FROM TEMPLATE

$ createdb -O <user> -Eutf8 app_test -T template_postgis
$ createdb -O <user> -Eutf8 app_development -T template_postgis

# Test that it worked with this command
$ psql -d app_development -c "SELECT postgis_full_version();"

Help Links: https://gist.github.com/1198957 http://anujjaiswal.wordpress.com/2011/06/14/installing-postgres9-0-and-postgis-on-centos/

NOTE: You may decide to just create a database without the template

Enable DB's for spatial awareness $ psql -d app_development -f /usr/local/Cellar/postgis/<version>/share/postgis/postgis.sql $ psql -d app_development -f /usr/local/Cellar/postgis/<version>/share/postgis/spatial_ref_sys.sql

Test that it worked with this command

$ psql -d app_development -c "SELECT postgis_full_version();" OR $ psql -d app_development -c "SELECT postgis_version();"

output: POSTGIS="" GEOS="3.3.5-CAPI-1.7.5" PROJ="Rel. 4.8.0, 6 March 2012" LIBXML="2.7.3" USE_STATS

Step 5: Generate shape file

1. Download shape files from https://github.com/evansiroky/timezone-boundary-builder/releases. Download desired timezone boundary file unzip into world folder and look for the .shp file.
2. Now use ship2pgsql to create a psql dump file for the tz_world table needed for timezone lookkup by polylines and import it into the database.
3. Create the dump.sql file like so (Make sure you're in the "world" directory when you run this.)

$ cd /<working_dir>/

$ /usr/local/Cellar/postgis/<version>/bin/shp2pgsql -D <filename>.shp > dump.sql

Step 6: Import the shape file

$ psql -d app_development -f dump.sql

Test timezone lookup by lat/lon, but first change the table owner to the user desired

Note that the table names should be obvious, but change depending on your shape file.

psql -d app_development
ALTER TABLE <world_table> OWNER TO user_name;
\q
psql -d app_development -U user_name
SELECT tzid FROM <world_table> WHERE ST_Contains(<geom_table>, ST_MakePoint(-122.420706, 37.776685));
OR
SELECT tzid FROM <world_table> WHERE ST_Contains(<geom_table>, ST_MakePoint(-122.420706, 37.776685));

and you should get

tzid

America/Los_Angeles