Looks pretty good to me. Might want to add 'use warnings;' after your 'use strict;'. Have to assume that 'use OSM::osm;' is a module you got from somewhere? I did not see it on CPAN, though I did see a number of GEO::OSM::* modules that look like they might be useful to you, including what appears to be a parser -> Here.

Am thinking that you will want to include the DBD::mysql and company when you start pumping those variable into your database.

Not exactly sure what you are looking for,(what is the question specifically)? 'Does this fit?' is a little bit open ended...

hello daar wjw many thanks for the quick reply

great to hear from you:

see the source of the perlmodule: http://svn.openstreetmap.org/applications/utils/gary68/OSM/osm.pm

wHAT IS AIMED: aimed is to transform POI out of German osm-pbf-files - in order to get the data (not to creat maps again)

see the source (s)= http://download.geofabrik.de/europe/germany.html ranging form 10 MB (Bremen) to 390 MB (Nordrhein Westfalen) the osm.pbf-files are not too big; Question: which method is the best the most appropiate? - to store the results in a mysql-db or just have big calc-sheets (with csv-data)

- only straightforward from files that

- no backimport of data to OSM from a .csv file btw: see here - a similar approach of a guy - he only looks for a list of brides

see the full overview: A self-updating OpenStreetMap database of US bridges – a step-by-step guide. http://oegeo.wordpress.com/2012/03/06/a-self-updating-openstreetmap-database-of-us-bridges-a-step-by-step-guide/

well i am pretty sure that the link to cpan gives back some very nice tools
- update: by the way - one last question



update. which method fits if i want to store the output of this request to a mysql database:


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



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



how can this be done? note we have the fild names and all the set of the datbase is clear. the question is - how to put the data into the database.....? <br

look forward to hear from you

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

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 - good morning ;<)

the script we discussed 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

Without regard to the correctness or fitness of your code for your purpose, there is scope for a little tidying up. Consider:

#!/usr/bin/perl

use strict;
use warnings;
use OSM::osm;

my @fieldNames = qw(name amenity operator vending);
openOsmFile($file);

do {
    my ($id, $lon, $lat, $nodeUser, $ref1) = getNode2();
    
    last if ! defined $id;

    my %fields;

    $fields{$_->[0]} = scalar $_->[1] for @$ref1;
    next if !grep {exists $fields{$_}} @fieldNames;
    print join ('^', $id, $lat, $lon, @fields{@fieldNames}), "\n";
} while (1);

closeOsmFile();
[download]

As this is a test script the output is to the console rather than to a file. Needless to say the script is untested!

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