fix biblionumber

[google-map-tiles.git] / koha-import.pl

diff --git a/koha-import.pl b/koha-import.pl
index 1682e34..b2ba4d1 100755 (executable)
--- a/koha-import.pl
+++ b/koha-import.pl
@@ -59,6 +59,8 @@ sub fetch_table {
 #fetch_table 'biblio' => 'biblionumber' ;
 #fetch_table 'biblioitems' => 'biblioitemnumber' ;
 
+sub geo_biblioitems {
+
 warn "# drop geo_biblioitems";
 eval { $g_dbh->do(qq{ drop table geo_biblioitems }) };
 
@@ -88,12 +90,17 @@ sub warn_dump {
        warn dump @_,$/ if $ENV{DEBUG};
 }
 
+my $i = 0;
+
 while ( my $row = $sth->fetchrow_hashref ) {
-       my $xml = XMLin( delete $row->{marcxml} );
+       my $xml = XMLin( delete $row->{marcxml}, ForceArray => [ 'datafield', 'subfield' ] );
 
        warn_dump($row, $xml);
 
        my @tag_260 = grep { $_->{tag} eq '260' } @{ $xml->{datafield} };
+
+       next unless @tag_260;
+
        warn_dump @tag_260 if $ENV{DEBUG};
 
        foreach my $sf ( @{ $tag_260[0]->{subfield} } ) {
@@ -104,10 +111,76 @@ while ( my $row = $sth->fetchrow_hashref ) {
        $row->{city} =~ s/['"]+//g;
        $row->{city} =~ s/\s*\[etc.*\].*$//;
        $row->{city} =~ s/\s*[:]\s*$//;
+       $row->{city} =~ s/[\[\]]+//g;
 
        warn_dump $row;
 
-       print $row->{city}, $/;
+       warn "# $i ", $row->{city}, $/ if $i++ % 100 == 0;
 
        $sth_insert->execute( $row->{biblioitemnumber}, $row->{biblionumber}, $row->{city} );
 }
+
+} # geo_biblioitems;
+
+#geo_biblioitems;
+
+eval {
+$g_dbh->do(qq{
+create table geo_city (
+       city_koha text not null primary key,
+       city text,
+       country text,
+       county text,
+       lat float,
+       lng float,
+       radius int,
+       dump text
+)
+});
+};
+
+my $sth_insert = $g_dbh->prepare(qq{insert into geo_city values (?,?,?,?,?,?,?,?)});
+
+my $sth = $g_dbh->prepare(qq{
+select count(*),city from geo_biblioitems where city not in (select city_koha from geo_city) group by city order by count(city) desc
+});
+$sth->execute;
+
+warn $sth->rows, " cities to geolocate";
+
+use Geo::Coder::PlaceFinder;
+
+my $geocoder = Geo::Coder::PlaceFinder->new( appid => 'eQWEGC58' );
+
+while( my $row = $sth->fetchrow_hashref ) {
+       my $location  = $geocoder->geocode(location => $row->{city});
+       warn dump($location);
+       $sth_insert->execute(
+               $row->{city}
+               , $location->{city},
+               , $location->{country}
+               , $location->{county}
+               , $location->{latitude}
+               , $location->{longitude}
+               , $location->{radius}
+               , dump($location)
+       );
+}
+
+eval { $g_dbh->do(qq{ drop table geo_count }) };
+$g_dbh->do(qq{
+
+select
+       count(biblionumber)
+       ,point(lat,lng)
+       ,geo_city.city
+       ,geo_city.city_koha
+       ,country
+into geo_count
+from geo_biblioitems
+join geo_city on city_koha = geo_biblioitems.city
+where length(geo_city.city) > 1
+group by geo_city.city, country, lat, lng, city_koha
+order by count(biblionumber) desc
+
+});