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

May 31, 2014

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>

</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, "

[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::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\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>

</node>

</node>

</node>

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

Sign In

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

Recommended Posts

Guest sayhello

Join the conversation

Browse

Activity

Posting Guidelines

Store

Support