Posts Tagged ‘postgresql’

Installing PostgreSQL 8.4, PostGIS 1.4.1, and pgRouting 1.0.3 on Ubuntu 9.10 (Karmic Koala)

01 Feb

Go to the bathroom before we start this one! hehe

I’m breaking this into the following 3 sections:

  1. Installing PostgreSQL 8.4
  2. Installing PostGIS 1.4.1
  3. Installing pgRouting 1.0.3

Installing PostgreSQL 8.4

Get a working copy of PostgreSQL running. At the time of this writing, Ubuntu has deprecated PostgreSQL 8.3, and has 8.4 as stable. So that’s what aptitude will install here.

sudo apt-get install postgresql

Normally, you wouldn’t have access to the database right now. You need to use the postgres user to set up. I’d recommend not changing the password of the postgres user. Just create a PostgreSQL super user.

sudo su -l postgres
createuser -E -P -s dbsuperuser

Now, try to login to the database with the super user you just created.

psql -U dbsuperuser -W -h postgres

You should come out with the following prompt:


Installing PostGIS 1.4.1

We’re compiling PostGIS, so we gotta get compilers. Normally you’d have them, unless you made a minimal install. I usually just get the build-essential package to get it over with.

sudo apt-get install build-essential
sudo apt-get install libgeos-dev
sudo apt-get install proj
sudo apt-get install postgresql-server-dev-8.4

You need the last one to be able to compile PostGIS. Had a lot of trouble without it, so I just added it. =)

Get the latest version of PostGIS here. At this time, PostGIS is at 1.4.1.

tar zxvf postgis-1.4.1.tar.gz
cd postgis-1.4.1
sudo make install

Everything should be compiled. Now we need to create the PostGIS database template. I normally have the naming scheme of template_XXX, so I’m naming my database as template_postgis.

For the next few statements, you can either switch to the postgres user, so you don’t have to enter the password everytime. I normally use the super user I create to do this.

createdb -U dbsuperuser -h -W -E UNICODE template_postgis
createlang -U dbsuperuser -h -W -d template_postgis plpgsql
psql -U dbsuperuser -h -W template_postgis < /usr/share/postgresql/8.4/contrib/postgis.sql
psql -U dbsuperuser -h -W template_postgis < /usr/share/postgresql/8.4/contrib/spatial_ref_sys.sql

Check your PostGIS installation with the following command:

psql -U dbsuperuser -h -W -c "SELECT postgis_lib_version();" template_postgis

Should give you the version of your PostGIS.

Installing pgRouting 1.0.3

You can follow the official guide here.

Now it gets complicated. Stable version 1.0.3 of pgRouting as of the moment isn’t compatible to compile with 8.4. You have to edit some code… not really. Just add a few include headers in the C files. =)

Let’s begin by installing dependencies. First up, libboost… this is a huge library.

sudo apt-get install libboost-graph-dev
sudo apt-get install cmake

Next, gaul. Check out the latest ones here. Right now, mine’s 0.1850-0.

tar -zxvf gaul-devel-0.1850-0.tar.gz
cd gaul-devel-0.1850-0
./configure --disable-slang
sudo make install
sudo ldconfig

Get pgRouting here. Now, I don’t use DD so I skipped installing CGAL and DD.

tar -zxvf pgRouting-1.03.tgz
cd pgrouting/
cmake -DWITH_TSP=on

At this point, if cmake spits out an error like this:

core/src/dijkstra.c: In function ‘fetch_edge_columns’:
core/src/dijkstra.c:98: error: ‘INT4OID’ undeclared (first use in this function)
core/src/dijkstra.c:98: error: (Each undeclared identifier is reported only once
core/src/dijkstra.c:98: error: for each function it appears in.)
core/src/dijkstra.c:100: error: ‘FLOAT8OID’ undeclared (first use in this function)

There’s an issue with PostgreSQL 8.4. v1.0.3 still doesn’t support 8.4 at the moment (pgRouting Ticket #160), so I have to edit the following files (Hat tip to thge on this one):


Just add the line:

#include "catalog/pg_type.h"

That’ll make pgRouting see PostgreSQL 8.4 data types.

cmake -DWITH_TSP=on
sudo make install

pgRouting should now be installed in /usr/share/postlbs. As I said before, I like my database naming schemes, so I’m creating another template for pgRouting called template_pgrouting. Create the database with template_postgis as your base template.

createdb -U dbsuperuser -h -W -E UNICODE -T template_postgis template_routing
psql -U dbsuperuser -h -W template_routing < /usr/share/postlbs/routing_core.sql
psql -U dbsuperuser -h -W template_routing < /usr/share/postlbs/routing_core_wrappers.sql
psql -U dbsuperuser -h -W template_routing < /usr/share/postlbs/routing_topology.sql
psql -U dbsuperuser -h -W template_routing < /usr/share/postlbs/routing_tsp.sql
psql -U dbsuperuser -h -W template_routing < /usr/share/postlbs/routing_tsp_wrappers.sql

We’re done!! Grab a coke and go pee!

Update: Fixed package name from build-essentials to build-essential; Added postgres to the psql command to connect to database; Thanks Thera