Toolserver:Comparison of osm2pgsql and osmosis for GeoShape

This page was moved from the Toolserver wiki.
Toolserver has been replaced by Toolforge. As such, the instructions here may no longer work, but may still be of historical interest.
Please help by updating examples, links, template links, etc. If a page is still relevant, move it to a normal title and leave a redirect.

Two tools to build a database of Openstreetmap objects (ways, areas, points, relations) are discussed to be used in the GeoShape project.

Osmosis

1. http://wiki.openstreetmap.org/wiki/Osmosis
2. http://wiki.openstreetmap.org/wiki/Osmosis/PostGIS_Setup (Table layout at the bottom)
3. Public domain Source: /core/src
4. Public domain Source: /package/script, with pgsnapshot_and_pgsimple.txt, a comparasion

Data can be in in PostGIS Snapshot Schema (pg_snapsnot schema), or "simple" (which is obsolet according to wambacher). options (Snapshot Schema ):

• pgsnapshot_schema_0.6.sql - Builds the minimal schema.
• pgsnapshot_schema_0.6_action.sql - Adds the optional "action" table which allows derivative tables to be kept up to date when diffs are applied.
• pgsnapshot_schema_0.6_bbox.sql - Adds the optional bbox column to the way table.
• pgsnapshot_schema_0.6_linestring.sql - Adds the optional linestring column to the way table. [1]

Osmosis tables

1. https://github.com/brettch/osmosis/blob/master/package/script/pgsnapshot_schema_0.6_action.sql
2. https://github.com/brettch/osmosis/blob/master/package/script/pgsimple_schema_0.6.sql
3. ChangesetAction.java, ActionDataType.java

nodes
       id  version  user_id  tstamp  changeset_id  tags  geom(4326)
ways
       id  version  user_id  tstamp  changeset_id  tags  nodes[]  bbox(4326)  linestring(4326)
way_nodes
       way_id  node_id  sequence_id 
relations
relation_members
 relation_id      bigint 
 member_id        bigint 
 member_type      character(1)       
 member_role      text 
 sequence_id      int 
actions
 data_type        character(1)       U  N  W  R  --USER NODE WAY or RELATION
 action           character(1)       N  C  M  D  --NONE CREATE MODIFY or DELETE  
 id               bigint 
node_tags, way_tags, relation_tags            --..simple schema only
geometry_colums,  spatial_ref_sys,  users,  schema_info

Rows bbox, linestring, table actions: optional. Add an action table for the purpose of capturing all actions applied to a database. The table is populated during application of a changeset, then osmosisUpdate is called, then the table is cleared all within a single database transaction. The contents of this table can be used to update derivative tables by customising the osmosisUpdate stored procedure.

I have a table with points (nodes) and another table ways. Every single way is defined through two or more nodes. The two tables are connected through way_nodes. [2],[3]

select * from way_nodes join nodes on way_nodes.node_id=nodes.id
  where way_nodes.way_id=id order by sequence_id

Or connect nodes and ways table with ways.nodes array: [4] pg 26

select * from nodes join ways on nodes.id = any(ways.nodes)
  where ways.tags->’name’=’Reichenhainer Straße’

Osmosis Features

Polygons

Polygons must eventually be created, but there might be tools.

Filter

Filtering out nodes and stuff at while osmosis transfer runs, is provided. Osmosis has "--node-key","--node-key-value", "--way-key", "--way-key-value" , "--tag-filter" which all filter nodes or stuff according to tags, and there is --used-node that filters nodes according to way? parent. Filtering according to relation parent is not implemented.[5]

Changemanagement for derivate tables

The contents of actions-table of simple schema can be used to update derivative tables, for example by customising the osmosisUpdate stored procedure. [6], [7]: action column is:

• "A" - Add
• "M" - Modify
• "D" - Delete

Osmosis Tools

1. Osm relation-multipolygon-assembling

Osm2pgsql

1. http://wiki.openstreetmap.org/wiki/Osm2pgsql

Osm2pgsql is filtering the information usually for Mapnik rendering and also creates tables to easily do that.

Osm2pgsql tables

1. http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema

 planet_osm_point
   osm_id
   amenity       shop     as specified in style file
   z_order
   way       POINT 
 planet_osm_line   
   osm_id
   highway       path     as specified in style file 
   z_order                Z order (if specified in style file), calculated automatically
   way_area               Area (if specified in the style file), calculated automatically
   way       LINESTRING 
 planet_osm_polygon
 planet_osm_roads (part of planet_osm_line)

and intermediate tables     
 planet_osm_nodes
   id
   lat
   lon
   tags          [amenity, shop, area, yes]
 planet_osm_ways
 planet_osm_rels
   id
   way_off       0
   rel_off       2
   parts         1234, 2345
   members       w1234,outer,w2345,inner
   tags          natural, water, type, multipolygon
   pending       f

Osm2pgsql features

Relationen

Osm2pgsql adds additional ways for relations with negative relation id and relation tags. Relations that have just points are not supported.

Eingangsfilter

default.style filters according to tag/patterns. [8]

Split

If the line is very long then it gets split every 100km. [9]

changemanagement for derivate tables

Updateable, but only the built-in tables are updated. When this is finished, the information which ways or areas actually are different since last update is lost. So, its not very convenient for derivate tables, originally. I just found the temporary pending column in planet_osm_rels table (virtually undocumented).

Comparasion osm2pgsql and osmosis

Both of them can do incremental updates. Both support hstore.

• Osmosis does no processing thats useful for rendering, that is, it has no polygons.
• On the other side it has more details, eg. also invalid keys or rarely-used keys, (I expect) and the structure is preserved, eg. from super-relations.
• osm2pgsql's planet_osm_roads table is just for rendering
• osm2pgsql's changemanagement for derivate tables would probably need a workaround [10],[11]

Sql Examples

on cool PostGIS Terminal

Osm2pgsql-tables on postgisterminal for reference:

osm_point,
osm_line,
osm_polygon,
osm_nodes,
osm_ways,
osm_rels,
osm_poi,
osm_poi_v,
osm_all,
osm_all_v,
geography_columns,
geometry_columns,
spatial_ref_sys

the examples:

SELECT ST_AsText(way) AS geom, tags->'wikipedia' AS label
FROM osm_line
WHERE exist(tags,'wikipedia')
LIMIT 1

SELECT ST_AsText(way) AS geom, tags->'wikipedia' AS label
FROM osm_line
WHERE exist(tags,'wikipedia')
AND tags->'wikipedia' LIKE 'de:Bürkliplatz%'
LIMIT 1

SELECT ST_AsText(ST_Collect(way)) AS geom, tags->'wikipedia' AS label
FROM osm_line
WHERE exist(tags,'wikipedia')
GROUP BY tags->'wikipedia'
LIMIT 5