#fetch_table 'biblio' => 'biblionumber' ;
#fetch_table 'biblioitems' => 'biblioitemnumber' ;
+sub geo_biblioitems {
+
warn "# drop geo_biblioitems";
eval { $g_dbh->do(qq{ drop table geo_biblioitems }) };
$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
+
+});