North American Local Calling Area Database
Produced: on or before 5th calendar day monthly
Last Release: September 3, 2014
Distribution Frequency: monthly or quarterly (issue date: January, April, July, October)
Distribution Format: Internet download
The Local Calling Area Database can be used to quickly identify the jurisdictional relationship between any originating NPA NXX and terminating NPA NXX call combination. These relationships include determining whether the call is local, intraLATA, interLATA, intraState, interState, and for wireless to wireless or wireless to landline whether the call is intraMTA or interMTA.
Compiled from current carrier sources, the NALENND™ Local Calling Area Database contains local calling area information to identify the basic and expanded local calling areas for telephone exchanges in the United States and Canada based on incumbent wireline carriers' calling area definitions. They can be used as an aid in calling plan design, traffic and least cost routing, or similar application that needs to distinguish between local and toll calls.
- Data Field Definitions
- SQL Table Definitions
- How to use this data
- Local Calling Area Demo
|Field||Data Type||Field Description|
|ORC_EXCHANGE||CHAR (8)||Unique originating exchange index within the Rate Center.|
|ORC_STATE||CHAR (2)||Originating Rate Center state, province, or territory abbreviation.|
|ORC_ABBR||CHAR (10)||Originating Rate Center standardized 10 character LERG abbreviation.|
|ORC_LATA||CHAR (3)||Originating Rate Center LATA number.|
|ORC_MTA||INT (11)||Originating Rate Center MTA number.|
|TRC_EXCHANGE||CHAR (8)||Unique terminating exchange index within the Rate Center.|
|TRC_STATE||CHAR (2)||Terminating Rate Center state, province, or territory abbreviation.|
|TRC_ABBR||CHAR (10)||Terminating Rate Center standardized 10 character LERG abbreviation.|
|TRC_LATA||CHAR (3)||Terminating Rate Center LATA number.|
|TRC_MTA||INT (11)||Terminating Rate Center MTA number.|
|OCN||CHAR (4)||When populated identifies the competitive local carrier whose local calling area differs from the incumbent local exchange carrier.|
|CALL_TYPE||VARCHAR (20)||Local call type relationship between originating and terminating Rate Centers.This field identifies terminating exchanges as local or expanded local in relationship to the originating Rate Center.|
|Field||Data Type||Field Description|
|NPA||CHAR (3)||Numbering Plan Area code, the first three digits of a 10 digit North American Numbering Plan (NANP) number, commonly called the Area Code.|
|NXX||CHAR (3)||Central Office Code, the first three digits following the Area Code in a 10 digit NANP number, commonly called the telephone exchange or prefix.|
|BLOCK_ID||CHAR (1)||Block identifier. Implies, represents, or identifies either the entire group of 10,000 numbers or a specific group of 1,000 numbers within the NPA NXX.|
|EXCHANGE||CHAR (8)||Unique exchange index used to identify the NPA-NXX-BLOCK_ID combination.|
CREATE DATABASE if not exists `nalennd`; USE `nalennd`; DROP TABLE IF EXISTS `local_xref`; CREATE TABLE `local_xref` ( `NPA` CHAR(3) NOT NULL, `NXX` CHAR(3) NOT NULL, `BLOCK_ID` CHAR(1) NOT NULL, `EXCHANGE` CHAR(8) NOT NULL, PRIMARY KEY (`NPA`,`NXX`,`BLOCK_ID`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `local`; CREATE TABLE `local` ( `ORC_EXCHANGE` CHAR(8) NOT NULL, `ORC_STATE` CHAR(2) NOT NULL, `ORC_ABBR` CHAR(10) NOT NULL, `ORC_LATA` CHAR(5) NOT NULL, `ORC_MTA` INT(11) NOT NULL, `TRC_EXCHANGE` CHAR(8) NOT NULL, `TRC_STATE` CHAR(2) NOT NULL, `TRC_ABBR` CHAR(10) NOT NULL, `TRC_LATA` CHAR(5) NOT NULL, `TRC_MTA` INT(11) NOT NULL, `OCN` CHAR(4) NULL, `CALL_TYPE` VARCHAR(20) NULL, PRIMARY KEY (`ORC_EXCHANGE`,`TRC_EXCHANGE`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
To effectively use this database you will first import it into an SQL engine such as MySQL, Microsoft SQL Server, IBM DB2, Oracle, Postgresql, SQLite, or similar product. These tools provide the mechanism to index the data files and create efficient table cross referencing.
To determine the local calling area for any particular telephone number we would first query the local_xref table to find the exchange key associated with the area code (NPA) and exchange prefix (NXX) of the telephone number. For example, given the telephone number (352) 357-1234:
SELECT local_xref.NPA, local_xref.NXX, local_xref.BLOCK_ID, local_xref.EXCHANGE FROM local_xref WHERE (((local_xref.NPA)='352') AND ((local_xref.NXX)='357'));
Upon success we then take the exchange key returned in our result set and use it to query the local table to find all terminating exchanges where the originating exchange is equal to our exchange key.
SELECT TRC_EXCHANGE FROM local WHERE local.ORC_EXCHANGE = 'exchange_key';
Upon success the result set will contain the local calling area using our original telephone number as the originating exchange. We can then iterate through the result set and for each record use the terminating exchange key to reference back to the local_xref table to retrieve the area codes and exchange prefixes that are in our original (352) 357-1234 telephone number's local calling area.
SELECT NPA, NXX, BLOCK_ID FROM local_xref WHERE EXCHANGE = 'exchange_key';
This process can be consolidated by creating a single query through SQL join statements. Thus we can retrieve all area codes (NPA) and prefixes (NXX) that are in our (352) 357-1234 telephone number's local calling area using the following query. Upon success we simply iterate through the result set to see our values.
SELECT local_xref.NPA, local_xref.NXX, local_xref.BLOCK_ID, local.ORC_STATE, local.ORC_ABBR, local.TRC_STATE, local.TRC_ABBR, local_xref_1.NPA, local_xref_1.NXX, local_xref_1.BLOCK_ID FROM (local_xref INNER JOIN `local` ON local_xref.EXCHANGE = local.ORC_EXCHANGE) INNER JOIN local_xref AS local_xref_1 ON local.TRC_EXCHANGE = local_xref_1.EXCHANGE WHERE (((local_xref.NPA)="352") AND ((local_xref.NXX)="357"));