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

Monday, February 27, 2017

Geocode a batch of addresses with perl and Google

I am working with this store locator and needed a way to geocode 7k addresses, so I wrote this quick-n-dirty script. Google only lets you geocode 2500 addresses a day. The script will start where it left off, then quit when you hit the query limit. So it feasibly could be run on a cron until all your addresses are coded.
01.#!/usr/bin/perl
02. 
03.use strict;
04.use DBI;
05.use Google::GeoCoder::Smart;
06. 
07.my $geo = Google::GeoCoder::Smart->new();
08. 
09.my $host ="";
10.my $database ="";
11.my $user ="";
12.my $mysqlpassword ="";
13. 
14.my $dbh = DBI->connect("DBI:mysql:database=$database;host=$host","$user","$mysqlpassword",{'RaiseError'=>1});
15. 
16. my $ctr= 0;
17. my $sql = "SELECT * FROM sb_locations WHERE lat = \"\""
18. my $sth = &db_query($sql);
19. while (my $ref = $sth->fetchrow_hashref) {
20.        my ($num, $error, @results, $returntext) = $geo->geocode(
21.        "address" => stripChars($ref->{'address'}),
22.        "city" => stripChars($ref->{'city'}),
23.        "state" => stripChars($ref->{'state'}),
24.        "zip" => stripChars($ref->{'postal'})
25.        );
26.        my $lat;
27.        my $lng;
28.        eval {
29.         $lat = $results[0]{geometry}{location}{lat};
30.        };
31.        eval {
32.         $lng = $results[0]{geometry}{location}{lng};
33.        }; 
34.        print "id: $ref->{'id'} returntext?: $returntext error?: $error lat: $lat lng: $lng\n";
35.        if ($error eq 'OVER_QUERY_LIMIT') {
36.         last;
37.        }        
38.        if (($lat) && ($lng)) {
39.         $lat = $dbh->quote($lat);
40.         $lng = $dbh->quote($lng);
41.         my $upd_q = "UPDATE sb_locations SET lat = $lat, lng = $lng WHERE id = $ref->{'id'}";
42.         my $sth2 = &db_query($upd_q);
43.         $sth2->finish();
44.        }
45.        $ctr++;
46.        if (($ctr%10)==0) {
47.         print "sleeping\n";
48.         sleep 5;
49.        }
50. }
51. $sth->finish();
52. undef ($sql);
53. undef ($sth);
54. 
55.$dbh->disconnect();
56. 
57.sub stripChars {
58.        my($text) = @_;
59.        $text =~ s/^\s*//; # strip out leading space if there is one
60.        $text =~ s/\n/ /g; # strip carraige returns
61.        $text =~ s/\t/ /g; # strip tabs
62.        $text =~ s/\a/ /g; # strip carraige returns
63.  $text =~ s/"/'/g; # strip quotes and replace with single quotes
64.        $text =~ s/\s+/ /g; # strip repeating spaces and replace with one
65.        $text =~ s/[^[:ascii:]]+//g;
66.        return ($text);
67.  
68.} # end sub strip chars
69. 
70.sub db_query {
71. my ($query) = @_;
72. my $sth = $dbh->prepare("$query");
73. #print $query;
74. $sth->execute;
75. #print $dbh->err;
76. my $err = $dbh->err; 
77. my $errstr = $dbh->errstr;
78. if ($err) { print "$err: $errstr on query $query"; }
79. return $sth;
80.  
81.} # end sub db_query

No comments:

Post a Comment