kirley/gist:3913166

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.0.4) and postgis (1.5.3) so i had to run

$ brew versions postgis $ cd /usr/local/Library/Formula $ git checkout e9799d5 /usr/local/Library/Formula/postgis.rb

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, 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 3.1: OPTIONAL - MAKE POSTGIS TEMPLATE

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!

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!

pdud · 2012-11-27T17:58:18Z

brew edit postgresql

Change url to the following

http://ftp.postgresql.org/pub/source/v9.0.4/postgresql-9.0.4.tar.bz2

pdud · 2012-11-27T19:03:50Z

Mountain Lion requires brew install postgis --without-ossp-uuid

https://github.com/zilkey/homebrew-versions/commit/f75f4b9ad54ac26f384d161ac1e7309e269978c3

pdud · 2012-11-27T19:25:32Z

Needed to remove old database. I had nothing I desired keeping, here are some instructions if you want to keep it.

http://blog.dyve.net/upgrade-your-mac-to-postgres-9-using-homebrew

kirley · 2012-11-28T02:33:31Z

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

kirley · 2013-02-28T04:50:33Z

How to reset the database in development:

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

kirley · 2013-06-13T02:25:32Z

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);

kumy · 2020-08-03T14:43:14Z

It's not ship2pgsql but shp2pgsql 😉

UndeadDemidov · 2023-01-11T08:04:21Z

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

kirley/gist:3913166

Select an option

No results found

Select an option

No results found

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

NOTE: if it fails, try this

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.

Step 2: Install Postgresql & Postgis

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

Step 2: follow the instructions displayed here

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/)

Step 3.1: OPTIONAL - MAKE POSTGIS TEMPLATE

Step 4: Enable database for spatial awareness

Test Step 4 worked with this command

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

Import the dump.sql file

Test timezone lookup by lat/lon, but first change the table owner to the user for rails

tzid

pdud commented Nov 27, 2012

Uh oh!

pdud commented Nov 27, 2012

Uh oh!

pdud commented Nov 27, 2012

Uh oh!

kirley commented Nov 28, 2012

Uh oh!

kirley commented Feb 28, 2013

Uh oh!

kirley commented Jun 13, 2013

Uh oh!

kumy commented Aug 3, 2020

Uh oh!

UndeadDemidov commented Jan 11, 2023

Uh oh!