User:Hholzgra

From OpenStreetMap Wiki
Jump to navigation Jump to search
Userboxes
OSM Logo This user submits data to OpenStreetMap under the name
Hartmut Holzgraefe.
Forums.png Hholzgra is on the Forum
Public-images-osm logo.svg I support OSM by being a member of the OpenStreetMap Foundation. Are you?
Flag of Germany.svg 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.
Hiker Hholzgra
is a hiker.
Bike Hholzgra
is a bicyclist.
JOSM Hholzgra submits data to OpenStreetMap using JOSM.
OsmAnd icon Hholzgra uses OsmAnd, mobile map viewing & navigation app.
StreetComplete Hholzgra submits data to OpenStreetMap using StreetComplete.
Vespucci logo Hholzgra submits data to OpenStreetMap using Vespucci.
Tux.svg Hholzgra uses a Linux-based computer.
Firefox Hholzgra prefers Mozilla Firefox.
Pear Leaf icon.jpg Hholzgra uses Leaflet, a JavaScript library for interactive maps.
OpenLayers logo.svg Hholzgra uses Openlayers, a JavaScript library for interactive maps.
Overpass-turbo.svg Hholzgra uses Overpass turbo, an OSM database search tool, and is willing to advise on it
Hot logo.svg Hholzgra volunteers at Humanitarian OSM Team.
...? Hholzgra appreciates the use of Good changeset comments.
Gears2.svg Hholzgra commits code to OpenStreetMap under the name hholzgra
GitHub logo Hholzgra has an account on GitHub as @hholzgra.
GitLab user Hholzgra has an account on GitLab as @hholzgra.
Cat silhouette.svg Hholzgra
is not a cat.

Hartmut Holzgraefe <hartmut@(php.net|mariadb.com)>

About Me

Projects

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

Motivation

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

Results

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

Source

See this GitHub project ...

Talks and Presentations

Categories