kirley · January 11, 2023 08:04 · pdud · Nov 27, 2012 · pdud · Nov 27, 2012
diff --git a/gistfile1.txt b/gistfile1.txt
 # 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: Install Postgresql & Postgis + dependencies using brew but FIRST do brew update.  

 # NOTE:  Update brew

 > brew update (if it fails, cd /usr/local/Library/Formula, then run git reset --hard HEAD)

 # NOTE: if postgres or postgis already exist, then uninstall them first.

 > "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:

 # NOTE: Make sure you're in the /usr/local/Library/Formula directory.

 # postgis 1.5.3
 git checkout e9799d5 /usr/local/Library/Formula/postgis.rb
 # postgresql 9.1.4
 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

 # Step 2: follow the instructions displayed here

 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

 # Step 4: Enable database for spatial awareness
 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

 # Test Step 4 worked with this command
 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
 # Create the dump.sql file like so (Make sure you're in the "world" directory when you run this.)
 /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql

 # Import the dump.sql 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(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!
	# 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: Install Postgresql & Postgis + dependencies using brew but FIRST do brew update.

	# NOTE: Update brew

	> brew update (if it fails, cd /usr/local/Library/Formula, then run git reset --hard HEAD)

	# NOTE: if postgres or postgis already exist, then uninstall them first.

	> "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:

	# NOTE: Make sure you're in the /usr/local/Library/Formula directory.

	# postgis 1.5.3
	git checkout e9799d5 /usr/local/Library/Formula/postgis.rb
	# postgresql 9.1.4
	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

	# Step 2: follow the instructions displayed here

	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

	# Step 4: Enable database for spatial awareness
	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

	# Test Step 4 worked with this command
	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
	# Create the dump.sql file like so (Make sure you're in the "world" directory when you run this.)
	/usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql

	# Import the dump.sql 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(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!
No results found