PostGIS/Installation

Help

Available languages

• English • 日本語 • русский

Other languages — Help us translating this wiki

• Afrikaans • አማርኛ • العربية • অসমীয়া • asturianu • azərbaycanca • български • বাংলা • brezhoneg • bosanski • català • corsu • česky • dansk • Deutsch • Zazaki • ދިވެހިބަސް • Ελληνικά • Esperanto • español • eesti • euskara • فارسی • suomi • français • Frysk • Gaeilge • Kréyòl gwadloupéyen • Gàidhlig • galego • Alemannisch • ગુજરાતી • هَوُسَ • עברית • हिन्दी • hrvatski • Kreyòl ayisyen • magyar • Հայերեն • interlingua • Bahasa Indonesia • Igbo • íslenska • italiano • Basa Jawa • ქართული • қазақша • ភាសាខ្មែរ • ಕನ್ನಡ • 한국어 • Kurdî • Lëtzebuergesch • ລາວ • lietuvių • latviešu • Malagasy • македонски • മലയാളം • मराठी • Bahasa Melayu • Malti • မြန်မာဘာသာ • नेपाली • Nederlands • ‪norsk (nynorsk)‬ • ‪norsk (bokmål)‬ • occitan • Oromoo • ଓଡ଼ିଆ • ਪੰਜਾਬੀ • polski • پښتو • português • português do Brasil • română • română (Moldova) • سنڌي • සිංහල • slovenčina • slovenščina • Soomaaliga • shqip • српски / srpski • svenska • தமிழ் • తెలుగు • тоҷикӣ • ไทย • Türkçe • українська • اردو • oʻzbekcha • Tiếng Việt • Wolof • isiXhosa • Yorùbá • ‪中文（简体）‬ • ‪中文（繁體）‬ • isiZulu

The following instructions describe how to install PostgreSQL and 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.

In these examples, gisuser is used as the name of the user and gis is used as the name of the database

The main OpenStreetMap Mapnik styles make use of PostgreSQL and PostGIS as part of the rendering pipeline. Creating a database is the same or similar for osmosis and osm2pgsql.

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

Install PostgreSQL and PostGIS

Debian Squeeze

As root:

aptitude install postgresql-8.4-postgis

Debian Wheezy

As root:

aptitude install postgresql-9.1-postgis

Ubuntu 12.04 LTS

sudo apt-get install postgresql postgresql-contrib postgis

OS X Leopard

Assuming you have darwinports,

% sudo port install postgresql83
% sudo port install postgis

or use the Mac OS X installers at KyngChaos.

Fedora

As root:

Fedora 15 and lower	Fedora 16 and above
yum install postgis postgresql-server service postgresql initdb service postgresql start	yum install postgis postgresql-server postgresql-setup initdb service postgresql start

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

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.

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.

Create database

General

On most systems you need to be authenticated as the PostgreSQL super user (usually named postgres) in order to execute many of the commands below.

sudo -u postgres createuser gisuser
sudo -u postgres createdb --encoding=UTF8 --owner=gisuser gis
sudo -u postgres createlang plpgsql gis  # note: this is obsolete with >=postgres-9.1

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 gisuser
C:\Program Files\PostgreSQL\8.3\bin\createdb -U postgres -E UTF8 -O gisuser gis
C:\Program Files\PostgreSQL\8.3\bin\createlang -U postgres plpgsql gis

If needed, the commands 'dropdb gis' and 'dropuser gisuser' 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 gisuser and the password "something". To change that, replace all occurences of "gisuser" and "something", respectively.

sudo su -c "createuser --superuser gisuser" postgres
sudo su -c "createdb -E UTF8 -O gisuser gis" postgres
sudo su -c "createlang plpgsql gis" postgres
echo "ALTER USER gisuser 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.

Activate PostGIS

You need to activate PostGIS on the newly created database. After the activation, the following command should list the tables geometry_columns and spatial_ref_sys:

psql --username=gisuser --dbname=gis --command="\d"

Debian Squeeze

psql --username=postgres --dbname=gis --file=/usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql --username=postgres --dbname=gis --file=/usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql --username=postgres --dbname=gis --command="ALTER TABLE geometry_columns OWNER TO gisuser"
psql --username=postgres --dbname=gis --command="ALTER TABLE spatial_ref_sys OWNER TO gisuser"

Debian Wheezy

psql --username=postgres --dbname=gis --file=/usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
psql --username=postgres --dbname=gis --file=/usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
psql --username=postgres --dbname=gis --command="ALTER TABLE geometry_columns OWNER TO gisuser"
psql --username=postgres --dbname=gis --command="ALTER TABLE spatial_ref_sys OWNER TO gisuser"

Ubuntu 11.04

sudo -u postgres psql -d gis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
sudo -u postgres psql -d gis -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
sudo -u postgres psql -d gis -c "ALTER TABLE geometry_columns OWNER TO gisuser;"
sudo -u postgres psql -d gis -c "ALTER TABLE spatial_ref_sys OWNER TO gisuser;"

Ubuntu 12.04 LTS

sudo -u postgres psql -d gis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/postgis.sql
sudo -u postgres psql -d gis -f /usr/share/postgresql/9.1/contrib/postgis-1.5/spatial_ref_sys.sql
sudo -u postgres psql -d gis -f /usr/share/postgresql/9.1/contrib/postgis_comments.sql
sudo -u postgres psql -d gis -c "GRANT SELECT ON spatial_ref_sys TO PUBLIC;"
sudo -u postgres psql -d gis -c "GRANT ALL ON geometry_columns TO gisuser;"

Fedora

As the postgres user or your postgresql super user:

psql -d gis -f /usr/share/pgsql/contrib/postgis.sql
psql -d gis -f /usr/share/pgsql/contrib/spatial_ref_sys.sql
echo "ALTER TABLE geometry_columns OWNER TO gisuser; ALTER TABLE spatial_ref_sys OWNER TO gisuser;"  | psql -d gis

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

openSUSE 11

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

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"

Populate the database

The database can be populated by :

• osm2pgsql
• Osmosis
• Imposm
• …

Troubleshooting

Problem:

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.

---

Problem:

createdb: database creation failed: ERROR:  new encoding (UTF8) is incompatible with the encoding of the template database (SQL_ASCII)

http://journal.tianhao.info/2010/12/postgresql-change-default-encoding-of-new-databases-to-utf-8-optional/ Change default template to UTF-8]] (Note: I used UTF8 instead UNICODE as that page says.) (I got this error on Postgis 9.0.7, Mandriva 2011)

---

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
# for Ubuntu >=12.04:
  sudo gedit /etc/postgresql/9.1/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

On some setups you may see an entry for ipv6:

host    all         all         ::1/128               md5

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

---

problem: missing the intarray for diff files (.osc) support

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.

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

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

See also

• Installation chapter of the PostGIS 2.0 Manual