Perl script to geocode addresses stored in an IBM Informix database. Uses Google's geocoding API.

geocodeAddrs.pl

#!/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();
}