transforming xml-data to csv

English Programming/Scripting
#1

i am pretty new to openstreetmap

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 & Hahn"/>
    <tag k="shop" v="books"/>
    <tag k="wheelchair" v="no"/>

loook forward to hear from you

#2

perhaps this is a way.

to use Perl for text-mangling - so we can use the XML::Simple](http://search.cpan.org/~grantm/XML-Simple-2.20/lib/XML/Simple.pm) module. here’s an example of a little script to parse your XML:


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

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

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

**Explantion: **iterating thru the array/hash it creates the file and helps carving up the data into comma separated lines of data than can be redirected to file.

#3

**Update: **another solution on the way from osmconvert to convert osm to .o5m and .pbf file:

i did some trials with osmconvert and osmfilter: it works fine but am having problem converting osm to .csv or excel file I used
./osmconvert addis.osm --all-to-nodes --csv="@id @lon @lat amenity cafe restaurant name" or ./osmconvert addis.osm --all-to-nodes --csv="@id @lon @lat amenity cafe name" but nothing comes up:

question: is there any other way to convert osm to excel sheet or csv file?

solution: First of all we need to have in mind: OSMconvert is not a filter. This means that all features will be converted. To achieve the goal of having only certain features in the csv file, we will have to filter the csv file generated by osmconvert afterwards. Also, the arguments to the --csv option of osmconvert are the tag keys we want to have in our csv file. If we want “amenity” and “name” columns in addition to id, lat and lon, we have to specify “@id @lon @lat amenity name”, and then we have to filter the CSV output.

For example we can do like so:

./osmconvert addis.osm --all-to-nodes --csv="@id @lon @lat amenity name" --out-csv -o=outfile.csv

grep cafe outfile.csv > cafes.csv
grep restaurant outfile.csv > restaurants.csv
cat cafes.csv restaurants.csv > cafes_and_restaurants.csv