S
sayhello
hello dear Linux-Experts
i have a xml-file that comes out of a osm-parser - it is formatted in xml:
which method fits if i want to store the output of this request to a mysql database: overpass turbo
see the output here
Code:
<node id="2064639440" lat="49.4873181" lon="8.4710548">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="turkish"/>
<tag k="email" v="info@lynso.de"/>
<tag k="name" v="Kilim - Café und Bar Restaurant"/>
<tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/>
<tag k="operator" v="Cengiz Kaya"/>
<tag k="phone" v="06 21 - 43 755 371"/>
<tag k="website" v="http://www.kilim-mannheim.de/"/>
</node>
<node id="2126473801" lat="49.4851170" lon="8.4756295">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="mannheim1@vapiano.de"/>
<tag k="fax" v="+49 621 1259 779"/>
<tag k="name" v="Vapiano"/>
<tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/>
<tag k="operator" v="Vapiano"/>
<tag k="phone" v="+49 621 1259 777"/>
<tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/>
<tag k="wheelchair" v="yes"/>
</node>
some questions
note; i run linux opensuse 13.1
- mysql is up and running
- the modules i have all
use DBI;
use XML::Twig;
... and i guess that i need furthermore another "library"
this library which is used in this line use OSM:
sm ; which is inspired by this page [note German language]: User:Brogo/OpenLayers Datenbankanbindung - OpenStreetMap Wiki
and this ", $title, "
[This module contains a lot of useful functions for working with osm files and data. it also includes functions for calculation and output.]
User:Gary68 - OpenStreetMap Wiki
questions:
where to put this library: ", $title, " should i put it into the folder where i have the following perl scripts:
note: well what i have i have a folder
home/perl where i have the following perl code:
a. osm_to_db.pl
b. create_db.pl
second question: - i am sure that i have to do some corrections;
in the code create_db.pl
a. the first correction:
at the line $file = "c:/osm/planet/pois.osm" ;
b the second correction:
at the line "open(AUSGABE, ">c:/osm/planet/mysql.txt"); "
...since i have no windows but a linux-system....:
below we see the both mentioned script:
a. create_db.pl
Code:
#!/usr/bin/perl -w
use strict ;
use OSM:sm ;
my $file ;
my $nodeUser ;
my @nodeTags ;
my $nodeTags ;
my $ref1 ;
my $line ;
my $tag;
my $nodeName;
my $id ="1" ;
my $lat ;
my $lon ;
my $name ;
my $amenity ;
my $operator ;
my $vending;
$file = "c:/osm/planet/pois.osm" ;
openOsmFile ($file) ;
open(AUSGABE, ">c:/osm/planet/mysql.txt");
($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;
while ($id != -1 ) {
$name ="" ;
$amenity ="" ;
$operator ="" ;
$vending ="" ;
@nodeTags = @$ref1;
foreach my $tag (@nodeTags) {
if ($tag->[0] eq "name") { $name = scalar ($tag->[1] )};
if ($tag->[0] eq "amenity") { $amenity = scalar ($tag->[1] )};
if ($tag->[0] eq "operator") { $operator = scalar ($tag->[1] )};
if ($tag->[0] eq "vending") { $vending = scalar ($tag->[1] )}
}
if ($name ne "" | $amenity ne "" | $operator ne"" | $vending ne"")
{print AUSGABE "$id^$lat^$lon^$name^$amenity^$operator^$vending\n";}
($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;
}
close(AUSGABE);
closeOsmFile () ;
and furthermore: b. the code osm_to_db.pl
Code:
#!/usr/bin/perl
use strict ;
use DBI;
use XML::Twig;
# prepare database
my $dbh=dbh(); # connect
init();
$dbh->do('USE db123');
#$dbh->do('DELETE FROM pois');
# sql
my $sql = 'REPLACE INTO pois VALUES (?,?,?,?,?,?)';
my $sth = $dbh->prepare($sql);
# set up handler
my $t = XML::Twig->new(
twig_handlers => { 'node' => \&node }
);
# parse xml
my $xml = do { local $/; <DATA> };
$t->parse($xml);
#$t->parsefile('.osm');
sub node {
my ($t,$elt) = @_;
my %data=(
'id' => $elt->att('id'),
'lat' => $elt->att('lat'),
'lon' => $elt->att('lon'),
);
for my $tag ( $elt->children() ){
$data{$tag->att('k')} = $tag->att('v');
#print $tag->att('k').' = '.$tag->att('v')."\n";
}
# update database
my @f = map{ $data{$_} }('id','lat','lon','name','amenity','operator');
if ($f[3] ne '' && $f[4] ne '' && $f[5] ne ''){
print "-- INSERT --\n".
(join "\n",@f).
"\n\n";
$sth->execute(@f);
}
}
sub init {
$dbh-> do('CREATE DATABASE IF NOT EXISTS db123
DEFAULT CHARACTER SET latin1
COLLATE latin1_german2_ci');
$dbh->do('USE db123');
$dbh->do('CREATE TABLE IF NOT EXISTS pois (
id BIGINT(20) UNSIGNED NOT NULL,
lat FLOAT(10,7) NOT NULL,
lon FLOAT(10,7) NOT NULL,
name VARCHAR(255) COLLATE utf8_bin NOT NULL,
amenity VARCHAR(255) COLLATE utf8_bin NOT NULL,
operator VARCHAR(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT
CHARSET=utf8
COLLATE=utf8_bin');
}
sub dbh {
my $dsn = "DBI:mysql:database=;host=localhost";
my $dbh = DBI->connect($dsn, 'user', 'pwd',
{RaiseError => 1, PrintError => 1})
or die (Error connecting " $DBI::errstr");
}
see the dataset; - which is stored in the file mysql.txt
the dataset - it is gathered from the request on the overpass-api which resides here overpass turbo
cf. overpass turbo
you see a request on the left part of the screen
note: to get the output - just press the button in the top-menu called "Ausführen"
after this you press the button called "DATEN" on the top-right -
just below the green button called "flatter this": after pressing this "DATEN"-button you see the data in the right window of the screen.
note - it has got various ids - that means that the osm-file does not give back constantly all the tags...
the last question; does this make any problems to our project - does this has any influence on our db-connection...!?!?
see the output here:
Code:
<node id="2064639440" lat="49.4873181" lon="8.4710548">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="turkish"/>
<tag k="email" v="info@lynso.de"/>
<tag k="name" v="Kilim - Café und Bar Restaurant"/>
<tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/>
<tag k="operator" v="Cengiz Kaya"/>
<tag k="phone" v="06 21 - 43 755 371"/>
<tag k="website" v="http://www.kilim-mannheim.de/"/>
</node>
<node id="2126473801" lat="49.4851170" lon="8.4756295">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="mannheim1@vapiano.de"/>
<tag k="fax" v="+49 621 1259 779"/>
<tag k="name" v="Vapiano"/>
<tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/>
<tag k="operator" v="Vapiano"/>
<tag k="phone" v="+49 621 1259 777"/>
<tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/>
<tag k="wheelchair" v="yes"/>
</node>
<node id="667927886" lat="49.4909673" lon="8.4764904">
<tag k="addr:city" v="Mannheim"/>
<tag k="addr:country" v="DE"/>
<tag k="addr:housenumber" v="5"/>
<tag k="addr
ostcode" v="68161"/>
<tag k="addr:street" v="Collinistraße"/>
<tag k="amenity" v="restaurant"/>
<tag k="name" v="Churrascaria Brasil Tropical"/>
<tag k="phone" v="+496211225596"/>
<tag k="wheelchair" v="limited"/>
</node>
<node id="689928440" lat="49.4798794" lon="8.4853418">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="greek"/>
<tag k="email" v="epirus70@hotmail.de"/>
<tag k="fax" v="0621/4407 762"/>
<tag k="name" v="Epirus"/>
<tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/>
<tag k="phone" v="0621/4407 761"/>
<tag k="smoking" v="separated"/>
<tag k="website" v="http://epirus-ma.blogspot.com/"/>
<tag k="wheelchair" v="no"/>
</node>
<node id="689928445" lat="49.4799409" lon="8.4851357">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="gianlucascurti@ristorante-augusta.de"/>
<tag k="name" v="Ristorante Augusta"/>
<tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/>
<tag k="phone" v="0621 449872"/>
<tag k="website" v="ristorante-augusta.com/"/>
<tag k="wheelchair" v="no"/>
</node>
well you see that i have some questions
the first ones are regarding the "port-over-to Linux" and ... how to include the above mentioned osm-library.
the second one is regarding the variations in the mysql.txt - file - i.e. the different number of tags.; How to make the script robust so that it is able to work with this - and does not stopt to work....!?
I look forward to hear from you
many many greetings
Continue reading...
i have a xml-file that comes out of a osm-parser - it is formatted in xml:
which method fits if i want to store the output of this request to a mysql database: overpass turbo
see the output here
Code:
<node id="2064639440" lat="49.4873181" lon="8.4710548">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="turkish"/>
<tag k="email" v="info@lynso.de"/>
<tag k="name" v="Kilim - Café und Bar Restaurant"/>
<tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/>
<tag k="operator" v="Cengiz Kaya"/>
<tag k="phone" v="06 21 - 43 755 371"/>
<tag k="website" v="http://www.kilim-mannheim.de/"/>
</node>
<node id="2126473801" lat="49.4851170" lon="8.4756295">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="mannheim1@vapiano.de"/>
<tag k="fax" v="+49 621 1259 779"/>
<tag k="name" v="Vapiano"/>
<tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/>
<tag k="operator" v="Vapiano"/>
<tag k="phone" v="+49 621 1259 777"/>
<tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/>
<tag k="wheelchair" v="yes"/>
</node>
some questions
note; i run linux opensuse 13.1
- mysql is up and running
- the modules i have all
use DBI;
use XML::Twig;
... and i guess that i need furthermore another "library"
this library which is used in this line use OSM:
sm ; which is inspired by this page [note German language]: User:Brogo/OpenLayers Datenbankanbindung - OpenStreetMap Wiki and this ", $title, "
[This module contains a lot of useful functions for working with osm files and data. it also includes functions for calculation and output.]
User:Gary68 - OpenStreetMap Wiki
questions:
where to put this library: ", $title, " should i put it into the folder where i have the following perl scripts:
note: well what i have i have a folder
home/perl where i have the following perl code:
a. osm_to_db.pl
b. create_db.pl
second question: - i am sure that i have to do some corrections;
in the code create_db.pl
a. the first correction:
at the line $file = "c:/osm/planet/pois.osm" ;
b the second correction:
at the line "open(AUSGABE, ">c:/osm/planet/mysql.txt"); "
...since i have no windows but a linux-system....:
below we see the both mentioned script:
a. create_db.pl
Code:
#!/usr/bin/perl -w
use strict ;
use OSM:
sm ; my $file ;
my $nodeUser ;
my @nodeTags ;
my $nodeTags ;
my $ref1 ;
my $line ;
my $tag;
my $nodeName;
my $id ="1" ;
my $lat ;
my $lon ;
my $name ;
my $amenity ;
my $operator ;
my $vending;
$file = "c:/osm/planet/pois.osm" ;
openOsmFile ($file) ;
open(AUSGABE, ">c:/osm/planet/mysql.txt");
($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;
while ($id != -1 ) {
$name ="" ;
$amenity ="" ;
$operator ="" ;
$vending ="" ;
@nodeTags = @$ref1;
foreach my $tag (@nodeTags) {
if ($tag->[0] eq "name") { $name = scalar ($tag->[1] )};
if ($tag->[0] eq "amenity") { $amenity = scalar ($tag->[1] )};
if ($tag->[0] eq "operator") { $operator = scalar ($tag->[1] )};
if ($tag->[0] eq "vending") { $vending = scalar ($tag->[1] )}
}
if ($name ne "" | $amenity ne "" | $operator ne"" | $vending ne"")
{print AUSGABE "$id^$lat^$lon^$name^$amenity^$operator^$vending\n";}
($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;
}
close(AUSGABE);
closeOsmFile () ;
and furthermore: b. the code osm_to_db.pl
Code:
#!/usr/bin/perl
use strict ;
use DBI;
use XML::Twig;
# prepare database
my $dbh=dbh(); # connect
init();
$dbh->do('USE db123');
#$dbh->do('DELETE FROM pois');
# sql
my $sql = 'REPLACE INTO pois VALUES (?,?,?,?,?,?)';
my $sth = $dbh->prepare($sql);
# set up handler
my $t = XML::Twig->new(
twig_handlers => { 'node' => \&node }
);
# parse xml
my $xml = do { local $/; <DATA> };
$t->parse($xml);
#$t->parsefile('.osm');
sub node {
my ($t,$elt) = @_;
my %data=(
'id' => $elt->att('id'),
'lat' => $elt->att('lat'),
'lon' => $elt->att('lon'),
);
for my $tag ( $elt->children() ){
$data{$tag->att('k')} = $tag->att('v');
#print $tag->att('k').' = '.$tag->att('v')."\n";
}
# update database
my @f = map{ $data{$_} }('id','lat','lon','name','amenity','operator');
if ($f[3] ne '' && $f[4] ne '' && $f[5] ne ''){
print "-- INSERT --\n".
(join "\n",@f).
"\n\n";
$sth->execute(@f);
}
}
sub init {
$dbh-> do('CREATE DATABASE IF NOT EXISTS db123
DEFAULT CHARACTER SET latin1
COLLATE latin1_german2_ci');
$dbh->do('USE db123');
$dbh->do('CREATE TABLE IF NOT EXISTS pois (
id BIGINT(20) UNSIGNED NOT NULL,
lat FLOAT(10,7) NOT NULL,
lon FLOAT(10,7) NOT NULL,
name VARCHAR(255) COLLATE utf8_bin NOT NULL,
amenity VARCHAR(255) COLLATE utf8_bin NOT NULL,
operator VARCHAR(255) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT
CHARSET=utf8
COLLATE=utf8_bin');
}
sub dbh {
my $dsn = "DBI:mysql:database=;host=localhost";
my $dbh = DBI->connect($dsn, 'user', 'pwd',
{RaiseError => 1, PrintError => 1})
or die (Error connecting " $DBI::errstr");
}
see the dataset; - which is stored in the file mysql.txt
the dataset - it is gathered from the request on the overpass-api which resides here overpass turbo
cf. overpass turbo
you see a request on the left part of the screen
note: to get the output - just press the button in the top-menu called "Ausführen"
after this you press the button called "DATEN" on the top-right -
just below the green button called "flatter this": after pressing this "DATEN"-button you see the data in the right window of the screen.
note - it has got various ids - that means that the osm-file does not give back constantly all the tags...
the last question; does this make any problems to our project - does this has any influence on our db-connection...!?!?
see the output here:
Code:
<node id="2064639440" lat="49.4873181" lon="8.4710548">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="turkish"/>
<tag k="email" v="info@lynso.de"/>
<tag k="name" v="Kilim - Café und Bar Restaurant"/>
<tag k="opening_hours" v="Su-Th 17:00-1:00; Fr, Sa 17:00-3:00"/>
<tag k="operator" v="Cengiz Kaya"/>
<tag k="phone" v="06 21 - 43 755 371"/>
<tag k="website" v="http://www.kilim-mannheim.de/"/>
</node>
<node id="2126473801" lat="49.4851170" lon="8.4756295">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="mannheim1@vapiano.de"/>
<tag k="fax" v="+49 621 1259 779"/>
<tag k="name" v="Vapiano"/>
<tag k="opening_hours" v="Su-Th 10:00-24:00; Fr-Sa 10:00-01:00"/>
<tag k="operator" v="Vapiano"/>
<tag k="phone" v="+49 621 1259 777"/>
<tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/>
<tag k="wheelchair" v="yes"/>
</node>
<node id="667927886" lat="49.4909673" lon="8.4764904">
<tag k="addr:city" v="Mannheim"/>
<tag k="addr:country" v="DE"/>
<tag k="addr:housenumber" v="5"/>
<tag k="addr
ostcode" v="68161"/><tag k="addr:street" v="Collinistraße"/>
<tag k="amenity" v="restaurant"/>
<tag k="name" v="Churrascaria Brasil Tropical"/>
<tag k="phone" v="+496211225596"/>
<tag k="wheelchair" v="limited"/>
</node>
<node id="689928440" lat="49.4798794" lon="8.4853418">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="greek"/>
<tag k="email" v="epirus70@hotmail.de"/>
<tag k="fax" v="0621/4407 762"/>
<tag k="name" v="Epirus"/>
<tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/>
<tag k="phone" v="0621/4407 761"/>
<tag k="smoking" v="separated"/>
<tag k="website" v="http://epirus-ma.blogspot.com/"/>
<tag k="wheelchair" v="no"/>
</node>
<node id="689928445" lat="49.4799409" lon="8.4851357">
<tag k="amenity" v="restaurant"/>
<tag k="cuisine" v="italian"/>
<tag k="email" v="gianlucascurti@ristorante-augusta.de"/>
<tag k="name" v="Ristorante Augusta"/>
<tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/>
<tag k="phone" v="0621 449872"/>
<tag k="website" v="ristorante-augusta.com/"/>
<tag k="wheelchair" v="no"/>
</node>
well you see that i have some questions
the first ones are regarding the "port-over-to Linux" and ... how to include the above mentioned osm-library.
the second one is regarding the variations in the mysql.txt - file - i.e. the different number of tags.; How to make the script robust so that it is able to work with this - and does not stopt to work....!?
I look forward to hear from you
many many greetings
Continue reading...