Re^3: from osm to mysql

how to put the data into the database.....?

#!/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);
  }
}
[download]

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");
}

__DATA__
<?xml version='1.0' encoding='UTF-8'?>
<osm>
<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  - Cafe 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>
  </osm>
[download]

I hope this answers your question.
poj

Comment on Re^3: from osm to mysql Select or Download Code

Replies are listed 'Best First'.
Re^4: from osm to mysql by Perlbeginner1 (Scribe) on May 24, 2014 at 15:37 UTC
hello dear poi many many thanks for all your ideas and help regarding the connection of the database!!! this is great! of course - i know that postgresql is THE mandantory standard i am willing to do all in postgresql. i have some experience in MySQL - and i am now willing to do all what is needed to do work with postgres note; i do not need the spatial - featuees (the options of creating maps.) i only want to export POI from the planet file. later the weekend i will have a closer look at the codesnippets and will come back and report all my experiecne Again - many many thanks greetings ps - i am very very happy about this great forums - it rocks and you do a superb work !!!! Community at its best"!!	[reply]
Re^4: from osm to mysql:: with insert-statement by Perlbeginner1 (Scribe) on May 29, 2014 at 15:05 UTC
hello dear Poi again many many thanks - well - very convincing - since i want to put all the data ( that i have in a xml-file) into the mysql db - i need to have a INSERT-Statement. where to place this INSERT-statement!? look forward to hear from you	[reply]
Re^5: from osm to mysql:: with insert-statement by poj (Abbot) on May 29, 2014 at 15:31 UTC
REPLACE is same as INSERT except if the record already exists it updates it rather than failing with a duplicate record error. This is useful when testing and running the same data in many times. Change this line to INSERT if you want to. `my $sql = 'REPLACE INTO pois VALUES (?,?,?,?,?,?)';` [download] poj	[reply] [d/l]
Re^6: from osm to mysql:: with insert-statement by Perlbeginner1 (Scribe) on May 29, 2014 at 15:59 UTC
good day - many many thanks great to hear from you - i am allready testin the script and it does work - it does store the data in the mysql db many many thanks	[reply]
Re^4: from osm to mysql by Perlbeginner1 (Scribe) on Jun 01, 2014 at 07:54 UTC
hello dear Poi- good morning ;<) the script we discussed (see above) was written for windows - see the corresponding line with the paths... #!/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"" \| $vendi +ng ne"") {print AUSGABE "$id^$lat^$lon^$name^$amenity^$operator^$ve +nding\n";} ($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ; } close(AUSGABE); closeOsmFile () ; [download] how would you say - does this fit for linux - no. We have to do some corrections first. note; i run linux opensuse 13.1 - mysql is up and running - the modules i have all `use DBI; use XML::Twig;` [download] ... 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: url=http://wiki.openstreetmap.org/wiki/User:Brogo/OpenLayers_DatenbankanbindungUser:Brogo/OpenLayers Datenbankanbindung - OpenStreetMap Wiki/url and this url=http://svn.openstreetmap.org/applications/utils/gary68/OSM/osm.pm", $title, "/url This module contains a lot of useful functions for working with osm files and data. it also includes functions for calculation and output. url=http://wiki.openstreetmap.org/wiki/User:Gary68User:Gary68 - OpenStreetMap Wiki/url bquestions: /b where to put this library: url=http://svn.openstreetmap.org/applications/utils/gary68/OSM/osm.pm", $title, "/url 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 bsecond question:/b - 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....: look forward to hear from you	[reply] [d/l] [select]
Re^5: from osm to mysql by poj (Abbot) on Jun 01, 2014 at 14:07 UTC
Just change the lines to `$file = "pois.osm" ; open(AUSGABE, ">mysql.txt");` [download] and put the `pois.osm` file in with `create_db.pl`. While you are in the early stages of testing, create a folder `/home/perl/OSM` and put the `osm.pm` in it. Looks like you also need `osmDB.pm` in there too. You will also need to install `Compress::Bzip2` poj	[reply] [d/l] [select]
Re^6: from osm to mysql by Perlbeginner1 (Scribe) on Jun 01, 2014 at 18:11 UTC
hello dear poi many many thanks for all the hints. Great to hear from you. you are very very supportive. That is so overwhelming,. many thanks again - greetings perlbeginner1	[reply]