From OpenStreetMap Wiki
Jump to: navigation, search
Public domain
All my contributions to OpenStreetMap are released into the public domain. This applies worldwide.
In case this is not legally possible, I grant anyone the right to use my contributions for any purpose, without any conditions, unless such conditions are required by law.
OSM Logo This user submits data to OpenStreetMap under the name Hartmut%20Holzgraefe.
Deutsche Flagge.jpg This user hails from Germany
de Dieser Benutzer spricht Deutsch als Muttersprache.
en-2 This user is able to contribute with an intermediate level of English.
Bike Hholzgra
is a hiker.
Go Do Some Mapping.png Hholzgra uses a Garmin_eTrex_Vista and is willing to answer questions on it from OSM users.

Go Do Some Mapping.png Hholzgra uses a Samsung Galaxy Nexus and is willing to answer questions on it from OSM users.

Go Do Some Mapping.png Hholzgra uses a OSMtracker_(Android) and is willing to answer questions on it from OSM users.

JOSM Hholzgra submits data to OpenStreetMap using JOSM.
Ubuntu.svg Hholzgra uses an Ubuntu-based computer.
Firefox This user prefers Mozilla Firefox.
Gears2.svg Hholzgra commits code to OpenStreetMap under the name hholzgra

Hartmut Holzgraefe <hartmut@(|>


Wanderwege in Bielefeld und umzu

Wege die in OpenStreetMap noch fehlen:

Lilienweg von Jöllenbeck nach Enger

ca. 8km

Bisher ist nur ein kleines Stück in Enger vorhanden ...

Lilienweg beim Heimatverein Jöllenbeck

Segelschiffweg von Jöllenbeck zum Hücker Moor

ca. 13km

Segelschiffweg beim Heimatverein Jöllenbeck

Mühlenweg von Jöllenbeck nach Spenge

ca. 9km

Mühlenweg beim Heimatverein Jöllenbeck

Rundwanderwege in Werther

Aus dem Gedächtnis: in Werther gibt es 3 oder 4 Rundwanderwege die alle am Busbahnhof beginnen

Stiftsweg rund um Herford

ca. 50km

Bisher nur ein paar Fragmente in Oldinhausen, Eickum und Vilsendorf vorhanden

Neuer Pilgerwegabschnitt Bielefeld -> Wesel

zweigt an der Klosterruine vom bisherigen Pilgerweg ab, verläuft westlich über Steinhagen und Brockhagen ...

MySQL Backend for osm2pgsql

The Plan

  • osm2pgsql in theory supports different database backends for output and for its caching layer (aka 'middle')
  • MySQL/MariaDB have basic GIS support (with improvements in MariaDB since 5.3 and in MySQL since 5.6)

So why not add an 'output' and 'middle' module to osm2pgsql that talks to MySQL/MariaDB instead of PostgreSQL?

Shouldn't be too hard, right?

Unfortunately the osm2pgsql turned out to be less modular than originally thought, with PostgreSQL specific code not only in the output-pgsql and middle-pgsql module code files but also throughout the main osm2pgsql code files themselves, and not PostgreSQL specific generic code that could be shared by other backend implementations being embedded into the PostgreSQL specific module files


  • be able to compare GIS functionality and performance between MySQL, MariaDB and PostgreSQL using large real world data sets
  • shared hosters often only provide MySQL out of the box, not PostgreSQL/PostGIS (or only for extra $$$)
  • when using an application or framework that relies on (or works best with) MySQL, like e.g. MediaWiki or WordPress it can make sense to have GIS data in the same DBMS instead of having to maintain a second DBMS installation alongside with MySQL
  • MySQL on-disk data file formats are architecture-agnostic, so it is e.g. possible to do an import on a fast local box and then to just copy the generated data files over to e.g. a webserver that has a different architecture (with PostgreSQL this already fails when trying to move files generated on a 64bit x86 system to a 32bit x86 system, with MySQL this has been a no-brainer between systems of different word length or endianess for over a decade now)
  • the above also applies to native replication setups ...

The Steps

  1. refactor all PostgreSQL specific code out of the main code and into the pgsql specific module files only (done)
  2. refactor generic functionality out of the PostgreSQL specific files into generic files so that it can be shared by different backends (done)
  3. create MySQL/MariaDB output module (done)
  4. create MySQL/MariaDB middle module (work in progress)
  5. find a way to support --hstore (not started yet)


  • imports are possible as long as there is enough RAM (no --slim mode yet due to lack of middle layer)
  • imports take about 4-5 times as long as with PostGIS (on the same machine)
  • query performance on the data imported so far is similar to that of PostGIS (this may change though once larger data sets can be imported so that the working set doesn't fit into RAM easily anymore)


See this GitHub project ...