Osm2pgsql/schema

From OpenStreetMap Wiki
Jump to: navigation, search
Available languages
English 日本語 русский

This page describes the structure of the PostgreSQL database produced by osm2pgsql. The information is mostly reverse-engineered from an osm2pgsql script by Stanton 22:37, 26 January 2011 (UTC)

Tables Created

Processed Data

These tables hold the data which Mapnik uses for rendering:

  • planet_osm_line: contains all imported ways
  • planet_osm_point: contains all imported nodes with tags
  • planet_osm_polygon: contains all imported polygons. Relations seem to be resolved for that.
  • planet_osm_roads: contains a subset of `planet_osm_line` suitable for rendering at low zoom levels. `planet_osm_line` contains too many elements to render on overview maps. Selection is based on certain tags being set (FIXME: highway? railway? other?)

Each table has a way column containing the geometry for the object in the chosen projection. Two indices are created for each table: one for the way column and one for the osm_id column. Geometry uses coordinates in the EPSG:900913 AKA G00GlE projection and can be easily used in e.g. OpenLayers based JavaScript.

Notice that relations are not imported directly. Ways which are members of relations are imported in a special manner (see description for planet_osm_line), but there is no easy way to establish a relationship between a relation and its members, or to get tags associated with a relation (unless they have ways as members).

planet_osm_line

This table contains all non-closed ways which were imported. Additional rows are created for ways which are members of type=route relations:

  • For each relation of type=route, its ways are concatenated into "chunks" of consecutive ways
  • One row is created for each of these chunks (one or more for each relation which has members of type way)
  • The osm_id is the negative ID of the relation
  • The tag columns are filled with the tags of the relation

Since there may be multiple chunks per relation, negative IDs are not necessarily unique.

Column Type Description
osm_id ID in OSM
tag as specified in style file One column for each tag specified in the style file along with the line flag, contains the tag value
z_order Z order (if specified in style file), calculated automatically
way_area Area (if specified in the style file), calculated automatically
way LINESTRING Way geometry (coordinates of all points)

planet_osm_point

This table contains all nodes with tags which were imported. Nodes without tags (as those whose only purpose is to define the position of a way) are not imported.

Note that the mechanism of creating additional rows for each relation membership, with the tags of the relation, does not apply to nodes. That is, with the current scheme, there is no way to get parent relation data for a given node.

Column Type Description
osm_id ID in OSM
tag as specified in style file One column for each tag specified in the style file along with the line flag, contains the tag value
z_order
way POINT Way geometry (coordinates)

Usage example: list all the cities together with their X and Y coordinates:

select name, place, ST_XMin(way), ST_YMin(way) 
    from planet_osm_point where place='city' order by name;
-
        name         | place |     st_xmin      |     st_ymin      
---------------------+-------+------------------+------------------
 Aachen              | city  | 677005.568925713 | 6581465.51491323
 Bielefeld           | city  | 949707.775300959 | 6803595.76136763
 Bochum              | city  | 803356.018491145 | 6707233.31587142
 Bonn                | city  | 790360.302837211 | 6574460.70904846
 ...

Coordinates returned are by default in the 900913 (G00GlE) projection and can be easily used in e.g. OpenLayers based JavaScript. `min` or `max` does not matter in this case because nodes have no size.

planet_osm_polygon

This table contains all polygons (closed ways) which were imported. For polygons which are members in one or more relations, multiple rows will be created: one with the polygon's own ID and tags, and one more for each relation membership. These additional rows contain the tags of the relation and the negative ID of the relation in the osm_id column (since a single relation may contain multiple polygons, negative IDs are not necessarily unique).

Column Type Description
osm_id ID in OSM
tag as specified in style file One column for each tag specified in the style file along with the line flag, contains the tag value
z_order Z order (if specified in style file), calculated automatically
way_area Area (if specified in the style file), calculated automatically
way GEOMETRY Way geometry (coordinates of all points)

planet_osm_roads

As with planet_osm_ways, additional rows will be created for each relation membership in the manner described there.

Column Type Description
osm_id ID in OSM
tag as specified in style file One column for each tag specified in the style file along with the line flag, contains the tag value
z_order Z order (if specified in style file), calculated automatically
way_area Area (if specified in the style file), calculated automatically
way LINESTRING Way geometry (coordinates of all points)

Intermediate tables

I noticed these tables being present after an import in slim mode. Those table are used as temporary table to lower memory consumption and also if you need to apply minutely diffs; geometry is still in OSM format (lat/lon for nodes in the given projection (spherical mercator by default), node references for ways) and only minimal conversion from OSM format has taken place.

  • planet_osm_nodes: imported nodes in raw format
  • planet_osm_rels: imported relations in raw format
  • planet_osm_ways: imported ways in raw format

planet_osm_nodes

Column Type Description
id ID in OSM
lat projected Latitude
lon projected Longitude
tags Array of tags and values, format: {tag1, value1, tag2, value2, ...}

planet_osm_rels

Column Type Description
id ID in OSM
way_off Offset to first way id in parts
rel_off Offset to first relation id in parts
parts List of node IDs, way IDs and relation IDs in this relation.
members Array of members and roles, format: {member1, role1, member2, role2, ...}. Members are represented by a letter denoting their type plus their ID
tags Array of tags and values, format: {tag1, value1, tag2, value2, ...}
pending Is an update to this row pending? Set to "t" during osmosis "minutely" update then to "f" when complete. Such updates should occur in a transaction so finding a row with "t" set should be rare.

planet_osm_ways

Column Type Description
id ID in OSM
nodes Array containing the IDs of the way's nodes
tags Array of tags and values, format: {tag1, value1, tag2, value2, ...}
pending