Openstreetmap Databases

This page contains historical information. It may be outdated or unreliable.

With OpenStreetMap, there are actually two possible types of PostgreSQL databases:

OpenStreetMap API database

The OpenStreetMap API database is the main database that the site runs on, interfacing through http://www.openstreetmap.org (rails port) or via the API. The API handles data in an xml format (either .osm or .osmChange for diffs), and the PostgreSQL database does not need PostGIS.

Setup process

Openstreetmap uses a Ruby-base process described in the Rails port instructions and database README. This process makes it possible to upgrade API DB from older version to the newer one.

Pre-requisites:

btree_gist.so from postgresql-contrib installed on the server
btree_gist.sql from postgresql-contrib loaded into the database
Three functions added to the PostgreSQL (using libpgosm.so):

CREATE FUNCTION maptile_for_point(int8, int8, int4) RETURNS int4
  AS '</home/saper/rails_port/db/functions/libpgosm', 'maptile_for_point'
  LANGUAGE C STRICT;

CREATE FUNCTION tile_for_point(int4, int4) RETURNS int8
  AS '</home/saper/rails_port/db/functions/libpgosm', 'tile_for_point'
  LANGUAGE C STRICT;

CREATE FUNCTION xid_to_int4(xid) RETURNS int4
  AS '/home/saper/rails_port/db/functions/libpgosm', 'xid_to_int4'
  LANGUAGE C IMMUTABLE STRICT;

Full OSM Database Structure

API 0.6 database is capable to store complete OpenstreetMap database. This database is capable of storing history and change descriptions. This database is used to answer OSM API queries and render OSM maps. Ruby scripts provide a web fronted to the database in the same way as the http://api.openstreetmap.org/ site does.

API 0.6 structure is being constantly updated. The schema_migrations table stores "database migration scripts" (database revisions) that had been applied to the database.

API 0.6 structure apidb06-pgsql-latest.sql contains database schema with revision up to the number 25, at the point when api 0.6 became default in the trunk (r14597) in April 2009.

Osmosis keeps a newer snapshot of api 0.6 database structure, up to revision 39. This should be usable with the trunk osmosis.

As of On Mon Nov 23 23:06:07 UTC 2009, Openstreetmap database has revisions up to 47 defined and installed. Current list is available in the OSM SVN as the set of migration scripts written in Ruby.

API 0.6 database (revision 39) contains 33 tables:

acls
changeset_tags
changesets
countries
current_node_tags
current_nodes
current_relation_members
current_relation_tags
current_relations
current_way_nodes
current_way_tags
current_ways
diary_comments
diary_entries
friends
gps_points
gpx_file_tags
gpx_files
languages
messages
node_tags
nodes
relation_members
relation_tags
relations
schema_migrations
sessions
user_preferences
user_tokens
users
way_nodes
way_tags
ways

current_* are storing current data, while their counterparts without current_ prefix (nodes, node_tags, relations, relation_tags, relation_members, way, way_tags, way_nodes) are supposed to contain historical data as well.

As of On Mon Nov 23 23:06:07 UTC 2009, there is no dump with full history available so historical information will not be populated by osmosis.

This schema is used by osmosis when --read-apidb, --write-apidb options are used.

Simple schema for Osmosis

Osmosis is able to use a so-called "simple" schema can be used for holding snapshots of the OpenStreetMap database, without the full history. Replication is possible with this schema. This schema requires PostGIS extensions to be installed. (pre-requisites)

Version 4 of this schema is available from Osmosis SVN.

This schema is used by osmosis when --read-pgsql, --write-pgsql options are used.

The following tables are created as of version 4:

actions (optional)
schema_info
users
nodes
node_tags
ways
way_nodes
way_tags
relations
relation_members
relation_tags

Import planet with Osmosis

Once the database is setup with a schema, the .osm planet (or other .osm file), obtained from http://planet.openstreetmap.org or a mirror, can be imported using osmosis (a utility program, written in Java).

Osmosis accepts as input and produces output in API DB format, simple database format, .osm XML files etc. See Detailed Usage and Osmosis Examples.

Mapnik database

The Mapnik database contains OpenStreetMap data in a more (GIS) standardized format, which is used by Mapnik to render map tiles. This uses the PostGIS extension, and the data is filtered to include just the features needed for rendering. The database is loaded using osm2pgsql, rather than osmosis.

Pre-requisites:

btree_gist.so from postgresql-contrib installed on the server
_int.so from postgresql-contrib installed on the server
btree_gist.sql from postgresql-contrib loaded into the database
_int.sql from postgresql-contrib loaded into the database
PostGIS extension installed (requirements)
PostGIS scripts loaded into the database:
1. lwpostgis.sql
2. spatial_ref_sys.sql
3. postgis_comments.sql

Create Mapnik database

Note: the location of the sql files may vary from the instructions below, depending on server configuration.

createuser openstreetmap -s -P
createdb -E UTF8 -O openstreetmap osm
createlang plpgsql osm
psql -d osm -f lwpostgis.sql
psql -d osm -f spatial_ref_sys.sql
psql -d osm -f postgis_comments.sql
psql -d osm < /usr/local/pgsql/share/contrib/btree_gist.sql
psql -d osm < /usr/local/pgsql/share/contrib/_int.sql

Using osm2pgsql, with a log file to report status as the import is running:

osm2pgsql -s -U openstreetmap -d gis /home/aude/osm/planet-090729.osm.bz2 2>osmstatus.log

Mapnik database structure

Osm2pgsql creates simple structure with four tables only (prefix planet_osm can be changed):

planet_osm_point
planet_osm_line
planet_osm_roads
planet_osm_polygon