nix, shell, perl, php, mysql and mac os x tips and tricks

Saturday, July 30, 2016

Geocode thousands of addresses with perl

I'm using this jquery store locator and needed a way to geocode 7K addresses. Google only lets you code 2500 in a 24H hour period, so this will quit when the query limit is reached. When it runs again it will pick up where is left off. So it could theoretically be run on a cron until it's done.
#!/usr/bin/perl

use strict;
use DBI;
use Google::GeoCoder::Smart;

my $geo = Google::GeoCoder::Smart->new();

my $host =""; 
my $database =""; 
my $user =""; 
my $mysqlpassword ="";

my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1}); 

 my $ctr= 0;
 my $sql = "SELECT * FROM sb_locations WHERE lat = \"\"";  
 my $sth = &db_query($sql);
 while (my $ref = $sth->fetchrow_hashref) {
        my ($num, $error, @results, $returntext) = $geo->geocode(
        "address" => stripChars($ref->{'address'}), 
        "city" => stripChars($ref->{'city'}), 
        "state" => stripChars($ref->{'state'}), 
        "zip" => stripChars($ref->{'postal'})
        );
        my $lat;
        my $lng;
        eval {
         $lat = $results[0]{geometry}{location}{lat};
        };
        eval {
         $lng = $results[0]{geometry}{location}{lng};
        };  
        print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
        if ($error eq 'OVER_QUERY_LIMIT') {
         last;
        }         
        if (($lat) && ($lng)) {
         $lat = $dbh->quote($lat);
         $lng = $dbh->quote($lng);
         my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
         my $sth2 = &db_query($upd_q);
         $sth2->finish();
        }
        $ctr++;
        if (($ctr%10)==0) {
         print "sleeping\n";
         sleep 5;
        }
 }
 $sth->finish();
 undef ($sql);
 undef ($sth);

$dbh->disconnect();

sub stripChars {
        my($text) = @_;
        $text =~ s/^\s*//; # strip out leading space if there is one
        $text =~ s/\n/ /g; # strip carraige returns
        $text =~ s/\t/ /g; # strip tabs
        $text =~ s/\a/ /g; # strip carraige returns
  $text =~ s/"/'/g; # strip quotes and replace with single quotes
        $text =~ s/\s+/ /g; # strip repeating spaces and replace with one
        $text =~ s/[^[:ascii:]]+//g;
        return ($text);
 
} # end sub strip chars

sub db_query {
 
 my ($query) = @_;

 my $sth = $dbh->prepare("$query"); 
 #print $query;
 $sth->execute;
 #print $dbh->err; 
 my $err = $dbh->err;  
 my $errstr = $dbh->errstr;
 if ($err) { print "$err: $errstr on query $query"; }
 return $sth;
 
} # end sub db_query