This is a description of Setup PostGIS in Debian / Ubuntu
- 1 Procedure to import data into PostgreSQL
- 2 See also
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
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/ ant all
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:
Note : The last release on github does not work with ant anymore :
sudo apt-get install openjdk-6-jdk git clone https://github.com/openstreetmap/osmosis.git cd osmosis ./gradlew build
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.
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
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'.
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.
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
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
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
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.
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.
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
see also User:Lübeck/Postgis92 Win7 64bit (german)
- A single command to install postgresql & postgis and have your OSM data ready: Osm2postgresql