Results 1 to 3 of 3

Thread: transforming xml-data to csv

  1. #1

    Default transforming xml-data to csv

    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/
    Code:
    <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

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

    Code:
    <?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"/>
    loook forward to hear from you
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  2. #2

    Default Re: transforming xml-data to csv

    perhaps this is a way.


    to use Perl for text-mangling - so we can use the XML::Simple module. here's an example of a little script to parse your XML:

    Code:
    #!/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;

    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.
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

  3. #3

    Default Re: transforming xml-data to csv

    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:

    Code:
    ./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
    dilbert ;-)
    Wordpress-development - a Toolset: wpgear.org

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •