Skip to content
This repository

OpenStreetMap data to PostgreSQL converter

branch: master

Merge pull request #104 from jeffjanes/master

Fix comma in the prepare for delete_node
latest commit f7ebc1d356
apmon apmon authored
Octocat-spinner-32 docs Typos, punctuation consistency, etc. on manpages. April 29, 2013
Octocat-spinner-32 geos-fallback Add a few header files for people compiling under Ubuntu. geos-dev January 31, 2011
Octocat-spinner-32 m4 Remove non-necessary echo in m4/ax_lib_geos.m4 November 16, 2013
Octocat-spinner-32 protobuf * improved configure setup (including automake and libtool) November 06, 2010
Octocat-spinner-32 tests Fix summery statistics in test after the change of stylesheet caused … September 21, 2013
Octocat-spinner-32 .gitignore Add osm2pgsql binary to .gitignore September 08, 2013
Octocat-spinner-32 900913.sql Add postgis definition for the 900913 spherical mercator projection w… August 12, 2008
Octocat-spinner-32 AUTHORS Add a few more authors to the AUTHORS file who seem to have done a lo… April 06, 2013
Octocat-spinner-32 COPYING * improved configure setup (including automake and libtool) November 06, 2010
Octocat-spinner-32 ChangeLog Revert "Remove the old SVN Changelog" October 03, 2013
Octocat-spinner-32 Makefile.am Remove references to legacy/Makefile July 21, 2013
Octocat-spinner-32 NEWS started a NEWS file November 07, 2010
Octocat-spinner-32 README Merge pull request #95 from pnorman/patch-2 December 23, 2013
Octocat-spinner-32 README_lua.md Create README_lua.md September 20, 2013
Octocat-spinner-32 TODO remove outdated gazetteer V1 code, June 12, 2012
Octocat-spinner-32 UTF8sanitizer.c [osm2pgsql] Free memory on error path December 13, 2012
Octocat-spinner-32 autogen.sh * improved configure setup (including automake and libtool) November 06, 2010
Octocat-spinner-32 binarysearcharray.c follow to r28957 - handle -Wdeclaration-after-statement warnings as I… November 21, 2012
Octocat-spinner-32 binarysearcharray.h Use async IO in persistent_cache_nodes_get_list July 26, 2012
Octocat-spinner-32 build_geometry.cpp Untabify changes from previous commit. April 20, 2013
Octocat-spinner-32 build_geometry.h [osm2pgsql] Add an --exclude-invalid-polygon option September 16, 2012
Octocat-spinner-32 configure.ac Increment version to 0.85.0 to development version October 19, 2013
Octocat-spinner-32 cygpkg.sh default.style was missing from cygwin package. August 19, 2013
Octocat-spinner-32 default.style Add abandoned: tags to phstore list September 27, 2013
Octocat-spinner-32 empty.style Add abandoned: tags to phstore list September 27, 2013
Octocat-spinner-32 expire-tiles.c [osm2pgsql] More cleanup work to support compiler flags -ansi -pedantic November 20, 2012
Octocat-spinner-32 expire-tiles.h [osm2pgsql] Fix expiry generation with --number-processes > 1 December 18, 2011
Octocat-spinner-32 input.c [osm2pgsql] Wrong use of sizeof in input.c December 15, 2012
Octocat-spinner-32 input.h osm2pgsql 0.07: Make UTF8sanitize optional since it is generally no l… September 07, 2007
Octocat-spinner-32 install-postgis-osm-db.sh [osm2pgsql] - small fix to install-postgis-osm-db.sh script December 18, 2011
Octocat-spinner-32 install-postgis-osm-user.sh [osm2pgsql] Update debian package scripts to handle postgis-9.1 October 15, 2011
Octocat-spinner-32 keyvals.c [osm2pgsql] More cleanup work to support compiler flags -ansi -pedantic November 20, 2012
Octocat-spinner-32 keyvals.h Fix bug in hstore rework. April 04, 2011
Octocat-spinner-32 mapnik-osm-updater.sh - changed path for geoinfo.db in mapnik-osm-updater.sh March 22, 2009
Octocat-spinner-32 middle-pgsql.c Fix comma in the prepare for delete_node November 22, 2013
Octocat-spinner-32 middle-pgsql.h Add a caching level to the slim-mode with configurable size, so it ac… April 12, 2008
Octocat-spinner-32 middle-ram.c Remove unused variables. October 02, 2013
Octocat-spinner-32 middle-ram.h Add a caching level to the slim-mode with configurable size, so it ac… April 12, 2008
Octocat-spinner-32 middle.h [osm2pgsql] More cleanup work to support compiler flags -ansi -pedantic November 20, 2012
Octocat-spinner-32 node-persistent-cache-reader.c [osm2pgsql] fix memory leak in node-persistent-cache-reader December 14, 2012
Octocat-spinner-32 node-persistent-cache.c Replace erroneous assignment with comparison. October 02, 2013
Octocat-spinner-32 node-persistent-cache.h possix_fallocate does not set errno (fixes #55) August 03, 2013
Octocat-spinner-32 node-ram-cache.c Fix a segfault when node ram cache is set to 0 September 08, 2013
Octocat-spinner-32 node-ram-cache.h Round results rather than truncate the in the DOUBLE_TO_FIX macro September 08, 2013
Octocat-spinner-32 osm2pgsql-svn.sh osm2pgsql: Remove hard coded maximum IDs. Support negative IDs. Add R… August 12, 2007
Octocat-spinner-32 osm2pgsql.c Warn and exit if both -j and -k are passed as these are exclusive opt… October 06, 2013
Octocat-spinner-32 osm2pgsql.spec.in * improved configure setup (including automake and libtool) November 06, 2010
Octocat-spinner-32 osmtypes.h [osm2pgsql] More cleanup work to support compiler flags -ansi -pedantic November 20, 2012
Octocat-spinner-32 output-gazetteer.c add addr:suburb and remove duplicate addr:country February 13, 2014
Octocat-spinner-32 output-gazetteer.h Initial work on generating a gazetteer database. April 30, 2009
Octocat-spinner-32 output-null.c Branch of osm2pgsql that does not use intarray. Unsure if it works May 20, 2011
Octocat-spinner-32 output-null.h Use bzip2 interface directly, rather than through the zlib compatibil… April 30, 2009
Octocat-spinner-32 output-pgsql.c Merge pull request #91 from RavuAlHemio/unwarn October 03, 2013
Octocat-spinner-32 output-pgsql.h Add the ability to perform tag tansform with a lua script May 09, 2013
Octocat-spinner-32 output.h Add the ability to perform tag tansform with a lua script May 09, 2013
Octocat-spinner-32 parse-o5m.c [osm2pgsql] order of memset arguments was mixed up December 14, 2012
Octocat-spinner-32 parse-o5m.h [osm2pgsql] Add o5m support to osm2pgsql October 29, 2012
Octocat-spinner-32 parse-pbf.c Fix memory leak in extra attributes (user name). February 26, 2014
Octocat-spinner-32 parse-pbf.h * improved configure setup (including automake and libtool) November 06, 2010
Octocat-spinner-32 parse-primitive.c [osm2pgsql] Null-terminate string before passing it into strchr December 13, 2012
Octocat-spinner-32 parse-primitive.h * improved configure setup (including automake and libtool) November 06, 2010
Octocat-spinner-32 parse-xml2.c follow to r28957 - handle -Wdeclaration-after-statement warnings as I… November 21, 2012
Octocat-spinner-32 parse-xml2.h * improved configure setup (including automake and libtool) November 06, 2010
Octocat-spinner-32 pgsql.c [osm2pgsql] More cleanup work to support compiler flags -ansi -pedantic November 20, 2012
Octocat-spinner-32 pgsql.h [osm2pgsql] More cleanup work to support compiler flags -ansi -pedantic November 20, 2012
Octocat-spinner-32 rb.c osm2pgsql 0.04: Further improve handling of polygons with holes. Redu… August 19, 2007
Octocat-spinner-32 rb.h osm2pgsql 0.04: Further improve handling of polygons with holes. Redu… August 19, 2007
Octocat-spinner-32 reprojection.c [osm2pgsql] More cleanup work to support compiler flags -ansi -pedantic November 20, 2012
Octocat-spinner-32 reprojection.h Allow creation of expiry lists for 900913 tiles even if your target November 03, 2009
Octocat-spinner-32 sanitizer.h osm2pgsql - make experimental version current, move previous implemen… May 07, 2007
Octocat-spinner-32 sprompt.c Add some changes from Edgemaster for MinGW support, see #926 May 28, 2008
Octocat-spinner-32 sprompt.h osm2pgsql: Add authentication options (user, host, port, password). February 18, 2008
Octocat-spinner-32 style.lua Fix small typo (tunel to tunnel) in the lua style to make sure the z_… December 24, 2013
Octocat-spinner-32 tagtransform.c Add necessary includes in tagtransform.c. October 02, 2013
Octocat-spinner-32 tagtransform.h Declare tagtransform_shutdown in tagtransform.h. October 02, 2013
Octocat-spinner-32 text-tree.c Restructure the projection code so it can support more projections, November 20, 2007
Octocat-spinner-32 text-tree.h osm2pgsql: Allow multiple .osm files to be imported simultaneoulsy, e… September 02, 2007
Octocat-spinner-32 wildcmp.c Add shell-style wildcard match (*,?) do style. May 06, 2012
Octocat-spinner-32 wildcmp.h Add shell-style wildcard match (*,?) do style. May 06, 2012
README
osm2pgsql
=========
Converts OSM planet.osm data to a PostgreSQL / PostGIS database suitable 
for specific applications like rendering into map tiles by Mapnik
or geocoding with Nominatim.

osm2pgsql currently supports two different database schemas
1) A database schema that is optimized for ease of rendering
by Mapnik.
2) A database schema that is optimized for geocoding with Nominatim,
emphasizing the spatially hierarchical organizations of objects.

Both schemas were specifically optimized for the purpose they were
intended for and they may therefore be less suitable for other
general purpose processing. Nevertheless, the rendering schema
might be useful for other purposes as well, and has been used
for a variety of additionally purposes.


For a broader view of the whole map rendering tool chain see
http://wiki.openstreetmap.org/index.php/Mapnik 
http://wiki.openstreetmap.org/index.php/Osm2pgsql
http://wiki.openstreetmap.org/index.php/Slippy_Map

You may find that the wiki pages are more up to date than this
readme and may include answers to issues not mentioned here.

Any questions should be directed at the osm dev list
http://wiki.openstreetmap.org/index.php/Mailing_lists 

Features
========
- Converts OSM files to a PostgreSQL DB
- Conversion of tags to columns is configurable in the style file
- Able to read .gz, .bz2, .pbf and .o5m files directly
- Can apply diffs to keep the database up to data
- Support the choice of output projection
- Configurable table names
- Gazetteer back-end for Nominatim
  http://wiki.openstreetmap.org/wiki/Nominatim
- Support for hstore field type to store the complete set of tags in one database
  field if desired

Source code
===========
The latest source code is available in the OSM git repository on github
and can be downloaded as follows:

$ git clone git://github.com/openstreetmap/osm2pgsql.git

Build requirements
==================
The code is written in C and C++ and relies on the libraries
below:
- libxml2    http://xmlsoft.org/
- geos       http://geos.refractions.net/
- proj       http://www.remotesensing.org/proj/
- bzip2      http://www.bzip.org/
- zlib       http://www.zlib.net/
- PostgreSQL http://www.postgresql.org/
- PostGIS    http://postgis.refractions.net/

To make use of the database generated by this tool you will
probably also want to install:
- Mapnik from http://mapnik.org/

Building
========
Make sure you have installed the development packages for the 
libraries mentioned in the requirements section and a C and C++
compiler.

e.g. on Fedora:
# yum install geos-devel proj-devel postgresql-devel libxml2-devel bzip2-devel gcc-c++

on Debian:
# aptitude install libxml2-dev libgeos-dev libgeos++-dev libpq-dev libbz2-dev libproj-dev protobuf-c-compiler libprotobuf-c0-dev autoconf automake libtool make g++

To use lua tag transforms you need a lua interpreter and development packages installed on your system. These are normally lua5.2 and liblua5.2-dev

On most Unix-like systems the program can be compiled by
running './autogen.sh && ./configure && make'.

Operation
=========
You must create a PostgreSQL user and a database with the 
PostGIS functions enabled. This requires access as the
database administrator, normally the 'postgres' user.

PostgreSQL 9.1 and PostGIS 2.0 or later are strongly suggested 
for databases in production. It is generally best to run the 
latest released versions if possible. PostgreSQL 8.4 and PostGIS 1.5 
will work but are substantially slower. Additionally, PostGIS 2.0 
contains enhancements that increase reliability as well as add new 
features that style sheet authors can use.

The default name for this database is 'gis' but this may
be changed by using the osm2pgsql --database option.

If the <username> matches the unix user id running the import
and rendering then this allows the PostgreSQL 'ident sameuser'
authentication to be used which avoids the need to enter a
password when accessing the database. This is setup by default
on many Unix installs but does not work on Windows (due to the
lack of unix sockets).

Some example commands are given below but you may find
this wiki page has more up to data information:
http://wiki.openstreetmap.org/wiki/Mapnik/PostGIS

$ sudo -u postgres createuser <username>
$ sudo -u postgres createdb -E UTF8 -O <username> <dbname>
$ sudo -u postgres createlang plpgsql <dbname>

Adding the PostGIS extensions.
$ sudo -u postgres psql -d <dbname> -c "CREATE EXTENSION postgis;"

On older versions of PostGIS you will have to run .sql scripts. Note the location of the
files may vary.

$ sudo -u postgres psql -d <dbname> -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql
$ sudo -u postgres psql -d <dbname> -f /usr/share/postgresql/8.4/contrib/postgis-1.5/spatial_ref_sys.sql

Next we need to give the <username> access to update the postgis
meta-data tables

$ sudo -u postgres psql -d <dbname> -c "ALTER TABLE geometry_columns OWNER TO <username>"
$ sudo -u postgres psql -d <dbname> -c "ALTER TABLE spatial_ref_sys  OWNER TO <username>"

The 900913 is not normally included with PostGIS. To add it you
should run:

$ sudo psql -u postgres psql -d <dbname> -f 900913.sql

If you want to use hstore support then you will also need to enable the PostgreSQL
hstore-new extension.

$ sudo -u postgres psql -d <dbname> -c "CREATE EXTENSION hstore;"

On PostgreSQL versions before 9.1 you need to install the hstore-new extension instead

$ sudo -u postgres psql -d <dbname> -f /usr/share/postgresql/8.4/contrib/hstore-new.sql

Now you can run osm2pgsql to import the OSM data.
This will perform the following actions:

1) osm2pgsql connects to database and creates the following 4 tables
when used with the default output back-end (pgsql):
   - planet_osm_point
   - planet_osm_line
   - planet_osm_roads
   - planet_osm_polygon
The default prefix "planet_osm" can be changed with the --prefix option.

If you are using --slim mode, it will create the following additional 3 tables:
   - planet_osm_nodes
   - planet_osm_ways
   - planet_osm_rels

2) Runs a parser on the input file (typically planet-latest.osm.pbf)
 and processes the nodes, ways and relations.

3) If a node has a tag declared in the style file then it is 
 added to planet_osm_point. If it has no such tag then
 the position is noted, but not added to the database.

4) Ways are read in converted into WKT geometries by using the 
 positions of the nodes read in earlier. If the tags on the way 
 are listed in the style file then the way will be written into
 the line or roads tables.

5) If the way has one or more tags marked as 'polygon' and 
 forms a closed ring then it will be added to the planet_osm_polygon
 table.

6) The relations are parsed. Osm2pgsql has special handling for a
 limited number of types: multipolygon, route, boundary
 The code will build the appropriate geometries by referencing the
 members and outputting these into the database.

7) Indexes are added to speed up the queries by Mapnik.

Tuning PostgreSQL
=================

For an efficient operation of PostgreSQL you will need to tune the config
parameters of PostgreSQL from its default values. These are set in the
config file at /etc/postgresql/9.1/main/postgresql.conf

The values you need to set will depend on the hardware you have available,
but you will likely need to increase the values for the following parameters:

- shared_buffers
- checkpoint_segments
- work_mem
- maintenance_work_mem
- effective_cache_size


A quick note on projections
===========================

Depending on the command-line switches you can select which projection you
want the database in. You have three choices:

4326: The standard lat/long coordinates
900913: The spherical Mercator projection, used by TileCache, Google Earth etc.
3395: The legacy (broken) WGS84 Mercator projection

Depending on what you're using one or the other is appropriate. The default
Mapnik style (osm.xml) assumes that the data is stored in 900913 and this 
is the default for osm2pgsql.

Combining the -v and -h switches will tell about the exact definitions of
the projections.

In case you want to use some completely different projection there is the -E
option. It will initialize the projection as +init=epsg:<num>. This allows
you to use any projection recognized by proj4, which is useful if you want
to make a map in a different projection. These projections are usually
defined in /usr/share/proj/epsg.

Database Access Examples
========================
If you wish to access the data from the database then the
queries below should give you some hints. Note that these 
examples all use the 'latlong' projection which is not the
default.

$ psql gis
gis=> \d
               List of relations
 Schema |        Name        | Type  |  Owner
--------+--------------------+-------+----------
...
 public | planet_osm_line    | table | jburgess
 public | planet_osm_point   | table | jburgess
 public | planet_osm_polygon | table | jburgess
 public | planet_osm_roads   | table | jburgess
...

gis=> \d planet_osm_line
  Table "public.planet_osm_line"
  Column   |   Type   | Modifiers
-----------+----------+-----------
 osm_id    | integer  |
 name      | text     |
 place     | text     |
 landuse   | text     |
... [ lots of stuff deleted ] ...
 way       | geometry | not null
 z_order   | integer  | default 0


Each of the tables contains a subset of the planet.osm file representing
a particular geometry type
- Point contains nodes which have interesting tags
  e.g. place=city, name=London
  
- Line contains ways with interesting tags
  e.g. highway=motorway, ref=M25
  
- Polygon contains ways which form an enclosed area
  e.g. landuse=reservoir

The DB columns are used as follows:
- osm_id = the planet.osm ID of the node(point) or way(line,polygon)
- name, place, landuse, ... = the value of the given key, if present on
the node/way. If the tag is not present, the value is NULL. Only a
subset of all possible tags are stored in the DB. Only ones rendered in
the osm.xml are actually interesting to mapnik.
- way = PostGIS geometry describing the physical layout of the object.

Querying specific data requires knowlege of SQL and the OSM key/value
system, e.g.

gis=> select osm_id,astext(way),name from planet_osm_point where amenity='cinema' limit 5;
  osm_id  |                  astext                   |        name
----------+-------------------------------------------+--------------------
 26236284 | POINT(-79.7160836579093 43.6802306464618) |
 26206699 | POINT(51.4051989797638 35.7066045032235)  | Cinema Felestin
 26206700 | POINT(51.3994885141459 35.7058460359352)  | Cinema Asr-e Jadid
 20979630 | POINT(151.225781789807 -33.8943079539886) | Paris Cinema
 20979684 | POINT(151.226855394904 -33.8946830511095) | Hoyts
(5 rows)

Mapnik renders the data in each table by applying the rules in the
osm.xml file.


> How could I get e.g. all highways in a given bounding box?

The 'way' column contains the geo info and is the one which you need to
use in your WHERE clause. e.g.

gis=> select osm_id,highway,name from planet_osm_line where highway is not null and way && GeomFromText('POLYGON((0 52, 0.1 52, 0.1 52.1, 0 52.1, 0 52))',4326);
 
osm_id  |   highway    |       name
---------+--------------+------------------
 4273848 | unclassified |
 3977133 | trunk        | to Royston (tbc)
 4004841 | trunk        |
 4019198 | trunk        |
 4019199 | trunk        |
 4238966 | unclassified |

See the Postgis docs for details, e.g.
http://postgis.net/docs/manual-2.0/reference.html
Something went wrong with that request. Please try again.