README

The support officially has been dropped for MySQL/Mariadb.
However, it is still possible using OSM 0.6 API.

Tested with MariaDB 10.3.22 and 10.4.12 using Osmosis Version 0.47.4 and 0.48.0

History

https://github.com/oschrenk/osmosis-mysql
I found an (the?) old mysql schema on Brett Henderson‘s Homepage and started from there. If you are interested in the changes I made, take a look into the problems and the history of git repository.

Import osm.bz2 into mariadb

Take the schema here from the repository.

mysql -u osm -posm -h localhost < mariadb-osm.sql

osmosis accept only .gz

bzip2 -dc planet-200504.osm.bz2 | gzip > planet-200504.osm.gz
 89G May  7 20:31 planet-200504.osm.bz2
118G May 11 23:04 planet-200504.osm.gz

Read data and populate database

./bin/osmosis --read-xml file="andorra-latest.osm.gz" --write-apidb-0.6 host="127.0.0.1" dbType="mysql" database="osm" user="osm" password="osm" validateSchemaVersion=no

Import POI from pbf

./bin/osmosis --read-pbf file="europe-latest.osm.pbf" \
    --tf accept-nodes \
    aerialway=station \
    aeroway=aerodrome,helipad,heliport \
    amenity=* building=school,university craft=* emergency=* \
    highway=bus_stop,rest_area,services \
    historic=* leisure=* office=* \
    public_transport=stop_position,stop_area railway=station \
    shop=* tourism=* outdoor=* \
    --tf reject-ways --tf reject-relations \
    --write-apidb-0.6 host="127.0.0.1" dbType="mysql" database="osm" user="osm" password="osm" validateSchemaVersion=no

osmconvert germany-latest.osm.pbf --drop-author --drop-ways --drop-relations --drop-versions --out-pbf > germany-latest.poi.pbf

Usage

Return all main streets in a bounding box

select w.`id`, w.sequence_id, latitude, longitude, t1.v as name, t2.v as type
from current_way_nodes w, current_nodes n, current_way_tags t1, current_way_tags t2
where w.`node_id` = n.`id`
and w.`id` = t1.`way_id`
and w.`id` = t2.`way_id`
and t1.k = 'name'
and t1.v <>  ''
and t2.k = 'highway'
and t2.v in ('primary', 'secondary', 'tertiary', 'motorway', 'residential', 'road', 'track', 'trunk')
and n.latitude > 53 * 10000000
and n.latitude < 54 * 10000000
and n.longitude > 8 * 10000000
and n.longitude < 9 * 10000000
order by w.`id` asc, w.sequence_id asc;

OSM uses Fixed Precision Integer for the geographical location. Instead of persisting 53,0749415 as a floating point number in the database, it will be persisted as 530749415. The precision of the floating point number is defined as 7 decimal places, so you have to multiply or divide with 10^7.