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).

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

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

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

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

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

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.

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.

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

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

Take the pseudocode given to you, implement this in perl, test it. This is not a code writing service.