Skip to content

Instantly share code, notes, and snippets.

@kirley

kirley/gist:3913166

Last active January 11, 2023 08:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save kirley/3913166 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save kirley/3913166 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. kirley revised this gist Nov 13, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -21,7 +21,7 @@ $ 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
    ##### NOTE: I wanted an older version of postgresql (9.2.4) and postgis (2.0.3) so i had to run
    $ brew versions postgis
    $ cd /usr/local/Library/Formula
    $ git checkout e9799d5 /usr/local/Library/Formula/postgis.rb (This may be different from what you want)
  2. kirley revised this gist May 15, 2013. 1 changed file with 4 additions and 2 deletions.
    6 changes: 4 additions & 2 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -52,7 +52,7 @@ $ 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/2.0.3/share/postgis/postgis.sql
    $ psql -d template_postgis -f /usr/local/Cellar/postgis/2.0.3/share/postgis/spatial_ref_sys.sql
    $ psql -d template_postgis -f helios/db/sql/dump.sql
    $ psql -d template_postgis -f ~/work/helios/db/sql/dump.sql
    $ psql -d template_postgis -c 'GRANT ALL ON geometry_columns TO public;'
    $ psql -d template_postgis -c 'GRANT ALL ON geography_columns TO public;'
    $ psql -d template_postgis -c 'GRANT ALL ON spatial_ref_sys TO public;'
    @@ -108,7 +108,7 @@ $ psql -d app_development -c "SELECT postgis_version();"

    $ cd /usr/local/var/world/april2013/

    $ /usr/local/Cellar/postgis/2.0.3/bin/shp2pgsql -D tz_world.shp > dump.sql
    $ /usr/local/Cellar/postgis/2.0.3/bin/shp2pgsql -D tz_world.shp > dump_n.sql


    ## Step 6: Import the shape file
    @@ -120,6 +120,8 @@ 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(geom, ST_MakePoint(-122.420706, 37.776685));
    OR
    SELECT tzid FROM tz_world WHERE ST_Contains(the_geom, ST_MakePoint(-122.420706, 37.776685));

    and you should get
  3. kirley revised this gist May 15, 2013. 1 changed file with 14 additions and 6 deletions.
    20 changes: 14 additions & 6 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -63,11 +63,19 @@ $ psql -d template_postgis -c 'ALTER TABLE spatial_ref_sys OWNER TO helios;'
    $ psql -d template_postgis -c 'ALTER TABLE tz_world OWNER TO helios;'

    $ psql -U helios -d template_postgis
    $ template_postgis=> select postgis_lib_version(); should return 1.5.3!
    $ template_postgis=> select postgis_lib_version(); should return 2.0.3!
    $ template_postgis=> \d+tz_world

    And you'll see that the template does exist

    NOW ACTUALLY CREATE DB FROM TEMPLATE

    $ createdb -Ouser_name -Eutf8 app_test -T template_postgis
    $ createdb -Ouser_name -Eutf8 app_development -T template_postgis

    # Test that it worked with this command
    $ psql -d app_development -c "SELECT postgis_full_version();"

    Help Links:
    https://gist.github.com/1198957
    http://anujjaiswal.wordpress.com/2011/06/14/installing-postgres9-0-and-postgis-on-centos/
    @@ -81,8 +89,8 @@ $ createdb -Ouser_name -Eutf8 app_development
    $ createdb -Ouser_name -Eutf8 app_test

    Enable DB's 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
    $ psql -d app_development -f /usr/local/Cellar/postgis/2.0.3/share/postgis/postgis.sql
    $ psql -d app_development -f /usr/local/Cellar/postgis/2.0.3/share/postgis/spatial_ref_sys.sql

    # Test that it worked with this command
    $ psql -d app_development -c "SELECT postgis_full_version();"
    @@ -95,12 +103,12 @@ $ psql -d app_development -c "SELECT postgis_version();"

    1. 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.
    2. 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.
    3. I moved my world folder to /usr/local/var/april2013/world/
    3. I moved my world folder to /usr/local/var/world/april2013/
    4. Create the dump.sql file like so (Make sure you're in the "world" directory when you run this.)

    $ cd /usr/local/var/april2013/world/
    $ cd /usr/local/var/world/april2013/

    $ /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql
    $ /usr/local/Cellar/postgis/2.0.3/bin/shp2pgsql -D tz_world.shp > dump.sql


    ## Step 6: Import the shape file
  4. kirley revised this gist May 15, 2013. 1 changed file with 10 additions and 5 deletions.
    15 changes: 10 additions & 5 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,5 @@
    https://gist.github.com/juniorz/1081907

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

    @@ -42,11 +44,14 @@ $ brew info postgresql

    ## Step 4: Create database user and database template

    $ createuser helios - n y n
    $ createuser helios --interactive - n y n
    $ Shall the new role be a superuser?
    $ Shall the new role be allowed to create databases? y
    $ Shall the new role be allowed to create more new roles? n
    $ 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
    $ psql -d template_postgis -f /usr/local/Cellar/postgis/2.0.3/share/postgis/postgis.sql
    $ psql -d template_postgis -f /usr/local/Cellar/postgis/2.0.3/share/postgis/spatial_ref_sys.sql
    $ psql -d template_postgis -f helios/db/sql/dump.sql
    $ psql -d template_postgis -c 'GRANT ALL ON geometry_columns TO public;'
    $ psql -d template_postgis -c 'GRANT ALL ON geography_columns TO public;'
    @@ -90,10 +95,10 @@ $ psql -d app_development -c "SELECT postgis_version();"

    1. 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.
    2. 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.
    3. I moved my world folder to /usr/local/var/world
    3. I moved my world folder to /usr/local/var/april2013/world/
    4. Create the dump.sql file like so (Make sure you're in the "world" directory when you run this.)

    $ cd /usr/local/var/world
    $ cd /usr/local/var/april2013/world/

    $ /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql

  5. kirley revised this gist Mar 30, 2013. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -81,6 +81,8 @@ $ psql -d app_development -f /usr/local/Cellar/postgis/1.5.3/share/postgis/spati

    # Test that it worked with this command
    $ psql -d app_development -c "SELECT postgis_full_version();"
    OR
    $ psql -d app_development -c "SELECT postgis_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
  6. kirley revised this gist Nov 29, 2012. 1 changed file with 13 additions and 8 deletions.
    21 changes: 13 additions & 8 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -84,15 +84,21 @@ $ 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
    ## Step 5: Generate shape file

    # Import the dump.sql file
    1. 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.
    2. 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.
    3. I moved my world folder to /usr/local/var/world
    4. Create the dump.sql file like so (Make sure you're in the "world" directory when you run this.)

    psql -d app_development -f dump.sql
    $ cd /usr/local/var/world

    $ /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql


    ## Step 6: Import the shape 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
    @@ -117,4 +123,3 @@ Next... cd to your app directory and run rake db:schema:load ... if it fails, ch


    # TROUBLESHOOTING

  7. kirley revised this gist Nov 29, 2012. No changes.
  8. kirley revised this gist Nov 29, 2012. 1 changed file with 49 additions and 27 deletions.
    76 changes: 49 additions & 27 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -40,32 +40,49 @@ $ brew info postgresql

    ##### NOTE: Depending on if you have an existing install of postgresql or not, just follow the instructions for setting up the LaunchAgent

    ## Step 4: 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 4: Create database user and database template

    $ createuser helios - n y n
    $ 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
    $ psql -d template_postgis -f helios/db/sql/dump.sql
    $ psql -d template_postgis -c 'GRANT ALL ON geometry_columns TO public;'
    $ psql -d template_postgis -c 'GRANT ALL ON geography_columns TO public;'
    $ psql -d template_postgis -c 'GRANT ALL ON spatial_ref_sys TO public;'
    $ psql -d template_postgis -c 'GRANT ALL ON tz_world TO public;'
    $ psql -d template_postgis -c 'ALTER TABLE geometry_columns OWNER TO helios;'
    $ psql -d template_postgis -c 'ALTER TABLE geography_columns OWNER TO helios;'
    $ psql -d template_postgis -c 'ALTER TABLE spatial_ref_sys OWNER TO helios;'
    $ psql -d template_postgis -c 'ALTER TABLE tz_world OWNER TO helios;'

    $ psql -U helios -d template_postgis
    $ template_postgis=> select postgis_lib_version(); should return 1.5.3!
    $ template_postgis=> \d+tz_world

    And you'll see that the template does exist

    Help Links:
    https://gist.github.com/1198957
    http://anujjaiswal.wordpress.com/2011/06/14/installing-postgres9-0-and-postgis-on-centos/

    ##### NOTE: You may decide to just create a database without the template
    Create User
    $ createuser user_name - n y n

    Create DB's
    $ createdb -Ouser_name -Eutf8 app_development
    $ createdb -Ouser_name -Eutf8 app_test

    Enable DB's 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 that it 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
    @@ -95,4 +112,9 @@ Next... cd to your app directory and run rake db:schema:load ... if it fails, ch

    > gem uninstall pg
    > bundle install
    > then it worked!
    > then it worked!


    # TROUBLESHOOTING

  9. kirley revised this gist Nov 29, 2012. 1 changed file with 8 additions and 8 deletions.
    16 changes: 8 additions & 8 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,25 +22,25 @@ $ brew uninstall postgresql
    ##### 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
    $ git checkout e9799d5 /usr/local/Library/Formula/postgis.rb (This may be different from what you want)

    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
    ##### NOTE: if you get an error (you will), check out the comments at the end of this Gist but basically you need to:

    # Step 2: follow the instructions displayed here
    $ vi /usr/local/Library/Formula/postgresql.rb and then swap out the path for postgresql

    brew info postgresql
    ## Step 3: follow the instructions displayed here

    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
    $ brew info postgresql

    # 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/)
    ##### NOTE: Depending on if you have an existing install of postgresql or not, just follow the instructions for setting up the LaunchAgent

    ## Step 4: 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
  10. kirley revised this gist Nov 29, 2012. 1 changed file with 14 additions and 12 deletions.
    26 changes: 14 additions & 12 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    ### 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.
    ## Step 1: Prepare to Install Postgresql & Postgis + dependencies using brew

    $ brew update

    @@ -9,24 +9,26 @@ $ cd /usr/local/Library/Formula
    $ git reset --hard HEAD
    $ brew update

    # NOTE: if postgres or postgis already exist, then uninstall them first.
    ##### 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", 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

    > Now do brew uninstall postgis and brew uninstall postgresql
    $ brew uninstall postgis
    $ 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:
    ## Step 2: Install Postgresql & Postgis

    # NOTE: Make sure you're in the /usr/local/Library/Formula directory.
    ##### 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

    # 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
    and the same with 'brew versions postgresql'...

    # or to get the latest repositories 'brew update' i think. Now, time to install everything with one easy command!
    Now, time to install everything with one easy command!

    brew install postgis
    $ 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

  11. kirley revised this gist Nov 29, 2012. 1 changed file with 7 additions and 3 deletions.
    10 changes: 7 additions & 3 deletions gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,13 @@
    ### 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.
    ## Step 1: Install Postgresql & Postgis + dependencies using brew but FIRST do brew update.

    $ Update brew
    $ brew update

    > brew update (if it fails, cd /usr/local/Library/Formula, then run git reset --hard HEAD)
    ##### 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.

  12. kirley revised this gist Nov 29, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    # 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.
    ### 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
  13. kirley renamed this gist Nov 29, 2012. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.txt → gistfile1.md
    View file
    File renamed without changes.
  14. kirley revised this gist Nov 29, 2012. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    # 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
    $ Update brew

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

    @@ -68,7 +68,6 @@ psql -d app_development -c "SELECT postgis_full_version();"
    /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql

    # Import the dump.sql file
    # First, change the gid column to id by opening the dump file.

    psql -d app_development -f dump.sql

  15. kirley revised this gist Nov 28, 2012. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -68,6 +68,8 @@ psql -d app_development -c "SELECT postgis_full_version();"
    /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql

    # Import the dump.sql file
    # First, change the gid column to id by opening the dump 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
  16. kirley revised this gist Nov 28, 2012. 1 changed file with 13 additions and 1 deletion.
    14 changes: 13 additions & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -40,6 +40,18 @@ 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
    @@ -76,4 +88,4 @@ Next... cd to your app directory and run rake db:schema:load ... if it fails, ch

    > gem uninstall pg
    > bundle install
    > then it worked!
    > then it worked!
  17. kirley revised this gist Nov 28, 2012. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -72,4 +72,8 @@ 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
    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!
  18. kirley revised this gist Nov 28, 2012. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -69,4 +69,7 @@ and you should get

    tzid
    ---------------------
    America/Los_Angeles
    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
  19. kirley revised this gist Nov 28, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -24,7 +24,7 @@ git checkout ed92469 /usr/local/Library/Formula/postgresql.rb

    brew install postgis

    #NOTE: if you get an error, check out the comments at the end of this Gist.
    #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

  20. kirley revised this gist Nov 28, 2012. 1 changed file with 12 additions and 2 deletions.
    14 changes: 12 additions & 2 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,12 +1,20 @@
    # 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.

    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
    @@ -16,6 +24,8 @@ git checkout ed92469 /usr/local/Library/Formula/postgresql.rb

    brew install postgis

    #NOTE: if you get an error, check out the comments at the end of this Gist.

    # Step 2: follow the instructions displayed here

    brew info postgresql
  21. kirley revised this gist Nov 28, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,7 @@

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

    IF YOU HAVE IT INSTALLED, drop the db's then stop the server with 'lunchy stop postgres' and then brew uninstall ...
    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
  22. kirley revised this gist Nov 28, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,7 @@

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

    IF YOU HAVE IT INSTALLED, stop the server with 'lunchy stop postgres' and then brew uninstall ...
    IF YOU HAVE IT INSTALLED, drop the db's 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
  23. kirley revised this gist Nov 27, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,5 @@
    # 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.
    # 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:

  24. kirley revised this gist Nov 27, 2012. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -3,6 +3,10 @@

    # 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, 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
  25. kirley revised this gist Oct 20, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -20,7 +20,7 @@ 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)
    # 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
  26. kirley revised this gist Oct 18, 2012. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -31,7 +31,7 @@ psql -d app_development -f /usr/local/Cellar/postgis/1.5.3/share/postgis/postgis
    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 -U user_name -c "SELECT postgis_full_version();"
    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

    @@ -44,7 +44,7 @@ psql -d app_development -U user_name -c "SELECT postgis_full_version();"
    # Import the dump.sql file
    psql -d app_development -f dump.sql

    # Test timezone lookup by lat/lon
    # 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
  27. kirley revised this gist Oct 18, 2012. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -42,9 +42,12 @@ psql -d app_development -U user_name -c "SELECT postgis_full_version();"
    /usr/local/Cellar/postgis/1.5.3/bin/shp2pgsql -D tz_world.shp > dump.sql

    # Import the dump.sql file
    psql -d app_development -U user_name -f dump.sql
    psql -d app_development -f dump.sql

    # Test timezone lookup by lat/lon
    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));

  28. kirley revised this gist Oct 18, 2012. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -27,8 +27,8 @@ createdb -Ouser_name -Eutf8 app_development
    createdb -Ouser_name -Eutf8 app_test

    # Step 4: Enable database for spatial awareness
    psql -d app_development -U user_name -f /usr/local/Cellar/postgis/1.5.3/share/postgis/postgis.sql
    psql -d app_development -U user_name -f /usr/local/Cellar/postgis/1.5.3/share/postgis/spatial_ref_sys.sql
    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 -U user_name -c "SELECT postgis_full_version();"
  29. kirley revised this gist Oct 18, 2012. 1 changed file with 5 additions and 5 deletions.
    10 changes: 5 additions & 5 deletions gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -27,11 +27,11 @@ 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
    psql -d app_development -U user_name -f /usr/local/Cellar/postgis/1.5.3/share/postgis/postgis.sql
    psql -d app_development -U user_name -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();"
    psql -d app_development -U user_name -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

    @@ -42,10 +42,10 @@ psql -d app_development -c "SELECT postgis_full_version();"
    /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
    psql -d app_development -U user_name -f dump.sql

    # Test timezone lookup by lat/lon
    psql -d app_development
    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
  30. kirley revised this gist Oct 18, 2012. 1 changed file with 10 additions and 1 deletion.
    11 changes: 10 additions & 1 deletion gistfile1.txt
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,14 @@
    # 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.
    # Step 1: Install Postgresql & Postgis + dependencies using brew.

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

    # 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