NICE! I will use this, but with a twist:

use 5.14.2;
use warnings;

use Text::CSV_XS qw( csv );
use DBI;
use Socket;
use Net::CIDR;

my $tbl = "geoip";
my $dbh = DBI->connect ("dbi:Pg:dbname=cidr", undef, undef, {
    AutoCommit          => 0,
    RaiseError          => 1,
    PrintError          => 1,
    ShowErrorStatement  => 1,
    });

if (grep m/\b $tbl $/ix => $dbh->tables (undef, undef, undef, undef)) 
+{
    say "Clear table $tbl";
    $dbh->do ("truncate table $tbl");
    }
else {
    say "Create table $tbl";
    $dbh->do (qq;
        create table $tbl (
        netblock     cidr     not null primary key,
        type         smallint not null,
        ip_from      text     not null,
        ip_to        text     not null,
        ip_from_n    bigint,
        ip_to_n      bigint,
        country_code text     not null,
        country_name text     not null);
        );
    }

my $sth = $dbh->prepare ("insert into $tbl values (?, ?, ?, ?, ?, ?, ?
+, ?)");

foreach my $file (qw( GeoIPCountryWhois.csv GeoIPv6.csv )) {
    print "Inserting from $file...\n";
    csv (in      => $file,
         out     => undef, allow_whitespace => 1,
         headers => [qw( firstip lastip x1 x2 iso name )],
         on_in   => sub {
            foreach my $cidr (Net::CIDR::range2cidr ("$_{firstip}-$_{l
+astip}")) {
                my @rng = Net::CIDR::cidr2range ($cidr);
                my ($f, $t) = split m/\s*-\s*/ => $rng[0];
                my ($type, $F, $T) = $f =~ m/:/
                    ? (6, undef, undef)
                    : (4, map { unpack "L>", inet_aton $_ } $f, $t);
                $sth->execute ($cidr, $type, $f, $t, $F, $T, $_{iso}, 
+$_{name});
                }
            },
        );
    }
$dbh->commit;

[download]

because I already have some scripts that use "L>" representations of IPv4 addresses as those are easy to check.

I did not know that this dataset was publicly available. I regularly check http://whois.domaintools.com/$ip in tools that analyze break-in attempts.


Enjoy, Have FUN! H.Merijn

In reply to Re: Perl+PostgreSQL+GeoIP = Awesome! by Tux
in thread 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":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.