将OpenStreetMap-Data(OSM-File)导入到MySQL-Database/SQL-File [英] Import OpenStreetMap-Data (OSM-File) to a MySQL-Database / SQL-File

问题描述

我下载并提取了OpenStreetMap源文件(请参见此处).我现在有一个400GB的XML源文件，我想(1)将数据插入到MySQL表 OR 中(2)将此数据导出到SQL文件中，没关系.

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.

1. 我有XML文件^^和MySQL服务器(5.6.14)
2. 我找到了工具渗透，可以转换OSM数据并安装了它
3. 我阅读了这篇文章:将城市和街道读入没有api的数据库
4. 我也阅读了这篇文章:是这个配置能够导入完整的osm planet文件吗?(是)
5. 我试图通过渗透插入数据，但几秒钟后抛出一个错误，或者数小时不执行任何操作，而没有插入一行.我也不知道该如何工作，因为我在MySQL中没有OSM表结构.

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.

问题

通常可以将整个文件导入数据库/SQL文件中吗?如果可以，怎么办?在哪里可以找到表/数据库结构?有没有人尝试过?

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?

非常感谢！

推荐答案

我相信这个问题的答案已经改变.从MySQL 5.6开始，MySQL中现已提供了功能齐全的OpenGIS地理空间功能(它甚至不是扩展程序；它是内置的).因此，从纯粹的理论角度来看，没有理由不能使用将用于PostgreSQL的标准的，受支持的数据库模式将OSM数据导入MySQL(当然，必须根据需要更改语法).实际上，

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...

当前，没有与osm2pgsql等效的官方osm2mysql.有几个名为osm2mysql的项目，但是我已经看到人们使用这些项目只能取得中等程度的成功.但是， GDAL 的开源ogr2ogr似乎是一个非常可行的解决方案.

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.

我下载，编译并安装了ogr2ogr，并能够将 shapefile 导入MySQL的地理空间模式中，并使用.我还没有尝试过OSM(这是我的下一步)，但是ogr2ogr声称完全支持OSM(XML 和二进制文件)，因此考虑到我对shapefile的成功，我想我会OSM也取得了类似的成功.

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.

我确实遇到了一些初始问题，其中一个可能与我在configure次尝试之间未使用make clean有关，另一个与涉及到的大型查询(某些插入> 1MB)和某些MySQL设置有关需要调整以进行补偿.您可以在此邮件列表线程中查看我的解决方案的详细信息. /a>

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.

这篇关于将OpenStreetMap-Data(OSM-File)导入到MySQL-Database/SQL-File的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！