Getting my web development stack back in order after upgrading to Leopard has been a little hit-or-miss. Now, in preparation for some geocoding work, I’m adding PostgreSQL and PostGIS to the mix. Here’s what I had to do to get stuff working. I haven’t tested it extensively yet, but I’ll update this post as necessary.

Update: I’m getting some strange bus errors from PostgreSQL (signal 10)… I’d eventually like to get the ports method working, but for now, I’m going to use the prepackaged binaries. Also of note these required ported frameworks.

PostgreSQL

• Install PostgreSQL
  $ sudo port -k install postgresql82 postgresql82-server
  (’-k’ doesn’t do cleanup… just in case I want the work files later in the install process)

• Make the default DB directory, set the owner to the postgres user, and initialize the DB
  $ sudo mkdir -p /opt/local/var/db/postgresql82/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql82/defaultdb
$ sudo su postgres -c '/opt/local/lib/postgresql82/bin/initdb -D /opt/local/var/db/postgresql82/defaultdb'
  (you could replace ‘postgres’ with something else if you want another user to be the postgres user)

• Add PostgreSQL server as a startup item
  $ sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql82-server.plist
  (add startup info)

• Add PostgreSQL executables to your path:
  $ PATH=${PATH}:/opt/local/lib/postgresql82/bin

• Create a DB user
  $ createuser -U postgres -s -P -E <name-of-superuser>
  (If you set the postgres user to something other that ‘postgres’ that should be reflected above.)

PostGIS

• Update the ports system
  $ sudo port selfupdate

• Navigate to the location of the PostGIS portfile
  $ /opt/local/var/macports/sources/rsync.macports.org/release/ports/databases/postgis

• Change the line in the portfile that reads:

  build.args “ICONV_LDFLAGS=\”-L${prefix}/lib -liconv\”"

  to

  build.args “ICONV_LDFLAGS=\”-L$/usr/lib -liconv\”"

  (OS-X already has the iconv library and I believe this will work fine. Using the ports version breaks.)

• Run the port installation procedure
  $ sudo port -k install postgis
  (without ‘-k’ in this case, the port will break before finishing instal)

Creating a PostGIS template

The following is adapted from Rob Braswell’s instructions. This allows non-superusers to create spatial databases using a template.

• Connect to the template database
  $ psql template1

• Execute the following commands:
  template1=# create database template_postgis with template = template1;
template1=# UPDATE pg_database SET datistemplate = TRUE where datname = 'template_postgis';

• Connect to the new template_postgis database:
  template1=# \c template_postgis

• Add PostGIS extensions and grant access to everyone to spatial tables:
  template_postgis=# CREATE LANGUAGE plpgsql;
template_postgis=# \i /opt/local/share/postgis/lwpostgis.sql;
template_postgis=# \i /opt/local/share/postgis/spatial_ref_sys.sql;
template_postgis=# GRANT ALL ON geometry_columns TO PUBLIC;
template_postgis=# GRANT ALL ON spatial_ref_sys TO PUBLIC;

• Prevent further modifications to the template_postgis database:
  template_postgis=# VACUUM FREEZE;

• Quit out of psql (^D or \q)

• Create a test database using the new template_postgis template…
  $ createdb test_gis_db -T template_postgis
  …and drop it again.
  $ dropdb test_gis_db

Cleanup PostgreSQL/PostGIS installation files

• Let’s get rid of the work files associated with the installation above:
  $ sudo port clean postgresql82 postgresql82-server postgis

Ruby and Rails

• Install Ruby library for PostgreSQL
  $ sudo gem install postgres

• Install GeoRuby
  $ sudo gem install georuby

Take a look at this GeoRuby and Rails tutorial for more rails integration info. I’ll update this post once i get to that stage.