Re^2: from osm to mysql

[Perlbeginner1]

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

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="[e-mail]"/>
    <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="[e-mail]"/>
    <tag k="fax" v="[phone]"/>
    <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="[phone]"/>
    <tag k="website" v="http://www.vapiano.de/newsroom/?store=29"/>
    <tag k="wheelchair" v="yes"/>
  </node>

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

[erix]

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

You mention (link 1):

#
# Copyright (C) 2008, 2009, 2010 Gerhard Schwanz
#

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

[poj]

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

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

[Perlbeginner1]

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

[Perlbeginner1]

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

[poj]

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 (?,?,?,?,?,?)';

poj

[Perlbeginner1]

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 () ;



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;

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

[poj]

Just change the lines to

$file = "pois.osm" ;
open(AUSGABE, ">mysql.txt");

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

[wjw]

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;

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

[Perlbeginner1]

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

[marto]

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