[Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXX db?

[Scott Lykens]

On Fri, 24 Sep 2004 15:02:53 +0200, Patrick <asterisk at puzzled.xs4all.nl> wrote:

> Would you mind sharing the perl script and the database schema?

Perl script and database layout are below. Its not pretty since I
never intended it for external consumption but it does get the job
done.

If you unzip the files you get from the previous post I made you need
to run this perl script against each file individually, ie script.pl
esutld.txt

I'm using npa-nxx as the key in the database as it is much much faster
to query based on the key than to do a "npa=212 and nxx=555" type
query with some other field as a key. On the P3-800 I'm hosting this
on right now the difference was 20 fold, from 0.2s to 0.01s.

Also, as far as I know, NANPA doesn't list individual carriers in NXXs
that are split up, for example NXXs where several different carriers
are assigned blocks of 1000 numbers. This database set up certainly
doesn't take that into account. I would assume, however, that rate
center information would be the same for all numbers in a particular
NXX.

Longer term, access to a LNP database may be needed to accurately
determine rate centers. For example, in my town, State College, PA,
the A and B cellular carriers do not have exchanges in the State
College rate center but rather in nearby rate centers that are local
calls. The A carrier is in Boalsburg and the B carrier is in
Bellefonte, each a distinct rate center, but it is possible to port
numbers between them. This means that someone in an outlying rate
center here, for example, Port Matilda, could believe they are making
a local call to a Bellefonte mobile number but since that number has
been ported to the other mobile carrier it becomes a regional toll
call to Boalsburg.

I don't know how the big telcos are dealing with this but I can't
imagine they're giving up the opportunity for revenue.

sl

-- perl script

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect("DBI:mysql:pbx:localhost", "user", "pass") or
die $DBI::errstr;

my $sth;

open (IN,"$ARGV[0]");

foreach (<IN>) {

       s/\x22//g;

       @npanxx = split("\t");

       if ($npanxx[0] eq "State") {
               next;
       }

#       print "$npanxx[1] / $npanxx[4], $npanxx[0] / $npanxx[3]
($npanxx[2]) $npanxx[5] / $npanxx[7]\n";

$sth = $dbh->prepare("INSERT INTO npanxx VALUES (\"$npanxx[0]\",
\"$npanxx[1]\", \"$npanxx[2]\", \"$npanxx[3]\", \"$npanxx[4]\",
\"$npanxx[5]\", \"$npanxx[7]\" , NULL)");

$rv = $sth->execute();

}

-- MySQL Table

CREATE TABLE npanxx (
       state CHAR(2),
       npanxx CHAR(7) NOT NULL PRIMARY KEY,
       ocn CHAR(4),
       company CHAR(52),
       ratecenter CHAR(11),
       switch CHAR(11),
       usetype CHAR(2),
       timestamp TIMESTAMP);