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.

The steps to install Postgres is outlined on the Installing Postgres page. After following that, I follow the steps below to add a user and a database, and Postgis enable that database.

Installing Postgis

As always, FreeBSD ports makes this step easy:

$ sudo portmaster /usr/ports/databases/postgis

Adding a user to Postgres

Postgres has added a user to the system, I 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 I 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.