Re: [Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXX db?
On Fri, 2004-09-24 at 04:32, Scott Lykens wrote: [snip] Then I have a perl script that reads each file in and puts them into a MySQL table. [snap] Would you mind sharing the perl script and the database schema? TIA, Patrick ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXX db?
On Fri, 24 Sep 2004 15:02:53 +0200, Patrick [EMAIL PROTECTED] 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
[EMAIL PROTECTED]
http://lists.digium.com/mailman/listinfo/asterisk-users
To UNSUBSCRIBE or update options visit:
http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXX db?
On Fri, 2004-09-24 at 17:03, Scott Lykens wrote: [snip] Perl script and database layout are below. [snap] Thanks! Regards, Patrick ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
RE: [Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXX db?
You might also want to try http://www.telcodata.us. Don't hit his servers hard, tho. If you do need bulk access to the data he has been quite helpful in the past. -- Brian -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of William Suffill Sent: Thursday, September 23, 2004 5:51 PM To: Asterisk Users Mailing List - Non-Commercial Discussion Subject: Re: [Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXX db? There used to be an NPA NXX sql on 1 of the asterisk site's. http://www.fnords.org/~eric/asterisk/ I doubt you will find a nice complete 1 for free unless you parse the npana data yourself which you could do. I did it recently not exactly fun. Still might not be 100% though. -- William ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
Re: [Asterisk-Users] Billing Fun - anybody know where to get aNPA/NXX db?
The NANPA maintains this database and the full database is available in 8 files. I have imported this into a MySQL table and can query based on rate center and npa-nxx. I use wget -a using a file containing the following: http://www.nanpa.com/nanp1/cnutlzd.zip http://www.nanpa.com/nanp1/csutlzd.zip http://www.nanpa.com/nanp1/eautlzd.zip http://www.nanpa.com/nanp1/enutlzd.zip http://www.nanpa.com/nanp1/esutlzd.zip http://www.nanpa.com/nanp1/wnutlzd.zip http://www.nanpa.com/nanp1/wputlzd.zip http://www.nanpa.com/nanp1/wsutlzd.zip Then I have a perl script that reads each file in and puts them into a MySQL table. I hope this helps with that you are looking for. sl ___ Asterisk-Users mailing list [EMAIL PROTECTED] http://lists.digium.com/mailman/listinfo/asterisk-users To UNSUBSCRIBE or update options visit: http://lists.digium.com/mailman/listinfo/asterisk-users
