run XML::Twig & DBI for storing xml-file into a myql-db

dilbertone · May 30, 2014, 5:44pm

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


<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::osm ; which is inspired by this page [note German language]: User:Brogo/OpenLayers Datenbankanbindung - OpenStreetMap Wiki

and this ", $title, "](http://svn.openstreetmap.org/applications/utils/gary68/OSM/osm.pm)
[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, "](http://svn.openstreetmap.org/applications/utils/gary68/OSM/osm.pm) 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**

 
#!/usr/bin/perl -w 
  
use strict ; 
use OSM::osm ; 
  
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
";} 
        ($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ; 
  
} 
close(AUSGABE); 
closeOsmFile () ;

and furthermore: **b. the code osm_to_db.pl **


#!/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')."
"; 
  } 
  
  # update database 
  my @f = map{ $data{$_} }('id','lat','lon','name','amenity','operator'); 
  if ($f[3] ne '' && $f[4] ne '' && $f[5] ne ''){ 
    print "-- INSERT --
". 
    (join "
",@f). 
    "

"; 
    $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:



<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:postcode" 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

dilbertone · June 1, 2014, 8:21pm

btw: since i run linux - i have to rewrite the lines in the create_db.pl -,


$file = "c:/osm/planet/pois.osm" ; 
  
openOsmFile ($file) ; 
open(AUSGABE, ">c:/osm/planet/mysql.txt"); 
($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;

well i guess that i just have to change the mentioned lines to



$file = "pois.osm" ; open(AUSGABE, ">mysql.txt"); 
$file = "pois.osm" ;

open(AUSGABE, ">mysql.txt");

subsequently i ought to put the pois.osm file in with create_db.pl.

But i think as long as i am in the early stages of testing, it should be okay to create a folder /home/perl/OSM and put the osm.pm into it!?

BTW: Looks we also need osmDB.pm in there too, don´ t we!?

Guess that we also need to install Compress::Bzip2