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 currently does not have a sole maintainer but is maintained by a community of contributors and Osm2pgsql does not a designate 'stable' or 'development' versions but most recent builds should be able to be used in a production environment. Until April 2013, it was hosted on svn.
It is highly recommended to use a version released since Sept. 2, 2012, which has support for 64-bit_Identifiers.
- 1 OS Support
- 2 Installation
- 3 Osm2pgsql schema
- 4 Import style
- 5 Avoiding pitfalls
- 6 Usage
- 7 Optimization
- 8 Benchmarks
- 9 Updating Data in Database
- 10 Bug reports
- 11 hstore
- 12 See also
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.
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
yum 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
pkg install converters/osm2pgsql
|This method will not get you a working install for the reasons noted below. Use Cygwin instead|
For native Windows compilation with only minor source adjustments it is recommended to use a C99 compatible compiler such as the Intel Compiler.
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.
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
Jburgess, who compiled the newest windows binary (2010-04-10), used mingw32 to cross-compile the windows binary from a Linux host.
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.
Download the zipped osm2pgsql Cygwin package (latest version (95) here: https://vanguard.houghtonassociates.com/browse/OSM-OSM2PSQL-95/artifact).
Unzip this into a directory usually cygwin_package, but perhaps better called osm2pgsql.
You can run osm2pgsql within this directory and/or add the directory to your PATH variable. (In practice I have a single windows directory for core OSM apps (osmosis, osmconvert, osmfilter, osm2pgsql, ogr2osm etc.) and copy apps and their libraries there.
(A quick hack if running this under Cygwin itself which I've used is simply to copy the .exe and .dll files to /usr/bin, some of the .dlls will already exist. Osm2pgsql will then be in the path for Cygwin.)
See also notes under the Windows Binary section regarding PATH and other environment variables.
Mac OS X
brew install osm2pgsql
Information about the schema created by Osm2pgsql can be found here: Osm2pgsql/schema.
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.
- OSM object type: can be
wayor both separated by a comma.
waywill also apply to relations with type=multipolygon, type=boundary, or type=route; all other relations are ignored by osm2pgsql.
- Tag: the tag to match on. If the fourth column is
polygon, a column for this tag will be created in each of the point, line, polygon, and road tables.
- PostgreSQL data type: This specifies how data will be stored in the tag's postgresql table column. Possible values are
real. If the fourth column is
phstore, this column has no meaning and should just be set to
- 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
linearflags 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
polygonflag 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
phstore: Behaves like the
polygonflag, 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.
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
realas 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
int4as 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.
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 man 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.
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.
-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.
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
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_segmentsso 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 22000seems 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.
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
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'); count ------- 447 (1 Zeile)
-k|--hstore Generate an additional hstore (key/value) column to postgresql tables
- a Step-By-Step guide to a minutely updated osm2pgsql hstore table (german but the commands are the same): DE:HowtoMinutelyHstore
- more postgresql hstore info
install into postgresql
Postgresql 9.1 and later
This sql code will install the extension in your current database :
CREATE EXTENSION hstore;
- 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
Alternatives to Osm2pgsql
- Osmosis - can also do imports of osm file to postgres DB with postgis extension
- Osm2postgresql powered by Osmosis
- OGR - OGR OSM driver with ogr2ogr