Osm2pgsql
From OpenStreetMap Wiki
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, but does not work directly with OSM files. 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 (c.f. 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 (planet_osm_nodes, planet_osm_ways and planet_osm_rels) 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 if you have enough system memory (As of 28/05/2010, you will probably need for planet.osm more than 256GB of RAM !! But I cannot confirm this of course, what I know is that 32Go is not enough for europe.osm).
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, or grab the existing Linux packages.
Contents
|
Installation
For Debian Etch
Configure your system with access to the backports.org repository (see http://www.backports.org/).
apt-get update apt-get install osm2pgsql
Feel free to contact dom@earth.li with questions about these packages.
Be warned: the packages might be old. If you see rendering artefacts, try compiling osm2pgsql from source.
For Debian Lenny (stable) or Sid (unstable) or Ubuntu (8.04 or later)
From the package manager
osm2pgsql is already packaged so you can just install it!
aptitude install osm2pgsql
Be warned: the packages might be old. If you see rendering artefacts, try compiling osm2pgsql from source.
From source
When compiling under Ubuntu, you will need the following dependencies:
$ sudo apt-get install build-essential libxml2-dev libgeos-dev libpq-dev libbz2-dev proj
After that, follow the from source instructions.
Fedora
Fetch the dependencies:
yum install geos-devel proj-devel postgresql-devel libxml2-devel bzip2-devel gcc-c++Then follow the from source instructions.
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
Windows XP
You can find a fairly up-to-date version from April 9, 2010 for Windows at http://tile.openstreetmap.org/osm2pgsql.zip This version supports also the new 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.
- Extract the zip to a new directory on your local disk
- Add the new directory path to your system variable path ([1])
- log out and back in so the new variable is active
- Open up command prompt ie... run
- Change directories so that you are located in the extracted osm2pgsql directory
- now run the > osm2pgsql -h this will show you the help
- Example prompt> osm2pgsql -c -d gis -U postgres -W -H localhost -P 5432 N:\Geo_data\OpenStreetMap\OSM_xml\yourOSM_filename.bz2
The version from http://artem.dev.openstreetmap.org/files/ is a bit out-of-date (no relations import)
Mac OS X
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:
svn export http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/
You will need the 'autoconf' command. On Debian (?) and Ubuntu, install the package:
sudo apt-get install autoconf
On Fedora:
yum install autoconf
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:
makeIf 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, you cannot import it. The main mapnik map renders coastlines from shapefiles so it does not need them. If you want to render OSM coastlines directly, you need to map them to a different tag before import.
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.
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
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.
Osmosis - can also do import of osm file to postgres DB with postgis extension
Benchmarks
Please see Osm2pgsql 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.
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 with
$ psql -d gis -f /usr/share/postgresql/8.4/contrib/hstore.sql
- 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
