comment on

EDIT: WARNING, THIS USES A LEGACY DATABASE THAT IS NOT UPDATED ANYMORE. Please take a look at GeoIP revisited for an updated version that uses an up-to-date version of the MaxMind GeoIP database.

Sometimes you have to work with GeoIP, e.g. mapping an IP address to the origin country. Be it for legal reasons (geoblocking) or just so you know where your target audience is coming from.

You could just make online lookups for every request. But if you are running a PostgreSQL database backend anyway, there is a simple way to do it in DB, since PostgreSQL supports a CIDR column type.

First, let us define a database table:

CREATE TABLE geoip
(
  netblock cidr NOT NULL,
  country_code text NOT NULL,
  country_name text NOT NULL,
  CONSTRAINT geoip_pk PRIMARY KEY (netblock)
  USING INDEX TABLESPACE "NAMEOFINDEXTABLESPACE"
)
WITH (
  OIDS=FALSE
)
TABLESPACE "NAMEOFDATATABLESPACE";
ALTER TABLE geoip OWNER TO "mydatabaseuser";
[download]

Next, we need a bash script we can run from crontab for our daily update:

#!/usr/bin/env bash

cd /home/myuser/src/geoip
wget http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCS
+V.zip
wget http://geolite.maxmind.com/download/geoip/database/GeoIPv6.csv.gz

gunzip GeoIPv6.csv.gz
unzip GeoIPCountryCSV.zip

rm GeoIPCountryCSV.zip
perl inserttodb.pl

rm *.csv
[download]

And of course some perl script to parse it all and write it to the database. Small problem here, the GeoIP files list IP ranges, but we need to convert it to subnet notation (CIDR). Net::CIDR to the rescue!

#/usr/bin/env perl

use DBI;

use Net::CIDR;

my $dbh = DBI->connect("dbi:Pg:dbname=Cables_DB;host=localhost", 'myda
+tabaseuser', 'secretpassword', {AutoCommit => 0, RaiseError => 0})
        or die("can't connect to DB");

$dbh->do("TRUNCATE geoip");
my $insth = $dbh->prepare_cached("INSERT INTO geoip (netblock, country
+_code, country_name) VALUES (?,?,?)")
        or die($dbh->errstr);

foreach my $file (qw[GeoIPCountryWhois.csv GeoIPv6.csv]) {
    print "Running on file $file...\n";
    open(my $ifh, '<', $file) or die("Can't open $file");
    my $linecount = 0;

    while((my $line = <$ifh>)) {
        $linecount++;
        chomp $line;
        my ($firstip, $lastip, undef, undef, $countrycode, $countrynam
+e) = split/\"\,\ ?\"/, $line;
        $firstip = sanitize($firstip);
        $lastip = sanitize($lastip);
        $countrycode = sanitize($countrycode);
        $countryname = sanitize($countryname);

        my @cidr = Net::CIDR::range2cidr($firstip . '-' . $lastip);

        foreach my $subcidr (@cidr) {
            $insth->execute($subcidr, $countrycode, $countryname) or d
+ie("Fail on $linecount $line: " . $dbh->errstr);
        }

    }
    close $ifh;
}

$dbh->commit;

sub sanitize {
    my ($val) = @_;

    $val =~ s/^\"//;
    $val =~ s/\"$//;

    return $val;
}
[download]

And add a crontab entry with crontab -e:

20 1 * * 1 /bin/bash /home/myuser/src/geoip/updategeoip.sh
[download]

Now we can request the country code for any IP address we encounter:


my $geoip_country = '';
my $geosth = $dbh->prepare("SELECT country_code FROM geoip WHERE ? << 
+netblock LIMIT 1")
        or croak($dbh->errstr);
if(!$geosth->execute($host)) {
    $dbh->rollback; # Not a big problem, GEOIP is just for information
+ anyway
} else {
    my $line = $geosth->fetchrow_hashref;
    if(defined($line->{country_code})) {
        $geoip_country = $line->{country_code};
    } else {
        $geoip_country = '??';
    }
    $dbh->rollback;
}
[download]

Of course, now that the up-to-date geoip lists are in the database, it's even possible to use an ON INSERT OR UPDATE trigger to any table that needs geoip data. But that i will leave as an excercise for the reader...

perl -e 'use MIME::Base64; print decode_base64("4pmsIE5ldmVyIGdvbm5hIGdpdmUgeW91IHVwCiAgTmV2ZXIgZ29ubmEgbGV0IHlvdSBkb3duLi4uIOKZqwo=");'

In reply to Perl+PostgreSQL+GeoIP = Awesome! by cavac

Are you posting in the right place? Check out Where do I post X? to know for sure.
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
Want more info? How to link or How to display code and escape characters are good places to start.


No such thing as a small change
	PerlMonks