5

Intro

I downloaded and extracted the OpenStreetMap Source File (See here). I have a 400GB XML-sourcefile now, and i would like to (1) insert the data in a MySQL Table OR (2) export this to an SQL file, doesn't matter.

What I already did / tried

  1. I have the XML File^^ and the MySQL Server (5.6.14)
  2. I found the tool Osmosis, to convert OSM data and I installed it
  3. I read this post: Read cities and streets into a database without api
  4. I also read this post: Is this config is capable to import complete osm planet file? (It is.)
  5. I tried to insert the data with osmosis, but it throws an error after a few seconds, or is doing nothing for hours, without inserting one single row. I also don't understand how this should work, because I don't have the OSM table structure in MySQL.

The Question

Is it generally possible to import the whole File in a Database / SQL-File, and if yes, how? Where do I find the table/database-structure? Has anyone already tried?

Thank you very much!

Community
  • 1
  • 1
user2896540
  • 228
  • 1
  • 2
  • 7
  • In 2019 I have developed a command-line tool named `osm-to-sql` to convert any OSM XML file to separate SQL files. After conversion, you can import these SQL files to your MSSql/SQLite/MySQL ... etc servers. https://github.com/whizsid/osm-to-sql – Ramesh Kithsiri HettiArachchi Oct 08 '19 at 22:43

2 Answers2

4

I believe the answer to this question has now changed. As of MySQL 5.6, there is now a fully-featured OpenGIS geospatial feature set in MySQL (it's not even an extension; it's built in). Thus, from a purely theoretical standpoint, there's no reason you can't import the OSM data into MySQL using the standard, supported database schema that you'd use for PostgreSQL (with syntax changed as necessary, of course). In fact, this presentation (which still refers to an older, incomplete version of MySQL's OpenGIS support) suggests that, with proper indexing, some MySQL geospatial actions may actually perform better than PostgreSQL PostGIS, though I'm sure that's up for debate. So the remaining problem is import tooling...

Currently, there's no official osm2mysql equivalent to osm2pgsql. There are a couple of projects out there named osm2mysql, but I've seen people have only moderate—if any—success using those projects. However, GDAL's open source ogr2ogr appears to be a very viable solution.

I downloaded, compiled, and installed ogr2ogr and was able to import a shapefile into a geospatial schema in MySQL, and successfully (and apparently accurately) query against the data using things like ST_Contains(...). I have not yet tried OSM (that's my next step), but ogr2ogr claims to have full support for OSM (XML and binary), so given my success with shapefiles, I imagine I'll have similar success with OSM.

I did have some initial problems, one of which was likely related to my not using make clean between configure attempts, and the others which were related to the large queries involved (some inserts > 1MB) and some MySQL settings that needed adjusting to compensate. You can view the details of my solutions in this mailing list thread.

Nick Williams
  • 2,864
  • 5
  • 29
  • 43
3

Well, indeed it is possible: http://wiki.openstreetmap.org/wiki/MySQL
But be warned, this is a not well supported solution:

  • has no geospatial extension
  • custom DB schema
  • not supported by major tools from OSM toolchain

I highly recommend to switch to postgreSQL with PostGIS extension, that is a semi-standard at the GIS world.

In the end it depends on your usecase (prototyping ... highly available DB server with live OSM data updates). You will find further informations at http://switch2osm.org

MaM
  • 2,043
  • 10
  • 20
  • First of all, thank you for your answer! Unfortunately, i never worked with postgre so far, so I will check this link out, thank you. – user2896540 Nov 21 '13 at 17:23
  • I hate to dredge this back up, but I think the answer has changed now. This part:"has no geospatial extension" is no longer correct (MySQL now has a geospatial extension that is in feature parity with PostgreSQL), and should be updated on this answer and on the OpenStreet Map wiki (I'll contact them separately). So, given that, the answer SHOULD be different now (but I don't know if it is). Is there a way to get this geospatial data into the geospatial extension for MySQL? Source: https://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html – Nick Williams Sep 22 '15 at 22:37