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

[Richard Cook]

Thank you very much.
 
--
Richard Cook
richard at aspworld.com
Tel: 705-497-9320 - ext 2010
 
 

-----Original Message-----
From: asterisk-users-bounces at lists.digium.com
[mailto:asterisk-users-bounces at lists.digium.com] On Behalf Of Scott Lykens
Sent: Friday, September 24, 2004 11:03 AM
To: Asterisk Users Mailing List - Non-Commercial Discussion
Subject: Re: [Asterisk-Users] Billing Fun - anybody know where to get
aNPA/NXXdb?

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);
_______________________________________________
Asterisk-Users mailing list
Asterisk-Users at lists.digium.com
http://lists.digium.com/mailman/listinfo/asterisk-users
To UNSUBSCRIBE or update options visit:
   http://lists.digium.com/mailman/listinfo/asterisk-users