1

I have a XML file which I created from the overpass API. I'd like to load this data from this file into a MySQL database. I've been using this code to test, but none of the data loads.

LOAD XML LOCAL INFILE '/home/jay/Downloads/interpreter1' into table jayDB.xml1 (id);

I'm new to XML so not sure if this is possible.

Here is some sample data from the file. What I'd like populated is: id,lat,lon,addr:housenumber, etc

<osm version="0.6" generator="Overpass API">
<note>
The data included in this document is from www.openstreetmap.org. The data is made available under ODbL.
</note>
<meta osm_base="2013-03-07T14:54:02Z" areas="2013-03-07T10:37:02Z"/>
<node id="240486180" lat="50.9744274" lon="3.0152858">
<tag k="addr:housenumber" v="9"/>
<tag k="addr:street" v="Marktplaats"/>
<tag k="amenity" v="cafe"/>
<tag k="email" v="vandaelekoen67@skynet.be"/>
<tag k="name" v="Paviljoentje"/>
<tag k="opening_hours" v="Mo-Su 09:00+; Tu off; Th 09:00-14:00"/>
<tag k="phone" v="+3251636211"/>
<tag k="website" v="http://www.paviljoentjestaden.be"/>
</node>
<node id="244312208" lat="51.2461401" lon="5.4390455">
<tag k="amenity" v="cafe"/>
<tag k="created_by" v="JOSM"/>
<tag k="name" v="De Club"/>
</node>

However I think due to the format of the file it thinks K and V are the columns. Where as I wanted amenity to be column and cafe to be the value

I tried to look at the details https://dev.mysql.com/doc/refman/5.5/en/load-xml.html

1

MySQL allows only the xml formats as stated in the linked document. You have to modify the osm xml into one of those.

Alternatively, you can use osmosis or GDAL to do the import.

See this Q&A for some guidance:

https://stackoverflow.com/questions/20125242/import-openstreetmap-data-osm-file-to-a-mysql-database-sql-file

http://forum.openstreetmap.org/viewtopic.php?pid=152695

http://mysqlserverteam.com/mysql-5-7-and-gis-an-example/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.