Installing Postgis

From the Postgis homepage:

PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension.

Not sure what all that means, but a friend of mine is works with GIS and I just setup a jail with postgres and postgis for him. I had a few minor glitches along the way. Here is the procedure I used to install Postgres and Postgis, and create a Postgis-enabled database.

Install the ports

Install the ports with default options:

[tykling@wackbox163 ~]$ portmaster /usr/ports/databases/postgresql83-server/
...snip...
[tykling@wackbox163 ~]$ portmaster /usr/ports/databases/postgis
...snip...

And as always, remember to enable Postgres in rc.conf:

[tykling@wackbox163 ~]$ grep postgres /etc/rc.conf
postgresql_enable="YES"

Initializing Postgres

When you install the Postgres server, the post-install message says (among other things):

To initialize the database, run

  /usr/local/etc/rc.d/postgresql initdb

You can then start PostgreSQL by running:

  /usr/local/etc/rc.d/postgresql start

For postmaster settings, see ~pgsql/data/postgresql.conf

NB. FreeBSD's PostgreSQL port logs to syslog by default
    See ~pgsql/data/postgresql.conf for more info

When I tried calling the rc.d script with the "initdb" argument, I had some problems which I later found out was due to the fact that Postgres is running in a jail. I was getting some errors about shared memory:

creating template1 database in /usr/local/pgsql/data/base/1 ... FATAL:  could not create shared memory segment: Function not implemented

I found the solution on the always great The FreeBSD Diary. I needed to change a sysctl on the jail host - not in the jail itself ofcourse - to allow the shared memory operation. Use:

[tykling@wackbox ~]$ sudo sysctl security.jail.sysvipc_allowed=1

to change the current value, and add:

security.jail.sysvipc_allowed=1

to /etc/sysctl.conf to ensure it stays that way after the next reboot.

I didn't need to restart the jail or anything after this change, it just immediately worked when I tried going back to rerun the initdb command.

After the database is initialized, start Postgres using the rc.d script, and you are ready to go.

Adding a user to Postgres

Postgres has added a user to the system, you need to su to that user to make changes to Postgres:

[tykling@wackbox163 ~]$ sudo su pgsql
[pgsql@wackbox163 /usr/home/tykling]$ cd ~
[pgsql@wackbox163 ~]$

Postgres uses the commands createuser and createdb to create users and databases. Since my friend is the only one on this server, I added him as a superuser:

[pgsql@wackbox163 ~]$ createuser -P myfriend
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y

Then I added a database for him:

[pgsql@wackbox163 ~]$ createdb -O myfriend gistestdb
[pgsql@wackbox163 ~]$

So far so good, nothing Postgis related yet.. but the database needs to be Postgis enabled, and to do that we need a couple of the files that the Postgis port installed:

[tykling@wackbox163 ~]$ pkg_info -L -x postgis
Information for postgis-1.3.6:

Files:
/usr/local/man/man1/pgsql2shp.1.gz
/usr/local/man/man1/shp2pgsql.1.gz
/usr/local/bin/pgsql2shp
/usr/local/bin/shp2pgsql
/usr/local/lib/liblwgeom.so
/usr/local/lib/liblwgeom.so.1
/usr/local/lib/postgresql/liblwgeom.so.1
/usr/local/share/postgis/lwpostgis.sql
/usr/local/share/postgis/lwpostgis_upgrade.sql
/usr/local/share/postgis/spatial_ref_sys.sql
/usr/local/share/postgis/utils/postgis_restore.pl
/usr/local/share/postgis/utils/profile_intersects.pl
/usr/local/share/postgis/utils/test_estimation.pl
/usr/local/share/postgis/utils/test_joinestimation.pl
/usr/local/share/postgis/utils/create_undef.pl
/usr/local/share/postgis/utils/README
/usr/local/share/doc/postgis/README.postgis
/usr/local/share/doc/postgis/postgis.html

We need to run the two .sql files /usr/local/share/postgis/lwpostgis.sql and /usr/local/share/postgis/spatial_ref_sys.sql in that order. To do that we need plpgsql language support in Postgres, so before running them go to the database and run the following command:

[pgsql@wackbox163 ~]$ psql gistestdb
Welcome to psql 8.3.7, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

android2=# CREATE LANGUAGE plpgsql;
CREATE LANGUAGE
android2=# \q
[pgsql@wackbox163 ~]$ 

Note how the Freebsd pgsql user is permitted access to the database (and any database) with no password. This is because postgres is installed with trust for the local machine, meaning that anyone with access to the local machine can access any database.

Now that that is sorted all that remains is to run the two sql files in the database I want Postgis enabled:

[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/contrib/postgis.sql > postgis.sql.log 2>&1
[pgsql@wackbox163 ~]$ psql gistestdb < /usr/local/share/postgis/contrib/spatial_ref_sys.sql > spatial_ref_sys.log 2>&1
[pgsql@wackbox163 ~]$ 

A quick look at the logfiles afterwards and I see nothing alarming. Thanks to #postgis on freenode for pointing me in the right direction on this one. The website BostonGIS also helped a lot.