Osm2pgsql

From OpenStreetMap Wiki
Jump to: navigation, search
Help
Available languages
• Deutsch • English • Русский
Other languages
• Afrikaans • አማርኛ • العربية • অসমীয়া • Asturianu • Azərbaycanca • Български • বাংলা • Brezhoneg • Bosanski • Català • Corsu • Česky • Dansk • ދިވެހިބަސް • Ελληνικά • 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'zbek • Tiếng Việt • Wolof • isiXhosa • Yorùbá • ‪中文(简体)‬ • ‪中文(繁體)‬ • isiZulu

Please help to translate this page into your language.


osm2pgsql is a utility program that converts OpenStreetMap (.OSM) data into a format that can be loaded into PostgreSQL. It is often used to render OSM data visually using Mapnik, as Mapnik can query PostgreSQL for map data (Mapnik can read directly from other data source types including raw osm files directly, but PostgreSQL is the most efficient and most commonly required by stylesheets). For more information on using osm2pgsql to render OpenStreetMap data with Mapnik, see the Mapnik page.

osm2pgsql is a lossy conversion utility. It only adds features that have certain tags, as defined in the config file, and it converts nodes and ways to linestrings and polygons. This means that you can't tell which linestring is connected to which, but for rendering a map that's not important (as opposed to routing).

osm2pgsql has two main modes of running - normal (is this what it's called?) and slim mode. The first uses RAM for intermediate storage, the second uses object tracking tables

in the database on-disk. As of early 2009, you must use slim-mode for planet imports on 32-bit systems, since there are too many nodes to store in RAM otherwise. This limitation doesn't apply to 64-bit systems.

For details of the scheme look at Osm2pgsql/schema.

Incremental updates (planet diffs) work in slim mode only (including initial load to populate the track tables). Thus only databases populated in slim mode can be updated with planet diffs, and only slim mode can apply planet diffs.

You can either build osm2pgsql from scratch (source on subversion), or grab the existing Linux packages.

To use a version of osm2pgsql which supports 64-bit IDs, check out the same version above from SVN then uncomment the #define OSMID64 line in osmtypes.h before compiling. 64-bit support was added by Frederik Ramm and previously had a separate osm2pgsql-64 branch in SVN.

Contents

Installation

For Debian Lenny (stable) or Sid (unstable) or Ubuntu (10.04 LTS or later)

From the package manager

On Ubuntu 9.10 (Karmic) or better, install the osm2pgsql package from Kai's PPA:

  1. Run sudo add-apt-repository ppa:kakrueger/openstreetmap to add the PPA
  2. Run sudo apt-get update to update your packaging system.
  3. Run sudo apt-get install osm2pgsql to install the osm2pgsql package.

On Debian systems, it's probably better to compile from source to get the latest features. If you would rather use packages, you should only install osm2pgsql via the package manager if you are on Debian unstable (also named sid), otherwise you get an outdated version, which lacks most of features like hstore or pbf support. 

sudo aptitude install osm2pgsql

From source

When compiling under Ubuntu (10.4 LTS), you will need the following dependencies: 

sudo apt-get install build-essential libxml2-dev libgeos-dev libpq-dev libbz2-dev proj libtool automake

If you want PBF read support you need libprotobuf-c0-dev at least in version 0.14-1. As there is only 0.11 in the 10.4 repository you need to build it from source [2]: 

sudo apt-get install protobuf-compiler libprotobuf-dev libprotoc-dev
svn checkout http://protobuf-c.googlecode.com/svn/trunk/ protobuf-c-read-only
cd protobuf-c-read-only
./autogen.sh
make
sudo make install


After that, follow the from source instructions.

Fedora

Fetch the dependencies: 

yum install geos-devel proj-devel postgresql-devel libxml2-devel bzip2-devel 
yum install gcc-c++ protobuf-c-devel autoconf automake libtool

Then follow the from source instructions. The osm2pgsql package for Fedora is under review.

openSUSE 11.0

You need to add a community package repository: 

sudo zypper ar http://download.opensuse.org/repositories/Application:/Geo/openSUSE_11.0

Then fetch the dependencies 

sudo zypper install libgeos-devel libproj-devel postgresql-devel libxml2-devel libbzip2-devel gcc-c++

Then follow the from source instructions.

openSUSE 11.1

You need to add a community package repository: 

sudo zypper ar http://download.opensuse.org/repositories/Application:/Geo/openSUSE_11.1

Then fetch the dependencies 

sudo zypper install libgeos-devel libproj-devel postgresql-devel libxml2-devel libbz2-devel gcc-c++

Then follow the from source instructions.

openSUSE 11.2

First add the Geo package repository: 

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

To download and build osm2pgsql, you first need to install quite a bit of dependencies and tools: 

sudo zypper install subversion make gcc gcc-c++ libxml2-devel postgresql-devel libgeos-devel libbz2-devel libproj-devel postgis postgresql-contrib

Then follow the from source instructions.

Arch Linux

Download the PKGBUILD tar.gz from http://aur.archlinux.org/packages.php?ID=22107

FreeBSD 

make -C /usr/ports/converters/osm2pgsql install clean

or 

portinstall osm2pgsql

Windows XP

You can find a version from April 9, 2010 for Windows at http://tile.openstreetmap.org/osm2pgsql.zip This version doesn't support the PBF-forma but supports the hstore option that can save all the tags from .osm file into PostgreSQL. Windows users can get a database that supports hstore by installing PostgreSQL 8.3.9 from http://wwwmaster.postgresql.org/download/mirrors-ftp/binary/v8.3.9/win32/postgresql-8.3.9-1.zip. Remember to select the hstore option when running the installer.

For using osm2pgsql just unzip, add directory to your path and run.

  1. Extract the zip to a new directory on your local disk
  2. Add the new directory path to your system variable path ([3])
  3. log out and back in so the new variable is active
  4. Open up command prompt ie... run
  5. Change directories so that you are located in the extracted osm2pgsql directory
  6. now run the > osm2pgsql -h this will show you the help
  7. on windows it would be important to use parameter -S or --style to locate the stylefile. Default is a unix like path


The "HOTOSM" installer bundles and automatically sets up the osm2pgsql binary noted above. This installer also includes Mapnik, Osmosis, and a variety of useful tools. The latest buildan be downloaded from: https://github.com/hotosm/installer/downloads

The version from http://artem.dev.openstreetmap.org/files/ is a bit out-of-date (no relations import)

From Source

Using MinGW there are currently a lot of problems to solve when building osm2pgsql:

osm2pgsql configure script does not find zlib and other libraries

The configure script created by autogen does not work correctly because the parameters for defining the libraries to be linked (e.g. -lz for libz) are not at the correct position. They have to be placed at the end of the command line. By manually adding the correspondent parameters to the

Workaround: Edit the configure script and add the libraries to link manually: 

ac_link='$CXX -o conftest$ac_exeext $CXXFLAGS $CPPFLAGS $LDFLAGS conftest.$ac_ext $LIBS -lz >&5'

Note: Adding the debug parameter d to autogen.sh (autoreconf -vfid) is extremely helpful in this situation for finding out which line to edit.

Linking problem 

build_geometry.cpp:206: undefined reference to `geos::geom::Coordinate::Coordinate(double, double, double)'

Workaround: Edit build_geometry.cpp and add #define GEOS_INLINE


Used versions: libxml2-2.7.8, zlib-1.2.5, proj-4.7.0, geos-3.2.2

Mac OS X

Binary Installer

SVN snapshot builds of osm2pgsql are packaged and available at: http://dbsgeo.com/downloads/#osm2pgsql.

With Homebrew

There's a Homebrew formula for osm2pgsql. It pulls the latest version from the OpenStreetMap svn and installs it in /usr/local/bin with the rest of the homebrew binaries. Nice and simple. 

brew install osm2pgsql

Note that the homebrew recipe currently installs only the osm2pgsql binary. The [default.style] file that you would probably also want to have can be obtained from the OpenStreetMap SVN separately. An [open issue] reflects this.

With MacPorts

The only catch to installing on Mac OS X with MacPorts is the fact that the default Postgres 8.3 installation doesn't install pg_config in the shell, so the call to pg_config --includedir and pg_config --libdir fails. Therefore, either change these paths in the Makefile or add pg_config to your shell via .bashrc, etc. For reference, an installation of 8.3 usually places the binary file at 

/opt/local/lib/postgresql83/bin/pg_config

With KyngChaos binaries 

PATH=$PATH:/Library/Frameworks/GEOS.framework/unix/bin/ CFLAGS="-I/Library/Frameworks/PROJ.framework/unix/include" LDFLAGS="-L/Library/Frameworks/PROJ.framework/unix/lib/" make

From source (generic)

You can get the source of the osm2pgsql utility using Subversion (about 180 MB): 

svn co http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/

If you need o5m file format support, refer to osm2pgsql/o5m, otherwise go on as described here:

You will need the 'autoconf' command, libtool and other dependencies. See above how to install these in your distribution.

Next, enter the newly created directory containing the source for the utility: 

cd osm2pgsql/

If no Makefile and configure script exist, generate them with: 

./autogen.sh
./configure

Optionally, you can configure the compiler to produce a faster binary that can only run on CPUs that have the same capabilities as yours. 

sed -i 's/-g -O2/-O2 -march=native -fomit-frame-pointer/' Makefile

Finally, compile the sources into an executable program: 

make

If you are using an old version of GCC, you may get an error: unrecognized option `-Wextra'. In this case, edit the Makefile to remove this unnecessary option.

Avoiding pitfalls

osm2pgsql is a specialized tool and there's some behaviour hardcoded into it that you should know about.

Coastlines

The natural=coastline tag is suppressed by default, even if you import the natural=* key. The main mapnik map renders coastlines from shapefiles so it does not need them. As of revision 24605, you can use the --keep-coastlines parameter to change this behavior if you want coastlines in your database.

Slim mode

There are some important operations that only work in -slim mode. Incremental updates and proper evaluation of multipolygons are among them. I recommend to always use -slim mode, even for small files that easily fit into memory.

Use the '-s' option to do this: "osm2pgsql -s -d ...."

Import style

osm2pgsql needs to distinguish between ways that represent areas and linear features like roads, as they are imported into different tables in the database. The last column of the import style file controls this behaviour. If any of the tags on an object are marked as "polygon", the way is considered a candidate for polygon rendering. If it is closed, it is treated as an area, if it is open, it is treated as a way. Features that are misinterpreted do not appear on the map.

The area tag controls the behaviour directly. area=yes always produces an area. (Likewise, area=no always produces a linear feature but this feels like a rather bad case of weird tagging to work around a display problem with a particular renderer).

In some cases the decision is difficult. E.g. the highway tag has both areas (pedestrian areas) and closed linear features (roundabouts). The practice on the main map is to set the import style to linear and use area=yes with area objects.

DB access

There is no way to specify a user's password with osm2pgsql. So you either have to set your DB's authentication mechanism to 'trust' for the user/all users or work around it by creating a password file. See the postgreSQL documentation on how to do this.

Compiling Problems

Error: 

/usr/bin/ld: cannot find -lstdc++
collect2: ld returned 1 exit status
make: *** [osm2pgsql] Fehler 1

the new version od gcc don't need lstdc++ anymore: remove the line in the 'Makefile' (or coment it): 

LDFLAGS += -lstdc++

to 

#LDFLAGS += -lstdc++

Optimization

Parameters

Alternatives

Fast disks

The bottleneck is usually the I/O when running osm2pgsql with --slim or even without it. However, when using 3Ghz processors and fast (XM-25 for instance) SSD disks, the bottleneck may be the CPU instead. In this case one can use the alternative osm2pgsql.c source file that is distributed in the sources since july 2010. It implements a crude and faster XML parsing. It is however experimental and should be used with care. It requires the following patch to avoid core dump when started and cannot be used to read against <(bunzip2 < file.osm.bz2), most probably because it does not properly handle short reads (reads that return less bytes than required). 

Index: osm2pgsql.c
===================================================================
--- osm2pgsql.c (revision 22625)
+++ osm2pgsql.c (working copy)
@@ -741,6 +741,7 @@
     struct output_options options;
     PGconn *sql_conn;
 
+    memset(&options, '\0', sizeof(options));
     fprintf(stderr, "osm2pgsql SVN version %s\n\n", VERSION);
 
     while (1) {

A benchmark done on a SSD XM-25 disk and a Intel(R) Xeon(R) CPU W3520 @ 2.67GHz showed that libxml2 parses at 30MB/s while primitive_xml_parsing parses at 60MB/s. The measure was done using -O null on an uncompressed version of france.osm.

Multicore

With a bzipped planet file, run osm2pgsql as

bunzip -c [planet.osm.bz2] | osm2pgsql [options] /dev/stdin

Uses one core for decompressing the bzip2, and another for osm2pgsql. This is tested on Ubuntu 11.04.

Benchmarks

Please see /benchmarks for general benchmarking information.

Updating Data in Database

osm2pgsql is one of the tools used to keep OSM data updated with changes to the OpenStreetMap servers in your own PostgreSQL database. Please see Minutely_Mapnik for more information.

Bug reports

Login http://trac.openstreetmap.org/ and file a report against the mapnik component and check that it shows in the list.

hstore

Hstore is for sets of key/value pairs. As associative array datatype, just like a hash in perl or dictionary in python.

This should come in handy especialy for rarely used tags. Using hstore one can use any tag in sql queries like this: 

gis=> SELECT COUNT(*) FROM planet_osm_point WHERE ((tags->'man_made') = 'tower');
 COUNT
-------
  447
(1 Zeile)


build osm2pgsql from svn you now will have a -k option 

-k|--hstore		Generate an additional hstore (key/value) column to  postgresql tables

install into postgresql

Before postgresql 9.1 

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

Postgresql 9.1 and later

This sql code will install the extension in your current database[1] : 

CREATE EXTENSION hstore;

See also

  1. ↑ [1]
Retrieved from "http://wiki.openstreetmap.org/wiki/Osm2pgsql"
Personal tools
Namespaces
Variants
Actions
site
Toolbox