From OpenStreetMap Wiki
Jump to: navigation, search
Available languages — Osm2pgsql
· Afrikaans · Alemannisch · aragonés · asturianu · Aymar aru · azərbaycanca · Bahasa Indonesia · Bahasa Melayu · bamanankan · Bân-lâm-gú · Basa Jawa · Basa Sunda · Baso Minangkabau · bosanski · brezhoneg · català · čeština · corsu · dansk · Deutsch · eesti · English · español · Esperanto · estremeñu · euskara · français · Frysk · Gaeilge · Gàidhlig · galego · Hausa · hrvatski · Igbo · interlingua · Interlingue · isiXhosa · isiZulu · íslenska · italiano · Kiswahili · Kreyòl ayisyen · kréyòl gwadloupéyen · kurdî · Latina · latviešu · Lëtzebuergesch · lietuvių · Limburgs · magyar · Malagasy · Malti · Nederlands · Nedersaksies · norsk · norsk nynorsk · occitan · Oromoo · oʻzbekcha/ўзбекча · Plattdüütsch · polski · português · română · shqip · slovenčina · slovenščina · Soomaaliga · suomi · svenska · Tagalog · Tiếng Việt · Türkçe · Türkmençe · Vahcuengh · vèneto · walon · Wolof · Yorùbá · Zazaki · isiZulu · српски / srpski · авар · Аҧсшәа · башҡортса · беларуская · български · қазақша · Кыргызча · македонски · монгол · русский · тоҷикӣ · українська · Ελληνικά · Հայերեն · ქართული · नेपाली · भोजपुरी · मराठी · संस्कृतम् · हिन्दी · অসমীয়া · বাংলা · ਪੰਜਾਬੀ · ગુજરાતી · ଓଡ଼ିଆ · தமிழ் · తెలుగు · ಕನ್ನಡ · മലയാളം · සිංහල · བོད་ཡིག · ไทย · မြန်မာဘာသာ · ລາວ · ភាសាខ្មែរ · ⵜⴰⵎⴰⵣⵉⵖⵜ · አማርኛ · 한국어 · 日本語 · 中文(简体)‎ · 中文(繁體)‎ · 吴语 · 粵語 · ייִדיש · עברית · اردو · العربية · پښتو · سنڌي · فارسی · ދިވެހިބަސް

osm2pgsql is a command-line based program that converts OpenStreetMap data to postGIS-enabled PostgreSQL databases.

Such databases are often used to render OSM data visually using Mapnik, as PostgreSQL is one of the most efficient and flexible formats Mapnik can use for querying large amounts of data. For more information on using osm2pgsql to render OpenStreetMap data with Mapnik, see the Mapnik page.

Its homepage is: https://github.com/openstreetmap/osm2pgsql It is currently maintained pnorman and lonvia. There are regular releases which can be used in production.

According to https://github.com/openstreetmap/osm2pgsql/blob/master/CONTRIBUTING.md#documentation "User documentation is stored in docs/. Pages on the OpenStreetMap wiki are known to be unreliable and outdated."

OS Support

osm2pgsql is available for Linux, Mac OS X, and Windows. Most osm2pgsql users run Ubuntu or a Debian-based distribution and you'll find more complete and up to date documentation and support for it.


From source

Prerequisite lists are listed in the osm2pgsql README. The main dependencies to be aware of for old distributions are a C++11 compiler and Boost 1.50 or later.

From package managers

osm2pgsql is packaged on most common Linux operating systems. If you are running an old distribution, check that you have at least version 0.82.0 with osm2pgsql -h

For Debian or Ubuntu

apt-get install osm2pgsql


dnf install osm2pgsql


For openSUSE 11.3 and newer: First add the Geo package repository (adapt URL to correct openSUSE version):

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

Then install osm2pgsql:

sudo zypper install osm2pgsql

Arch Linux

Build the osm2pgsql-git package from the AUR. (Download the tarball and compile/install with 'makepkg', or use an AUR helper such as 'yaourt'.)


pkg install converters/osm2pgsql



Unofficial builds for Windows are available from AppVeyor but you need to find the right build artifacts. For the release 0.96.0 they are:

For other releases adapt tag as required.

From Source

For native Windows compilation you need a C++11-compatible compiler. Visual Studio 2015 is known to work. Follow the usual steps for compiling cmake-enabled sources.


Cross-compilation problems with osm2pgsql are documented on github. Whilst these problems are being worked on, an alternative compilation path for windows users targeting Cygwin has been used.

This can be run directly in Windows as all relevant DLLs are included in the distribution. Alternatively it can be used under Cygwin within Windows.

Mac OS X

With Homebrew

brew install osm2pgsql

Osm2pgsql schema

Information about the schema created by Osm2pgsql can be found here: Osm2pgsql/schema.

Import style

Aspects of how osm2pgsql converts OSM data into PostgreSQL tables can be configured via a style file. The default style file that is installed with osm2pgsql is suitable for rendering the standard OSM Mapnik style or similar styles. It also contains the documentation of the style syntax. With a custom style file, you can control how different object types and tags map to different columns and data types in the database.

The style file is a plain text file containing 4 columns separated by spaces. As each OSM object is processed it is checked against conditions specified in the first two columns. If they match, processing options from the third and fourth columns are applied.

  1. OSM object type: can be node, way or both separated by a comma. way will also apply to relations with type=multipolygon, type=boundary, or type=route; all other relations are ignored by osm2pgsql.
  2. Tag: the tag to match on. If the fourth column is linear or polygon, a column for this tag will be created in each of the point, line, polygon, and road tables.
  3. PostgreSQL data type: This specifies how data will be stored in the tag's postgresql table column. Possible values are text, int4, real. If the fourth column is delete or phstore, this column has no meaning and should just be set to text.
  4. Flag: Zero or more flags separated by commas. Possible values:
    • linear: Specifies that ways with this tag should be imported as lines by default, even if they are closed. Other conditions can still override this. See the Osm2pgsql/schema#planet_osm_line schema documentation for details on how route relations are handled.
    • polygon: Specifies that closed ways with this tag should be imported as polygons by default. This will override any linear flags that would apply to the same object. Closed ways with area=yes and closed relations with type=multipolygon or type=boundary will be imported as polygons even if no polygon flag is set. Non-closed ways and closed ways with area=no will always be imported as lines.
    • nocolumn: The two flags above automatically create a column for the specified tag. This flag overrides this behaviour such that no column is created. This is especially useful for hstore, where all key value data is stored in the hstore column such that no dedicated columns are needed.
    • phstore: The same as polygon,nocolumn for backward compatibility
    • delete: Prevents the specified tag (but not the entire object) from being stored in the database. Useful for tags that tend to have long values but will not be used for rendering, such as source=*. This flag only affects --slim mode imports.
    • phstore: Behaves like the polygon flag, but is used in hstore mode when you do not want to turn the tag into a separate PostgreSQL column.
    • nocache: This flag is depreciated and does nothing.

The style file may also contain comments. Any text between a # and the end of the line will be ignored.

Special 'tags'

There are several special values that can be used in the tag column (second column) of the style file for creating additional fields in the database which contain things other than tag values.

  • way_area: Creates a database column that stores the area (calculated in the units of the projection, normally Mercator meters) for any objects imported as polygons. Use with real as the data type.
  • z_order: Adds a column that is used for ordering objects in the render. It mostly applies to objects with highway=* or railway=*. Use with int4 as the data type.
  • osm_user: Adds a column that stores the username of the last user to edit an object in the database.
  • osm_uid: Adds a column that stores the user ID number of the last user to edit an object in the database.
  • osm_version: Adds a column that stores the version of an object in the database (ie, how many times the object has been modified).
  • osm_timestamp: Adds a column that stores the date and time that the most recent version of an object was added to OpenStreetMap.

To use the osm_user, osm_uid, osm_version, and osm_timestamp tags, you must use the osm2pgsql option --extra-attributes when importing.

Avoiding pitfalls

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


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. You can use the --keep-coastlines parameter to change this behavior if you want coastlines in your database.


For basic usage, see the main page of osm2pgsql (man osm2pgql) and the README found on its github page.

Before running osm2pgsql, you first must create your postgresql database and enable postgis on it.

Basic usage:

osm2pgsql -s -U postgres -d nameofdatabase /file/path/toosm/fileorpbf/name.osm

In the example above, postgres is the user of the database, the user enabled slim mode (generally recommended).

The above sample is feasible for someone looking to export a city's OSM data into a postGIS database. For more advanced and larger datasets, read the Optimization section.

Additional parameters:

-G|--multi-geometry Generate multi-geometry features in postgresql tables.

--flat-nodes flat-nodes is an alternative node store in a file which uses ~20GB (Oct 2013) instead of 100GB in Postgresql. It is also faster during import and during diff updates, and thus can only be used in slim mode. Put this file on a SSD, if you can.


Best practices

Optimising the Mapnik/osm2pgsql Rendering Toolchain 2.0 @ SOTM 2012

Slim mode

osm2pgsql has two main modes of running - normal and slim mode.

It is highly recommended to run osm2pgsql in slim mode. Some important features (including incremental updates (planet diffs, the initial load to populate the track tables, and proper evaluation of multipolygons) only work in slim mode.

The normal mode uses RAM for intermediate storage, Slim mode uses object tracking tables

  • planet_osm_nodes
  • planet_osm_ways
  • planet_osm_rels

in the database on-disk. 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.

One benefit of not using slim mode is that osm2pgsql is a bit faster since it doesn't read from the DB until the index creation.

Implement slim mode by using the '-s' option: "osm2pgsql -s -d ...."


  • Large imports into PostGIS are very sensitive to maintenance and monitoring configuration: it is smart to increase the value of checkpoint_segments so that autovacuum tasks don't slow down imports. This can be done by editing postgresql.conf.

See the Postgres Wiki for reference.

  • osm2pgsql relies much on its node cache during import. If the nodes do not fit into the cache in slim mode it needs to do database lookups which slow down the process. (Without slim mode, it fails if the nodes do not fit in the cache). Use enough cache so all nodes are cached. -C 22000 seems to do the job, even if that means you have to configure more swap space.
    • Without slim mode, there is also a "way" cache which takes up about as much space as the node cache does, but is not charged against -C. In slim mode, there is no "way" cache.

Fast disks

The bottleneck is usually the I/O when running osm2pgsql with --slim or even without it.


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

Please report bugs to the github tracker.


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 especially 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');
(1 Zeile)
-k|--hstore		Generate an additional hstore (key/value) column to  postgresql tables

install into postgresql

Postgresql 9.1 and later

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


See also

  • Manually building a tile server - Also explains how to create the database for osm2pgsql, import data, etc
  • PostGIS/Installation - Explains how to create the database for osm2pgsql
  • Mapnik - Explains how to import OSM data
  • High Road - collection of Postgres views that make rendering roads from Osm2pgsql schema easier and better-looking
  • dtexpand – expanding the dirty-tiles list to get the identifiers of all tiles which were affected by a data update

Alternatives to Osm2pgsql