http://overpass-turbo.eu/?q=PCEtLQpUaGlzIHF1ZXJ5IGxvb2vEiGZvciBub2Rlcy
+wgd2F5xIhhbmQgcmVsYXRpb27EiAp3aXRoIMS0ZSBnaXZlbiBrZXkvdmFsxIsgY29tYml
+uxKvErW4uCkNoxJFzxLh5b3XEl8SoxLrEriDEpMSmxIZ0xLZoxLhSdcS-YnV0dMWfYWJv
+xLwhCsSCPgp7e8WAeT1hbcS9xLN5fX3FuHvFhMWGZT3EqHN0YcWbxKR0xoMKPG9zbS1zY
+3JpcMWkxZp0cMWsPSJ4bWwixbcgIDzFqcWQxqnGqsarxIrEjMSOdHlwxokixJnEm8aoCs
+awxrA8aGFzLWt2xL_Go8W5xbvGgyIgdseIxobFhcSLx4svxq_GvjxixbF4LcayxI0gxbn
+HmG94xoPHlMa9xr8vx5x5x5XGscSLx53Gtca3xqPEoHnGvMa-xqrHgMeCx4THhmvHj8eK
+fceMx44ixbnGh8eSx73HpMe0xqvHoMeax6jHnnvIiMejx6o8x6fHrMepx6XHtciKx67Gu
+MSoxKrErMSux7PHtMe2x4PHhceHyIDFusWBx4vHjcePyIJlx5PHqse1yIjHm8iSyIvIjX
+3Ihcemx6jGr8iQxq3Ersi3cMabbsWkbcSaxrjFsWTHssi0PMSoY8WbxZfEtsa2xrhkb3d
+uIsmGyLzFjci_yYHGo3PFgGxlxa7JksekyJDGlcaXxpnGm8adPg&c=BNJBWRBb1P
[download]

<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>
[download]

Just FYI: OpenStreetMap switched from Mysql to PostgreSQL (postgresql), a few years ago.

You mention (link 1):

#
# Copyright (C) 2008, 2009, 2010 Gerhard Schwanz
#
[download]

and in link 2:

"I want all bridges in the US that are mapped in OpenStreetMap in a PostGIS database"

So, of the two links you gave, one is from before that database switch to postgres, and the other explicitly mentions part of the postgres ecosystem (PostGIS: http://postgis.org/).

No doubt, it is possible to use a database other than postgres but it might be something to be aware of (esp. when using some of the osm tools).

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]

Read more... (3 kB)
I hope this answers your question.
poj

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"!!

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

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

This is interesting, thanks for the background. I have only looked as OSM from the point of view of end user apps at this point(android), so the links and info you provide are appreciated.

In answer to your first question: My preference is to use a database if at all possible. For smaller projects I like SQLite, as it is very portable, with the whole data base stored in one file. That limits it in size, but not terribly. I have no porblem sticking 500k records in an SQLite database. If it is likely that your going to have a much larger database, the MySQL is my preference. As erix pointed out, it looks like osm is leaning toward Postres. Postgres is fine, but I worked with it only once years ago, so I am somewhat abivilant towards it. I am sure it would work fine.

As far as methodology goes: You already have your parser pretty much worked out as close as I can tell. I would take the script that you already have, add the DBI/DBD for your chosen database(does not matter from the Perl/DBI standpoint), and then write the rather short code to take your parsed data and insert it to the database. You are really already half done.

You can use SuperSearch and the POD for your chosen database driver, to come up with examples. In simplistic psudo-code it looks a bit like the following:

use DBD::mysql;
set up connection (database, user, password)
establish connection
establish routine to accept parsed values and insert to db
run the routine for all your records
disconnect from the db;
[download]

Again, I hope you find this somewhat helpful...

...the majority is always wrong, and always the last to know about it...
Insanity: Doing the same thing over and over again and expecting different results...
wjw

hello dear wjw

again many many thanks for all your hints; first of all i like the idea of sqlite i have to digg deeper into that next time

your explanations were very convincing


At the moment i am willing to store all the data into the MySQL-DB.

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

#!/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]

use DBI; 
use XML::Twig;
[download]