Calvin's

The new postgis 2.0 library (easily installed via `sudo port install postgis2 +postgresql92` if you are using macports as mentioned in my previous post) helps us convert a standard postgresql database into a spatially-aware database via the simple `CREATE EXTENSION postgis;` command:

$  psql92 -p 5433 -U postgres -d your_db

psql92 (9.2.1)
Type "help" for help.
your_db=#  CREATE EXTENSION postgis;

Unfortunately, when we run our django tests, `test_your_db`, which is newly created by django’s test runner whenever the test begins, will not be postgis-enabled. This leads immediately to test failure when our code fails to recognize the Geometry fields in our project’s GeoDjango models.

To get around this problem, we will need to specifically set up a `template_postgis` template database, much like what we did before postgis 2.0 (reference django docs https://docs.djangoproject.com/en/dev/ref/contrib/gis/install/postgis/#creating-a-spatial-database-template-for-earlier-versions).  But because I am running an instance of postgresql 9.1 server and an instance of 9.2 server concurrently, I will need to modify my commands a little.

Like this:-

calvin$ psql92 -p 5433 -U postgres
psql92 (9.2.1)
Type "help" for help.

postgres=# CREATE DATABASE template_postgis ENCODING='utf-8';
CREATE DATABASE
postgres=# UPDATE pg_database SET datistemplate='true' WHERE datname='template_postgis';
UPDATE 1
postgres=# \q

calvin$ POSTGIS_SQL_PATH=/opt/local/share/postgresql92/contrib/postgis-2.0

calvin$ psql92 -p 5433 -U postgres -d template_postgis -f $POSTGIS_SQL_PATH/postgis.sql

calvin$ psql92 -p 5433 -U postgres -d template_postgis -f $POSTGIS_SQL_PATH/spatial_ref_sys.sql

calvin$ psql92 -p 5433 -U postgres -d template_postgis -c "GRANT ALL ON geometry_columns TO PUBLIC;"
GRANT

calvin$ psql92 -p 5433 -U postgres -d template_postgis -c "GRANT ALL ON geography_columns TO PUBLIC;"
GRANT

calvin$ psql92 -p 5433 -U postgres -d template_postgis -c "GRANT ALL ON spatial_ref_sys TO PUBLIC;"
GRANT

With this, running `./manage.py test` with your geodjango-based application will work perfectly fine because django’s default test runner will look for the template_postgis template database to instantiate our now spatially-aware test database.

One of the trickier problems when managing multiple projects involve supporting different versions of open source library or software that your varying projects depend on.

In my current scenario, I need to support web projects which are still running on postgresql 9.1, while building up new projects which uses postgresql 9.2.

Getting postgresql92, postgresql92-server packages installed via macports is the easy part.

$ sudo port install postgresql92 postgresql92-server

A simple `port install` command does the  job.  The problem is getting making sure that they run alongside each other so that I can switch out from working on a project that uses postgresql 9.1 server easily to another project that uses postgresql 9.2.

Once our macports install completes running, we will create our initial database. Like so:

$ sudo mkdir -p /opt/local/var/db/postgresql92/defaultdb
$ sudo chown postgres:postgres /opt/local/var/db/postgresql92/defaultdb
$ sudo su postgres -c '/opt/local/lib/postgresql92/bin/initdb -D /opt/local/var/db/postgresql92/defaultdb'

Nothing surprising here. Except that our postgresql 9.1 server has occupied the default 5432, so we will need to make sure that our postgresql 9.2 server uses a different port.  Opening up our postgresql 9.2 server’s postgresql.conf file with `sudo vim /opt/local/var/db/postgresql92/defaultdb/postgresql.conf`,we set our postgresql 9.2′s port to a different one.

port = 5433 # (change requires restart)

Once our postgresql 9.2 server is properly initialized, we can manually start it using

$ sudo su postgres -c '/opt/local/lib/postgresql92/bin/postgres -D /opt/local/var/db/postgresql92/defaultdb'

which may not release the process to the background.

To release the process to the background, we can instead use Mac OSX’s daemondo. Like this:

$ sudo daemondo --label=postgresql92-server --start-cmd /opt/local/etc/LaunchDaemons/org.macports.postgresql92-server/postgresql92-server.wrapper start

Finally, to ensure that our postgresql 9.2 server starts up everytime we boot up our Mac, alongside our postgresql 9.1 server, we can set

$ sudo launchctl load -w /Library/LaunchDaemons/org.macports.postgresql92-server.plist

No different from our we make `launchctl` start up postgresql 9.1 server when our Mac boots up.

To check that our postgresql 9.2 server is indeed running, we can execute:

$ psql92 -p 5433 -U postgres
psql92 (9.2.1)
Type "help" for help.
postgres=#

From now on, we will have to be sure that settings and configuration for projects that try to connect to our postgresql 9.2 server uses port 5433.

One more thing – Postgis 2.0.

$ sudo port -v install postgis2 +postgresql92 +raster +topology

Since we use postgis regularly in our projects (for applications requiring GIS functionalities), we need to install the version compatible with postgresql 9.2. This is trivial with Postgis 2.0(.1). We no longer need to go through multiple steps to create a template spatial database (“template_postgis”). In Postgis 1.5, we need to initialize our database with this “template_postgis” template spatial database that we create.

From Postgis 2.0 onwards, it’s straightforward.

$ psql92 -p 5433 -U postgres -d your_db

psql92 (9.2.1)
Type "help" for help.

your_db=#  CREATE EXTENSION postgis;

your_db=# CREATE EXTENSION postgis_topology;

The 2nd `CREATE EXTENSION postgis_topology` is optional at this time of writing as geodjango does not have the necessary functionality to support Postgis’ Topological features yet.