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?
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 () { 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 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?
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
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