Last active
January 11, 2023 08:04
-
-
Save kirley/3913166 to your computer and use it in GitHub Desktop.
Setting up PostGIS for Timezone Lookup
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # 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. | |
| $ 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 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! |
Mountain Lion requires brew install postgis --without-ossp-uuid
https://github.com/zilkey/homebrew-versions/commit/f75f4b9ad54ac26f384d161ac1e7309e269978c3
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
Author
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
Author
How to reset the database in development:
- rake db:drop and rake db:schema:load
(neither seem to work?)
Author
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);
It's not ship2pgsql but shp2pgsql 😉
efele.net is not maintained for now
download shape files from https://github.com/evansiroky/timezone-boundary-builder/tags
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
brew edit postgresqlChange url to the following
http://ftp.postgresql.org/pub/source/v9.0.4/postgresql-9.0.4.tar.bz2