Toolserver:GeoShape

Wikimedia's GeoShape as I just called it, following the Query-to-Map-idea, will be a tool to display Wikipedia tags of the Openstreetmap Database. It should illustrate Wikipedia articles.

Frontend Examples

 * 1) Bürkliplatz-Shape for Bürkliplatz article (static)
 * 2) Mariahilf in OpenStreetBrowser
 * 3) Fossgis2011-WP-GEO Plans of the subject

Thomas' bicycle-map
uses Vector rendering of toolserver
 * 1) http://osm.t-i.ch/bicycle/map/
 * 2) https://jira.toolserver.org/browse/TS-1073

Relation Analyzer for OSM
by Adrian uses Java+SpringFramework
 * 1) http://ra.osmsurround.org/index
 * 2) GNU Affero General Public License Source

PostGIS Terminal
Similar in display and db but not storing shapes in db. uses EOSMDBOne / Osm2pgsql, PGSql, Openlayers. Programming in JavaScript, PHP see unten
 * 1) http://labs.geometa.info/postgisterminal/

OSM Inspector
by Geofabrik Tools, background not public

OpenStreetBrowser 2.0
by Stephan P. data from original .osm is processed by osmosis (--write-pgsql-dump to $ROOT_PATH/data/) to get the dump which can be loaded into osmosis-db (pgsql_simple_load.sql), the style-xml files(?) are created and with the dumps? rendered to get tiles (probably) and site-specific categorydata.
 * 1) http://wiki.openstreetmap.org/wiki/OpenStreetBrowser
 * 2) http://www.sotm-eu.org/slides/29_StephanPlepelits_OpenStreetBrowser2.pdf
 * 3) Example: Mariahilf in OSB  (rel_ Nummer in url ist osm kompatibel)
 * 4) GNU Affero General Public License (AGPLv3) Source
 * usesPostgreSQL 9.0 + PostGIS 1.5,
 * osmosis (pgsql_simple_schema) for analyse and osm2pgsql for rendering [DOCU file]
 * Mapnik 0.7.1/mod_tile/renderd, Cascadenik (not Tirex)
 * OpenLayers 2.9
 * Osmosis 1.5.1 for updating database + stored procedure OsmosisUpdate

Tables
osm_point osm_id               text                 node_65 osm_tags             hstore osm_way              POINT (900913) osm_line (without osm_polygon-s) osm_id               text                 way_13 osm_tags             hstore osm_way              LINESTRING (900913) osm_rel (without osm_polygon-s) osm_id               text                 rel_72 osm_tags             hstore osm_way              GEOMETRY (900913) member_ids           text[]               ['node_24','way_26','rel_27','error_29'] member_roles         text[]               ['from','to','via'] osm_polygon osm_id               text                 way_13   rel_77 rel_id               text                 NULL     rel_77 osm_tags             hstore               ..       .. osm_way              GEOMETRY (900913)    ..       .. member_ids           text[]                        ['node_4','way_6','rel_7','error_9'] member_roles         text[]                        ['outer','inner'] ___ Licence of this text: GNU Affero General Public License (AGPLv3) (see above)
 * 1) 02_create_tables.sql AGPLv3 Source

Sql Functions
01_tables_functions.sql: way_get_geom            (wayid)        RETURNS ways(4326)        --without using ways.linestring rel_get_geom            (relid, dummy) RETURNS nodes_and_ways(4326)   --no sub relations browsed node_assemble_tags      (nodeid) RETURNS hstore way_assemble_tags       (wayid) RETURNS hstore rel_assemble_tags       (relid) RETURNS hstore assemble_point          (nodeid) RETURNS boolean     >osm_point assemble_line           (wayid) RETURNS boolean      >osm_line assemble_rel            (relid) RETURNS boolean      >osm_rel assemble_polygon        (wayid) RETURNS boolean      >osm_polygon assemble_multipolygon   (relid) RETURNS boolean      >osm_polygon  ..(900913) 01_multipolygon.sql make_multipolygon       (way[]) returns rings                  --helper for build_multipolygon build_multipolygon      (outer_way[], inner_way[]) returns multipolygon 02_update_tables.sql osmosisUpdate            RETURNS void                         --called by Osmosis ___ Licence of this text: GNU Affero General Public License (AGPLv3) (see above)

Multipolygon processing
OpenStreetBrowser uses PostGIS functions in a plpgsql script for creating the derivate tables that OpenStreetBrowser is based on, including the table osm_polygon. The inner and outer roles have to be tagged as such. The 'seperated polygons' situation should work, with holes in all of the islands. Todo: The 'connected-holes' and 'connected polygons' situation? Todo: The 'holes in holes' situation? Algorithm:
 * 1) 01_multipolygon.sql AGPLv3 Source
 * 2) 01_tables_functions.sql AGPLv3 Source
 * Find all ways tagged with 'outer' role for a multipolygon-relation
 * Ring Assignment:
 * Take all closed ways aside
 * Merge the rest with ST_LineMerge and again, take all closed ways aside
 * Collect the closed ways (rings) with ST_Collect
 * Find all ways tagged with 'inner' role
 * Ring Assignment as above
 * Substract the inner way collection from outer way collection with ST_Difference

Nested Relations
Openstreetbowser currently doesnt support sub-relations.01_tables_functions.sql --geom_rels:=..

Updating
by the osmosisUpdate stored procedure
 * 1) http://gitorious.org/openstreetbrowser/openstreetbrowser/blobs/master/src/sql/02_update_tables.sql

Lonvia

 * 1) http://dev.lonvia.de/trac/wiki/OsgendeFramework
 * 2) http://dev.lonvia.de/trac/browser/README which is in the GNU GENERAL PUBLIC LICENSE V.3 Source

uses Osgende Python-framework In Osgende each derived table is implemented by one class. If you know the Django web frame work then you are familiar with the concept. By convention, each class should implement three functions: construct (to create a new table), create (to create data from scratch), update (to bring data up to date). Osgende provides base classes for common cases, like collecting nodes with a certain tagging or creating polygons.
 * Osmosis, PGSql, PostGIS, Mapnik, OpenLayers. Osmosis Data in Snapshot Schema, own derivate tables may have been (once?) in Simple Schema but look like snapshot after all (no highway column and thelike).
 * Powerful postprocessing for Small derivative tables using ..full programming language

Osgende uses psycopg aka Python bindings for osmosis and Shapely aka Python bindings for the geos library.

Tables

 * 1) http://dev.lonvia.de/trac/browser/tools/dumper/schema_setup.sql
 * 2) http://dev.lonvia.de/trac/wiki/HikingMap then locally ./db/src/makedb.py

makedb.py, conf.py guideposts id bigint, name text, ele text, POINT"900913" networknodes --network nodes (as used in the Netherlands) rwn_ref routes name    text        - the default name, generally taken from name-tag however, if the name is entirely in non-latin symbols, name:en is prefered if existing intnames hstore     - collection of translated names symbol  text        - unique name of the computed shield to use country char(3)     - coutry the route is mainly in (in terms of                           numbers of sections, TODO check this heuristic) network varchar(2)  - special network it belongs to, may affect rendering, default is '' level   int         - importance in network with 0 being the most important top     boolean     - if false then route constitutes only a subsection of    GEOMETRY "900913"      another route, check hierarchy table for potential parents segments  --Segments are the basic way system of the network. id        bigserial nodes     bigint[], country   type? ways      bigint[], rels      bigint[] LINESTRING "900913" countries --country polygons id bigint, code varchar(2), GEOMETRY"900913" defstyle -- style information for the default style changed_objects -- changed geometries ? action char(1), GEOMETRY"900913" osmosis_update_proc.sql, models.py  node_changeset (id bigint, action char); way_changeset (id bigint, action char); relation_changeset (id bigint, action char) hierarchy               --  hierarchy.py, relation relations parent    bigint, child     bigint, depth     int relation_hierarchy       --relationModels.py, Django     --depth < 10 parent       The parent relation child        A sub-ordinate relation of parent a relation always has itself as a child with depth 0 depth        Number of levels the child relations is below the parent level        The level row of the parent relation. (see below) top          A boolean stating, whether the parent is a top-level relation. __ Licence of this text: GNU GENERAL PUBLIC LICENSE V.3 (see Lonvia Source above)

Multipolygon processing

 * 1) http://dev.lonvia.de/trac/browser/osgende/relations/polygons.py

Nested Relations

 * 1) http://dev.lonvia.de/trac/browser/osgende/relations/hierarchy.py

Updating
by the osmosisUpdate stored procedure osmosis_update_proc.sql: missing: mark relations whose nodes have been changed?
 * 1) http://dev.lonvia.de/trac/browser/contrib/osmosis_update_proc.sql

GeoShape on Ptolemy

 * 1) http://toolserver.org/~kolossos/openlayers/kml-on-ol.php?lang=de&uselang=de&zoom=15&lat=51.05811&lon=13.74135
 * 2) http://a.www.toolserver.org/tiles/osm/4/3/3.png
 * 3) Ungarischer style: http://toolserver.org/~osm/locale/hu.html
 * 4) doku: https://wiki.toolserver.org/view/OpenStreetMap
 * 5) DOKU outdated mentioning Ptolemy: http://meta.wikimedia.org/wiki/OpenStreetMap

An OSM Multi-maintainer project. Toolserver Ptolemy has a Solaris OS (?).

data goes over a front-end proxy (a.www.toolserver.org, b.www.toolserver.org..)
 * uses a PGSql db with PostGIS and hstore
 * osm2pgsql to fill up the db
 * Tirex with Mapnik-Backend
 * finally OpenLayers

Nested Relations
Processing of trees is a graph problem, and processing this kind of relation tree is a directed graph problem. The data is a subset of the relation_members table (the relation to relation part) which is an edge list and can be transformed to an adjacency_list with SQL. The first thing to do is building the relation tree, for example with a DFS deep tree? search. This way, also invalid connections can be found, like forward edges, back edges and cross edges that may build closed loops.
 * 1) Osmwiki Super-Relation/Implementation

Models of trees in relational databases from Tropashko, Troels' links, Bill Karwin's examples, Quassnoi's pgsql examples

simple adjacency list
loop-prone query cycle detection done with recursive query with path as an array of id.s checking each new node against that array, indicating a cycle in a different column in case of sucess.
 * arbitrary graph representation, unstructured
 * local connection between immediate neighbors aka parent- and child-nodes
 * directed graph, loops possible
 * hierarchical queries with SQL extensions like WITH RECURSIVE

transitive closure tables
(adjacency list)
 * all superficious connections from each parent to each child are added
 * generally for directed acyclic graph: no forward edges, back edges and cross edges = no loops Dong et al: Maintaining Transitive Closure of Graphs in SQL, PDF
 * hierarchical queries with simple SQL possible

path enumeration
aka Materialized Path
 * tree representation, encoding system, structured
 * connections from each node to all precessors
 * directed acyclic graph: no forward edges, back edges and cross edges = no loops
 * hierarchical queries with simple SQL possible

Nested Sets
see
 * intervals of integers coding to model tree
 * numbering is is volatile, inserting a record means updating half of other records
 * easy to convert from adjacency list by depth-first_search with detecting forward edges, back edges and cross edges
 * directed acyclic graph: no forward edges, back edges and cross edges = no loops

As an alternative, programming language modules can be used. They are usually not linked to stored procedures for incremental db update.
 * Django treebeard

others
Imposm kann auch Daten in die Datenbank schaufeln, hab ich aber keine Erfahrung mit. Osm2postgresql ebenso.
 * 1) http://forum.openstreetmap.org/viewtopic.php?id=12182
 * 2) http://www.mail-archive.com/talk-de@openstreetmap.org/msg85284.html
 * 1) http://wiki.openstreetmap.org/wiki/Osm2postgresql

Osmium is not originally a db-builder tool but works with spatialite database (files?). It can also be used to filter osm files before adding them to the db with another tool. Though it cannot work with change files (.osc) yet.

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

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

Connect

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 or the action-column XXlonvia specific can be used to update derivative tables, for example by customising the osmosisUpdate stored procedure. http://www.mail-archive.com/dev@openstreetmap.org/msg04645.html , http://gis.638310.n2.nabble.com/Osmosis-questions-about-applying-changesets-td5811546.html: 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.

Osm2pgsql tables

 * planet_osm_point
 * planet_osm_line
 * planet_osm_polygon
 * planet_osm_roads (auch in planet_osm_line)

and intermediate tables
 * planet_osm_nodes
 * planet_osm_ways
 * planet_osm_rels

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

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

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

Updateable, but just for the built-in tables, not for derivate tables, originally. I just found temporary pending column in planet_osm_rels table (virtually undocumented).
 * changemanagement for derivate tables

Openstreetmap-Server inklusive einer osm2pgsql Datenbank mit hstore Spalte in einer Linux-Umgebung einrichten und nachher mit Tirex (wie Ptolemy und auch OSM Dev-Server): 
 * Setup

Comparasion osm2pgsql and osmosis
Both of them can do incremental updates. Both Support for hstore (not stated: the war).
 * 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

Tables there, von EOSMDBOne, done with Osm2pgsql: 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

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ü%' 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

tbc.