Setting up PostGIS for Timezone Lookup

gistfile1.md

https://gist.github.com/juniorz/1081907

I borrowed heavily from http://blog.shupp.org/2012/08/12/local-timezone-lookups-by-coordinates-with-postgis/ to make # this gist which is geared towards mac users like myself.

Step 1: Prepare to Install Postgresql & Postgis + dependencies using brew

$ brew update

NOTE: if it fails, try this

$ cd /usr/local/Library/Formula $ git reset --hard HEAD $ brew update

NOTE: if postgres or postgis already exist, then uninstall them first, do this for each database or you may wan to upgrade the database if you're upgrading postgres from 9.0 to 9.1 for example.

$ dropdb -U helios -i helios_development $ dropdb -U helios -i helios_test

$ brew uninstall postgis $ brew uninstall postgresql

Step 2: Install Postgresql & Postgis

NOTE: I wanted an older version of postgresql (9.2.4) and postgis (2.0.3) so i had to run

$ brew versions postgis $ cd /usr/local/Library/Formula $ git checkout e9799d5 /usr/local/Library/Formula/postgis.rb (This may be different from what you want)

and the same with 'brew versions postgresql'...

Now, time to install everything with one easy command!

$ brew install postgis

NOTE: if you get an error (you will), check out the comments at the end of this Gist but basically you need to:

$ vi /usr/local/Library/Formula/postgresql.rb and then swap out the path for postgresql

Step 3: follow the instructions displayed here

$ brew info postgresql

NOTE: Depending on if you have an existing install of postgresql or not, just follow the instructions for setting up the LaunchAgent

Step 4: Create database user and database template

$ createuser helios --interactive - n y n $ Shall the new role be a superuser? $ Shall the new role be allowed to create databases? y $ Shall the new role be allowed to create more new roles? n $ createdb -E UTF8 -U helios template_postgis $ createlang -d template_postgis plpgsql (might fail but okay) $ psql -d template_postgis -f /usr/local/Cellar/postgis/2.0.3/share/postgis/postgis.sql $ psql -d template_postgis -f /usr/local/Cellar/postgis/2.0.3/share/postgis/spatial_ref_sys.sql $ psql -d template_postgis -f ~/work/helios/db/sql/dump.sql $ psql -d template_postgis -c 'GRANT ALL ON geometry_columns TO public;' $ psql -d template_postgis -c 'GRANT ALL ON geography_columns TO public;' $ psql -d template_postgis -c 'GRANT ALL ON spatial_ref_sys TO public;' $ psql -d template_postgis -c 'GRANT ALL ON tz_world TO public;' $ psql -d template_postgis -c 'ALTER TABLE geometry_columns OWNER TO helios;' $ psql -d template_postgis -c 'ALTER TABLE geography_columns OWNER TO helios;' $ psql -d template_postgis -c 'ALTER TABLE spatial_ref_sys OWNER TO helios;' $ psql -d template_postgis -c 'ALTER TABLE tz_world OWNER TO helios;'

$ psql -U helios -d template_postgis $ template_postgis=> select postgis_lib_version(); should return 2.0.3! $ template_postgis=> \d+tz_world

And you'll see that the template does exist

NOW ACTUALLY CREATE DB FROM TEMPLATE

$ createdb -Ouser_name -Eutf8 app_test -T template_postgis $ createdb -Ouser_name -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

Create User $ createuser user_name - n y n

Create DB's $ createdb -Ouser_name -Eutf8 app_development $ createdb -Ouser_name -Eutf8 app_test

Enable DB's for spatial awareness $ psql -d app_development -f /usr/local/Cellar/postgis/2.0.3/share/postgis/postgis.sql $ psql -d app_development -f /usr/local/Cellar/postgis/2.0.3/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="1.5.3" 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 efele.net. Download tz_world.zip (28,000 rows), unzip into world folder and look for the tz_world.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. I moved my world folder to /usr/local/var/world/april2013/
4. Create the dump.sql file like so (Make sure you're in the "world" directory when you run this.)

$ cd /usr/local/var/world/april2013/

$ /usr/local/Cellar/postgis/2.0.3/bin/shp2pgsql -D tz_world.shp > dump_n.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 for rails

psql -d app_development ALTER TABLE tz_world OWNER TO user_name; \q psql -d app_development -U user_name SELECT tzid FROM tz_world WHERE ST_Contains(geom, ST_MakePoint(-122.420706, 37.776685)); OR SELECT tzid FROM tz_world WHERE ST_Contains(the_geom, ST_MakePoint(-122.420706, 37.776685));

and you should get

tzid

America/Los_Angeles

Next... cd to your app directory and run rake db:schema:load ... if it fails, check out this post. http://stackoverflow.com/questions/11171330/why-is-rake-aborting-because-of-libpq-5-dylib but in the end this didnt fix it. I had to run:

gem uninstall pg bundle install then it worked!

TROUBLESHOOTING

i followed these steps but stopped after the initdb command and it worked.

http://rudygems.com/post/3460770239/upgrading-postgres-8-4-to-9-0-with-homebrew

How to reset the database in development:

1. rake db:drop and rake db:schema:load
   (neither seem to work?)

if you get an error with dump.sql for addGeometryColumn try adding line 7 to this:

SELECT AddGeometryColumn(''::text,'tz_world'::text,'geom'::text,0,'MULTIPOLYGON'::text,2,TRUE);

It's not ship2pgsql but shp2pgsql 😉

efele.net is not maintained for now
download shape files from https://github.com/evansiroky/timezone-boundary-builder/tags