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.
$ Update brew
brew update (if it fails, cd /usr/local/Library/Formula, then run git reset --hard HEAD)
"dropdb -U helios -i helios_development", 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.
Now do brew uninstall postgis and brew uninstall postgresql
Note: I wanted an older version of postgresql (9.1.4) and postgis (1.5.3) so i had to run 'brew versions postgresql' and 'brew versions postgis', then cd to /usr/local/Library/Formula and run the approperiate git commands like:
git checkout e9799d5 /usr/local/Library/Formula/postgis.rb
git checkout ed92469 /usr/local/Library/Formula/postgresql.rb
or to get the latest repositories 'brew update' i think. Now, time to install everything with one easy command!
brew install postgis
#NOTE: if you get an error, check out the comments at the end of this Gist but basically you need to vi /usr/local/Library/Formula/postgresql.rb
brew info postgresql
mkdir -p ~/Library/LaunchAgents cp /usr/local/Cellar/postgresql/9.1.4/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
Step 3: Create database user and database (alternatively you can make a template as shown here https://gist.github.com/1198957 and http://anujjaiswal.wordpress.com/2011/06/14/installing-postgres9-0-and-postgis-on-centos/)
createuser user_name - n y n createdb -Ouser_name -Eutf8 app_development createdb -Ouser_name -Eutf8 app_test
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/1.5.3/share/postgis/postgis.sql psql -d template_postgis -f /usr/local/Cellar/postgis/1.5.3/share/postgis/spatial_ref_sys.sql
check template exists with psql -U helios -d template_postgis
template_postgis=> select postgis_lib_version(); should return 1.5.3!
psql -d app_development -f /usr/local/Cellar/postgis/1.5.3/share/postgis/postgis.sql psql -d app_development -f /usr/local/Cellar/postgis/1.5.3/share/postgis/spatial_ref_sys.sql
psql -d app_development -c "SELECT postgis_full_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: 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
/usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.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(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!
brew edit postgresqlChange url to the following
http://ftp.postgresql.org/pub/source/v9.0.4/postgresql-9.0.4.tar.bz2