Osm2pgsql/schema
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)
Contents |
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, 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 | Latitude | |
| lon | 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 |
