Mapnik/PostGIS

Mapnik makes use of PostgreSQL / PostGIS as part of the rendering pipeline.

The following instructions describe how to install PostgreSQL / PostGIS on Linux and Windows, create the appropriate spatial databases, and tune database parameters for better performance. Please see the Mapnik page for more information on using Mapnik to render OpenStreetMap data.

It is recommended to use PostgreSQL 9.0 or 8.3. Version 8.4 has some performance issues.

The below assumes you will replace [username] with the name of the user who will be running mapnik

Complete Installation for Ubuntu

This chapter describes a quick installation procedure for Ubuntu >=8.04. If you are using another operating system, or need to tune-up the installed database, please refer to the sections below this chapter. It is recommended to enter the following command lines step by step; that makes it much easier to deal with possibly occurring errors.

# for Ubuntu <10.04:
  sudo apt-get install postgresql-8.3-postgis postgresql-contrib-8.3
  sudo -u postgres -i -H
  createuser -SdR gisuser
  createdb -E UTF8 -O gisuser gis
  createlang plpgsql gis
  psql -d gis -f /usr/share/postgresql/8.3/contrib/_int.sql
  psql -d gis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql


# for Ubuntu >=10.04:
  sudo apt-get install postgresql-8.4-postgis postgresql-contrib-8.4
  sudo -u postgres -i -H
  createuser -SdR gisuser
  createdb -E UTF8 -O gisuser gis
  createlang plpgsql gis
  psql -d gis -f /usr/share/postgresql/8.4/contrib/_int.sql  #could cause "Please use a database without intarray" error on newer osm2pgsql
  psql -d gis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
  # to populate the table spatial_ref_sys (mandatory for use with osm2pgsql):
  psql -d gis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

# common for both
psql gis -c "ALTER TABLE geometry_columns OWNER TO gisuser"
psql gis -c "ALTER TABLE spatial_ref_sys OWNER TO gisuser"
exit

To enable easy database login by user gisuser you must change some lines in one of the database configuration files.

# for Ubuntu <=9.10:
  sudo gedit /etc/postgresql/8.3/main/pg_hba.conf
# for Ubuntu >=10.04:
  sudo gedit /etc/postgresql/8.4/main/pg_hba.conf

Near to the bottom of the file you will find these lines:

local   all         all                               ident
host    all         all         127.0.0.1/32          md5

Change the words ident and md5 each to trust and close the editor. Now reload the database configuration:

# for Ubuntu <= 9.10:
  sudo /etc/init.d/postgresql-8.3 reload
# for Ubuntu 10.04:
  sudo /etc/init.d/postgresql-8.4 reload
# for Ubuntu >= 10.10:
  sudo /etc/init.d/postgresql reload

For a short test, login to the database by using the previously created database user gisuser:

psql gis gisuser

Type \d to see a list with the two tables which we have created some minutes ago (geometry_columns and spatial_ref_sys), then logout with: \q

Install Step by Step, all Operating Systems

For Debian Sarge (old stable)

As root, then following the etch instructions

# echo deb http://www.backports.org sarge-backports main >> /etc/apt/sources.list
# apt-get -t sarge-backports install ...

For Debian etch (onwards)

To install PostGIS (as root):

# aptitude install postgresql-postgis

or with unstable

# aptitude install postgresql-8.4-postgis

or with Ubuntu

see #Complete Installation for Ubuntu

For OS X Leopard

Assuming you have darwinports,

% sudo port install postgresql83
% sudo port install postgis

or use the Mac OS X installers at KyngChaos.

For Fedora

To install PostGIS (as root):

# yum install postgis postgresql-server
# service postgresql initdb
# service postgresql start

# yum install postgis postgresql-server
# postgresql-setup initdb
# service postgresql start

For Opensuse 11

To let the package manager know about PostGIS, you have to add a community package repository:

sudo zypper ar http://download.opensuse.org/repositories/Application:/Geo/openSUSE_11.2/ "Geo"
sudo zypper refresh

Then you can install PostGIS:

sudo zypper install postgis postgresql-contrib

For Windows

There are setup executables for PostGIS. Download the installer that matches your PostgreSQL version and run it. Your PostgreSQL server must be running. This will install some files in a subdirectory of your PostgreSQL installation and two database tables.

For Gentoo

Installing osm2pgsql will pull postgres and postgis as dependencies:

emerge osm2pgsql

Or you can install them explicitely with emerge postgresql-server postgis. The config file for pg_ctl will be in /etc/conf.d/postgresql-8.3 and for the server itself there'll be a sample file in /usr/share/postgresql-8.3/postgresql.conf.sample You can configure the cluster using emerge postgresql-server --config or manually if you want to customise it, then start it with /etc/init.d/posgresql-8.3 start before proceeding with the createuser instructions below.

Tuning the database

Since we are predominantly doing bulk loads and handling lots of data, it is advisable to tune Postgres a bit for this load; the default settings are generally fairly conservative. The following is just a brief overview of the settings you need to think about, read the documentation for more info. Remember at all times that the rest of your system (including a webserver for example) also needs memory to run, so don't starve them out just for PostgreSQL. You need to edit the file postgresql.conf which may be in /etc/postgresql/8.3/main or /var/lib/pgsql/data or similar depending on your distribution. The server needs to be restarted after the changes.

On openSUSE, the configuration files will not exist until you have started the server at least once:

sudo /etc/init.d/postgresql start

After that, it is located in /var/lib/pgsql/data/postgresql.conf

If you are using a version older than 8.2 you will need to use the numbers rather than the more descriptive units.

shared_buffers = 128MB    # 16384 for 8.1 and earlier

This controls the amount of memory used for stored things like index pages, data pages directly into local cache. More is not necessarily better. Remember that osm2pgsql needs lots of memory too and this memory is permanently reserved for PostgreSQL.

If the server won't start after making this change, check for errors appearing in pgstartup.log about exceeding the kernel SHMMAX variable. If this occurs, increase the kernel.shmmax variable using the sysctl command:

# increase max shared memory to 256 megabytes
sysctl -w kernel.shmmax=268435456
 
# for OS X Leopard or newer use the line below and add kern.sysv.shmmax=268435456 to /etc/sysctl.conf
sudo sysctl -w kern.sysv.shmmax=268435456

Edit /etc/sysctl.conf to make it permanent. If you are using linux-vserver technology, please mention you have to set shmmax separately for each guest. If you want to tune your kernel, do not forget to read Managing Kernel Resources part of PostgreSQL documentation.

checkpoint_segments = 20

The default is far too low for bulk inserts. It costs more diskspace this way but the load will be smoother; it depends a bit on your disk subsystem. To determine the optimal setting you need to play a bit. Check your postgres logs for the following:

LOG:  checkpoints are occurring too frequently (28 seconds apart)
HINT:  Consider increasing the configuration parameter "checkpoint_segments".

For advanced users: you can also tune the bgwriter to smooth performance.

maintenance_work_mem = 256MB    # 256000 for 8.1 and earlier

This setting is the amount of memory used while building indexes. The default is far too small for the size of tables we're dealing with. This memory is released after completion so you don't need to be as stingy as above. Note that osm2pgsql is still large at this point so you can't overdo it.

work_mem = 256MB # 256000 for 8.1 and earlier

This setting is the amount of memory used when performing an ORDER BY for which no index can be used. osm2pgsql pre-orders some tables before creating indexes on them, and for this the default setting once again is far too small. All notes on maintenance_work_mem above apply to work_mem, too.

autovacuum = off

Auto-vacuuming means PostgreSQL starts cleaning up your data. This might slow down your data inserting and reading considerably. If you do not use the database for anything else, you can definitely turn autovacuuming off.

Create Database

General

<username> should be the name of the user who will do the rendering later. For normal desktop systems, I recommend using your own username. To do this, you can substitute "<username>" in the commands below with "`whoami`" (mind the fancy quotes).

On most systems you need to be authenticated as the PostgreSQL super user in order to execute many of the commands below. Often you can not login as this user. Instead you should issue a command like this as root:-

# su - postgres
$ <commands...>

or

# sudo -u postgres <command>

or on a system like Ubuntu which does not want to let you run as root either

user@machine$ sudo -u postgres -i
postgres@machine$ <commands...>

Now as the postgres user run the following to setup the database:

$ createuser <username>
$ createdb -E UTF8 -O <username> gis
$ createlang plpgsql gis

If on Windows, these commands can be issued from the Admin tool GUI or run by navigating to the bin directory (e.g. C:\Program Files\PostgreSQL\8.3\bin) in an command prompt run as Administrator, then running:

C:\Program Files\PostgreSQL\8.3\bin\createuser -U postgres <username>
C:\Program Files\PostgreSQL\8.3\bin\createdb -U postgres -E UTF8 -O <username> gis
C:\Program Files\PostgreSQL\8.3\bin\createlang -U postgres plpgsql gis

If needed, the commands 'dropdb gis' and 'dropuser <username>' can be used. In particular, you might find it necessary to make the user a postgres administrator (you will be asked for this when creating the user), to simplify user permissions.

openSUSE 11.2

You have to create a user in Postgresql that will be used to create all tables and data. Then some SQL scripts containing vital mapping-related instructions need to be executed. These instructions generate the user with the same name as you are currently logged in with and the password "something". To change that, replace all occurences of "`whoami`" and "something", respectively.

sudo su -c "createuser --superuser `whoami`" postgres
sudo su -c "createdb -E UTF8 -O `whoami` gis" postgres
sudo su -c "createlang plpgsql gis" postgres
echo "ALTER USER `whoami` WITH PASSWORD 'something';" | psql -d gis

Please note: using the default openSUSE 11.2 configuration of PostgreSQL, you will definitely run in to this issue: Mapnik#Authentication failed. Follow the steps listed there to fix it.

Diff files (.osc) support

Please ignore everything that is said about intarray if you are using osm2pgsql version 0.80 or higher. That version of osm2pgsql does not require intarray, in fact it will refuse to work if you install intarray.

To support daily, hourly, etc osc files, osm2pgsql requires the intarray contrib module. If it's missing, you will see the following message when you populate the database with osm2pgsql:

 *** WARNING: intarray contrib module not installed
 *** The resulting database will not be usable for applying diffs.

For Ubuntu

 $ sudo apt-get install postgresql-contrib-8.3

As the postgres user or your postgresql super user:

 $ psql -d gis -f /usr/share/postgresql/8.3/contrib/_int.sql

For Fedora

 $ yum install postgresql-contrib

As the postgres user or your postgresql super user:

 $ psql -d gis -f /usr/share/pgsql/contrib/_int.sql

openSUSE 11.2

Execute this command when logged in as the same user you used to create the PostgreSQL user.

psql -d gis -f /usr/share/postgresql/contrib/_int.sql

PostGIS

You need to activate PostGIS on the newly created database. Note that lwpostgis.sql / lwpostgis-64.sql mentioned below is now postgis.sql / postgis-64.sql starting with PostGIS v1.4. note: this file can also be located in /usr/share/postgresql/8.4/contrib/

For Debian Lenny (stable) and Ubuntu < 10.4

As the postgres user or your postgresql super user:

$ psql -d gis -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
$ echo "ALTER TABLE geometry_columns OWNER TO <username>; ALTER TABLE spatial_ref_sys OWNER TO <username>;"  | psql -d gis

where <username> is the name of the user that you set to own the database gis.

if one error occurs with lwpostgis.sql try

$ psql -d gis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql

For Debian testing or unstable and Ubuntu 10.4

As the postgres user or your postgresql super user:

psql -d gis -f /usr/share/postgresql/8.4/contrib/postgis.sql 
echo "ALTER TABLE geometry_columns OWNER TO <username>; ALTER TABLE spatial_ref_sys OWNER TO <username>;"  | psql -d gis

where <username> is the name of the user that you set to own the database gis.

For Ubuntu 11.04

psql -d gis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
echo "ALTER TABLE geometry_columns OWNER TO <username>; ALTER TABLE spatial_ref_sys OWNER TO <username>;"  | psql -d gis

where <username> is the name of the user that you set to own the database gis.

For Fedora

As the postgres user or your postgresql super user:

$ psql -d gis -f /usr/share/pgsql/contrib/lwpostgis.sql
$ psql -d gis -f osm2pgsql/900913.sql
$ echo "ALTER TABLE geometry_columns OWNER TO username; ALTER TABLE spatial_ref_sys OWNER TO username;"  | psql -d gis

Note: If the path above does not work, you may have to use psql -d gis -f /usr/share/pgsql/contrib/lwpostgis-64.sql

For Opensuse 11

If you did not use your own username as user in PostgreSQL, log in as the user "postgres" and change "`whoami`" below to the username you used.

psql -d gis -f /usr/share/postgresql/contrib/postgis.sql
psql -d gis -f osm2pgsql/900913.sql
echo "ALTER TABLE geometry_columns OWNER TO `whoami`; ALTER TABLE spatial_ref_sys OWNER TO `whoami`;" | psql -d gis

For Windows

Enter this in an Administrator command prompt If you are running PostGIS 1.5:

C:\Program Files\PostgreSQL\8.4\bin\psql -U postgres -d gis -f "%ProgramFiles%\PostgreSQL\8.4\share\contrib\postgis-1.5\postgis.sql"

If you are running an older PostGIS version :

C:\Program Files\PostgreSQL\8.3\bin\psql -U postgres -d gis -f "%ProgramFiles%\PostgreSQL\8.3\share\contrib\lwpostgis.sql"

Troubleshooting

could not access file "$libdir/postgis-1.5": No such file or directory

This error shows up when both postgresql-9.0 and postgresql-8.4 are installed (Debian/testing). Uninstalling 9.0 helps, it should be also possible to switch the active toolset version.