OpenStreetMap Dataset

This page describes how to use the OpenStreetMap dataset in testing.

Database Schema

The database schema is available here. To import:

mysqladmin create osm
cat osmdb06.sql | mysql osm

By default, this schema uses a mixture of InnoDB and MyISAM tables. To convert all tables to Aria:

sed -i -e 's/InnoDB/Aria/gi' osmdb06.sql
sed -i -e 's/MyISAM/Aria/gi' osmdb06.sql

30 tables are created.

Data

The data is provided in the form of XML files (.OSM files) that require the Java-based Osmosis tool to load into MariaDB. The tool is available from dev.openstreetmap.org. Version 0.36 is known to work.

Various .OSM files are available, including the entire world (>200Gb unzipped) and individual countries.

Data is loaded with the following command-line (in the example, we're using the bulgaria.osm file, replace with the file you choose):

chmod +x bin/osmosis
bin/osmosis --read-xml file=bulgaria.osm --write-apidb dbType="mysql" host="localhost:port" validateSchemaVersion=no database="osm" user="root" password="<password-goes-here>"

Data is inserted into 19 tables, as follows:

MariaDB [(none)]> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [information_schema]> select TABLE_NAME, TABLE_ROWS from TABLES
    -> where TABLE_ROWS > 0
    -> AND
    -> TABLE_SCHEMA='osm'
    -> ORDER BY TABLE_ROWS DESC;
+--------------------------+------------+
| TABLE_NAME               | TABLE_ROWS |
+--------------------------+------------+
| current_way_nodes        |    1559099 |
| way_nodes                |    1559099 |
| current_nodes            |    1477247 |
| nodes                    |    1477247 |
| node_tags                |     311751 |
| way_tags                 |     287585 |
| ways                     |     100007 |
| current_ways             |     100007 |
| changeset_tags           |      18738 |
| current_relation_members |      14560 |
| relation_members         |      14560 |
| changesets               |       9369 |
| relation_tags            |       3948 |
| current_relations        |        937 |
| relations                |        937 |
| users                    |        537 |
+--------------------------+------------+
16 rows in set (0.00 sec)

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.