how to store this in mysql
- Code: Select all
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>
this is a question regarding textmangling and converting.
well i want to use perl for text-mangling - so we can use the
- Code: Select all
XML::Simple module. here's an example of a little script to parse your XML:
see the 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 trough the array / hash - this does create a file and helps carving up the data
into comma separated lines of data than can be redirected to file.
see another example:
- Code: Select all
<?xml version="1.0" encoding="UTF-8"?>
<root>
<node id="297467" lat="49.5014" lon="8.1465">
<tag k="addr:city" v="Stuttgart"/>
<tag k="addr:housenumber" v="23"/>
<tag k="addr:postcode" v="69115"/>
<tag k="addr:street" v="Sofienstrae"/>
<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.40012" lon="8.6929652">
<tag k="addr:city" v="Mainz"/>
<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-WUrttemberg"/>
<tag k="addr:street" v="Rohrbacher StraSSe"/>
<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>
</root>
__OUTPUT__
- Code: Select all
#ID, LAT, LON, CITY, HOUSNUMBER, POSTCODE, STREET, NAME, PHONE, SHOP, SOURCE, WEBSITE, WHEELCHAIR
297467, 49.5014, 8.1465, Stuttgart, 23, 69115, Sofienstrae, ARLT, +49 6221 20229, computer, survey, http://www.arlt.com,
yes,
305144906, 49.40012, 8.6929652, Mainz, DE, 13-15, 69115, Baden-WUrttemberg, Rohrbacher StraSSe, Heidel-bike, Tu-Fr 10:00
-18:30; Sa 10:00-14:00, bicycle, http://www.heidelbike.de/, yes,
another suggstion: XML::Twig
well i am not an XML expert so i confess i found some probelm in the data example, german char included..
I put the modified data in a file. We can see the content after the __END__ token in the script:
- Code: Select all
use strict;
use warnings;
use XML::Twig;
my $t= XML::Twig->new( pretty_print => 'indented',
twig_handlers => {
'node'=>sub{print $_[1]->att('id'),', ',$_[1]->att('lat'),', ',$_[1]->att('lon'),', ' ;
foreach my $tag ( $_[1]->children ){
print $tag->att('v').", ";
}
print "\n";
},
}
);
print "#ID, LAT, LON, CITY, HOUSNUMBER, POSTCODE, STREET, NAME, PHONE, SHOP, SOURCE, WEBSITE, WHEELCHAIR\n";
$t->parsefile('xml-001.xml');
which method is the best one - love to hear from you
greetinss
