Technical Specifications & Data Dictionary

The Area Code Database (also known as the NPA-NXX Database) is a comprehensive electronic dataset containing all valid telephone numbering assignments across the North American Numbering Plan. The database includes approximately 558,000 NPA-NXX records covering the United States, U.S. territories, and Canadian reference data.

NPA-NXX Explained: NPA (Numbering Plan Area) is the 3-digit area code, and NXX (Central Office Code) is the 3-digit exchange prefix. Together, they represent the first 6 digits of a 10-digit phone number and determine geographic location, carrier assignment, and routing information.

Unlike raw NANPA assignment files, our database adds significant value through:

• ZIP Code Correlations: Proprietary frequency-ranked mappings linking NPA-NXX blocks to ZIP codes
• Geographic Enrichment: Latitude/longitude coordinates (weighted and standard), city, county, FIPS codes, MSA/CBSA classifications
• Carrier Attribution: Company names, OCN codes, rate center assignments
• Time Intelligence: Time zones, DST observance flags for scheduling applications
• Service Classification: Wireless vs. landline designation, active/inactive status tracking
• Production-Ready Formats: Clean CSV, TAB, and Access MDB files ready for immediate import
• Quarterly Updates: Synchronized with NANPA releases to maintain accuracy

The database supports phone number validation, call routing systems, fraud detection, CRM enrichment, time zone scheduling, telecom analytics, and TCPA compliance applications.

Each edition is optimized for different technical use cases and implementation complexity:

Technical recommendation. Choose Standard for basic validation and routing where a minimal database footprint matters. Choose Deluxe when you need carrier attribution, geographic precision, time zone scheduling, or any analytics beyond a simple lookup. Most production systems end up wanting Deluxe.

You can upgrade from Standard to Deluxe at any time by paying the price difference. Your schema will need updating to accommodate the additional 22 fields, but the natural key — NPANXX plus ZipCode — is unchanged.

Our database is derived directly from NANPA (North American Numbering Plan Administration)-the official authority responsible for area code and central office code assignments. This is the same authoritative source used by all telecommunications carriers in North America.

Data Processing Pipeline:

1. Quarterly NANPA Sync: We download NANPA's official "utilized codes" and "available codes" files for all regions
2. Format Normalization: Convert NANPA's ASCII tab-delimited format to clean CSV/TAB/MDB with standardized field names
3. Proprietary Enrichment:
   • ZIP code correlation analysis using USPS and Census data
   • Coordinate geocoding validated against multiple authoritative sources
   • Time zone assignment and DST observance tracking
   • County population and FIPS code linkage
   • MSA/CBSA classification from Census Bureau data
4. Quality Validation: Cross-reference checks, anomaly detection, and historical consistency verification

Accuracy by Field Category:

Important Accuracy Considerations:

• Quarterly Update Window: New assignments occur monthly at NANPA; our quarterly sync means 1-3 month lag for brand new NPA-NXX blocks
• Number Portability: Individual phone numbers can move between carriers and types (wireless?landline); block-level data reflects original assignment
• Carrier Mergers: Company names may become outdated between updates as carriers merge or rebrand; OCN codes are more stable

The Status field (Deluxe edition only) indicates whether an NPA-NXX combination is currently in service or reserved for future use. Understanding this distinction is critical for proper implementation.

Status Values Explained:

Implementation Guidance:

-- Production routing and validation (only assigned blocks)
SELECT * FROM npanxx WHERE Status = 'Active';

-- Fraud detection (flag calls from unassigned blocks)
SELECT * FROM npanxx WHERE Status = 'Inactive';
-- Note: While not 100% definitive, calls from Inactive blocks 
-- have significantly higher fraud probability

-- Planning & forecasting (complete dataset)
SELECT * FROM npanxx;
-- No filter; analyze both Active and Inactive for capacity planning

Common Use Cases for Inactive Records:

• Planning & Forecasting: Track which NPA-NXX blocks are provisioned for future growth in specific markets
• Fraud Detection: Flag incoming calls from unassigned blocks as suspicious (though not conclusive proof of fraud)
• Historical Tracking: Monitor when inactive blocks transition to active status (indicates new carrier entry or market expansion)
• Numbering Exhaustion Analysis: Understand available capacity in area codes approaching depletion

Status Transitions Over Time:

When NANPA assigns an Inactive block to a carrier, it becomes Active in our next quarterly update. The record is enriched with full carrier information (Company, OCN), complete geographic data, rate center assignment, and all routing details. The NPANXX key remains consistent, so you can track the transition by comparing quarterly releases.

NANPA enforces specific structural rules for valid area codes (NPA) and exchange prefixes (NXX). Understanding these constraints is essential for input validation and data integrity.

NPA (Area Code) Rules:

NXX (Exchange Prefix) Rules:

Special Reserved Codes (N11):

Codes following the N11 pattern (where NPA or NXX has format X11) are reserved for special services and are not included in our database:

• 211 - Community information / non-emergency
• 311 - Non-emergency services
• 411 - Directory assistance
• 511 - Traffic/travel information
• 611 - Carrier customer service
• 711 - Telecommunications relay service
• 811 - Call before you dig
• 911 - Emergency services

Implementation Examples:

// JavaScript/TypeScript validation
function isValidNPA(npa: string): boolean {
    if (npa.length !== 3) return false;
    const first = parseInt(npa[0]);
    return first >= 2 && first <= 9;
}

function isValidNXX(nxx: string): boolean {
    if (nxx.length !== 3) return false;
    const first = parseInt(nxx[0]);
    return first >= 2 && first <= 9;
}

function isValidNPANXX(npanxx: string): boolean {
    if (npanxx.length !== 6) return false;
    return isValidNPA(npanxx.substring(0, 3)) && 
           isValidNXX(npanxx.substring(3, 6));
}

// SQL constraint (PostgreSQL/MySQL)
ALTER TABLE npanxx ADD CONSTRAINT check_npa_valid
    CHECK (SUBSTRING(NPA, 1, 1) BETWEEN '2' AND '9');

ALTER TABLE npanxx ADD CONSTRAINT check_nxx_valid
    CHECK (SUBSTRING(NXX, 1, 1) BETWEEN '2' AND '9');

Our database uses empty strings ("") rather than NULL values for missing or unavailable data. This is a deliberate design choice with specific technical implications.

Why Empty Strings Instead of NULL?

Years ago, we worked with small businesses and individuals who understood "empty" but struggled with the null concept. Looking back, we probably should have stuck with NULL, but here we are. The good news: it's consistent across all fields, so once you understand the pattern, implementation is straightforward.

Field-by-Field Behavior:

Critical Implementation Details:

• State Field: Technically VARCHAR(2) to accommodate potential empty values, but in practice all records have State populated (one errant record excluded via data cleaning)
• Inactive Records: Status='Inactive' records typically have most fields empty (City, County, Carrier, etc.) since they haven't been assigned yet. NPANXX, NPA, NXX, State, and Status are always present.
• Placeholder Values: Some inactive records use placeholders: RateCenter=XXXXXXXXXX, OCN=MULT, Company=MULTIPLE OCN LISTING. Treat these as semantically equivalent to empty strings.

Query Pattern Examples:

-- SQL: Find records with missing city data
SELECT * FROM npanxx WHERE City = '' OR City IS NULL;

-- SQL: Find records with valid coordinates
SELECT * FROM npanxx WHERE Latitude != 0 AND Longitude != 0;

-- SQL: Count records missing carrier information
SELECT COUNT(*) FROM npanxx 
WHERE Company = '' OR Company = 'MULTIPLE OCN LISTING';

-- Python: Check for empty fields
if record['City'] == '' or record['City'] is None:
    # Handle missing city

-- Python: Validate coordinates (remember 0 = unknown)
if record['Latitude'] == 0 or record['Longitude'] == 0:
    # Coordinates unavailable

-- JavaScript: Defensive checking
if (!field || field === '' || field === null) {
    // Field is empty or missing
}

Latitude and Longitude fields use 0 (zero) to indicate unknown or unavailable coordinates, which is an exception to our general empty-string convention. This design choice has important technical implications.

Why Zero Instead of Empty String?

Geographic coordinate fields are typically stored as numeric types (DECIMAL or FLOAT) rather than text. While text fields can store empty strings, numeric fields require an actual numeric value. We chose 0 as a sentinel value because:

• Type Consistency: Maintains numeric data type integrity across all database platforms
• Mathematical Operations: Allows coordinate fields to participate in calculations without type conversion errors
• Import Simplicity: CSV/TAB files can represent numeric fields directly without special handling
• Historical Precedent: Industry convention established before NULL handling became universal

Geographic Reality of (0, 0):

The coordinate (0°, 0°) represents a location in the Atlantic Ocean off the coast of West Africa (Gulf of Guinea), approximately 380 miles south of Ghana. Since no North American telephone infrastructure exists at this location, 0 serves as an unambiguous indicator of missing data.

Fields Affected:

• Latitude (Deluxe only)
• Longitude (Deluxe only)
• WeightedLatitude (Deluxe only)
• WeightedLongitude (Deluxe only)

Implementation Patterns:

-- SQL: Filter to records with valid coordinates
SELECT * FROM npanxx 
WHERE Latitude != 0 AND Longitude != 0;

-- SQL: Count records missing coordinates
SELECT COUNT(*) FROM npanxx 
WHERE Latitude = 0 OR Longitude = 0;

-- SQL: Safe distance calculation (skip invalid coordinates)
SELECT 
    NPANXX,
    CASE 
        WHEN Latitude != 0 AND Longitude != 0 
        THEN SQRT(POW(Latitude - @targetLat, 2) + POW(Longitude - @targetLon, 2))
        ELSE NULL 
    END AS rough_distance
FROM npanxx;

-- Python: Validate before mapping
def has_valid_coordinates(record):
    return (record['Latitude'] != 0 and 
            record['Longitude'] != 0 and
            record['Latitude'] is not None and 
            record['Longitude'] is not None)

if has_valid_coordinates(record):
    plot_on_map(record['Latitude'], record['Longitude'])
else:
    print(f"No coordinates for {record['NPANXX']}")

-- JavaScript: Safe coordinate access
function getCoordinates(record) {
    if (record.Latitude === 0 || record.Longitude === 0) {
        return null; // Invalid/unknown
    }
    return {
        lat: parseFloat(record.Latitude),
        lng: parseFloat(record.Longitude)
    };
}

When Are Coordinates Zero?

• Inactive Records: Most Status='Inactive' records have coordinates set to 0 since geographic assignment hasn't occurred
• Geocoding Failures: Rare cases where rate center location cannot be determined with reasonable confidence
• Non-Geographic Codes: Certain special-purpose NPA-NXX blocks without traditional geographic assignment

The ZipCodeFreq field uses a counter-intuitive ranking system where negative numbers indicate correlation strength. Understanding this system is critical for accurate ZIP code attribution.

ZipCodeFreq Value Meanings:

Why Negative Numbers?

The negative ranking system (-1, -2) was chosen to sort naturally in descending order. When you sort by ZipCodeFreq ascending, the most important records (-1) appear first, followed by secondary (-2), then unknown (0). This makes database queries more intuitive.

Implementation Patterns:

-- Get primary ZIP code only (most common use case)
SELECT ZipCode, ZipCodeCount 
FROM npanxx 
WHERE NPANXX = '212555' AND ZipCodeFreq = -1;

-- Get primary and secondary ZIPs (comprehensive coverage)
SELECT ZipCode, ZipCodeFreq, ZipCodeCount
FROM npanxx 
WHERE NPANXX = '212555' 
  AND ZipCodeFreq IN (-1, -2)
ORDER BY ZipCodeFreq ASC; -- Returns -1 first, then -2

-- Find all NPA-NXX blocks serving a specific ZIP
SELECT NPANXX, NPA, NXX, ZipCodeFreq, ZipCodeCount
FROM npanxx
WHERE ZipCode = '10001' AND ZipCodeFreq = -1
ORDER BY ZipCodeCount DESC; -- Highest population first

-- Validate correlation strength
SELECT 
    NPANXX,
    ZipCode,
    ZipCodeFreq,
    ZipCodeCount,
    CASE 
        WHEN ZipCodeFreq = -1 THEN 'High Confidence'
        WHEN ZipCodeFreq = -2 THEN 'Moderate Confidence'
        ELSE 'Low Confidence'
    END AS ConfidenceLevel
FROM npanxx
WHERE NPANXX = '212555';

Real-World Example:

Consider NPA-NXX 212-555 (New York):

Note: Since a single NPA-NXX can map to multiple ZIP codes (one record per correlation), the NPANXX field is not unique in the database. Always include ZipCodeFreq in your queries to control which correlation level you're retrieving.

The NXXUseType field classifies NPA-NXX blocks as Wireless (W) or Landline (L) based on original NANPA carrier assignment. Understanding the reliability and limitations of this classification is critical for compliance and business logic.

Classification Accuracy:

At the block level (NPA-NXX), the classification is 85-90% accurate for identifying the original carrier type. However, at the individual phone number level, accuracy can be significantly lower due to number portability.

Why Accuracy Varies:

• Number Portability: Individual phone numbers can move between carriers and types. A "wireless" block may contain ported landline numbers and vice versa.
• VoIP Complexity: Modern VoIP services blur traditional wireless/landline distinctions. VoIP numbers may be classified as landline even if used on mobile devices.
• Carrier Type Changes: As carriers merge or restructure, block classifications may become outdated between our quarterly updates.
• Wholesale/MVNO: Mobile Virtual Network Operators (MVNOs) may use blocks originally assigned to traditional carriers.

Appropriate Use Cases:

Implementation Example:

-- Two-stage approach: Filter first, verify critical ones
-- Stage 1: Bulk filter using our database (fast, free)
SELECT * FROM call_queue cq
JOIN npanxx n ON SUBSTRING(cq.phone, 1, 6) = n.NPANXX
WHERE n.NXXUseType = 'W' AND n.Status = 'Active'
  AND n.ZipCodeFreq = -1;
-- Result: Likely wireless numbers (85-90% accuracy)

-- Stage 2: For TCPA-critical calls, verify via real-time API
-- (pseudocode)
foreach (likelyWireless in filteredList) {
    if (requiresTCPACompliance) {
        definitive = callWirelessVerificationAPI(likelyWireless.phone);
        if (definitive.isWireless) {
            // Ensure explicit consent before calling
        }
    }
}

Field Values:

• W - Wireless (mobile/cellular)
• L - Landline (traditional wireline)
• "" - Empty string (classification unknown or not applicable)

For general information about wireless/landline classification, see the overview FAQ. This technical FAQ provides implementation-specific guidance for developers.

No-NPANXX is not unique in the database and therefore cannot serve as a primary key. This is a critical design consideration that trips up many first-time implementers.

Why NPANXX Is Not Unique:

A single NPA-NXX combination can serve multiple ZIP codes. Since telephone exchange boundaries and ZIP code boundaries don't align perfectly, one NPA-NXX block may have phone numbers distributed across 2-5 different ZIP codes. Our database includes one record per NPA-NXX/ZIP correlation, resulting in duplicate NPANXX values.

Example:

Recommended Primary Key Strategies:

Strategy 1: Auto-Increment Surrogate Key (Most Common)

CREATE TABLE npanxx (
    id INT AUTO_INCREMENT PRIMARY KEY,
    NPANXX CHAR(6) NOT NULL,
    NPA CHAR(3) NOT NULL,
    NXX CHAR(3) NOT NULL,
    ZipCode VARCHAR(10),
    ZipCodeFreq INT,
    State VARCHAR(2),
    -- ... additional fields
    INDEX idx_npanxx (NPANXX),
    INDEX idx_npanxx_zip (NPANXX, ZipCode),
    INDEX idx_zip (ZipCode)
);

Pros: Simple, guaranteed unique, works with ORMs. Cons: ID values change across quarterly updates (not stable for external references).

Strategy 2: Composite Primary Key

CREATE TABLE npanxx (
    NPANXX CHAR(6) NOT NULL,
    ZipCode VARCHAR(10) NOT NULL,
    ZipCodeFreq INT,
    State VARCHAR(2),
    -- ... additional fields
    PRIMARY KEY (NPANXX, ZipCode),
    INDEX idx_npanxx (NPANXX),
    INDEX idx_zip (ZipCode)
);

Pros: Natural key, stable across updates, enforces uniqueness constraint. Cons: More complex joins, some ORMs struggle with composite keys.

Strategy 3: Single-ZIP Subset (If You Don't Need Multiple ZIPs)

-- Import only primary ZIP correlations
CREATE TABLE npanxx (
    NPANXX CHAR(6) PRIMARY KEY,
    ZipCode VARCHAR(10),
    ZipCodeFreq INT DEFAULT -1,
    State VARCHAR(2),
    -- ... additional fields
);

-- During import, filter to primary ZIP only
LOAD DATA LOCAL INFILE 'npanxx.csv'
INTO TABLE npanxx
WHERE ZipCodeFreq = -1;

Pros: NPANXX becomes unique, simplifies schema. Cons: Loses secondary ZIP correlations, less comprehensive coverage.

Strategy 4: GUID/UUID Surrogate Key

CREATE TABLE npanxx (
    uuid CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    NPANXX CHAR(6) NOT NULL,
    ZipCode VARCHAR(10),
    -- ... additional fields
    INDEX idx_npanxx (NPANXX),
    INDEX idx_npanxx_zip (NPANXX, ZipCode)
);

Pros: Globally unique, stable across systems, works for distributed databases. Cons: Larger storage footprint, slower joins than INT keys.

Common Mistake to Avoid:

--  DON'T DO THIS - Will fail on duplicate NPANXX
CREATE TABLE npanxx (
    NPANXX CHAR(6) PRIMARY KEY,  -- WRONG! Not unique!
    ZipCode VARCHAR(10),
    -- ...
);

-- Import will fail with duplicate key error:
-- ERROR 1062: Duplicate entry '212555' for key 'PRIMARY'

Update Strategy: If using auto-increment IDs, remember they're not stable across quarterly updates. Don't store ID references externally. Use (NPANXX, ZipCode) composite as your logical key for cross-quarter tracking.

Proper indexing is critical for query performance. With ~558,000 records, well-designed indexes enable millisecond lookups; poor indexing leads to full table scans and multi-second response times.

Essential Indexes (Required for All Implementations):

-- Index 1: NPANXX lookup (most common query pattern)
CREATE INDEX idx_npanxx ON npanxx(NPANXX);

-- Index 2: NPA (area code) lookup
CREATE INDEX idx_npa ON npanxx(NPA);

-- Index 3: ZIP code reverse lookup
CREATE INDEX idx_zipcode ON npanxx(ZipCode);

-- Index 4: State filtering (for geographic queries)
CREATE INDEX idx_state ON npanxx(State);

Recommended Composite Indexes (Query Pattern Optimization):

-- Index 5: NPANXX + ZipCodeFreq (primary ZIP lookup)
CREATE INDEX idx_npanxx_freq ON npanxx(NPANXX, ZipCodeFreq);
-- Optimizes: WHERE NPANXX = ? AND ZipCodeFreq = -1

-- Index 6: NPANXX + ZipCode (composite key stability)
CREATE INDEX idx_npanxx_zip ON npanxx(NPANXX, ZipCode);
-- Optimizes: WHERE NPANXX = ? AND ZipCode = ?

-- Index 7: State + Status (active codes by state)
CREATE INDEX idx_state_status ON npanxx(State, Status);
-- Optimizes: WHERE State = ? AND Status = 'Active'

-- Index 8: Status + NPANXX (active/inactive filtering)
CREATE INDEX idx_status_npanxx ON npanxx(Status, NPANXX);
-- Optimizes: WHERE Status = 'Active' ORDER BY NPANXX

Advanced Indexes (Deluxe Edition, Specialized Use Cases):

-- Index 9: Carrier lookup (OCN)
CREATE INDEX idx_ocn ON npanxx(OCN);
-- Optimizes: WHERE OCN = ? (find all blocks by carrier)

-- Index 10: Time zone scheduling
CREATE INDEX idx_timezone ON npanxx(TimeZone, ObservesDST);
-- Optimizes: WHERE TimeZone = ? AND ObservesDST = 'Y'

-- Index 11: Wireless/Landline filtering
CREATE INDEX idx_nxxusetype ON npanxx(NXXUseType, Status);
-- Optimizes: WHERE NXXUseType = 'W' AND Status = 'Active'

-- Index 12: Geographic queries (coordinates)
-- Note: Only index if doing spatial queries
CREATE INDEX idx_coordinates ON npanxx(Latitude, Longitude);
-- Optimizes: Bounding box queries, proximity searches

Query Performance Examples:

Index Maintenance Considerations:

• Storage Overhead: Each index adds ~5-15 MB depending on field size. Total indexed database: Standard ~40MB, Deluxe ~250MB
• Write Performance: Indexes slow inserts/updates. For quarterly bulk updates, consider dropping indexes before import and rebuilding after
• Memory Usage: MySQL/PostgreSQL cache hot indexes in RAM. With proper indexing, entire working set fits in ~128MB RAM for sub-millisecond queries

Optimal Bulk Import Strategy:

-- Step 1: Drop indexes before quarterly import
DROP INDEX idx_npanxx ON npanxx;
DROP INDEX idx_npa ON npanxx;
-- ... drop all indexes

-- Step 2: Truncate and import data (10x faster without indexes)
TRUNCATE TABLE npanxx;
LOAD DATA LOCAL INFILE 'npanxx.csv' INTO TABLE npanxx ...;

-- Step 3: Rebuild indexes (one-time cost)
CREATE INDEX idx_npanxx ON npanxx(NPANXX);
CREATE INDEX idx_npa ON npanxx(NPA);
-- ... recreate all indexes

-- Step 4: Analyze table for query optimization
ANALYZE TABLE npanxx;

Verify Index Usage:

-- Check if your query uses indexes (MySQL)
EXPLAIN SELECT * FROM npanxx WHERE NPANXX = '212555';
-- Look for "Using index" in the Extra column

-- PostgreSQL equivalent
EXPLAIN ANALYZE SELECT * FROM npanxx WHERE NPANXX = '212555';
-- Look for "Index Scan" instead of "Seq Scan"

Don't Over-Index: Only create indexes for fields you actually query. Unused indexes waste storage and slow down imports. Monitor query patterns for 2-4 weeks after launch, then add targeted indexes for slow queries. Start with the essential 4-8 indexes above and expand as needed.

Proper data type selection ensures data integrity, optimal storage, and query performance. Here are field-by-field recommendations with rationale.

Standard Edition Fields:

Deluxe Edition Additional Fields:

Complete MySQL Schema Example:

CREATE TABLE npanxx (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Core identifiers (Standard Edition)
    NPA CHAR(3) NOT NULL,
    NXX CHAR(3) NOT NULL,
    NPANXX CHAR(6) NOT NULL,
    State VARCHAR(2),
    LATA VARCHAR(10),
    
    -- ZIP correlation (Standard Edition)
    ZipCode VARCHAR(10),
    ZipCodeCount INT,
    ZipCodeFreq INT,
    
    -- Geographic data (Deluxe Edition)
    Latitude DECIMAL(10,6),
    Longitude DECIMAL(10,6),
    WeightedLatitude DECIMAL(10,6),
    WeightedLongitude DECIMAL(10,6),
    City VARCHAR(100),
    County VARCHAR(100),
    CountyPop INT,
    FIPS VARCHAR(10),
    MSA_CBSA VARCHAR(255),
    
    -- Carrier & routing (Deluxe Edition)
    Company VARCHAR(255),
    OCN VARCHAR(10),
    RateCenter VARCHAR(100),
    CoverageAreaName VARCHAR(255),
    
    -- Service classification (Deluxe Edition)
    NXXUseType VARCHAR(2),
    Status VARCHAR(20),
    NXXIntroVersion VARCHAR(20),
    
    -- Area code changes (Deluxe Edition)
    NPANew VARCHAR(10),
    Overlay INT,
    
    -- Time intelligence (Deluxe Edition)
    TimeZone INT,
    ObservesDST VARCHAR(2),
    
    -- Indexes
    INDEX idx_npanxx (NPANXX),
    INDEX idx_npa (NPA),
    INDEX idx_zipcode (ZipCode),
    INDEX idx_state (State)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

All data files (CSV, TAB, and Access MDB) use UTF-8 character encoding to ensure proper handling of special characters, international names, and symbols. Understanding encoding is critical to avoid data corruption during import.

Encoding Specifications:

• CSV/TAB Files: UTF-8 without BOM (Byte Order Mark)
• Access MDB: Native Unicode support (automatically UTF-16LE internally)
• Database Target: Must be configured for UTF-8 (MySQL: utf8mb4, PostgreSQL: UTF8, SQL Server: UTF-8 or nvarchar)

Why UTF-8 Matters:

While most data contains standard ASCII characters, certain fields may include special characters:

• Carrier Names: Company names with special characters (e.g., "AT&T", "O'Fallon Telephone")
• Geographic Names: Cities and counties with apostrophes, hyphens, or accented characters
• Canadian Data: French-influenced place names (though rare in this database)
• Special Symbols: Copyright symbols, trademark symbols in company names

Import Configuration by Platform:

MySQL:

-- Set database character set
CREATE DATABASE npanxx_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

-- Import with explicit encoding
LOAD DATA LOCAL INFILE 'npanxx.csv'
INTO TABLE npanxx
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

PostgreSQL:

-- Set database encoding
CREATE DATABASE npanxx_db 
ENCODING 'UTF8' 
LC_COLLATE='en_US.UTF-8' 
LC_CTYPE='en_US.UTF-8';

-- Import automatically uses UTF-8 if database is UTF-8
COPY npanxx FROM '/path/to/npanxx.csv'
DELIMITER ',' CSV HEADER;

SQL Server:

-- Use nvarchar for Unicode support
CREATE TABLE npanxx (
    ...
    Company NVARCHAR(255), -- Note: NVARCHAR not VARCHAR
    City NVARCHAR(100),
    ...
);

-- Import with UTF-8 code page (65001)
BULK INSERT npanxx
FROM 'C:\path\to\npanxx.csv'
WITH (
    CODEPAGE = '65001', -- UTF-8
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

Verification After Import:

-- Check for encoding issues
SELECT Company FROM npanxx WHERE Company LIKE '%&%';
-- Should return clean "AT&T", not "AT&T"

SELECT Company FROM npanxx WHERE Company LIKE '%?%';
-- Question marks often indicate failed character conversion

-- Verify database encoding (MySQL)
SHOW VARIABLES LIKE 'character_set%';

-- Verify database encoding (PostgreSQL)
SHOW SERVER_ENCODING;

Note: Our CSV files are UTF-8 without BOM (Byte Order Mark). Some text editors add BOM automatically when saving-avoid this as it can cause the first field name to be misread during import (e.g., "?NPANXX" with invisible BOM character).

Detailed platform-specific import instructions with complete examples. Choose your database platform below for copy-paste-ready code.

MySQL / MariaDB Import:

-- Step 1: Create database
CREATE DATABASE npanxx_db 
CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

USE npanxx_db;

-- Step 2: Create table (see Question 12 for complete schema)
CREATE TABLE npanxx (
    id INT AUTO_INCREMENT PRIMARY KEY,
    NPANXX CHAR(6) NOT NULL,
    NPA CHAR(3) NOT NULL,
    NXX CHAR(3) NOT NULL,
    State VARCHAR(2),
    ZipCode VARCHAR(10),
    ZipCodeCount INT,
    ZipCodeFreq INT,
    LATA VARCHAR(10),
    -- Add remaining fields based on edition
    INDEX idx_npanxx (NPANXX),
    INDEX idx_npa (NPA),
    INDEX idx_zipcode (ZipCode),
    INDEX idx_state (State)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Step 3: Import CSV
LOAD DATA LOCAL INFILE '/path/to/npanxx.csv'
INTO TABLE npanxx
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS
(NPA, NXX, @ignore, ZipCode, ZipCodeCount, ZipCodeFreq, @ignore, NPANXX, State, LATA)
SET id = NULL;

-- Step 4: Verify import
SELECT COUNT(*) FROM npanxx;
SELECT * FROM npanxx LIMIT 10;

-- Step 5: Optimize table
ANALYZE TABLE npanxx;

Troubleshooting MySQL Import:

• Error 1148 "not allowed to execute this command": Enable local_infile: SET GLOBAL local_infile = 1;
• File not found: Use absolute paths, verify file permissions
• Encoding issues: Ensure CHARACTER SET utf8mb4 is specified

PostgreSQL Import:

-- Step 1: Create database
CREATE DATABASE npanxx_db 
ENCODING 'UTF8' 
LC_COLLATE='en_US.UTF-8' 
LC_CTYPE='en_US.UTF-8';

\c npanxx_db

-- Step 2: Create table
CREATE TABLE npanxx (
    id SERIAL PRIMARY KEY,
    NPANXX CHAR(6) NOT NULL,
    NPA CHAR(3) NOT NULL,
    NXX CHAR(3) NOT NULL,
    State VARCHAR(2),
    ZipCode VARCHAR(10),
    ZipCodeCount INTEGER,
    ZipCodeFreq INTEGER,
    LATA VARCHAR(10)
    -- Add remaining fields based on edition
);

-- Step 3: Create indexes
CREATE INDEX idx_npanxx ON npanxx(NPANXX);
CREATE INDEX idx_npa ON npanxx(NPA);
CREATE INDEX idx_zipcode ON npanxx(ZipCode);
CREATE INDEX idx_state ON npanxx(State);

-- Step 4: Import CSV
COPY npanxx(NPA, NXX, ZipCode, ZipCodeCount, ZipCodeFreq, NPANXX, State, LATA)
FROM '/absolute/path/to/npanxx.csv'
DELIMITER ',' 
CSV HEADER;

-- Step 5: Verify import
SELECT COUNT(*) FROM npanxx;
SELECT * FROM npanxx LIMIT 10;

-- Step 6: Update table statistics
ANALYZE npanxx;

Troubleshooting PostgreSQL Import:

• Permission denied: File must be readable by postgres user, or use \COPY command (runs as your user)
• \COPY alternative: \COPY npanxx FROM 'npanxx.csv' DELIMITER ',' CSV HEADER;
• Column mismatch: Ensure field order in CSV matches table definition or specify column order

SQL Server Import:

-- Step 1: Create database
CREATE DATABASE npanxx_db;
GO

USE npanxx_db;
GO

-- Step 2: Create table
CREATE TABLE npanxx (
    id INT IDENTITY(1,1) PRIMARY KEY,
    NPANXX CHAR(6) NOT NULL,
    NPA CHAR(3) NOT NULL,
    NXX CHAR(3) NOT NULL,
    State VARCHAR(2),
    ZipCode VARCHAR(10),
    ZipCodeCount INT,
    ZipCodeFreq INT,
    LATA VARCHAR(10)
    -- Add remaining fields (use NVARCHAR for Unicode support)
);

-- Step 3: Create indexes
CREATE INDEX idx_npanxx ON npanxx(NPANXX);
CREATE INDEX idx_npa ON npanxx(NPA);
CREATE INDEX idx_zipcode ON npanxx(ZipCode);
CREATE INDEX idx_state ON npanxx(State);
GO

-- Step 4: Import CSV
BULK INSERT npanxx
FROM 'C:\path\to\npanxx.csv'
WITH (
    CODEPAGE = '65001',       -- UTF-8
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,             -- Skip header
    TABLOCK
);

-- Step 5: Verify import
SELECT COUNT(*) FROM npanxx;
SELECT TOP 10 * FROM npanxx;

-- Step 6: Update statistics
UPDATE STATISTICS npanxx;

Troubleshooting SQL Server Import:

• Cannot bulk load: File must be accessible to SQL Server service account
• Network path: Use UNC paths for remote files: \\server\share\npanxx.csv
• Quoted fields: BULK INSERT doesn't handle quoted fields well; consider SSIS or bcp utility for complex CSV

Alternative: Import via Programming Language:

// Python with pandas (works for all databases)
import pandas as pd
from sqlalchemy import create_engine

# Read CSV
df = pd.read_csv('npanxx.csv', encoding='utf-8')

# Create database connection
engine = create_engine('mysql+pymysql://user:pass@localhost/npanxx_db')

# Import to database
df.to_sql('npanxx', con=engine, if_exists='replace', index=False)

print(f"Imported {len(df)} records")

For more detailed schema definitions including all Deluxe edition fields, see Question 12: Data Type Recommendations.

When processing large volumes of phone numbers (1M+ records), query optimization and batching strategies are critical. Poor implementation can take hours; optimized approaches complete in minutes.

Performance Comparison:

Strategy 1: JOIN with Temporary Table (Recommended for SQL-based processing)

-- Step 1: Create temporary table with phone numbers to lookup
CREATE TEMPORARY TABLE phone_lookup (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone_number VARCHAR(15),
    npanxx_key CHAR(6),
    INDEX idx_npanxx (npanxx_key)
);

-- Step 2: Bulk insert your phone numbers
LOAD DATA LOCAL INFILE 'phones_to_lookup.csv'
INTO TABLE phone_lookup
(phone_number)
SET npanxx_key = LEFT(phone_number, 6);

-- Step 3: Single JOIN query (processes all records at once)
SELECT 
    pl.phone_number,
    n.NPANXX,
    n.State,
    n.City,
    n.ZipCode,
    n.TimeZone,
    n.NXXUseType,
    n.Company
FROM phone_lookup pl
LEFT JOIN npanxx n ON pl.npanxx_key = n.NPANXX
WHERE n.Status = 'Active' AND n.ZipCodeFreq = -1;

-- Result: 1M records processed in ~3 minutes with proper indexes

Strategy 2: In-Memory Lookup (Best for application-level processing)

// Python example with pandas (works similarly in other languages)
import pandas as pd

# Step 1: Load entire NPANXX database into memory (~250MB for Deluxe)
npanxx_db = pd.read_csv('npanxx.csv', dtype={'NPANXX': str})

# Step 2: Filter to primary ZIP correlations (optional, reduces memory)
npanxx_db = npanxx_db[npanxx_db['ZipCodeFreq'] == -1]

# Step 3: Create lookup dictionary (O(1) lookup time)
npanxx_dict = npanxx_db.set_index('NPANXX').to_dict('index')

# Step 4: Process phone numbers
def lookup_phone(phone):
    npanxx_key = phone[:6]
    return npanxx_dict.get(npanxx_key, None)

# Step 5: Batch process millions of records
phones = pd.read_csv('phones_to_process.csv')
phones['npanxx_data'] = phones['phone'].apply(lookup_phone)

# Result: 1M records in ~2 minutes, scales linearly

Strategy 3: Indexed Batch Queries (When temp tables aren't available)

-- Extract unique NPA-NXX combinations first (deduplication)
SELECT DISTINCT LEFT(phone_number, 6) AS npanxx_key
FROM customer_phones
INTO OUTFILE '/tmp/unique_npanxx.csv';

-- Batch lookup with IN clause (max 1000 items per batch)
SELECT NPANXX, State, City, ZipCode, TimeZone, NXXUseType
FROM npanxx
WHERE NPANXX IN (
    '212555', '213555', '214555', ...  -- up to 1000 values
)
AND Status = 'Active' AND ZipCodeFreq = -1;

-- Repeat for each batch of 1000 unique NPA-NXX codes

Strategy 4: Database Views for Repeated Queries

-- Create optimized view (primary ZIPs, active only)
CREATE VIEW npanxx_primary AS
SELECT 
    NPANXX, NPA, NXX, State, ZipCode, City, County,
    Latitude, Longitude, TimeZone, ObservesDST,
    NXXUseType, Company, OCN
FROM npanxx
WHERE Status = 'Active' AND ZipCodeFreq = -1;

-- Use view for all lookups (pre-filtered, faster)
SELECT phone_number, n.*
FROM customer_phones cp
JOIN npanxx_primary n ON LEFT(cp.phone_number, 6) = n.NPANXX;

Optimization Checklist:

• Use proper indexes: NPANXX must be indexed (see Question 11)
• Filter early: Status='Active' and ZipCodeFreq=-1 reduces result set
• Extract NPA-NXX once: Don't compute LEFT(phone, 6) repeatedly
• Deduplicate lookups: If same NPA-NXX appears 1000x, look up once
• Use batch operations: Never query row-by-row in loops
• Consider caching: For real-time apps, cache frequent lookups in Redis/Memcached
• Profile first: Use EXPLAIN to verify index usage before production

Real-World Example: CRM Enrichment

-- Scenario: Enrich 5M customer records with time zone data
-- Step 1: Add columns to customer table
ALTER TABLE customers 
ADD COLUMN npanxx CHAR(6),
ADD COLUMN timezone INT,
ADD COLUMN observes_dst VARCHAR(2);

-- Step 2: Extract NPA-NXX from phone numbers
UPDATE customers 
SET npanxx = LEFT(phone, 6)
WHERE phone IS NOT NULL AND LENGTH(phone) >= 6;

-- Step 3: Single UPDATE with JOIN (completes in minutes)
UPDATE customers c
JOIN npanxx n ON c.npanxx = n.NPANXX
SET 
    c.timezone = n.TimeZone,
    c.observes_dst = n.ObservesDST
WHERE n.Status = 'Active' AND n.ZipCodeFreq = -1;

-- Result: 5M customers enriched in ~8 minutes

Quarterly updates synchronize with NANPA releases and include new assignments, status changes, and data corrections. Choosing the right update strategy depends on your uptime requirements and change tracking needs.

Update Approach Comparison:

Strategy 1: Full Replacement (Recommended for Most Use Cases)

-- Step 1: Drop indexes (speeds up import 10x)
DROP INDEX idx_npanxx ON npanxx;
DROP INDEX idx_npa ON npanxx;
DROP INDEX idx_zipcode ON npanxx;
DROP INDEX idx_state ON npanxx;

-- Step 2: Clear existing data
TRUNCATE TABLE npanxx;

-- Step 3: Import new quarterly data
LOAD DATA LOCAL INFILE '/path/to/npanxx_Q1_2025.csv'
INTO TABLE npanxx
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

-- Step 4: Rebuild indexes
CREATE INDEX idx_npanxx ON npanxx(NPANXX);
CREATE INDEX idx_npa ON npanxx(NPA);
CREATE INDEX idx_zipcode ON npanxx(ZipCode);
CREATE INDEX idx_state ON npanxx(State);

-- Step 5: Update statistics
ANALYZE TABLE npanxx;

-- Total downtime: 5-15 minutes depending on server

Strategy 2: Table Swap / Blue-Green Deployment (Zero Downtime)

See Question 17: Zero-Downtime Updates for detailed implementation.

Strategy 3: Incremental Merge (For Change Tracking)

-- Step 1: Create staging table with new data
CREATE TABLE npanxx_staging LIKE npanxx;

LOAD DATA LOCAL INFILE '/path/to/npanxx_Q1_2025.csv'
INTO TABLE npanxx_staging ...;

-- Step 2: Identify changes (new, updated, deleted)
-- New records
INSERT INTO npanxx_changes (change_type, npanxx, zipcode, timestamp)
SELECT 'NEW', s.NPANXX, s.ZipCode, NOW()
FROM npanxx_staging s
LEFT JOIN npanxx p ON s.NPANXX = p.NPANXX AND s.ZipCode = p.ZipCode
WHERE p.NPANXX IS NULL;

-- Updated records (compare critical fields)
INSERT INTO npanxx_changes (change_type, npanxx, zipcode, timestamp)
SELECT 'UPDATED', s.NPANXX, s.ZipCode, NOW()
FROM npanxx_staging s
JOIN npanxx p ON s.NPANXX = p.NPANXX AND s.ZipCode = p.ZipCode
WHERE s.Status != p.Status 
   OR s.Company != p.Company
   OR s.City != p.City;

-- Deleted records
INSERT INTO npanxx_changes (change_type, npanxx, zipcode, timestamp)
SELECT 'DELETED', p.NPANXX, p.ZipCode, NOW()
FROM npanxx p
LEFT JOIN npanxx_staging s ON p.NPANXX = s.NPANXX AND p.ZipCode = s.ZipCode
WHERE s.NPANXX IS NULL;

-- Step 3: Apply changes
DELETE FROM npanxx;
INSERT INTO npanxx SELECT * FROM npanxx_staging;

-- Step 4: Cleanup
DROP TABLE npanxx_staging;

Strategy 4: Dual Version (Test Before Switching)

-- Maintain two complete tables
CREATE TABLE npanxx_current LIKE npanxx;
CREATE TABLE npanxx_next LIKE npanxx;

-- Load current production data into npanxx_current
-- Load new quarterly data into npanxx_next

-- Test and validate npanxx_next
SELECT COUNT(*) FROM npanxx_next;
SELECT * FROM npanxx_next WHERE Status = 'Active' LIMIT 100;

-- When ready, atomic switch via view or table rename
RENAME TABLE 
    npanxx TO npanxx_old,
    npanxx_next TO npanxx;

-- Rollback if issues discovered
RENAME TABLE 
    npanxx TO npanxx_next,
    npanxx_old TO npanxx;

Typical Changes Per Quarterly Update:

• New NPA-NXX Assignments: 2,000-5,000 new blocks (Status changes from Inactive to Active)
• Status Changes: 500-1,000 blocks (reassignments, retirements)
• Carrier Changes: 1,000-2,000 blocks (mergers, acquisitions, transfers)
• Geographic Updates: 100-500 blocks (rate center changes, coordinate corrections)
• ZIP Correlation Updates: 500-1,500 blocks (improved correlation analysis)

Automated Update Script Template:

#!/bin/bash
# quarterly_update.sh - Automated NPANXX database update

# Configuration
DB_NAME="npanxx_db"
BACKUP_DIR="/backups"
DATA_FILE="/downloads/npanxx_Q1_2025.csv"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

# Step 1: Backup current database
echo "Creating backup..."
mysqldump $DB_NAME npanxx > "$BACKUP_DIR/npanxx_backup_$TIMESTAMP.sql"

# Step 2: Update database
echo "Importing new data..."
mysql $DB_NAME << EOF
TRUNCATE TABLE npanxx;
LOAD DATA LOCAL INFILE '$DATA_FILE' INTO TABLE npanxx ...;
ANALYZE TABLE npanxx;
EOF

# Step 3: Verify record count
RECORD_COUNT=$(mysql -N $DB_NAME -e "SELECT COUNT(*) FROM npanxx;")
echo "Import complete: $RECORD_COUNT records"

if [ $RECORD_COUNT -lt 500000 ]; then
    echo "ERROR: Record count too low, restoring backup"
    mysql $DB_NAME < "$BACKUP_DIR/npanxx_backup_$TIMESTAMP.sql"
    exit 1
fi

echo "Update successful!"

For applications requiring continuous availability (24/7 operations, real-time APIs), use table swapping (blue-green deployment) to update the database with zero service interruption. The switch happens in milliseconds.

Blue-Green Table Swap Strategy:

Step 1: Prepare New Table (Background, No Impact)

-- Create new table with identical structure
CREATE TABLE npanxx_new LIKE npanxx;

-- Copy indexes from production table
SHOW CREATE TABLE npanxx;
-- Apply same indexes to npanxx_new

-- Import quarterly data into new table (production unaffected)
LOAD DATA LOCAL INFILE '/path/to/npanxx_Q1_2025.csv'
INTO TABLE npanxx_new
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 ROWS;

-- Rebuild indexes on new table
ANALYZE TABLE npanxx_new;

-- Validate new data (while production continues serving requests)
SELECT COUNT(*) FROM npanxx_new;
SELECT * FROM npanxx_new WHERE Status = 'Active' LIMIT 1000;
-- Run validation queries to ensure data quality

Step 2: Atomic Swap (Sub-Second Downtime)

-- Rename tables atomically (blocks for ~100ms)
RENAME TABLE 
    npanxx TO npanxx_old,
    npanxx_new TO npanxx;

-- Production now uses new data instantly
-- Old table preserved as npanxx_old for rollback

Step 3: Verify & Cleanup

-- Verify production is using new data
SELECT COUNT(*) FROM npanxx;
SELECT MAX(NXXIntroVersion) FROM npanxx;  -- Should show recent dates

-- Monitor application for 24-48 hours

-- If everything stable, drop old table
DROP TABLE npanxx_old;

-- If issues discovered, instant rollback:
RENAME TABLE 
    npanxx TO npanxx_new,
    npanxx_old TO npanxx;

Alternative: Database Views (Application Transparency)

-- One-time setup: Use view instead of direct table access
CREATE VIEW npanxx_live AS SELECT * FROM npanxx_blue;

-- Application queries view, not table:
-- SELECT * FROM npanxx_live WHERE NPANXX = '212555';

-- Quarterly update process:
-- Step 1: Load new data into inactive table
LOAD DATA INTO npanxx_green ...;

-- Step 2: Switch view to new table (instant)
CREATE OR REPLACE VIEW npanxx_live AS SELECT * FROM npanxx_green;

-- Step 3: Next quarter, switch back to blue
CREATE OR REPLACE VIEW npanxx_live AS SELECT * FROM npanxx_blue;

-- Ping-pong between blue and green tables each quarter

Complete Blue-Green Deployment Script:

#!/bin/bash
# zero_downtime_update.sh

DB="npanxx_db"
NEW_DATA="/downloads/npanxx_Q1_2025.csv"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)

echo "[$TIMESTAMP] Starting zero-downtime update..."

# Step 1: Create and populate new table (no production impact)
echo "Creating new table..."
mysql $DB << EOF
CREATE TABLE npanxx_new LIKE npanxx;
LOAD DATA LOCAL INFILE '$NEW_DATA' 
INTO TABLE npanxx_new
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' IGNORE 1 ROWS;

CREATE INDEX idx_npanxx ON npanxx_new(NPANXX);
CREATE INDEX idx_npa ON npanxx_new(NPA);
CREATE INDEX idx_zipcode ON npanxx_new(ZipCode);
CREATE INDEX idx_state ON npanxx_new(State);
ANALYZE TABLE npanxx_new;
EOF

# Step 2: Validate new data
NEW_COUNT=$(mysql -N $DB -e "SELECT COUNT(*) FROM npanxx_new;")
echo "New table record count: $NEW_COUNT"

if [ $NEW_COUNT -lt 500000 ]; then
    echo "ERROR: Record count too low, aborting"
    mysql $DB -e "DROP TABLE npanxx_new;"
    exit 1
fi

# Step 3: Atomic swap
echo "Performing atomic table swap..."
mysql $DB -e "RENAME TABLE npanxx TO npanxx_old, npanxx_new TO npanxx;"

# Step 4: Verify production
PROD_COUNT=$(mysql -N $DB -e "SELECT COUNT(*) FROM npanxx;")
echo "Production table record count: $PROD_COUNT"

if [ $PROD_COUNT -eq $NEW_COUNT ]; then
    echo "? Update successful! Production now using Q1 2025 data"
    echo "  Old table preserved as npanxx_old for 48h monitoring"
else
    echo "ERROR: Count mismatch, rolling back..."
    mysql $DB -e "RENAME TABLE npanxx TO npanxx_new, npanxx_old TO npanxx;"
    exit 1
fi

Downtime Comparison:

Production Considerations:

• Storage: Requires 2x disk space during transition (old + new table)
• Indexes: Build all indexes on new table before swap; don't defer
• Foreign Keys: If other tables reference npanxx, use view approach instead of direct table swap
• Replication: In master-slave setups, RENAME replicates cleanly; verify slave consistency post-swap
• Monitoring: Keep old table for 24-48 hours; monitor error rates and query patterns before dropping
• Rollback Window: Instant rollback available until npanxx_old is dropped

Testing Your Swap: Before running in production, practice the table swap with a test database. Time the RENAME operation under load to understand exact behavior. Most systems complete the swap in 50-200ms regardless of table size.

Yes, FTP/SFTP access is available upon request for automated quarterly retrieval. This eliminates manual download steps and enables fully automated update pipelines.

FTP Access Details:

• Availability: All license types (Standard, Deluxe) and all editions
• Request Process: Email info@zip-codes.com or call 1-800-425-1169
• Provisioning Time: 1-2 business days
• Access Type: Pull-only (you retrieve files from our server)
• Connection: FTP (port 21) or SFTP (port 22) depending on your preference

Automated Download Script (Bash + SFTP):

#!/bin/bash
# auto_download_npanxx.sh - Automated quarterly FTP retrieval

# Configuration
SFTP_HOST="ftp.zip-codes.com"
SFTP_USER="your_username"
SFTP_PASS="your_password"
REMOTE_DIR="/npanxx/deluxe"
LOCAL_DIR="/data/npanxx/downloads"
TIMESTAMP=$(date +%Y%m%d)

# Create local directory if needed
mkdir -p "$LOCAL_DIR"

# Download latest files via SFTP
sshpass -p "$SFTP_PASS" sftp -oBatchMode=no "$SFTP_USER@$SFTP_HOST" << EOF
cd $REMOTE_DIR
lcd $LOCAL_DIR
mget *.zip
bye
EOF

# Verify download
if [ $? -eq 0 ]; then
    echo "[$TIMESTAMP] Download successful"
    
    # Unzip files
    cd "$LOCAL_DIR"
    unzip -o "*.zip"
    
    # Trigger database update script
    /scripts/quarterly_update.sh
else
    echo "[$TIMESTAMP] ERROR: Download failed"
    exit 1
fi

Python Automated Download:

#!/usr/bin/env python3
# auto_download_npanxx.py

import ftplib
import os
from datetime import datetime

# Configuration
FTP_HOST = 'ftp.zip-codes.com'
FTP_USER = 'your_username'
FTP_PASS = 'your_password'
REMOTE_DIR = '/npanxx/deluxe'
LOCAL_DIR = '/data/npanxx/downloads'

def download_latest_files():
    """Download latest NPANXX files via FTP"""
    timestamp = datetime.now().strftime('%Y%m%d_%H%M%S')
    
    try:
        # Connect to FTP server
        ftp = ftplib.FTP(FTP_HOST)
        ftp.login(FTP_USER, FTP_PASS)
        ftp.cwd(REMOTE_DIR)
        
        # List files
        files = ftp.nlst()
        print(f"[{timestamp}] Found {len(files)} files on server")
        
        # Download each file
        os.makedirs(LOCAL_DIR, exist_ok=True)
        for filename in files:
            if filename.endswith('.zip') or filename.endswith('.csv'):
                local_path = os.path.join(LOCAL_DIR, filename)
                print(f"Downloading {filename}...")
                
                with open(local_path, 'wb') as f:
                    ftp.retrbinary(f'RETR {filename}', f.write)
                
                print(f"? {filename} downloaded")
        
        ftp.quit()
        print(f"[{timestamp}] Download complete")
        return True
        
    except Exception as e:
        print(f"[{timestamp}] ERROR: {str(e)}")
        return False

if __name__ == '__main__':
    if download_latest_files():
        # Trigger database update
        os.system('/scripts/quarterly_update.sh')
    else:
        exit(1)

Scheduled Automation (Cron Job):

# Add to crontab: crontab -e

# Run on the 5th of every month at 2:00 AM (after new data is posted)
0 2 5 * * /scripts/auto_download_npanxx.sh >> /var/log/npanxx_download.log 2>&1

# Alternative: Weekly check (Sundays at 3 AM)
0 3 * * 0 /scripts/auto_download_npanxx.sh >> /var/log/npanxx_download.log 2>&1

Complete Automated Pipeline Example:

#!/bin/bash
# complete_update_pipeline.sh - Full end-to-end automation

TIMESTAMP=$(date +%Y%m%d_%H%M%S)
LOG_FILE="/var/log/npanxx_pipeline_$TIMESTAMP.log"

{
    echo "[$TIMESTAMP] Starting automated update pipeline"
    
    # Step 1: Download latest files
    echo "Step 1: Downloading from FTP..."
    /scripts/auto_download_npanxx.sh
    if [ $? -ne 0 ]; then
        echo "ERROR: Download failed"
        exit 1
    fi
    
    # Step 2: Validate downloaded files
    echo "Step 2: Validating files..."
    FILE_SIZE=$(stat -f%z "/data/npanxx/downloads/npanxx.csv")
    if [ $FILE_SIZE -lt 100000000 ]; then  # Less than 100MB
        echo "ERROR: File size too small ($FILE_SIZE bytes)"
        exit 1
    fi
    
    # Step 3: Backup current database
    echo "Step 3: Backing up current database..."
    mysqldump npanxx_db npanxx > "/backups/npanxx_backup_$TIMESTAMP.sql"
    
    # Step 4: Import new data (zero-downtime)
    echo "Step 4: Importing new data..."
    /scripts/zero_downtime_update.sh
    
    # Step 5: Verify update
    echo "Step 5: Verifying update..."
    RECORD_COUNT=$(mysql -N npanxx_db -e "SELECT COUNT(*) FROM npanxx;")
    echo "New record count: $RECORD_COUNT"
    
    # Step 6: Send notification
    echo "Step 6: Sending completion notification..."
    echo "NPANXX database updated: $RECORD_COUNT records" | \
        mail -s "NPANXX Update Complete" admin@yourcompany.com
    
    echo "[$TIMESTAMP] Pipeline complete!"
    
} | tee "$LOG_FILE"

File Organization on FTP Server:

/npanxx/
+-- npanxx-standard/
¦   +-- {YYYY}-{MM}-StandardAreaCodeDatabase-csv.zip
¦   +-- {YYYY}-{MM}-StandardAreaCodeDatabase-mdb.zip
¦   +-- {YYYY}-{MM}-StandardAreaCodeDatabase-tab.zip
¦   +-- ...
+-- npanxx-deluxe/
¦   +-- {YYYY}-{MM}-DeluxeAreaCodeDatabase-csv.zip
¦   +-- {YYYY}-{MM}-DeluxeAreaCodeDatabase-mdb.zip
¦   +-- {YYYY}-{MM}-DeluxeAreaCodeDatabase-tab.zip
+-- ...

# Each zip contains: CSV, TAB, and MDB formats

Yes, you receive email notifications when each quarterly update is released. Notifications are sent to the email address associated with your account, typically within 24 hours of the new data being posted.

Notification Content:

• Release announcement (e.g., "Q1 2025 NPA-NXX Database Now Available")
• Direct link to your account download page
• Your account username (password not included for security)
• Brief summary of update highlights (if applicable)
• Technical support contact information

Notification Timing:

Managing Notifications:

• Whitelist Our Address: Add info@zip-codes.com to your email whitelist/safe sender list to ensure delivery
• Update Email Address: Contact support to change the email address receiving notifications
• Multiple Recipients: Request multiple email addresses for team distribution
• Unsubscribe: Use the unsubscribe link in emails (account access remains active)

Webhook Integration (For Automated Workflows):

While we don't currently offer webhook callbacks, you can achieve automated notification handling using these approaches:

Approach 1: Email-to-Webhook Service

# Use services like Zapier, IFTTT, or Mailgun to convert emails to webhooks

# Example Zapier workflow:
1. Trigger: New email from info@zip-codes.com with subject "Q* Database"
2. Filter: Subject contains "NPA-NXX" or "Area Code"
3. Action: POST to your webhook endpoint
4. Action: Execute download script

# Your webhook endpoint receives:
POST /webhooks/npanxx-update
{
    "quarter": "Q1 2025",
    "release_date": "2025-04-05",
    "edition": "Deluxe"
}

Approach 2: Scheduled Polling

#!/usr/bin/env python3
# poll_for_updates.py - Check FTP for new files periodically

import ftplib
import json
import os
from datetime import datetime

LAST_CHECK_FILE = '/var/lib/npanxx/last_update.json'

def check_for_updates():
    """Poll FTP server for new files"""
    
    # Load last known file list
    if os.path.exists(LAST_CHECK_FILE):
        with open(LAST_CHECK_FILE, 'r') as f:
            last_files = json.load(f)
    else:
        last_files = []
    
    # Check FTP for current files
    ftp = ftplib.FTP('ftp.zip-codes.com')
    ftp.login('username', 'password')
    ftp.cwd('/npanxx/deluxe')
    current_files = ftp.nlst()
    ftp.quit()
    
    # Detect new files
    new_files = set(current_files) - set(last_files)
    
    if new_files:
        print(f"New update detected: {new_files}")
        
        # Trigger your update pipeline
        os.system('/scripts/complete_update_pipeline.sh')
        
        # Update last check record
        with open(LAST_CHECK_FILE, 'w') as f:
            json.dump(current_files, f)
        
        return True
    else:
        print("No new updates available")
        return False

if __name__ == '__main__':
    check_for_updates()

# Schedule in cron: Daily check at 6 AM
# 0 6 * * * /scripts/poll_for_updates.py

For more details on FTP automation to eliminate dependence on email notifications, see the previous FAQ, Can I automate downloads via FTP or SFTP?

The WeightedLatitude and WeightedLongitude fields (Deluxe edition only) provide population-adjusted coordinates that account for multi-ZIP NPA-NXX distributions. They offer more accurate geographic representation than standard rate center coordinates.

The Problem Weighted Coordinates Solve:

Telephone exchange boundaries and ZIP code boundaries don't align perfectly. A single NPA-NXX block can serve multiple ZIP codes with different population densities. The standard Latitude/Longitude represents the rate center's base location, which may not reflect where most phone numbers in that block are actually located.

Calculation Methodology:

// Weighted coordinate formula
WeightedLatitude = S(ZIP_Latitude × ZipCodeCount) / S(ZipCodeCount)
WeightedLongitude = S(ZIP_Longitude × ZipCodeCount) / S(ZipCodeCount)

// Example: NPA-NXX 212-555 serves 3 ZIP codes
ZIP 10001: Lat 40.7580, Pop 5,423
ZIP 10002: Lat 40.7162, Pop 1,876  
ZIP 10003: Lat 40.7310, Pop 987

// Standard coordinate (rate center)
Latitude = 40.7580  (rate center location)

// Weighted coordinate (population center)
WeightedLatitude = (40.7580×5423 + 40.7162×1876 + 40.7310×987) / (5423+1876+987)
WeightedLatitude = 40.7475  (shifted toward population density)

Visual Comparison:

When to Use Standard vs Weighted Coordinates:

Use Standard Coordinates (Latitude, Longitude) for:

• Telecom Infrastructure: Rate center location is the authoritative point for network planning
• Regulatory Compliance: LATA and rate center definitions use official rate center coordinates
• Carrier Routing: Traditional call routing based on rate center geography
• Historical Analysis: Comparing with older data that predates weighted coordinates

Use Weighted Coordinates (WeightedLatitude, WeightedLongitude) for:

• Customer Density Mapping: Visualizing where phone numbers are actually concentrated
• Service Area Optimization: Defining coverage areas based on population distribution
• Demographic Targeting: More accurate attribution for market analysis
• Distance Calculations: Better estimates of distance to customer base
• Sales Territory Planning: Assigning territories based on where customers actually are
• Proximity Searches: "Find customers within 10 miles" queries

Implementation Examples:

-- Query 1: Map customer density (use weighted)
SELECT 
    NPANXX,
    WeightedLatitude,
    WeightedLongitude,
    SUM(ZipCodeCount) as total_population
FROM npanxx
WHERE Status = 'Active'
GROUP BY NPANXX
ORDER BY total_population DESC
LIMIT 100;

-- Query 2: Find NPA-NXX near a location (use weighted for customer proximity)
SELECT 
    NPANXX,
    City,
    State,
    SQRT(
        POW(WeightedLatitude - 40.7128, 2) + 
        POW(WeightedLongitude - (-74.0060), 2)
    ) * 69 AS approx_miles  -- Rough conversion to miles
FROM npanxx
WHERE Status = 'Active' AND ZipCodeFreq = -1
  AND WeightedLatitude != 0 AND WeightedLongitude != 0
HAVING approx_miles < 50
ORDER BY approx_miles;

-- Query 3: Infrastructure planning (use standard for rate center)
SELECT 
    RateCenter,
    Latitude,
    Longitude,
    COUNT(*) as npanxx_count
FROM npanxx
WHERE Status = 'Active'
GROUP BY RateCenter, Latitude, Longitude;

Accuracy Improvement Statistics:

• Single-ZIP NPA-NXX: No difference (coordinates identical)
• Dual-ZIP NPA-NXX: 15-30% more accurate on average (~0.5-2km improvement)
• Triple-ZIP+ NPA-NXX: 30-60% more accurate on average (~2-8km improvement)
• Wide-Area NPA-NXX: Up to 80% more accurate (can be 10-20km improvement in rural areas)

Remember: Both coordinate sets use 0 to indicate unknown location (never empty string). Always check for WHERE WeightedLatitude != 0 AND WeightedLongitude != 0 before using in calculations.