i am pretty new to openstreetmap

update see below!

we can run Perl for text-mangling XML-Simple-2.20/lib/XML/Simple.pm here's an example of a little script to parse our XML:

#!/usr/bin/perl
use strict;
use warnings;
use XML::Simple;
use Data::Dumper;

my $xmlfile = shift || die "Usage: $0 <XML_FILE>\n";

my $ref;
eval {
  $ref = XMLin($xmlfile,
    ForceArray    => 0,
    KeyAttr       => [ ],
    SuppressEmpty => '',
  ) or die "Can't read XML from $xmlfile: $!\n";
};
die $@ if($@);
print Dumper $ref;

which, if passed our XML file goes very well

on http://overpass-turbo.eu/ i have runned the following code

i run the following code in opverpass-api - see here http://overpass-turbo.eu/

<query type="node">
  <has-kv k="place" v="city"/>
  <has-kv k="name" v="Peking"/>
</query>
<query type="node">
  <around radius="1000"/>
    <has-kv k="shop"/>
</query>
<print/>

Export of the data to the following formats

to GeoJSON
to GPX
to KML

get the data from

see the Overpass API-explanations: i have the options to loat them to JOSM laden (only for requests, that give back valid OSM-XML with Metadata) GeoJSON to save it as gist

note - i did not install the overpass-api on my opensuse 13.1 yet. but i am willing to do so.

as for now - running the above mentioned code in the oerpass-api - here. http://overpass-turbo.eu/

how to treat it to get it exported as csv-formated hope i was able to provide all the necessary things for a clear and concise question.

all i want is to transforme the xml-data to csv - in order to get a excel or calc based output.

<?xml version="1.0" encoding="UTF-8"?>
<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="2014-04-27T13:49:02Z"/>

  <node id="297489767" lat="49.4085014" lon="8.6941465">
    <tag k="addr:city" v="Heidelberg"/>
    <tag k="addr:housenumber" v="23"/>
    <tag k="addr:postcode" v="69115"/>
    <tag k="addr:street" v="Sofienstraße"/>
    <tag k="name" v="ARLT"/>
    <tag k="phone" v="+49 6221 20229"/>
    <tag k="shop" v="computer"/>
    <tag k="source" v="survey"/>
    <tag k="website" v="http://www.arlt.com"/>
    <tag k="wheelchair" v="yes"/>
  </node>
  <node id="305144906" lat="49.4060012" lon="8.6929652">
    <tag k="addr:city" v="Heidelberg"/>
    <tag k="addr:country" v="DE"/>
    <tag k="addr:housenumber" v="13-15"/>
    <tag k="addr:postcode" v="69115"/>
    <tag k="addr:state" v="Baden-Württemberg"/>
    <tag k="addr:street" v="Rohrbacher Straße"/>
    <tag k="name" v="Heidel-bike"/>
    <tag k="opening_hours" v="Tu-Fr 10:00-18:30; Sa 10:00-14:00"/>
    <tag k="shop" v="bicycle"/>
    <tag k="website" v="http://www.heidelbike.de/"/>
    <tag k="wheelchair" v="yes"/>
  </node>
  <node id="305963167" lat="49.4139877" lon="8.6924247">
    <tag k="addr:city" v="Heidelberg"/>
    <tag k="addr:country" v="DE"/>
    <tag k="addr:housenumber" v="4"/>
    <tag k="addr:postcode" v="69120"/>
    <tag k="addr:street" v="Brückenstraße"/>
    <tag k="name" v="Buchhandlung Schmitt &amp; Hahn"/>
    <tag k="shop" v="books"/>
    <tag k="wheelchair" v="no"/>

look forward to hear from you

update found this interesting thing: http://stackoverflow.com/questions/5491056/how-to-import-xml-file-into-mysql-database-table-using-xml-load-function

have an XML file which looks like this :

    <?xml version="1.0" encoding="UTF-8"?>

<resultset statement="YOUR SQL STATEMENTS TO GENERATE THIS XML FILE" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
    <field name="personal_number">539</field>
    <field name="firstname">Name</field>
    <field name="lastname">Surname</field>
    <field name="email">email.domain.com</field>
    <field name="start_time">2011-04-02 13:30:00</field>
    <field name="end_time">2011-04-02 18:15:00</field>
    <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
  </row>
  <row>
    <field name="personal_number">539</field>
    <field name="firstname">Name</field>
    <field name="lastname">Surname</field>
    <field name="email">email.domain.com</field>
    <field name="start_time">2011-04-02 13:30:00</field>
    <field name="end_time">2011-04-02 18:15:00</field>
    <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
  </row>
  <row>
    <field name="personal_number">539</field>
    <field name="firstname">Name</field>
    <field name="lastname">Surname</field>
    <field name="email">email.domain.com</field>
    <field name="start_time">2011-04-02 13:30:00</field>
    <field name="end_time">2011-04-02 18:15:00</field>
    <field name="employee_category">1,2,4,5,22,37,38,39,41,43,44</field>
  </row>

I am trying to import it in MySQL using SQL statement :

use databasename;
LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename;

The table my_tablename has the following fields :

id (auto increment id)
personal_number(varchar)
firstname(varchar) 
lastname(varchar)
email(varchar) 
start_time(varchar)
end_time(varchar)
employee_category(varchar)

I get error : Error Code: 1136 Column count doesn't match value count at row 1

I am using MySQL 5.1.56

I assume this error occurs because the database table has field id, which is not present in the XML file. How is it possible to import this XML file using MySQL queries of built in functions such that it skips id column during the import and relies on the auto increment function for the id column? Is there some smarter way of handling XML file imports im MySQL? Maybe there is better statement which allows to specify column mapping?

we can specify fields like this:

LOAD XML LOCAL INFILE '/pathtofile/file.xml' 
INTO TABLE my_tablename(personal_number, firstname, ...);

since ID is auto increment, you can also specify ID=NULL as,

LOAD XML LOCAL INFILE '/pathtofile/file.xml' INTO TABLE my_tablename SET ID=NULL;

asked 28 Apr '14, 21:09

say_hello_to_the_world's gravatar image

say_hello_to...
19232427
accept rate: 0%

edited 30 Apr '14, 10:33

2

Please edit your question to make it easier to read for everybody and that supporters don't need to walk trough dozens of lines. 1. What is your problem? 2. What did you tried? 3. Where do you fail?

(29 Apr '14, 11:42) iii

Overpass API v0.7.51 now comes with native CSV support, so you don't need to download an extract from Geofabrik and post-process that extract using osmconvert anymore:

Here's an example for Overpass turbo which returns all pharmacies in Lüneburg along with their OSM id, lat, lon, name, operator, etc.

[out:csv(::id, ::lat, ::lon,  amenity, name, operator, opening_hours, "contact:website", "contact:phone", brand, dispensing, lastcheck)];

{{geocodeArea:Lüneburg}}->.searchArea;

(
  node["amenity"="pharmacy"](area.searchArea);
  way["amenity"="pharmacy"](area.searchArea);
  relation["amenity"="pharmacy"](area.searchArea);
);
out center;

Example: http://overpass-turbo.eu/s/5Qc

If you click on "Export" -> "raw data directly from Overpass API" you can even directly load OSM data into LibreOffice/Excel. Data is separated by a "tab" character by default and comes with a header line (can be adjusted).

permanent link

answered 08 Nov '14, 11:50

mmd's gravatar image

mmd
5.6k4988
accept rate: 37%

Please type CSV in the text searchbox of this FAQ site, and you will get some hints how you can convert raw OSM data to CSV data.

And please avoid to do crosspostings in OSM forum and here at the same time. Thanks.

permanent link

answered 29 Apr '14, 16:21

stephan75's gravatar image

stephan75
12.5k453209
accept rate: 6%

Possibly the simplest way to convert OSM data to CSV is using osmconvert: http://wiki.openstreetmap.org/wiki/Osmconvert

permanent link

answered 30 Apr '14, 11:39

Richard's gravatar image

Richard ♦
27.6k40245368
accept rate: 19%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×350
×78
×26

question asked: 28 Apr '14, 21:09

question was seen: 7,386 times

last updated: 08 Nov '14, 11:50

powered by OSQA