CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Beginner:
adding 100 000 lines of xml to db - with dbi

 



dilbert
User

Jul 16, 2014, 12:01 PM

Post #1 of 12 (1942 views)
adding 100 000 lines of xml to db - with dbi Can't Post

hello dear perl-gurus

have done a request to overpass-api.de - regarding the planet-file. (note: i have done the requests here at http://overpass-api.de/query_form.html


i get the follwing data - that can be seen below; this has to be added to a db - how to do that!?

with perl::XML::Twig

</way>


<way id="292712179" version="1" timestamp="2014-07-14T11:29:26Z" changeset="24138442" uid="626239" user="FranoiseR">
<nd ref="2962460710"/>
<nd ref="2962460709"/>
<nd ref="2962460708"/>
<nd ref="2962460707"/>
<nd ref="2962460706"/>
<nd ref="2962460703"/>
<nd ref="2962460704"/>
<nd ref="2962460705"/>
<nd ref="1923686106"/>
<nd ref="2962460712"/>
<nd ref="2962460713"/>
<nd ref="2962460719"/>
<nd ref="2962460720"/>
<nd ref="1923686117"/>
<nd ref="1923686120"/>
<nd ref="832922814"/>
<nd ref="2962460710"/>
<tag k="amenity" v="school"/>
<tag k="name" v="cole maternelle Saint-Joseph Les Micocouliers"/>
<tag k="ref:UAI" v="0132189J"/>
<tag k="school:FR" v="maternelle"/>
<tag k="source" v="data.gouv.fr:Ministre de l'ducation nationale, de la Jeunesse et de la Vie associative - 05/2012"/>
</way>
<way id="292712180" version="1" timestamp="2014-07-14T11:29:26Z" changeset="24138442" uid="626239" user="FranoiseR">
<nd ref="2962460714"/>
<nd ref="2962460715"/>
<nd ref="2962460717"/>
<nd ref="833156863"/>
<nd ref="2962460721"/>
<nd ref="1923686120"/>
<nd ref="833208582"/>
<nd ref="832922814"/>
<nd ref="2962460718"/>
<nd ref="2962460710"/>
<nd ref="2962460714"/>
<tag k="amenity" v="school"/>
<tag k="name" v="cole primaire Saint-Joseph Les Micocouliers"/>
<tag k="ref:UAI" v="0132192M"/>
<tag k="school:FR" v="lmentaire"/>
<tag k="source" v="data.gouv.fr:Ministre de l'ducation nationale, de la Jeunesse et de la Vie associative - 05/2012"/>
</way>
<way id="292720117" version="2" timestamp="2014-07-14T12:34:27Z" changeset="24139706" uid="1959576" user="mont1">
<nd ref="2780077861"/>
<nd ref="2962532368"/>
<nd ref="2780077868"/>
<nd ref="2780077861"/>
<tag k="amenity" v="school"/>
<tag k="name" v="cole Maternelle Publique"/>
<tag k="ref:UAI" v="0311679G"/>
<tag k="school:FR" v="maternelle"/>
<tag k="source" v="data.gouv.fr:Ministre de l'ducation nationale, de la Jeunesse et de la Vie associative - 05/2012"/>
</way>
<way id="292720198" version="1" timestamp="2014-07-14T12:34:28Z" changeset="24139707" uid="2175149" user="hesdrib">
<nd ref="2962537963"/>
<nd ref="2962537964"/>
<nd ref="2962537965"/>
<nd ref="2962537966"/>
<nd ref="2962537963"/>
<tag k="amenity" v="school"/>
<tag k="name" v="Ecole d'Xhoffraix"/>
</way>
<way id="292728376" version="1" timestamp="2014-07-14T13:27:36Z" changeset="24140524" uid="436930" user="StevePO16">
<nd ref="2962601262"/>
<nd ref="2962601265"/>
<nd ref="2962601261"/>
<nd ref="2962601264"/>
<nd ref="2962601263"/>
<nd ref="2962601262"/>
<tag k="amenity" v="school"/>
<tag k="name" v="St Pauls RC Primary School"/>
</way>
<way id="292730201" version="1" timestamp="2014-07-14T13:39:45Z" changeset="24140963" uid="251029" user="Voidoid">
<nd ref="2962615920"/>
<nd ref="2962615921"/>
<nd ref="2962615922"/>
<nd ref="2962615923"/>
<nd ref="2962615924"/>
<nd ref="2962615925"/>
<nd ref="2962615926"/>
<nd ref="2962615927"/>
<nd ref="2962615928"/>
<nd ref="2962615920"/>
<tag k="amenity" v="school"/>
<tag k="name" v="IES Esteban Manuel de Villegas"/>
</way>

</osm>


this is a planetfile of openstreetmap:

well how to add all the data - approx 100 000 lines to a mysqldb


(This post was edited by dilbert on Jul 16, 2014, 2:25 PM)


bulrush
Novice

Jul 16, 2014, 12:09 PM

Post #2 of 12 (1939 views)
Re: [dilbert] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

Please provide all relevant table layouts for your database.

I'm currently trying to figure out how to get Perl to access a Postgres database on another machine myself. So far I have these steps:

  • Install Postgresql lib files. I'm not sure if this will install dependencies or not, or if it will "upgrade" current library files as we have mission critical apps on this machine.
  • Install Perl DBI module via cpanm.
  • Install Perl DBD::Pg module via cpanm.
    -----
    * Redhat Linux RHEL 5.5.56
    * Perl 5.8.8


  • Laurent_R
    Veteran / Moderator

    Jul 16, 2014, 1:26 PM

    Post #3 of 12 (1933 views)
    Re: [dilbert] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    Do you have the DBI module and the mysql database driver already installed on your platform?


    dilbert
    User

    Jul 16, 2014, 2:04 PM

    Post #4 of 12 (1932 views)
    Re: [Laurent_R] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    hello you both hello dear Laurent and hello dear bulrush, great to hear from you!


    many many thanks for the quick answer: Great to hear from you. At the moment i am short of time - but i am starting with a short answer:;


    on my opensuse version 13.1 i have up and running perl and a mysql db is also up and running...

    Note: i have mysql db up and running on my opensuse but i am willing to do it in postgresql - i whold be amazed if we go the postgresql-way

    i want to gather data of schools from southamerica

    well at the moment i only need a short extract of the whole set of shools that are available at this downloadcenter:http://download.geofabrik.de/south-america.html

    https://help.openstreetmap.org/questions/34477/gather-the-latest-records-with-osmconvert ) .


    a want to gather all the newest records - of south america with the tag amenity=School - / combined with the following condition
    b and and all that have a website....
    c. i am only interested in POIs - points of interest - in other words that are textual data - i du not want to remapp the data - as a backward engineering back to
    get maps out of it.

    all together i have used some techniques - as the following

    - overpass-API,
    - the osmconvert-tool (Manual: m.m.i24.cc/osmconvert.c )
    - the osmfilter-tool ( Manual http://m.m.i24.cc/osmfilter.c )
    - the opaQuery.pl - a very poweful Perl-Programme (http://http://www.easyclasspage.de/maptools/seite-2.html - note: with this you can get great results - ready to store in db )


    well - regarding the dataset:


    i do not want to have the nodes - i a mnot interested in remapping the data back to the mapsl


    see the dataset i need to store: gathered by doing requests at http://overpass-api.de/query_form.html

    see the exampledataset 1: shools in Argentina - which have been added / edited at 1.6.2014:

    [date:"2014-06-01T00:00:00Z"];area[name="Argentina"][boundary=administrative]->.a;(node(area.a)[amenity=school];way(area.a)[amenity=school];);out center;

    exampledataset 2: changes on Schools in Argentinia since 1.7.2014 (with erased Objekts)

    [adiff:"2014-07-01T00:00:00Z"];area[name="Argentina"][boundary=administrative]->.a;(node(area.a)[amenity=school];way(area.a)[amenity=school];);out center;


    by the way; i have pasted a exampledataset here:
    http://pastebin.com/mq5kAHyy - here you can see a datset: note; i only need to have the POIs - not the overload of data...

    in ohter words: i need the values of the following tags

    timestamp (important to me)
    name of the school
    Country
    Adress:
    Postal-Code
    Town
    Street
    Housenumber
    Website (important for me.)
    e-mail-adress


    the rest - it is nice to have - but not mandantory and so i would be happy if i have the above data... at least

    conclusion: so we have the dataset - that needs to be stored in mysql or postgresql


    i will answer more and give you more details tomorrow


    again Laurent - all the necessary things are on the opensuse allready in place.


    - perl
    - the mysql-db is up and running
    - perl::DBI is allready on the machine


    now i have to create the tables and do the rest ... to insert the example-dataset.... love to hear from you

    greetings
    dilbert

    ps: Technologically and methodolocically i am inspired by Martijn van Exel approach to keep an overview on the US-bridges note;: he looks for the bridges in the US ( http://oegeo.wordpress.com/2012/03/06/a-self-updating-openstreetmap-database-of-us-bridges-a-step-by-step-guide/ )


    (This post was edited by dilbert on Jul 16, 2014, 2:27 PM)


    bulrush
    Novice

    Jul 17, 2014, 3:44 AM

    Post #5 of 12 (1910 views)
    Re: [dilbert] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    Someone answered my question and suggested I do this:


    Code
    $ sudo yum install perl-DBD-Pg 

    ... Lots of output snipped ...

    Dependencies Resolved

    ================================================================================
    Package Arch Version Repository Size
    ================================================================================
    Installing:
    perl-DBD-Pg x86_64 1.49-4.el5_8 centos-update 115 k
    Installing for dependencies:
    postgresql-libs x86_64 8.1.23-6.el5_8 centos-update 197 k

    Transaction Summary
    ================================================================================
    Install 2 Package(s)
    Upgrade 0 Package(s)

    Total download size: 312 k
    Is this ok [y/N]:


    It will install the PosgreSql libraries and the perl DBD-Pg driver. Perl also needs the DBI module. Since I'm just learning myself how to install the Postgre libraries, my help is limited, but I can share what I've learned.

    I'm still trying to figure out if installing the Postgre libraries will clobber anything else I have on this Linux machine.
    -----
    * Redhat Linux RHEL 5.5.56
    * Perl 5.8.8

    (This post was edited by bulrush on Jul 17, 2014, 3:46 AM)


    FishMonger
    Veteran / Moderator

    Jul 17, 2014, 6:25 AM

    Post #6 of 12 (1905 views)
    Re: [dilbert] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    dilbert,

    Don't expect us to write your script for you. perlguru is not a code writing service. If you need that, you should hire a programmer to do this script for you.

    If you plan on writing your own script, then you need to post your code and a specific question on the part that is giving you trouble. Include any/all errors and warnings that your code produces and explain how it's failing to accomplish your needs.


    dilbert
    User

    Jul 20, 2014, 2:13 PM

    Post #7 of 12 (1708 views)
    Re: [FishMonger] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    hello dear bulrush, hello dear Fishmonger

    first of all many many thanks for the mail - great to hear from you..


    some additional questions note; i run linux opensuse 13.1

    - mysql is up and running
    - the modules i have all

    well i am workin with XML::Twig

    see the following modules i use - with another example:

    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]: http://wiki.openstreetmap.org/wiki/User:Brogo/OpenLayers_Datenbankanbindung

    and this http://svn.openstreetmap.org/applications/utils/gary68/OSM/osm.pm
    [This module contains a lot of useful functions for working with osm files and data. it also includes functions for calculation and output.]
    http://wiki.openstreetmap.org/wiki/User:Gary68


    questions:


    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



    below we see the both mentioned script:

    a. create_db.pl


    Code
      
    #!/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 = "/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"" | $vending ne"")
    {print AUSGABE "$id^$lat^$lon^$name^$amenity^$operator^$vending\n";}
    ($id, $lon, $lat, $nodeUser, $ref1) = getNode2 () ;

    }
    close(AUSGABE);
    closeOsmFile () ;



    and furthermore: b. the code osm_to_db.pl


    Code
    #!/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);
    }
    }


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


    see the dataset; - which is stored in the file mysql.txt

    the dataset - it is gathered from the request on the overpass-api which resides here http://www.overpass-turbo.eu

    cf. http://overpass-turbo.eu/?q=PCEtLQpUaGlzIHF1ZXJ5IGxvb2vEiGZvciBub2Rlcywgd2F5xIhhbmQgcmVsYXRpb27EiAp3aXRoIMS0ZSBnaXZlbiBrZXkvdmFsxIsgY29tYmluxKvErW4uCkNoxJFzxLh5b3XEl8SoxLrEriDEpMSmxIZ0xLZoxLhSdcS-YnV0dMWfYWJvxLwhCsSCPgp7e8WAeT1hbcS9xLN5fX3FuHvFhMWGZT3EqHN0YcWbxKR0xoMKPG9zbS1zY3JpcMWkxZp0cMWsPSJ4bWwixbcgIDzFqcWQxqnGqsarxIrEjMSOdHlwxokixJnEm8aoCsawxrA8aGFzLWt2xL_Go8W5xbvGgyIgdseIxobFhcSLx4svxq_GvjxixbF4LcayxI0gxbnHmG94xoPHlMa9xr8vx5x5x5XGscSLx53Gtca3xqPEoHnGvMa-xqrHgMeCx4THhmvHj8eKfceMx44ixbnGh8eSx73HpMe0xqvHoMeax6jHnnvIiMejx6o8x6fHrMepx6XHtciKx67GuMSoxKrErMSux7PHtMe2x4PHhceHyIDFusWBx4vHjcePyIJlx5PHqse1yIjHm8iSyIvIjX3Ihcemx6jGr8iQxq3Ersi3cMabbsWkbcSaxrjFsWTHssi0PMSoY8WbxZfEtsa2xrhkb3duIsmGyLzFjci_yYHGo3PFgGxlxa7JksekyJDGlcaXxpnGm8adPg&c=BNJBWRBb1P


    you see a request on the left part of the screen

    note: to get the output - just press the button in the top-menu called "Ausführen"

    after this you press the button called "DATEN" on the top-right -
    just below the green button called "flatter this": after pressing this "DATEN"-button you see the data in the right window of the screen.

    note - it has got various ids - that means that the osm-file does not give back constantly all the tags...

    the last question; does this make any problems to our project - does this has any influence on our db-connection...!?!?

    see the output here:


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


    <node id="667927886" lat="49.4909673" lon="8.4764904">
    <tag k="addr:city" v="Mannheim"/>
    <tag k="addr:country" v="DE"/>
    <tag k="addr:housenumber" v="5"/>
    <tag k="addr:postcode" v="68161"/>
    <tag k="addr:street" v="Collinistraße"/>
    <tag k="amenity" v="restaurant"/>
    <tag k="name" v="Churrascaria Brasil Tropical"/>
    <tag k="phone" v="+496211225596"/>
    <tag k="wheelchair" v="limited"/>
    </node>
    <node id="689928440" lat="49.4798794" lon="8.4853418">
    <tag k="amenity" v="restaurant"/>
    <tag k="cuisine" v="greek"/>
    <tag k="email" v="epirus70@hotmail.de"/>
    <tag k="fax" v="0621/4407 762"/>
    <tag k="name" v="Epirus"/>
    <tag k="opening_hours" v="Mo-Sa 12:00-15:00,18:00-24:00"/>
    <tag k="phone" v="0621/4407 761"/>
    <tag k="smoking" v="separated"/>
    <tag k="website" v="http://epirus-ma.blogspot.com/"/>
    <tag k="wheelchair" v="no"/>
    </node>
    <node id="689928445" lat="49.4799409" lon="8.4851357">
    <tag k="amenity" v="restaurant"/>
    <tag k="cuisine" v="italian"/>
    <tag k="email" v="gianlucascurti@ristorante-augusta.de"/>
    <tag k="name" v="Ristorante Augusta"/>
    <tag k="opening_hours" v="Mo-Fr 12:00-14:00,18:00-23:00;Su 12:00-14:00,18:00-23:00"/>
    <tag k="phone" v="0621 449872"/>
    <tag k="website" v="ristorante-augusta.com/"/>
    <tag k="wheelchair" v="no"/>
    </node>



    well you see that i have some questions: is it more clever to run this on postgresql? I am willing to do so...

    note: this was only an example ... as mentined above i am interested in gathering the data of south american shools



    I look forward to hear from you

    many many greetings

    dilbert Wink


    dilbert
    User

    Aug 1, 2014, 1:03 AM

    Post #8 of 12 (1211 views)
    Re: [dilbert] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    hello and good day

    i tried alot - sometimes it works - and wirth slightly different datas it do nt work.

    q: how to make this robust?

    look forward to any and all help


    Laurent_R
    Veteran / Moderator

    Aug 1, 2014, 9:47 AM

    Post #9 of 12 (1198 views)
    Re: [dilbert] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    Well, what do you mean by saying that sometimes it does not work? In which way it does not work? Does it crash? Does it do nothing? Does is do something different from what you expect? Does it do only one part of the work? Which part does not work (if you know)?

    Please help up by giving information which might enable us to help you.


    dilbert
    User

    Aug 6, 2014, 7:57 AM

    Post #10 of 12 (1153 views)
    Re: [Laurent_R] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    hello dear Laurent_R


    first of all - many many thanks for the reply.

    as mentioned before: very very helpful your answer. Youre very supportive and all your postings are a valuable asset of knowledge. Ready to apply in my work.

    again: Many thanks!!

    sorry for the delay - i am in holiday - and i have a very very weak interntet-connection here:


    In Reply To
    Well, what do you mean by saying that sometimes it does not work? In which way it does not work? Does it crash? Does it do nothing? Does is do something different from what you expect? Does it do only one part of the work? Which part does not work (if you know)?

    Please help up by giving information which might enable us to help you.



    have the scripts:

    a, perl_db.pl
    this was named ( create_db.pl )


    and furthermore:
    b. guru_osm.pl
    this was named ( the code osm_to_db.pl )




    after running the first script i got back the following error:



    Code
    martin@linux-70ce:~/perl/guru> perl guru.db.pl 
    Can't open perl script "guru.db.pl": Datei oder Verzeichnis nicht gefunden
    martin@linux-70ce:~/perl/guru> perl guru_db.pl
    Can't locate OSM/osm.pm in @INC (you may need to install the OSM::osm module) (@INC contains: /usr/lib/perl5/site_perl/5.18.1/i586-linux-thread-multi /usr/lib/perl5/site_perl/5.18.1 /usr/lib/perl5/vendor_perl/5.18.1/i586-linux-thread-multi /usr/lib/perl5/vendor_perl/5.18.1 /usr/lib/perl5/5.18.1/i586-linux-thread-multi /usr/lib/perl5/5.18.1 /usr/lib/perl5/site_perl .) at guru_db.pl line 8.
    BEGIN failed--compilation aborted at guru_db.pl line 8.


    what can i do here - well i am wondering what goes wrong here....

    look forward to any and alll help


    (This post was edited by dilbert on Aug 6, 2014, 7:59 AM)


    Laurent_R
    Veteran / Moderator

    Aug 6, 2014, 10:21 AM

    Post #11 of 12 (1146 views)
    Re: [dilbert] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

    The first message is pretty clear: perl does not find the "guru.db.pl" script in the directory that you gave (or in the current directory if you did not specify any directory). Perhaps you are in the wrong directory, or perhaps you've got a wrong filename for your script.

    The second message indiucates that you are trying to use the OSM module, but that it is not installed on your system. So you probably need to install it.


    dilbert
    User

    Aug 6, 2014, 11:37 PM

    Post #12 of 12 (1133 views)
    Re: [Laurent_R] adding 100 000 lines of xml to db - with dbi [In reply to] Can't Post

     





    thx alot

    you helped me. the code - (i renamed it ) gave back



    Code
     

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


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



    gave back this


    Code
     
    e_perl .) at guru_db.pl line 8.
    BEGIN failed--compilation aborted at guru_db.pl line 8.
    martin@linux-70ce:~/perl/guru> perl prepare_db.pl
    install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (you may need to install the DBD::mysql module) (@INC contains: /usr/lib/perl5/site_perl/5.18.1/i586-linux-thread-multi /usr/lib/perl5/site_perl/5.18.1 /usr/lib/perl5/vendor_perl/5.18.1/i586-linux-thread-multi /usr/lib/perl5/vendor_perl/5.18.1 /usr/lib/perl5/5.18.1/i586-linux-thread-multi /usr/lib/perl5/5.18.1 /usr/lib/perl5/site_perl .) at (eval 9) line 3.
    Perhaps the DBD::mysql perl module hasn't been fully installed,
    or perhaps the capitalisation of 'mysql' isn't right.
    Available drivers: DBM, ExampleP, File, Gofer, Pg, Proxy, SQLite, Sponge.
    at prepare_db.pl line 70.
    martin@linux-70ce:~/perl/guru>


    so i have to install some more prerequisits

    and furthermore the module osm - /that is needed for running the other script. (see above).

    will look after all the prerequisits and come back later

     
     


    Search for (options) Powered by Gossamer Forum v.1.2.0

    Web Applications & Managed Hosting Powered by Gossamer Threads
    Visit our Mailing List Archives