Osmosis/PostGIS Setup

From OpenStreetMap Wiki
Jump to: navigation, search
Available languages
English

This is a description of Setup PostGIS in Debian / Ubuntu

Procedure to import data into PostgreSQL

A bit of set-up is required to get PostgreSQL to work with osmosis. This page describes a procedure to get it set up to import a Planet dump. The procedure works on Ubuntu 9.04. Other Debian based distributions will be similar.

We should probably separate generic topics like 'creating postgis enabled database' and 'running osmosis' into separate wiki pages. Creating a database is the same or similar for osmosis and osm2pgsql tools and could be better maintained in one common place PostGIS through a bigger user base.


Install Postgresql and PostGIS

sudo apt-get install postgresql-8.3-postgis postgresql-contrib-8.3 postgis

or

sudo aptitude install postgresql-8.4-postgis postgresql-contrib-8.4 postgis

Install and compile Osmosis

Osmosis needs the openjdk java development kit and the 'ant' build tool. The code below uses the git client to obtain the source code from the github server.

sudo apt-get install openjdk-6-jdk ant ant-optional
git clone https://github.com/openstreetmap/osmosis.git
cd osmosis/
./gradlew build

Note: I get an error on one of the tests, but it seems to work otherwise. The error is: "[junit] Test org.openstreetmap.osmosis.extract.apidb.v0_6.DatabaseTimeLoaderTest FAILED".

Note: On Mac OS X, if you get an error about missing classes or incompatible java class versions you need to execute:

export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/1.6/Home/

Create and Initialise Database

The pgsql_simple_schema_0.6.sql file used below is provided with the osmosis source in the "script" directory. Note that for the default postgresql configuration you need to set the postgresql username (<username> below) to be the same as the system login user name. The code below sets the password to 'osm' but you can pick anything.

Postgres 8.3

sudo su - postgres
createdb osm
createlang plpgsql osm
createuser <username>

psql -d osm -f /usr/share/postgresql-8.3-postgis/lwpostgis.sql
# The following command seems to be needed at least on OpenSUSE 11.1 with prebuild packages from build.opensuse.org - 
# otherwise I got the following error during the next command:
#  "psql:script/pgsql_simple_schema_0.6.sql:42: ERROR:  AddGeometryColumns() - invalid SRID"
psql -d osm -f /usr/share/postgis/spatial_ref_sys.sql
psql -d osm -f script/pgsql_simple_schema_0.6.sql

echo "alter role <username> password 'osm';" | psql -d osm

Postgres 8.4

From Postgres 8.4 onwards, the lwpostgis.sql file has been renamed to postgis.sql. Running Ubuntu 11.04 the above lines should read

sudo su - postgres

createuser <username>
psql --command "ALTER USER <username> WITH ENCRYPTED PASSWORD 'osm'";

createdb osm
createlang plpgsql osm
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
psql -d osm -f <path-to-osmosis>/package/script/pgsimple_schema_0.6.sql

In order to use hstore to store the tags replace the last line with the following:

psql -d osm -f /usr/share/postgresql/8.4/contrib/hstore.sql
psql -d osm -f <path-to-osmosis>/script/pgsnapshot_schema_0.6.sql

Debian 6 (Squeeze)

Under Debian 6 some paths differ.

The following commands are needed to create the user.

sudo su - postgres
createuser <username>
exit

Administrator rights are provided to the user 'username'.

As 'username':

createdb osm
createlang plpgsql osm
psql -d osm -f /usr/share/postgresql/8.4/contrib/hstore.sql
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
psql -d osm -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql
cd <dir to>/osmosis/package/script
psql -d osm -f pgsnapshot_schema_0.6.sql
psql -d osm -f pgsnapshot_schema_0.6_action.sql
psql -d osm -f pgsnapshot_schema_0.6_bbox.sql
psql -d osm -f pgsnapshot_schema_0.6_linestring.sql

Note that the 'snapshot' version is used, because if not the osmosis import stops with an error message about a wrong schema version.

Postgres 9.0.1

Debian 6 (squeeze) does not include postgresql 9.0. You can install it from backports. Add backports to sources.list

deb http://backports.debian.org/debian-backports squeeze-backports main

Then

aptitude -t squeeze-backports install postgresql postgresql-contrib-9.0 postgresql-server-dev-9.0

You need to build postgis for 9.0 from source as no package exists

wget http://www.postgis.org/download/postgis-1.5.3.tar.gz
tar -xvzf postgis-1.5.3.tar.gz
cd postgis-1.5.3
./configure
make
make comments
make install
make comments-install

In even later versions, the postgis file does not automatically populate all of the tables needed for OSM. Combining this difference with the hstore.sql note below, we have:

sudo su - postgres
createdb osm
createlang plpgsql osm
createuser <username>

psql -d osm -f /usr/local/Cellar/postgresql/9.0.1/share/contrib/hstore.sql
psql -d osm -f /usr/local/share/postgis/postgis.sql
psql -d osm -f /usr/local/share/postgis/spatial_ref_sys.sql
psql -d osm -f pgsql_simple_schema_0.6.sql

both

The above code uses the 'simple' database schema provided with osmosis. Other schemas are available in the osmosis 'script' directory, which add additional columns such as the bounding box around ways etc., which may be useful for some applications. These schemas can be used by running the appropriate .sql file as is done for the simple one above.

The most recent version of the osmosis schema uses hstore for tags. If you use that, then you also need to do the following before loading the schema. The path to the hstore.sql script should be altered to fit wherever your distribution stores contrib files.

psql -U postgres osm -f /usr/share/postgresql-8.4/contrib/hstore.sql

Postgres 9.1

v9.1 is currently the default in Debian Wheezy (testing at the time of writing). When trying to create the schema, I got an error like:

psql:./script/pgsnapshot_schema_0.6.sql:38: ERROR:  type "hstore" does not exist

I found the solution documented in http://trac.openstreetmap.org/ticket/3987, specifically

psql -U osm
ALTER USER osm WITH SUPERUSER;
(Ctrl-D)
psql -U osm osm
CREATE EXTENSION hstore;

I was then able to run the script. On ubuntu 12.04 the scripts menchioned above, have moved from the posgis directory to a subdirectory of postgres.

"/opt/local/share/postgresql/9.1/extension/"

If you use Mac Ports you may get the following error:

ERROR:  could not open extension control file "/opt/local/share/postgresql91/extension/hstore.control": No such file or directory

I found the solution documented in http://stackoverflow.com/questions/4776563/how-do-i-install-the-hstore-module-on-postgresql-9-0-macports-install

Import a osm file into the database

In this example the osm file from UK is imported. Note, that you must use --write-pgsimp if you used the pg_simple schema and --write-pgsql if you used the pg_snapsnot schema. Also make sure that your user was assigned a password.

/home/disk2/OSM/osmosis/trunk/bin/osmosis \
 --read-xml file="/home/disk2/OSM/OSM/uk-090610.osm.bz2" \
 --write-pgsimp user="<username>" database="osm" password="osm"

Osmosis gives the following output if it works ok:

18-Jun-2009 22:57:45 org.openstreetmap.osmosis.core.Osmosis run
INFO: Osmosis Version 0.31.1
18-Jun-2009 22:57:46 org.openstreetmap.osmosis.core.Osmosis run
INFO: Preparing pipeline.
18-Jun-2009 22:57:46 org.openstreetmap.osmosis.core.Osmosis run
INFO: Launching pipeline execution.
18-Jun-2009 22:57:46 org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline executing, waiting for completion.
19-Jun-2009 01:36:41 org.openstreetmap.osmosis.core.Osmosis run
INFO: Pipeline complete.
19-Jun-2009 01:36:41 org.openstreetmap.osmosis.core.Osmosis run
INFO: Total execution time: 9536016 milliseconds.

If you want to import a complete planet.osm file, you need some disk space and some time. The following space and time assessments are based on the planet file from 2011-02-24.

A lot of data is cached in the tmp directory - which is under Linux typically located under /tmp. About 250GByte temporary space is needed. If you do not have enough space under /tmp, it is possible to set a variable to change the temporary directory. This must be done before calling osmosis.

JAVACMD_OPTIONS=-Djava.io.tmpdir=/database/osm/tmp
export JAVACMD_OPTIONS
../osmosis ...

Please adapt the path of the java.io.tmpdir to your needs.

Check Results

You can check the database by logging into postgresql and checking the database structure:

psql -d osm
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

osm=# \d
             List of relations
 Schema |       Name       | Type  | Owner  
--------+------------------+-------+--------
 public | geometry_columns | table | graham
 public | node_tags        | table | graham
 public | nodes            | table | graham
 public | relation_members | table | graham
 public | relation_tags    | table | graham
 public | relations        | table | graham
 public | schema_info      | table | graham
 public | spatial_ref_sys  | table | graham
 public | users            | table | graham
 public | way_nodes        | table | graham
 public | way_tags         | table | graham
 public | ways             | table | graham
(12 rows)

osm=# \d+ nodes
                         Table "public.nodes"
    Column    |            Type             | Modifiers | Description 
--------------+-----------------------------+-----------+-------------
 id           | bigint                      | not null  | 
 version      | integer                     | not null  | 
 user_id      | integer                     | not null  | 
 tstamp       | timestamp without time zone | not null  | 
 changeset_id | bigint                      | not null  | 
 geom         | geometry                    |           | 
Indexes:
    "pk_nodes" PRIMARY KEY, btree (id)
    "idx_nodes_geom" gist (geom)
Check constraints:
    "enforce_dims_geom" CHECK (ndims(geom) = 2)
    "enforce_geotype_geom" CHECK (geometrytype(geom) = 'POINT'::text OR geom IS NULL)
    "enforce_srid_geom" CHECK (srid(geom) = 4326)
Has OIDs: no

Postgres 9.2

see also User:Lübeck/Postgis92 Win7 64bit (german)

See also

  • A single command to install postgresql & postgis and have your OSM data ready: Osm2postgresql