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) Bezirk Neubau in OpenStreetBrowser

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) [hiking.lonvia.de hiking.lonvia.de]
 * 2) http://dev.lonvia.de/trac/wiki/OsgendeFramework
 * 3) http://dev.lonvia.de/trac/browser/README which is in the GNU GENERAL PUBLIC LICENSE V.3 Source

uses Osgende Python-framework
 * Osmosis, PGSql, PostGIS, Mapnik, OpenLayers. Osmosis Data in Snapshot Schema,.
 * psycopg, Shapely (Python bindings for osmosis and 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
 * 1) http://dev.lonvia.de/trac/browser/contrib/osmosis_update_proc.sql

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

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
 * 6) Fossgis2011-WP-GEO Backend Plans on the subject

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

Layout
Osm2pgsql or Osmosis database is used and updated automatically with built in procedures. Data cannot be restricted to objects with wikipedia tag, because tagged relations can have untagged objects as members. New table is: wiki_members lang            character(10)  pk    de        namespace lemma           character(255) pk    Bern      lemma member_type     character(1)   pk    N         NODE WAY or RELATION member_id       bigint         pk  action           character(1)         NULL      NULL or R (Rebuild namespaced lemma (classification))

update procedures todo..
Rebuilds the wiki_members table from scratch, that is, from the nodes, ways and relations tables of Osm2pgsql or Osmosis. Function (still missing) findwiki filters those tables to find tags like wikipedia:en=>Girard or wikipedia=>en:Girard or  wikipedia=>http://en.wikipedia.org/wiki/Ren%C3%A9_Girard#Religi.C3.B6ses_Denken  or wikipedia=>de:Girard, wikipedia:es=>Giño. DROP wiki_members; INSERT INTO wiki_members SELECT f.wikiLang, f.wikiLemma, 'N', n.member_id FROM nodes n, select findwiki(tags) AS f Is done along with updating data source tables (from Osm2pgsql). In Osm2pgsql, updating is happening 'undetected' without a workaround. The following workaround adds pgSql event handler hooks to change in altered tables:
 * rebuild
 * update prepare

trigger nodes on UPDATE BEFORE or DELETE: ALTER wiki_members SET action='R' SELECT f.wikiLang, f.wikiLemma, 'N', n.member_id FROM nodes n, select findwiki(n.tags) AS f WHERE n.member_id=ALTERED.id trigger nodes on UPDATE AFTER or NEW: IF tags(..) IF EXISTS .. ALTER action='R' FROM wiki_members .. ELSE INSERT INTO wiki_members ..

In Osmosis this can be done by the osmosisUpdate stored procedure.

Starts after updating data source tables is finished, that is, nodes, ways and relations tables of Osm2pgsql or Osmosis are up to date and the. FOR EACH lang, lemma FROM wiki_members WHERE action='R': rebuild rows update kml files
 * update postprocess

Step 3, kml files
Make them using PostGis functions most probably, update them, store them

Step 4, test link to wikipedia
Via WikiMiniAtlas, GeoHack, etc. using OpenLayers most probably.

Step around 6, Rendering?
Or simplification of big relations.

Step around 7, interwikilinks
Merge de:Donau with en:Danube.

Step 10, 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 or adjacency_list. The thing to do is to eliminate cycles and build the relation tree.
 * 1) see Osmwiki Super-Relation/Implementation