01.
02.
03.
use strict;
04.
use DBI;
05.
use Google::GeoCoder::Smart;
06.
07.
my $geo = Google::GeoCoder::Smart-&
gt
;new();
08.
09.
my $host =
""
;
10.
my $database =
""
;
11.
my $user =
""
;
12.
my $mysqlpassword =
""
;
13.
14.
my $dbh = DBI-&
gt
;connect(
"DBI:mysql:database=$database;host=$host"
,
"$user"
,
"$mysqlpassword"
,{'RaiseError'=&
gt
;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-&
gt
;fetchrow_hashref) {
20.
my ($num, $error, @results, $returntext) = $geo-&
gt
;geocode(
21.
"address"
=&
gt
; stripChars($ref-&
gt
;{'address'}),
22.
"city"
=&
gt
; stripChars($ref-&
gt
;{'city'}),
23.
"state"
=&
gt
; stripChars($ref-&
gt
;{'state'}),
24.
"zip"
=&
gt
; stripChars($ref-&
gt
;{'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-&
gt
;quote($lat);
40.
$lng = $dbh-&
gt
;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-&
gt
;finish();
44.
}
45.
$ctr++;
46.
if
(($ctr%10)==0) {
47.
print
"sleeping\n"
;
48.
sleep
5;
49.
}
50.
}
51.
$sth-&
gt
;finish();
52.
undef ($sql);
53.
undef ($sth);
54.
55.
$dbh-&
gt
;disconnect();
56.
57.
sub stripChars {
58.
my($text) = @_;
59.
$text =~ s/^\s*//;
60.
$text =~ s/\n/ /g;
61.
$text =~ s/\t/ /g;
62.
$text =~ s/\a/ /g;
63.
$text =~ s/"/'/g;
64.
$text =~ s/\s+/ /g;
65.
$text =~ s/[^[:ascii:]]+//g;
66.
return
($text);
67.
68.
}
69.
70.
sub db_query {
71.
my ($query) = @_;
72.
my $sth = $dbh-&
gt
;prepare(
"$query"
);
73.
74.
$sth-&
gt
;execute;
75.
76.
my $err = $dbh-&
gt
;err;
77.
my $errstr = $dbh-&
gt
;errstr;
78.
if
($err) { print
"$err: $errstr on query $query"
; }
79.
return
$sth;
80.
81.
}
No comments:
Post a Comment