Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

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";

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

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; }

And add a crontab entry with crontab -e:

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

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; }

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

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • 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 or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others browsing the Monastery: (3)
As of 2021-11-30 06:36 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?