/_ _  _  _   _ . _  /__/_
/\ /_'/_'/_/ / / /_// //
        /        _/
Data Consistency Checks for openstreetmap.org
----------------------------------------------

openstreetmap.org (OSM) provides a wiki-style means of creating a world-wide street map where everybody is encouraged to contribute. This is a collection of scripts that will examine part of the OSM database and try to find errors that should be corrected by users. As a result you get ugly lists of errors and are invited to correct them.

This document explains how to run data consistency checks on your own database and set up a webpage presenting the results.


PREREQUISITES
-------------

Packages required on Linux:
php5
php5-cli
apache
postgis
postgresql >= 8.3 with matching release of postgis (postgresql-8.3-postgis)
postgres-client
php5-mysql
php5-pgsql
php5-intl     (support for utf-8)
php5-idn      (support for IDN domain names)
mysql-server
mysql-client
phpMyAdmin
phpPgAdmin
sun-java7-jre
wget
wput
bzip2

Optional:
joe
mc


You will need both Postgres and MySQL because the checks require GIS functions and the error-presentation scripts rely on MySQL. They will not be recoded to use Postgres because you won't find Postgres on many webhosters.

Using sun-java7-jre is not optional. You need java7 by Sun (now Oracle), at least release 6.

The checks depend on a copy of the OSM database, split up in parts. Using only a subset of the planet file will result in false-positives because ways are cut in two at the border. To avoid this the splitting is done with overlapping borders - the border regions are included in both adjacent dumps. In the end errors in the overlapping area are discarded.
The planet is split up in currently 85 parts, so called 'schemas'. They are processed sequentially and independently.


It looks like the osmosis plugin heavily depends on the osmosis version being used. The plugin is tested and works with osmosis_0.42.


THE BIG PICTURE
---------------

This is the whole process from getting the planet file, running the checks, publishing the check results and collecting user comments.
error_view is the resulting table containing all errors. It's the source for the map presentation.

backend scripts running on processing servers:

main.php
	update source code
	loop over all database schemas and process them one by one
	finally start all over

process_schema.php
	do all that is necessary for processing a single schema:
	prepeare database (create db tables)
	diff-update planet file
	load database with planet file
	run the checks
	export & upload results to web server

prepareDB.php
	create database tables, activate postGIS

planet.php
	call osmosis with options for diff-updating a planet file part
	let osmosis use a custom plugin called 'pl' that creates special dump files

osmosis plugin 'pl'
PostgreSqlMyDatasetDumpWriter.java
	create dump files suitable for loading with COPY commands in PostgreSQL
	the format mainly differs from the current 'snapshot' format in that all geometries
	are in meters instead of lat/lon
	it was established before the current 'snapshot' format evolved and cannot be changed
	with realistic effort any more

prepare_helpertables.php
	update redundant columns

prepare_countries.php
	create structures needed for boundary processing

run-checks.php
	start all the check routines found in config file error_types.php
	0010_*.php ... 9999_*.php
	compare old and new errors, update error states
	rebuild the error_view table

export_errors.php
	export error_view to dump file

webUpdateClient.php
	upload error_view to web server
	start procedures on web server for loading the new file
	communicating with webUpdateServer.php



frontend scripts running on web server:


report_map.php
myText.js, myTextFormat.js
	main display script including the map and myText layer
	derived from OpenLayers using an extended version of the Text layer

points.php
	deliver error entries to the client browser
	selecting errors matching error type selection and current viewport of map

comment.php
	receive user feedback and store it on the webserver's comments table




PLANET FILE MANAGEMENT & SQUID
------------------------------

The planet file is split in appriximately 85 rectangular areas called 'schemas' (this wrong term evolved in the early days, because every part of the planet resides in its own database schema). Have a look at config/planet.odg for the splitting layout.

When processing a file osmosis will download all diffs since last update and apply them to the schema's planet file. As the planet diffs include updates for the whole planet osmosis includes objects out of scope to the current schema's planet file. That is why cutting the schema's planet file has to be repeated after the diff-update.

All of these files are diff-updated individually. That means you always work with the most recent version of each file but you end up downloading the same diff files over and over. That's where the web proxy squid comes into play: Squid caches all web access. It speeds up your downloads and avoids unnecessary traffic (the saving is by a factor of 85 - 1).

Setting up squid is quite easy. On Debian/ubuntu Linuxes do something like this:

> aptitude install squid

change the config file /etc/squid/squid.conf to increase overall cache size to 1000MB if you like (default is 100MB which is a little bit small). Choose cache size big enough to hold all planet diffs that are needed for updating even the oldest schema in the loop (depending on loop cycle time).

cache_dir ufs /var/spool/squid 1000 16 256

restart squid
>/etc/init.d/squid restart

tell your osmosis:
add this line to ~/.osmosis:

JAVACMD_OPTIONS="-Dhttp.proxyHost=localhost -Dhttp.proxyPort=3128 "

my ~/.osmosis looks like this:
JAVACMD_OPTIONS=" -Xmx2500m -Djava.io.tmpdir=/media/big_harddisk/tmp/ -Dhttp.proxyHost=localhost -Dhttp.proxyPort=3128 "

The website check (#410) too benefits from an http proxy. Have a look at the respective options in you keepright user config file (~/.keepright). Increasing the cache size to 5000MB for use with the website check seems appropriate.


SETTING UP LOCAL DATABASES
--------------------------

[Don't skip this section if you already have a local database!]

This project uses a modified version of the "simple PostgreSQL schema" as specified in osmosis/script/pgsql_simple_schema.sql, which is part of the source distribution of Osmosis. This means that the base tables are the same, but there are additional columns providing redundancy. This redundancy is used to boost performance of the queries as it can save some joins. For example the ways table has the number of nodes, as well as the id and lat/lon of the first and last node as additional columns; in way_nodes you find lat/lon of the nodes.

The downside is, you cannot use a default database. And you have to use a modified version of Osmosis to convert the planet file. A plugin for Osmosis is provided with the sources. It teaches Osmosis a new option --pl that will create dump files with parts of the redundancy needed.

This is the short form of an article on the wiki http://wiki.openstreetmap.org/wiki/Mapnik/PostGIS

Tuning PostgreSQL configuration for performance of OSM databases is an adventure. Since PostgreSQL 9.0 you can use the pgtune tool. It creates a modified version of your postgresql.conf depending on main memory installed and depending on the usage type you provide (DW seems to be the best matching one).

These are setup parameters you could set before starting out manually:

>>>Tune database parameters
edit /etc/postgresql/8.3/main/postgresql.conf and add/modify these parameters:
shared_buffers = 1024MB
work_mem = 128MB
maintenance_work_mem = 128MB
wal_buffers = 512kB
checkpoint_segments = 20
max_fsm_pages = 1536000
effective_cache_size = 512MB
autovacuum = off

>>>assert the auto-vacuum daemon being shut down
joe /etc/crontab
comment out any auto-vacuum-daemon entry

>>>Tune shmmax kernel parameter
joe /etc/sysctl.conf
edit/add the parameter
kernel.shmmax=300000000
after that reboot the machine or simply execute
sysctl -w kernel.shmmax=300000000 && /etc/init.d/postgresql-8.3 restart

>>>Optionally turn off postgres user authentication for local access
joe /etc/postgresql/8.3/main/pg_hba.conf
Add this line:
local   all         all                               trust
This is a security risk. You will not need a password when using the command line psql shell. Most probably you'll use phppgadmin and won't need this.

>>> Alternatively to turning off local password prompting you may create a .pgpass file
joe ~/.pgpass
add a line of this form: hostname:port:database:username:password
127.0.0.1:*:*:keepright:yourpasswordhere
chmod 0600 ~/.pgpass


>>>Create the new user
su - postgres
createuser keepright
Shall the new role be a superuser? (y/n) y

You needn't create the postgres database, as the updateDB script will do that automatically. But you have to set the password for the keepright user inside postgres.

Still as user postgres start the psql shell:
> psql

postgres=# ALTER ROLE keepright WITH PASSWORD 'shhh!';
ALTER ROLE


just in case the scripts don't work as expected: creating the database and installing postGIS is easy if you're using postgresql>=9.1

CREATE DATABASE osm WITH OWNER = osm;

inside the newly created database just run
CREATE LANGUAGE plpgsql;		-- (should already be there)
CREATE EXTENSION postgis;



Wondering why the auto-vac-daemon ist shut off?
The daemon will start analyzing and vacuuming tables every few hours to keep index performance up on a high level. But this consumes large amounts of IO bandwidth and disturbes normal operation. Vacuuming is done by hand throughout the scripts because there are many temporary tables that need analyzing and the daemon never comes at the right time. Basically it is done once after loading data and then manually after creation of temp tables and adding indexes.

For inserting actual data take a look at updateDB.php, planet.php and config: These scripts download a planet dump from the net or diff-update an already existing set of planet excerpts and insert the planet files contents in a database. In config you can define the databases and the coordinates of the areas.

Don't forget to adapt the appropriate configuration variables to match your database credentials in ~/.keepright.
OSMOSIS_BIN has to point to the location where you have put the osmosis executable.

Configuration is split in two parts: config/config is the default file. This file will always be read first and it will be updated via svn to add new setup options. You will want to make settings differ from the standard settings. Therefore you can change the file ~/.keepright which includes only the system-specific settings (this file will be created upon the first run of main.php). ~/.keepright will be read after the built-in config file so any settings made here will overwrite the default.


Finally you will have to setup a MySQL database and user for the destination tables needed by the presentation scripts if you want to run a web server. Update ~/.keepright and webconfig.inc.php with the new database credentials.



RUNNING THE CHECKS
------------------

First of all you need to specify database credentials in ~/.keepright.

Second, take a look at the list of error types in config/error_types.php. Here you may specify which types of checks should be executed. Anything different from zero will enable a job.

Assuming you already have a populated database you start checking by calling run-checks.php from the shell:
> php run-checks.php 1 20 30 40
will start the checks 20, 30 and 40 on the database schema called 1. Providing check numbers on the command line is optional. If none are given, all checks are run if they are enabled in ~/.keepright.

When processing has finished you will have (among others) a newly created table called public.error_view. Here you can find records for all errors that exist. This postgres table will get transferred into MySQL by export_errors.php and webUpdateClient.php.

As time goes by you will update your database and maybe errors are getting corrected. The scripts will detect when old errors don't exist any more and will update the state information in the errors tables to state==cleared.


VISUALIZING RESULTS
-------------------

report_map.php is used for displaying errors on the map. This script displays a slippy map using an exra layer to draw icons. Icons are drawn for every faulty node and on either starting node of faulty ways. They display some hint about the error when hovered. Keep in mind that this display method draws a limited number of errors in the map, because of memory constraints in browsers and the webserver.


WRITING YOUR OWN CHECKS
-----------------------

Take a look at the existing checks to see how they work. Then take a look at the template file 0000_template.php. If you write a new check you also have to mention it in the config file.

Keep in mind that all checks are included using include() inside a while loop that is running inside run-checks.php. Surprisingly that doesn't matter much, with some exceptions:
Any checks run in the same scope, you are even allowed to declare functions (inside the while-loop!) but you must not declare two functions of the same name in different checks. Also don't rely on global variables not being used at the beginning of your script (maybe another check did already initialize a variable of the same name). The same is valid for temporary tables you may need. Always check if a table already exists before creating it. At the end of the script drop any tables you have created.

Maybe in the future I will change this into an oop-styled buch of classes, but up to now it is working great this way.

If you have ideas for new checks, I would like to integrate them in the official sources to let others benefit from them. So please let me know! And please let me assign a unique check numer for your checks to avoid collisions.


SCHEMA SPLITTING WORKFLOW
--------------------------

when planet schema files grow beyond certain limits it is necessary to further split them.
e.g. splitting old schema '1' into '86' and '87'

determine optimum splitting boundaries to achieve equally sized files of up to 4GB uncompressed xml data:

/home/harald/OSM/osmosis-0.36/bin/osmosis --rx 1.osm --tee 2 --bb left=-30 top=85 right=1.8 bottom=52.3 idTrackerType=BitSet completeWays=yes completeRelations=yes --wx 86.osm --bb left=-30 top=52.3 right=1.8 bottom=49.3 idTrackerType=BitSet completeWays=yes completeRelations=yes --wx 87.osm > log 2>&1 &

update config blocks in config/schemas.php
use planet.php to get boundaries including padding:
php planet.php --cut 1.osm 86 87
again use osmosis to split files using definitive boundaries

in planet directory copy config directory of old planet file to new directories

in 0130_islands.php check that there is a starting point in every schema, add some as required

copy old error records to both new schemas:

first duplicate errors into secondary new schema:
insert into public.errors(error_id, error_type, object_type, object_id,
state, first_occurrence, last_checked, lat, lon, "schema", msgid, txt1,
txt2, txt3, txt4, txt5)
SELECT error_id, error_type, object_type, object_id, state,
first_occurrence, last_checked, lat, lon, '87', msgid, txt1, txt2, txt3, txt4, txt5
FROM public.errors
where "schema"='1';

last move errors from old schema into primary new schema:
update public.errors set "schema"='86' where "schema"='1';

update make.sh to include the new schemas and exclude the old ones
run checks

on webserver db:

copy comments from old schema to new ones:
INSERT INTO `comments`(`schema`, `error_id`, `state`, `comment`, `timestamp`, `ip`, `user_agent`)
SELECT '86', `error_id`, `state`, `comment`, `timestamp`, `ip`, `user_agent` FROM `comments`
WHERE `schema`='1'

UPDATE comments SET `schema`='87' WHERE `schema`='1';

update table schemata:
drop old schema line and add new schema with updated boundaries

delete old planet file, old error_view table from webserver, old config file sections




RUNNING ON WINDOWS
------------------

install packages from these locations:

PHP
http://windows.php.net/download/

PostgreSQL
http://www.enterprisedb.com/products-services-training/pgdownload#windows
choose PostgreSQL 9.2 or later, choose the x64 flavor

PostGIS
http://postgis.refractions.net/download/windows/
choose version 2 or later
install PostGIS using application stack builder shipping with PostgreSQL

bzip2
http://sourceforge.net/projects/gnuwin32/files/bzip2/1.0.5/bzip2-1.0.5-bin.zip/download

SVN client (Apache SVN)
http://www.sliksvn.com/en/download

Java Runtime Environment
http://www.oracle.com/technetwork/java/javase/downloads/
make sure you catch the 64 bit version of Java


Add some directories to PATH environment variable pointing to the programs you just installed (change paths accordingly to match your environment):

C:\Program Files\SlikSvn\bin;C:\Program Files (x86)\bzip2\bin;C:\Program Files\php5;C:\Program Files (x86)\Java\jre7\bin


Create an OSM directory (eg. C:\OSM\) and check out the source files. In a cmd window type
C:
cd \
md OSM
cd OSM
mkdir keepright
svn co svn://svn.code.sf.net/p/keepright/code/ keepright

Create a copy of the config.php file you find in C:\OSM\keepright\config\config.php.template and rename it to userconfig.php in the same directory. This file replaces the ~/.keepright file used on Linux environments and is never overwritten by svn updates.

Open the file in your favourite text editor and change the paths accordingly.


Create a file called C:\users\<your user name>\osmosis.bat and give it the following content:

set JAVACMD=C:\Program Files\Java\jre7\bin\java.exe
set JAVACMD_OPTIONS=-Xmx2500m -Djava.io.tmpdir=C:\temp\

increase the memory limit in case osmosis should crash.

in case you want to use a http proxy add this part to your JAVACMD_OPTIONS
	" -Dhttp.proxyHost=<your.proxy.host> -Dhttp.proxyPort=3128 "


in OSM\osmosis\bin\osmosis.bat you may have to change the next-to last line to include your JAVACMD in apos (") in case it contains spaces just like this:

SET EXEC="%JAVACMD%" %JAVACMD_OPTIONS% -cp "%PLEXUS_CP%" -Dapp.home="%MYAPP_HOME%" -Dclassworlds.conf="%MYAPP_HOME%\config\plexus.conf" %MAINCLASS%  %OSMOSIS_OPTIONS% %*


Create/modify your php.ini file to enable the PostgreSQL-Extension
If you downloaded the non-installer version of php you need to create a php.ini file yourself. Just copy and rename php.ini-production in your php directory to php.ini. Find the extension_dir setting and point it to the ext directory in your php installation:

extension_dir = "C:\Programme\php5\ext"

find and uncomment the line loading the PostgreSQL-Extension:
extension=php_pgsql.dll



LEGAL STUFF
-----------

Sources are licensed under GPLv2.

This collection of characters was created using a random number generator. I don't think these files are useful for anything or anyone. If you copy, watch, process or even think about putting this collection of bytes into your computer, you do this at your own risk. Don't blame me.


IMPRESSUM
---------

This work is done without commercial background, just for my personal pleasure. I would be very happy if it was helpful for the OSM Project.

If you like to contact me, my mailbox at the austrian server of gmx is labelled keepright