Created
March 9, 2015 02:52
-
-
Save matthallamew/834baef10b93ebb8cc35 to your computer and use it in GitHub Desktop.
Perl script to geocode addresses stored in an IBM Informix database. Uses Google's geocoding API.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/opt/perl514/bin/perl | |
use DBI; | |
use URI::Escape; | |
use JSON; | |
use Time::HiRes qw(usleep); | |
use feature qw(switch); | |
# set delay so script doesn't reach API hits per second threshold | |
my $delay = 250; | |
my $begUrl='https://maps.googleapis.com/maps/api/geocode/json?address='; | |
my $limit = $ARGV[0]; | |
if($limit eq ''){ | |
print "no limit in, limit set to 2500\n"; | |
$limit = 2500; | |
} | |
&main; | |
sub main{ | |
&openDBConn; | |
&getData; | |
&closeDBConn; | |
} | |
sub getData { | |
$key = &getKey; | |
if(!$key){ | |
print "API Key could not be found, quitting.\n"; | |
last; | |
} | |
$sql = qq#SELECT first $limit city,state,zip | |
FROM zipcodetable | |
where (state <> "" and city <>"") | |
AND (lat=0.0 and lon=0.0) | |
order by state;#; | |
$sth = $dbh->prepare($sql); | |
$sth->execute(); | |
while ( ($city,$state,$zip) = $sth->fetchrow_array ) { | |
print "$city $state $zip\n"; | |
$endUrl = "$city, $state $zip"; | |
$endUrl = uri_escape($endUrl); | |
$data ="$begUrl$endUrl&key=$key"; | |
$jsonData= `curl -s "$data"`; | |
$result = &setLatLon($jsonData); | |
if($result == 1){ | |
&updateLatLon($zip,$lat,$lon); | |
} | |
if($result == 2 or $result == 5){ | |
&updateLatLon($zip,-1.0,-1.0); | |
} | |
if($result == 3){ | |
print "OVER_QUERY_LIMIT\n"; | |
last; | |
} | |
usleep($delay); | |
} | |
$sth->finish(); | |
} | |
# --------- Utility functions ------------------- | |
sub getKey { | |
# Assume our API key for geocoding is in a file called .googleapis | |
# in our home directory. Split string on : and look for key of geocoding. | |
# Set token which will be the API key. | |
$_fileName="$ENV{HOME}/.googleapis"; | |
open(FILE, $_fileName) or die "Could not read from $_fileName!"; | |
while(defined($_ = <FILE>)) { # Read each line in the file. | |
chomp; # remove newline character from each line. | |
($k,$token) = split(':',$_); # set token | |
if($k eq 'geocoding'){ | |
last; | |
} | |
} | |
close FILE; | |
return $token; | |
} | |
sub setLatLon { | |
# Decode JSON returned from Google API. | |
# Determine status of request and return status. | |
my $inJson = $_[0]; | |
$json = JSON->new->allow_nonref; | |
my $jsnCnvrt = $json->decode($inJson); | |
$status = $jsnCnvrt->{status}; | |
given($status){ | |
when("OK") { $result = 1; } | |
when("ZERO_RESULTS") { $result = 2; } | |
when("OVER_QUERY_LIMIT") { $result = 3; } | |
when("REQUEST_DENIED") { $result = 4; } | |
when("INVALID_REQUEST") { $result = 5; } | |
when("UNKNOWN_ERROR") { $result = 6; } | |
} | |
if($result == 1){ | |
$lat = $jsnCnvrt->{results}[0]{geometry}{location}{lat}; | |
$lon = $jsnCnvrt->{results}[0]{geometry}{location}{lng}; | |
} | |
return $result; | |
} | |
sub updateLatLon { | |
# Update lat,lon in the zipcode table based on zipcode. | |
# This is assuming zipcode is a unique key in the table. | |
my $inZip = "$_[0]"; | |
my $inLat = $_[1]; | |
my $inLon = $_[2]; | |
$sql=qq#UPDATE zipcodetable | |
SET lat=$inLat,lon=$inLon | |
WHERE zip="$inZip";#; | |
print "$sql\n\n"; | |
$dbh->do($sql); | |
$sql = undef; | |
} | |
sub openDBConn { | |
$dbh = DBI->connect("dbi:Informix:$ENV{DB}") | |
or die "Error $DBI::errstr connecting to database $ENV{DB}\n"; | |
$dbh->{PrintError} = 1; | |
$dbh->{RaiseError} = 1; | |
$dbh->{ChopBlanks} = 1; | |
$dbh->begin_work(); | |
} | |
sub closeDBConn { | |
$dbh->commit(); | |
$dbh->disconnect(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment