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

Monday, February 27, 2017

clean the apache cache nicely

/usr/local/apache/bin/htcacheclean -vn -p/var/cache/apache -l500M

Kinda rough regex for pattern matching and capturing apache log info

the IP is parsed into \2 \3 and \4
^(((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)) \- \- (\[.*?\]) "(.*?)" (\d+) (\d+) "(.*?)" "(.*?)"$

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.
#!/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