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

 IF YOU HAVE IT INSTALLED, drop the db's "dropdb -U helios -i helios_development" then stop the server with 'lunchy stop postgres' and then brew uninstall ...

 # 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

 # 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
	# 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: 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.

	IF YOU HAVE IT INSTALLED, drop the db's "dropdb -U helios -i helios_development" then stop the server with 'lunchy stop postgres' and then brew uninstall ...

	# 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

	# 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
No results found