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.
$ brew update
$ 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
$ 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
$ 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
$ 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/
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
$ 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
- 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.
- 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.
- I moved my world folder to
/usr/local/var/world/april2013/ - 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
$ psql -d app_development -f dump.sql
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
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!