Toolserver:Comparison of osm2pgsql and osmosis for GeoShape

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

Osmosis
Data can be in in PostGIS Snapshot Schema (pg_snapsnot schema), or "simple" (which is obsolet according to wambacher). options (Snapshot Schema ):
 * 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


 * 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.

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. ,

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

Osmosis Features
Polygons must eventually be created, but there might be tools.
 * Polygons

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.
 * Filter

The contents of actions-table of simple schema can be used to update derivative tables, for example by customising the osmosisUpdate stored procedure. , : action column is:
 * Changemanagement for derivate tables
 * "A" - Add
 * "M" - Modify
 * "D" - Delete

Osmosis Tools

 * 1) Osm relation-multipolygon-assembling

Osm2pgsql
Osm2pgsql is filtering the information usually for Mapnik rendering and also creates tables to easily do that.
 * 1) http://wiki.openstreetmap.org/wiki/Osm2pgsql

Osm2pgsql tables
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
 * 1) http://wiki.openstreetmap.org/wiki/Osm2pgsql/schema

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

default.style filters according to tag/patterns. 
 * Eingangsfilter

If the line is very long then it gets split every 100km. 
 * Split

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).
 * changemanagement for derivate tables

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 ,

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