ZIP Code Boundary Data Technical Specifications

Boundary and Centroid files represent two different ways of depicting ZIP Codes geographically.

Boundary Files (~41,000 records):

• Geometry: Polygon or MultiPolygon shapes
• Coverage: Only ZIP Codes with deliverable street addresses
• Use Cases: Choropleth maps, territory management, spatial analysis, boundary visualization
• Exclusive Field: COLOR field for map rendering
• File Types: All 8 formats (SHP, GeoJSON, SQL, KML, etc.)

Centroid Files (~33,000 records):

• Geometry: Point coordinates (latitude/longitude)
• Coverage: All ZIP Code types including PO Box, Unique, and point-only ZIPs
• Use Cases: Pin maps, proximity analysis, geocoding, distance calculations
• Exclusive Field: ENCZIP field (indicates which ZIP encloses a point-only ZIP)
• File Types: All 8 formats

Bottom line: Use Boundary files when you need to show ZIP Code coverage areas and perform spatial operations. Use Centroid files when you need single-point representation for all ZIP Codes, including those without street delivery boundaries.

Filler ZIP Codes are synthetic codes we create to ensure continuous nationwide coverage without map holes. They represent approximately 176 out of 41,113 records (0.43%) of the boundary dataset.

Two types of filler codes:

• ###MH - Marine/water areas (e.g., 999MH, 998MH)
• ###MX - Land areas without USPS delivery service (e.g., 997MX, 996MX)

Why they exist:

The USPS only creates ZIP Codes where mail is delivered. This leaves gaps-open water, national forests, military reservations, uninhabited land. Without filler codes, your maps would have holes where spatial queries fail or rendering breaks.

Should you include them?

How to filter them out:

-- SQL example
SELECT * FROM zip_boundaries 
WHERE ZIPTYPE != 'FILLER';

-- Or filter by ZIP code pattern
WHERE ZIP NOT LIKE '%MH' AND ZIP NOT LIKE '%MX';

Pro tip: Keep filler ZIPs in your database for complete spatial coverage, but use ZIPTYPE field to exclude them from business logic, reporting, and demographics as needed.

Yes, absolutely. GIS tools and libraries can convert between all spatial formats-but we provide all 8 formats to save you the work and ensure proper configuration.

Popular conversion tools:

QGIS (Free, Open Source):

• Right-click layer → Export → Save Features As
• Select target format (GeoJSON, Shapefile, KML, etc.)
• Configure CRS (EPSG:4326) and field mappings

GDAL/OGR Command Line (Free):

# Shapefile to GeoJSON
ogr2ogr -f "GeoJSON" output.geojson input.shp

# GeoJSON to PostGIS
ogr2ogr -f "PostgreSQL" PG:"dbname=mydb" input.geojson -nln zip_boundaries

# Shapefile to KML
ogr2ogr -f "KML" output.kml input.shp

Python (GeoPandas):

import geopandas as gpd

# Read any format
gdf = gpd.read_file('ZIP_Boundaries.shp')

# Write to any format
gdf.to_file('output.geojson', driver='GeoJSON')
gdf.to_file('output.gpkg', driver='GPKG')
gdf.to_file('output.kml', driver='KML')

ArcGIS Pro:

• Use "Export Features" or "Feature Class to Feature Class"
• Supports all major formats with GUI options

Why we provide all formats:

• Ensures CRS is correctly defined (EPSG:4326)
• Field types properly configured for each format
• Coordinate precision preserved (6 decimals)
• Saves you 30-60 minutes of conversion and validation work

Bottom line: You can convert formats if needed, but we've already done the work to ensure each format is properly configured and ready to use.

All coordinates use WGS84 geodetic datum (EPSG:4326) with 6 decimal places of precision.

What this means:

• Datum: WGS84 (World Geodetic System 1984) - same as GPS
• SRID: 4326
• Precision: 6 decimal places = ±0.11 meters (~4 inches)
• Format: Decimal degrees (not degrees-minutes-seconds)
• Axis Order: Longitude, Latitude (x, y)

Example coordinates:

LAT: 30.485018 (6 decimals)
LON: -87.190713 (6 decimals)

This represents Pensacola, Florida with ~4-inch precision.

Precision comparison:

Database storage:

-- Recommended data types
PostgreSQL/PostGIS: DECIMAL(11,8) or DOUBLE PRECISION
SQL Server: DECIMAL(11,8) or FLOAT
MySQL: DECIMAL(11,8) or DOUBLE

-- Example schema
LAT DECIMAL(11,8)  -- -90.000000 to 90.000000
LON DECIMAL(11,8)  -- -180.000000 to 180.000000

Note: 6 decimal places is the sweet spot for ZIP Code boundaries-more precision than needed for delivery zones, but ensures sub-meter accuracy for analysis and rendering.

No. Our boundaries are high-precision polygons aligned with USPS carrier routes-not simplified or generalized.

What this means:

• Boundaries follow actual street centerlines and delivery routes
• Coastal boundaries include complex shoreline details
• Urban boundaries capture tight, irregular shapes
• No vertex reduction or smoothing applied
• Topology preserved (no gaps or overlaps between adjacent ZIPs)

Why we don't simplify:

• Accuracy: Customers need precise boundaries for spatial analysis
• Geocoding: Point-in-polygon queries require accurate edges
• Legal/Compliance: Some applications require exact USPS-aligned boundaries
• Flexibility: You can simplify if needed; we can't restore lost detail

If you need simplified boundaries:

You can simplify geometries yourself for specific use cases like web mapping at small scales:

-- PostGIS simplification
UPDATE zip_boundaries 
SET geom_simplified = ST_Simplify(geom, 0.001);

-- Python (GeoPandas)
gdf['geometry'] = gdf['geometry'].simplify(tolerance=0.001)

-- QGIS: Vector → Geometry Tools → Simplify

Typical file size impact:

• Full precision: ~150 MB (GeoJSON)
• Simplified (0.001°): ~50 MB (loses <1% accuracy)
• Simplified (0.01°): ~15 MB (noticeable accuracy loss)

Pro tip: Keep the full-precision boundaries in your database for analysis. Create simplified versions for web map tiles at zoom levels below 1:100,000 scale where users can't see the difference anyway.

Yes, we maintain historical ZIP Code boundary data dating back to 2017.

What's available:

• Quarterly snapshots from 2017 to present
• Same field structure and format options as current data
• Includes retired ZIP Codes that have since been discontinued
• Both Boundary and Centroid files for each quarter

Common use cases:

• Time-series analysis: How ZIP boundaries changed over time
• Historical geocoding: Match addresses to correct ZIP for past dates
• Research studies: Longitudinal studies requiring historical geography
• Compliance/audit: Reconstruct territory assignments as of specific dates
• Data reconciliation: Match legacy records to boundaries that existed when data was created

How to obtain:

Contact us with your specific timeframe needs. Availability and pricing vary by quarter and format. We can provide:

• Individual quarterly snapshots
• Year-long packages (4 quarters)
• Custom date ranges for specific projects

Contact Us About Historical Data

Note: Historical data availability begins in 2017. For earlier timeframes, contact us to discuss possible alternatives or custom research options.

The COLOR field is a map colorization index that ensures no two adjacent ZIP Codes share the same color value.

How it works:

• Values: 1, 2, 3, 4 (four-color theorem implementation)
• Rule: Adjacent/touching ZIP boundaries have different COLOR values
• Purpose: Creates visually distinct boundaries on choropleth maps
• Availability: Boundary files only (not in Centroid files)

Use cases:

1. Choropleth Map Rendering:

-- Assign distinct colors to adjacent ZIPs
SELECT 
    ZIP,
    CASE COLOR
        WHEN 0 THEN '#3B82F6'  -- Blue
        WHEN 1 THEN '#10B981'  -- Green
        WHEN 2 THEN '#F59E0B'  -- Orange
        WHEN 3 THEN '#EF4444'  -- Red
    END as fill_color
FROM zip_boundaries;

2. Web Mapping (Mapbox/Leaflet):

// Style based on COLOR field
map.setPaintProperty('zip-layer', 'fill-color', [
  'match',
  ['get', 'COLOR'],
  0, '#3B82F6',
  1, '#10B981',
  2, '#F59E0B',
  3, '#EF4444',
  '#CCCCCC'  // fallback
]);

3. Print Maps / Static Exports:

Use COLOR to ensure clear boundary delineation when printing grayscale or color maps-adjacent territories will always have contrasting shades.

Why this matters:

• Prevents visual confusion when ZIP boundaries touch
• Saves you from calculating adjacency relationships
• Works immediately-no graph coloring algorithms needed
• Consistent across quarterly updates (same ZIP = same color where possible)

Note: The four-color theorem guarantees that four colors are sufficient to color any planar map so no adjacent regions share the same color. We've done the work so you don't have to.

ZIPTYPE classifies each ZIP Code by its delivery characteristics and usage. This field helps you filter and process boundaries based on their real-world function.

ZIPTYPE values:

How to use ZIPTYPE:

Filter for deliverable addresses only:

SELECT * FROM zip_boundaries 
WHERE ZIPTYPE IN ('NON_UNIQUE', 'UNIQUE');

Exclude synthetic filler ZIPs:

SELECT * FROM zip_boundaries 
WHERE ZIPTYPE != 'FILLER';

Identify business/organizational ZIPs:

SELECT ZIP, NAME, STATE 
FROM zip_boundaries 
WHERE ZIPTYPE = 'UNIQUE'
ORDER BY STATE, ZIP;

Real-world applications:

• Address validation: Exclude PO_BOX and FILLER types from street address validation
• Demographic analysis: Filter out FILLER types (zero population)
• Territory assignment: Focus on NON_UNIQUE ZIPs for standard delivery territories
• Market analysis: Identify UNIQUE ZIPs for B2B targeting (universities, military bases, corporate campuses)

Best practice: Always check ZIPTYPE before applying business logic. A PO_BOX ZIP has no geographic street delivery area, and a FILLER ZIP has no residents or businesses-filtering by ZIPTYPE prevents misleading analysis.

All major GIS platforms work with our boundary data. We provide industry-standard formats that integrate seamlessly with desktop GIS, web mapping, and spatial databases.

Desktop GIS Software:

• ArcGIS Pro / ArcMap (Esri): Import Shapefile or GeoJSON directly. Native support for all spatial operations.
• QGIS (Free/Open Source): Drag-and-drop Shapefile or GeoJSON. Full editing, analysis, and export capabilities.
• MapInfo Pro: Import Shapefile with native TAB conversion. Full topology and styling support.
• Global Mapper: Supports all 8 formats. Excellent for format conversion and batch processing.
• GeoPandas (Python): gpd.read_file() works with Shapefile, GeoJSON, or any format.

Web Mapping Libraries:

• Leaflet: Use GeoJSON format. Load via AJAX or bundle with your application.
• Mapbox GL JS: GeoJSON or Mapbox Vector Tiles (convert from our formats).
• OpenLayers: Native support for GeoJSON, KML, and WKT formats.
• Google Maps API: Use KML format or convert GeoJSON to Google Maps data layer.
• D3.js: GeoJSON format for custom cartographic visualizations.

Spatial Databases:

• PostGIS (PostgreSQL): Use our .sql file or import Shapefile via shp2pgsql
• SQL Server: Use our .sql file with geography type, SRID 4326
• MySQL / MariaDB: Use our .sql file with spatial extensions enabled
• Oracle Spatial: Import Shapefile via SQL Loader or convert from WKT
• SQLite / SpatiaLite: Import Shapefile or GeoJSON

Business Intelligence Tools:

• Tableau: Import Shapefile directly. Join to your data on ZIP field.
• Power BI: Import Shapefile or use ArcGIS Maps for Power BI connector.
• Looker: Load to PostGIS, query via SQL with spatial functions.

Cloud Platforms:

• Google BigQuery: Load GeoJSON to GEOGRAPHY column
• Amazon Athena: Query GeoJSON files in S3 with geospatial functions
• Snowflake: Load WKT or GeoJSON to GEOGRAPHY type

Bottom line: If your software handles standard GIS formats (Shapefile, GeoJSON, KML, SQL), it will work with our boundary data. We've been in business since 2003 serving Fortune 500 clients-our data integrates with everything.

Yes, our boundary data works excellently with business intelligence platforms for creating territory maps and spatial dashboards.

Tableau:

1. Import the Shapefile: Data → New Data Source → Spatial file → select .shp file
2. Tableau automatically detects the geometry and attribute fields
3. Join your business data to the boundary data using the ZIP field
4. Create filled maps (polygons) showing metrics by ZIP Code
5. Use dual-axis maps to overlay points and boundaries

Tableau tip: Use the Centroid file for pin maps, Boundary file for territory/choropleth maps. Join both to your dataset for maximum flexibility.

Power BI:

1. Method 1 - Native Shapefile: Get Data → File → Shapefile. Power BI imports as a table with geometry.
2. Method 2 - ArcGIS Maps: Use the ArcGIS Maps for Power BI visual (free). Import our Shapefile as a reference layer.
3. Method 3 - Custom Visual: Use the "Shape Map" visual with GeoJSON or TopoJSON format.
4. Join your data to ZIP field and visualize with filled maps

Looker / Looker Studio (Google Data Studio):

1. Load boundary data to BigQuery (GeoJSON → GEOGRAPHY column)
2. Join to your business data on ZIP field
3. Use Geo Chart or Geo Map visualizations
4. Query with BigQuery geospatial functions (ST_Contains, ST_Intersects)

Qlik Sense:

1. Load Shapefile or convert to CSV with WKT geometry
2. Use GeoAnalytics extension for spatial visualization
3. Join on ZIP field to business metrics

Common BI use cases:

• Sales Territory Analysis: Color-code ZIP Codes by sales performance
• Market Penetration: Show customer density by ZIP boundary
• Service Coverage: Display service areas and availability
• Demographic Overlays: Combine with ZIP Code Database for population/income heatmaps
• Store Trade Areas: Map customer ZIP Codes around retail locations

Example Tableau calculation:

// Color territories by sales performance
IF SUM([Sales]) > 100000 THEN "High"
ELSEIF SUM([Sales]) > 50000 THEN "Medium"
ELSE "Low"
END

Performance note: For dashboards with 41,000+ ZIP boundaries, consider filtering to state or region of interest. Most BI tools handle this well, but filtering improves rendering speed for interactive dashboards.

With 41,000+ ZIP boundaries, file size and performance require strategic handling. Here are proven techniques for different application types.

Database Applications:

1. Spatial Indexes (Critical):

-- PostGIS
CREATE INDEX idx_zip_geom ON zip_boundaries USING GIST(geom);

-- SQL Server
CREATE SPATIAL INDEX idx_zip_geom ON zip_boundaries(geom);

-- MySQL
CREATE SPATIAL INDEX idx_zip_geom ON zip_boundaries(geom);

Spatial indexes reduce point-in-polygon queries from minutes to milliseconds.

2. Attribute Indexes:

CREATE INDEX idx_zip_state ON zip_boundaries(STATE);
CREATE INDEX idx_zip_county ON zip_boundaries(COUNTYFIPS);
CREATE INDEX idx_zip_code ON zip_boundaries(ZIP);

3. Partitioning:

-- Partition by state for regional queries
CREATE TABLE zip_boundaries_fl PARTITION OF zip_boundaries
    FOR VALUES IN ('FL');
-- Repeat for each state

Web Mapping Applications:

1. Vector Tiles (Best Performance):

• Convert boundaries to Mapbox Vector Tiles (MVT) using tippecanoe
• Host tiles on your server or CDN
• Dynamically load only visible tiles at each zoom level
• Reduces initial load from 150MB to <1MB per viewport

2. Geometry Simplification by Zoom:

-- Create multiple resolution versions
CREATE TABLE zip_boundaries_z1 AS 
    SELECT ZIP, ST_Simplify(geom, 0.1) as geom FROM zip_boundaries;
    
CREATE TABLE zip_boundaries_z5 AS 
    SELECT ZIP, ST_Simplify(geom, 0.01) as geom FROM zip_boundaries;
    
CREATE TABLE zip_boundaries_z10 AS 
    SELECT ZIP, geom FROM zip_boundaries; -- Full detail

3. Server-Side Filtering:

// Leaflet example - load only visible ZIPs
map.on('moveend', function() {
    const bounds = map.getBounds();
    fetch(`/api/boundaries?bbox=${bounds.toBBoxString()}`)
        .then(r => r.json())
        .then(data => {
            zipLayer.clearLayers();
            L.geoJSON(data).addTo(zipLayer);
        });
});

4. Client-Side Optimization:

• Use GeoJSON with gzip compression (reduces size 70-80%)
• Implement viewport-based rendering (only draw visible features)
• Use WebGL-based libraries (Mapbox GL, Deck.gl) for GPU acceleration
• Cache tiles in browser localStorage or IndexedDB

Desktop GIS / Analysis:

• Use Shapefile or GeoPackage (more efficient than GeoJSON)
• Enable spatial indexes in QGIS / ArcGIS
• Filter by STATE before loading if doing regional work
• Use projected CRS appropriate to your region for faster processing

API / Microservices:

• Load boundaries into PostGIS at application startup
• Use connection pooling for database access
• Cache frequent queries (e.g., "which ZIP contains this point?") in Redis
• Return only attributes, not full geometry, unless specifically needed

Performance reality check: With proper spatial indexing, PostGIS can execute point-in-polygon queries against 41,000 ZIP boundaries in under 5ms. The bottleneck is rarely the data size-it's almost always missing indexes or poor query patterns.

The download includes all U.S. states and territories as a complete dataset. However, you can easily extract specific states or regions after download using GIS tools or database queries.

Method 1: Extract During Import (Recommended):

PostGIS / SQL Server / MySQL:

-- Import only specific states during load
-- Example: Florida and Georgia only
COPY zip_boundaries 
FROM '/path/to/boundaries.csv'
WHERE STATE IN ('FL', 'GA');

Python (GeoPandas):

import geopandas as gpd

# Read full dataset
gdf = gpd.read_file('ZIP_Boundaries.shp')

# Filter to specific state(s)
florida = gdf[gdf['STATE'] == 'FL']

# Save filtered version
florida.to_file('ZIP_Boundaries_FL.shp')

Method 2: GIS Desktop Tools:

QGIS:

1. Load the Shapefile
2. Right-click layer → Filter
3. Enter: "STATE" IN ('FL', 'GA', 'AL')
4. Right-click → Export → Save Features As → Save filtered version

ArcGIS Pro:

1. Load the Shapefile
2. Use Select by Attributes: STATE IN ('FL', 'GA')
3. Right-click → Data → Export Features
4. Saves only selected features to new file

Method 3: Command Line (GDAL/OGR):

# Extract single state
ogr2ogr -where "STATE='FL'" ZIP_Boundaries_FL.shp ZIP_Boundaries.shp

# Extract multiple states
ogr2ogr -where "STATE IN ('FL','GA','AL')" Southeast.shp ZIP_Boundaries.shp

# Extract by region (example: ZIP codes starting with 3)
ogr2ogr -where "ZIP LIKE '3%'" Southeast.shp ZIP_Boundaries.shp

Regional Groupings:

Why we don't offer pre-filtered downloads:

• Most customers need nationwide data eventually
• Filtering takes 30 seconds with the methods above
• Managing 50+ state-specific download SKUs creates complexity
• You get all formats and full flexibility with the complete dataset

Pro tip: Even if you only need one state for your application, keep the full dataset. You may need neighboring states for border analysis, or expansion into new markets. Storage is cheap-flexibility is valuable.

No, this is a downloadable database product designed for local/offline use. This approach offers significant advantages over API-based services.

Why downloadable vs. API?

Real-world cost comparison:

Example: Geocoding 10 million addresses annually

API Service:
- $0.005 per query × 10M queries = $50,000/year
- Plus overage fees, rate limit upgrades

Our Boundary Data:
- One-time annual license
- Unlimited queries
- Total cost: [Contact for pricing]

How to build your own API:

If you need API functionality for internal or customer-facing applications, you can easily build your own using our data:

Simple Flask API Example (Python):

from flask import Flask, jsonify, request
import geopandas as gpd
from shapely.geometry import Point

app = Flask(__name__)
boundaries = gpd.read_file('ZIP_Boundaries.shp')

@app.route('/geocode')
def geocode():
    lat = float(request.args.get('lat'))
    lon = float(request.args.get('lon'))
    point = Point(lon, lat)
    
    # Find containing ZIP
    result = boundaries[boundaries.contains(point)]
    
    if len(result) > 0:
        return jsonify({
            'zip': result.iloc[0]['ZIP'],
            'name': result.iloc[0]['NAME'],
            'state': result.iloc[0]['STATE']
        })
    return jsonify({'error': 'No ZIP found'})

PostGIS REST API (Production-Grade):

-- Create function for geocoding
CREATE OR REPLACE FUNCTION get_zip_from_point(lon double precision, lat double precision)
RETURNS TABLE(zip char(5), name varchar(40), state char(2)) AS $$
BEGIN
    RETURN QUERY
    SELECT z.ZIP, z.NAME, z.STATE
    FROM zip_boundaries z
    WHERE ST_Contains(z.geom, ST_SetSRID(ST_MakePoint(lon, lat), 4326));
END;
$$ LANGUAGE plpgsql;

-- Call from your API layer (Node.js, Python, Go, etc.)

When you might want an API approach:

• Very low query volume (<1,000/month)
• No in-house infrastructure or database expertise
• Need real-time updates more frequently than quarterly

When our downloadable product is better:

• High query volume (>10,000/month)
• Batch processing requirements
• Need offline/local operation
• Require sub-50ms response times
• Cost-sensitive at scale

Bottom line: Downloadable data gives you complete control, zero per-query costs, and eliminates external dependencies. For high-volume applications, it's both more performant and more economical than API services.

Plan for approximately 1.2GB of uncompressed storage per dataset (Boundary or Centroid). All 8 formats are included in each download.

Complete download package:

• Compressed download: ~750 MB (.zip file)
• Uncompressed total: ~1.22 GB (all 8 formats)

Individual format sizes (uncompressed):

Database storage requirements:

PostGIS (PostgreSQL):

• Table data: ~250 MB
• Spatial index (GIST): ~100 MB
• Attribute indexes: ~50 MB
• Total: ~400 MB

SQL Server:

• Table data: ~300 MB
• Spatial index: ~120 MB
• Attribute indexes: ~60 MB
• Total: ~480 MB

MySQL (with spatial extensions):

• Table data: ~280 MB
• Spatial index: ~110 MB
• Attribute indexes: ~55 MB
• Total: ~445 MB

Quarterly update strategy:

If you maintain multiple quarterly versions for historical comparison:

• Single quarter: 1.2 GB
• 1 year (4 quarters): 4.8 GB
• 3 years (12 quarters): 14.4 GB

Recommended storage allocation:

Optimization tips:

• Keep only the formats you actually use (delete the other 7)
• Compress older quarterly versions (70-80% size reduction with gzip)
• Store in database format only (no need to keep raw files)
• Use table partitioning by STATE to improve query performance

Reality check: In 2025, 1.2GB is trivial. A single iPhone photo can be 5MB, so this dataset is equivalent to ~240 photos. Storage is cheap-don't let file size drive your decision.

ZIP Code boundaries are built from monthly USPS carrier route updates and released quarterly. Most changes are incremental rather than dramatic boundary shifts.

Update cycle:

• USPS updates: Monthly carrier route data releases
• Our compilation: Quarterly boundary builds from monthly USPS feeds
• Your updates: Download new quarterly release, typically ~1 month after USPS data

Types of boundary changes:

1. ZIP Code Splits (Most Common):

A high-growth ZIP exceeds capacity and divides into multiple ZIPs. The original boundary splits, with each new ZIP covering part of the former area.

2. Boundary Adjustments:

Carrier route reassignments shift boundaries between adjacent ZIPs. Streets get reassigned from one ZIP to another for routing efficiency.

3. New ZIP Codes:

New developments, military bases, or organizational facilities receive new ZIP Codes. Boundaries carved out of existing ZIPs or filler areas.

4. ZIP Code Retirements:

Low-volume or redundant ZIPs get discontinued. Their territory absorbed by neighboring ZIPs.

Typical change volume per quarter:

• Boundary modifications: 50-200 ZIPs per quarter
• New ZIPs added: 5-20 per quarter
• ZIPs retired: 2-10 per quarter
• Unchanged: ~40,800 ZIPs remain stable

Geographic patterns:

• High-growth areas: More frequent changes (e.g., Texas, Florida, North Carolina)
• Established metros: Occasional boundary tweaks
• Rural areas: Very stable, minimal changes

Why boundaries change:

• Population growth requiring new delivery infrastructure
• Carrier route optimization for efficiency
• New construction developments
• Postal facility openings or closures
• Address conversions (rural routes to city-style addressing)

How to track changes:

Use the RELVER field to identify data vintage:

-- Compare two quarterly releases
SELECT 
    old.ZIP,
    'Boundary Changed' as change_type
FROM zip_boundaries_q1 old
INNER JOIN zip_boundaries_q2 new ON old.ZIP = new.ZIP
WHERE NOT ST_Equals(old.geom, new.geom);

Bottom line: Quarterly updates keep you current without constant change management. Most ZIPs remain stable quarter-to-quarter, so updates are manageable-but critical for maintaining accuracy in growth markets.

ZIP Codes follow USPS mail delivery routes, not political boundaries. When postal efficiency dictates, a single post office can serve addresses across county or (rarely) state lines.

Why this happens:

• Postal logistics: ZIP Codes are designed for mail delivery efficiency, not geographic neatness
• Historical boundaries: Post office service areas established before strict boundary alignment
• Natural features: Rivers, mountains, or highways that cross political lines also define delivery routes
• Rural coverage: Single post offices serving large areas that span multiple counties

How common is this?

• Multi-county ZIPs: ~2-5% of ZIP Codes cross county lines
• Multi-state ZIPs: Extremely rare (<0.1%), primarily in New England border areas

How our data handles this:

STATE Field:

Each ZIP is assigned to ONE state-the state used in mailing addresses, not necessarily where the geographic centroid falls.

COUNTYFIPS Field:

Primary county where the majority of the ZIP's delivery points are located.

Examples of cross-border ZIPs:

Implications for analysis:

County-level reporting:

-- Get all ZIPs in a county (including partial ZIPs)
SELECT DISTINCT ZIP, NAME, STATE
FROM zip_boundaries
WHERE ST_Intersects(
    geom,
    (SELECT geom FROM county_boundaries WHERE COUNTYFIPS = '12033')
);

State-level aggregation:

-- Sum metrics by state
-- Use STATE field, not spatial intersection
SELECT STATE, 
       COUNT(*) as zip_count,
       SUM(TOTRESCNT) as total_residential
FROM zip_boundaries
WHERE ZIPTYPE != 'FILLER'
GROUP BY STATE;

Best practices:

• Use the STATE field for state assignment, not spatial centroid location
• For county analysis, use spatial intersection if you need "all ZIPs touching this county"
• Document your methodology when aggregating to political boundaries
• Be aware that population/demographic data may not align perfectly with political boundaries

Bottom line: ZIP Codes are postal delivery zones, not political districts. Cross-border ZIPs are a natural consequence of optimizing mail routes. Use the STATE and COUNTYFIPS fields as USPS-assigned identifiers, not strict geographic boundaries.

ZIP Codes aren't designed around residential population-they're designed for mail delivery. Many ZIP Codes serve specific functions with few or no homes.

Understanding the delivery count fields:

• TOTRESCNT: Total residential deliveries (homes + apartments)
• SFDU: Single-family dwelling units (houses)
• MFDU: Multi-family dwelling units (apartments, condos)
• BOXCNT: PO Box deliveries
• BIZCNT: Business deliveries (includes PO Boxes at businesses)

ZIP types with low/zero residential counts:

1. Business Districts (ZIPTYPE = NON_UNIQUE, low TOTRESCNT):

• Downtown cores with office buildings, no residents
• Industrial parks and warehouses
• Shopping centers and commercial zones
• Example: High BIZCNT, zero SFDU/MFDU

2. PO Box-Only ZIPs (ZIPTYPE = PO_BOX):

• Post office facilities serving box holders only
• No street delivery in these ZIPs
• Example: BOXCNT > 0, SFDU = 0, MFDU = 0

3. Unique/Organizational ZIPs (ZIPTYPE = UNIQUE):

• Universities, military bases, government facilities
• Large corporations with dedicated ZIP Codes
• May have on-site housing (dorms, barracks) not counted as residential

4. Filler ZIPs (ZIPTYPE = FILLER):

• Water areas (###MH) - zero everything
• Uninhabited land (###MX) - zero everything

Example query - ZIPs with zero residential but high business:

SELECT ZIP, NAME, STATE, BIZCNT, TOTRESCNT
FROM zip_boundaries
WHERE TOTRESCNT = 0 
  AND BIZCNT > 100
ORDER BY BIZCNT DESC
LIMIT 20;

Why this matters for your analysis:

• Market research: Don't use residential counts alone - businesses are customers too
• Territory planning: Business-heavy ZIPs need daytime coverage, not evening
• Delivery logistics: High BIZCNT = weekday traffic, parking challenges
• Demographics: These ZIPs need external demographic data

Bottom line: Low residential counts don't mean "bad data"-they mean the ZIP serves a specific non-residential function. For complete demographic intelligence, pair boundary data with our ZIP Code Database products.

Boundaries are built from licensed USPS carrier route data. We maintain data integrity through quarterly verification processes, but we defer to the USPS as the authoritative source.

Our quality assurance process:

1. Source Data Validation:

• Verify USPS monthly carrier route feeds are complete
• Confirm all ZIP Codes in USPS national directory are represented
• Check for data corruption or transmission errors

2. Record Count Verification:

• Compare total ZIP count to USPS national totals
• Verify no duplicates or missing records
• Confirm filler ZIP coverage for complete national representation

3. Coordinate Validation:

• Check all coordinates fall within valid ranges (lat: -90 to 90, lon: -180 to 180)
• Verify coordinates align with expected state/county boundaries
• Confirm SRID 4326 (WGS84) is properly defined

4. Topology Verification:

• No gaps between adjacent boundaries (except water/filler areas)
• No overlapping polygons (each point on land belongs to exactly one ZIP)
• Closed polygons (first vertex = last vertex)
• No self-intersecting boundaries

5. Quarterly Change Analysis:

• Review all ZIPs added, modified, or retired
• Verify RELVER field matches current release
• Spot-check boundary changes against USPS documentation

What we DON'T validate:

• Ground truth verification: We don't physically survey ZIP boundaries
• Individual address validation: We don't verify every address falls in the correct ZIP
• Sub-carrier route accuracy: We use USPS data as provided

Why we defer to USPS:

• The USPS is the legal authority for ZIP Code boundaries
• They maintain the carrier route master database
• They update boundaries monthly based on operational needs
• Any discrepancies should reference USPS as ground truth

If you find an error:

1. Contact us at info@zip-codes.com with specific details
2. We'll verify against our source data and USPS documentation
3. If it's a data processing error on our end, we'll correct immediately
4. If it's a USPS source data issue, we'll document and monitor for next USPS update

Bottom line: We ensure data integrity and consistency in our quarterly compilation process, but the USPS carrier route data is the authoritative source. Our boundaries reflect USPS operational reality, not idealized cartography.

Retired ZIP Codes are removed from our dataset in the quarter following USPS discontinuation. Their territory is absorbed by neighboring ZIPs or reassigned.

Why ZIPs get retired:

• Low volume: Insufficient mail volume to justify dedicated ZIP
• Consolidation: Multiple ZIPs merged for postal efficiency
• Facility closure: Post office closed, service transferred to nearby facility
• Unique entity closure: Military base, university, or business closes/relocates
• Address standardization: Legacy ZIP Code systems converted to modern standards

How territory is handled:

• Delivery addresses reassigned to adjacent or logical replacement ZIP
• Mail forwarding typically in place for 12-18 months
• Boundary geometry absorbed by neighboring ZIP(s)
• ZIP Code officially marked inactive in USPS systems

Typical retirement volume:

• Per quarter: 2-10 ZIP Codes retired nationally
• Per year: 10-40 retirements (varies significantly by year)
• Most common: Rural post offices, military base closures, unique organizational ZIPs

For historical analysis:

If you need retired ZIP Code boundaries for historical geocoding or time-series analysis, our historical data archives (back to 2017) include discontinued ZIPs:

-- Identify ZIPs that existed in Q1 but not Q2
SELECT q1.ZIP, q1.NAME, q1.STATE
FROM zip_boundaries_2024q1 q1
LEFT JOIN zip_boundaries_2024q2 q2 ON q1.ZIP = q2.ZIP
WHERE q2.ZIP IS NULL;

Mail forwarding implications:

• USPS typically forwards mail to new ZIP for 12-18 months after retirement
• During transition, old ZIP may still appear in some databases
• Address validation should use current dataset to avoid using retired ZIPs
• Historical records should maintain original ZIP for data integrity

Best practices for handling retirements:

• Quarterly updates: Download new releases to capture retirements
• Historical preservation: Keep old quarterly datasets for reference
• Address cleansing: Flag records with retired ZIPs for manual review
• Migration paths: USPS provides replacement ZIP in official notices

Historical note: Retired ZIPs remain in our historical datasets. If you need to geocode addresses as-of a specific past date, use the quarterly release that was current at that time.

Use spatial analysis to identify ZIPs whose boundaries intersect multiple counties. The COUNTYFIPS field shows the primary county, but not the complete picture for multi-county ZIPs.

Why ZIPs cross county lines:

• Postal delivery routes optimized for efficiency, not political boundaries
• Rural post offices serving large areas across county lines
• Urban areas where ZIP boundaries don't align with county boundaries
• Growth areas where ZIP assignments predate current county boundaries

Method 1: Spatial Intersection Query (Most Accurate):

If you have county boundary data loaded in your spatial database:

-- PostGIS example
SELECT 
    z.ZIP,
    z.NAME,
    z.STATE,
    z.COUNTYFIPS as primary_county,
    COUNT(DISTINCT c.county_fips) as county_count,
    STRING_AGG(DISTINCT c.county_name, ', ') as all_counties
FROM zip_boundaries z
JOIN county_boundaries c 
    ON ST_Intersects(z.geom, c.geom)
GROUP BY z.ZIP, z.NAME, z.STATE, z.COUNTYFIPS
HAVING COUNT(DISTINCT c.county_fips) > 1
ORDER BY county_count DESC;

Method 2: Approximation Using Boundaries:

If you need a quick approximation without county boundary data:

-- Find ZIPs that border different county FIPS codes
SELECT DISTINCT z1.ZIP, z1.NAME, z1.COUNTYFIPS
FROM zip_boundaries z1
JOIN zip_boundaries z2 
    ON ST_Touches(z1.geom, z2.geom)
WHERE z1.COUNTYFIPS != z2.COUNTYFIPS
ORDER BY z1.ZIP;

Note: This method identifies ZIPs adjacent to other counties, which may or may not actually cross the county line.

Method 3: Using GIS Software:

QGIS / ArcGIS:

1. Load ZIP boundaries and county boundaries
2. Use "Intersection" or "Spatial Join" tool
3. Count distinct counties per ZIP Code
4. Filter for ZIPs with count > 1

Python (GeoPandas):

import geopandas as gpd

# Load datasets
zips = gpd.read_file('ZIP_Boundaries.shp')
counties = gpd.read_file('county_boundaries.shp')

# Spatial join to find all county intersections
joined = gpd.sjoin(zips, counties, how='inner', predicate='intersects')

# Find ZIPs in multiple counties
multi_county = joined.groupby('ZIP').filter(lambda x: len(x['county_fips'].unique()) > 1)

# Summary
summary = multi_county.groupby('ZIP')['county_name'].apply(
    lambda x: ', '.join(x.unique())
).reset_index()

print(summary)

Common multi-county patterns:

• Urban sprawl: Metro ZIPs expanding across suburban county lines
• Rural coverage: Large rural ZIPs serving low-density areas
• Interstate metro areas: Cities on state/county borders (e.g., Kansas City, Cincinnati)
• Military bases: Large installations spanning multiple jurisdictions

Implications for analysis:

• County aggregation: Be aware ZIPs may contribute to multiple county totals
• Demographic apportionment: May need to split ZIP-level metrics by county
• Political boundaries: Voting districts, tax jurisdictions may not align cleanly
• Service areas: County-level services may have jurisdiction issues

Bottom line: Multi-county ZIPs are a natural result of optimizing mail delivery across political boundaries. For precise county-level analysis, use spatial intersection queries with actual county boundary data.

Our boundary data is typically 1-2 months behind USPS monthly carrier route updates. This lag is due to data compilation, quality assurance, and quarterly release cycles.

Update timeline:

Example timeline (Q2 2024):

• April 1, 2024: USPS releases April carrier route data
• May 1, 2024: USPS releases May data
• June 1, 2024: USPS releases June data
• June 15-30, 2024: We compile Q2 boundaries from April/May/June data
• July 1-15, 2024: QA and format generation
• July 20, 2024: Q2 2024 release available to customers

What this means for you:

For most applications ( Data is current enough):

• Territory management and sales analysis
• Market research and demographic overlays
• Web mapping and visualization
• Historical geocoding and address standardization
• Business intelligence and reporting

For time-sensitive applications (️ Consider lag):

• New construction geocoding: Very recent addresses may not be in latest dataset
• Real-time address validation: Consider using USPS API for absolute current data
• Regulatory compliance: Verify data vintage meets requirements

How to check data vintage:

-- RELVER field shows data vintage
SELECT DISTINCT RELVER FROM zip_boundaries;

-- Example output: 202408 (August 2024)
-- This means data is current as of USPS updates through August 2024

Quarterly vs. real-time updates:

Why we use quarterly releases:

• Most ZIP boundaries are stable month-to-month (98%+ unchanged)
• Quarterly cadence balances currency with manageable change management
• Allows thorough QA that would be difficult with monthly releases
• Customers prefer predictable quarterly updates vs. constant monthly changes

If you need more current data:

• Contact us about interim updates for specific high-growth regions
• Use USPS APIs (CASS, AMS) for real-time address validation alongside our boundaries
• Subscribe to USPS direct feeds if you need monthly carrier route updates

Practical advice: For 99% of applications, 1-2 month lag is insignificant. ZIP boundaries change slowly, and quarterly updates keep you well within acceptable currency for mapping, analysis, and most business applications.

These fields show USPS delivery point counts-valuable data for market sizing and density analysis. They represent actual mail delivery destinations, not population estimates.

Field definitions:

Important distinctions:

Delivery Points ≠ Population:

• TOTRESCNT counts dwelling units, not people
• A house with 5 people = 1 SFDU count
• An apartment building with 200 units = 200 MFDU count
• Multiply by average household size (~2.5) for rough population estimate

Business vs. Residential:

• A business can have a street address (counted in BIZCNT)
• A business can have a PO Box (counted in both BOXCNT and BIZCNT)
• Some residential addresses use PO Boxes (not reflected in counts)

Practical use cases:

1. Market Density Analysis:

-- Find high-density residential ZIPs
SELECT ZIP, NAME, STATE, TOTRESCNT,
       ROUND(MFDU * 100.0 / NULLIF(TOTRESCNT, 0), 1) as pct_multifamily
FROM zip_boundaries
WHERE TOTRESCNT > 10000
ORDER BY TOTRESCNT DESC
LIMIT 20;

2. Business District Identification:

-- Find business-heavy ZIPs
SELECT ZIP, NAME, STATE, BIZCNT, TOTRESCNT,
       ROUND(BIZCNT * 100.0 / NULLIF(BIZCNT + TOTRESCNT, 0), 1) as pct_business
FROM zip_boundaries
WHERE ZIPTYPE != 'FILLER'
  AND (BIZCNT + TOTRESCNT) > 0
ORDER BY pct_business DESC
LIMIT 20;

3. Rough Population Estimation:

-- Approximate population (2.5 persons per household average)
SELECT 
    STATE,
    SUM(TOTRESCNT) as total_residences,
    ROUND(SUM(TOTRESCNT) * 2.5) as estimated_population
FROM zip_boundaries
WHERE ZIPTYPE != 'FILLER'
GROUP BY STATE
ORDER BY estimated_population DESC;

4. Housing Type Distribution:

-- Compare single-family vs. multi-family density
SELECT 
    CASE 
        WHEN MFDU * 100.0 / NULLIF(TOTRESCNT, 0) > 80 THEN 'High-Rise Urban'
        WHEN MFDU * 100.0 / NULLIF(TOTRESCNT, 0) > 40 THEN 'Mixed Urban/Suburban'
        WHEN MFDU * 100.0 / NULLIF(TOTRESCNT, 0) > 10 THEN 'Primarily Single-Family'
        ELSE 'Pure Single-Family'
    END as housing_type,
    COUNT(*) as zip_count
FROM zip_boundaries
WHERE TOTRESCNT > 0
GROUP BY 1
ORDER BY 1;

Limitations:

• Counts reflect delivery points, not actual occupancy rates
• Vacant homes still counted as SFDU/MFDU
• No demographic details (age, income, education)
• Seasonal variations not captured (vacation homes counted same as permanent residences)
• University dorms, military barracks may be undercounted

Bottom line: Use delivery counts for quick density assessments and market sizing. They're accurate representations of USPS delivery infrastructure and excellent proxies for residential and business density.

Yes, you can embed boundaries in commercial applications, websites, and services you provide to customers. The license covers internal organizational use, which includes customer-facing applications where the data stays on your infrastructure.

What you CAN do:

• Web applications: Display ZIP boundaries on interactive maps
• Mobile apps: Show service territories or delivery zones
• SaaS platforms: Provide territory analysis features to subscribers
• Internal tools: CRM systems, BI dashboards, sales territory management
• Customer services: Store locators, service area displays, market analysis
• Printed materials: Create maps for reports, presentations, marketing
• API services: Build APIs that query the data and return results (data stays on your servers)

What you CANNOT do:

• Resell the raw data files: Cannot provide downloads of the boundary files to third parties
• Redistribute to clients: Cannot give customers copies of the database
• Sublicense: Cannot grant others the right to use the data independently
• Competitive products: Cannot create competing boundary data products
• Public data sharing: Cannot post the files on open data portals or file-sharing sites

The key distinction: Data Output vs. Raw Data

Real-world examples:

ALLOWED - Store Locator Application:

You build a "Find Locations Near You" tool. User enters ZIP code, your app queries the boundary database to find stores within that ZIP or adjacent ZIPs, displays results on a map with ZIP boundaries shown. Data stays on your servers, users interact with your application.

ALLOWED - Territory Management Dashboard:

You create a sales dashboard that shows sales rep territories as colored ZIP Code regions. Reps log in to your system, see their assigned ZIPs highlighted on a map. Internal tool using boundary data for visualization.

NOT ALLOWED - Providing Downloadable Files:

You build a data marketplace where customers can download various datasets. You cannot offer our ZIP boundary files (Shapefile, GeoJSON, etc.) as downloadable products. This is redistribution of raw data.

NOT ALLOWED - Including in Client Deliverables:

You're a consultant building a custom GIS system for a client. You cannot deliver our boundary files as part of the project handoff. Each end user needs their own license.

Third-party contractors:

You can allow contractors to access the data while working on your behalf, provided:

• They work solely for your benefit (not their own clients)
• They agree to comply with license terms
• They're not competitors in the GIS/boundary data space
• Access terminates when their work for you concludes

When in doubt: If users interact with YOUR application that uses the data internally = OK. If you're giving users copies of the raw boundary files = Not OK. Contact us at info@zip-codes.com for specific use case questions.

Unlimited downloads during your active subscription period. Download as many times as you need for backup, testing, or deploying to multiple environments.

Common download scenarios:

• Quarterly updates: Download each new release to stay current
• Multiple environments: Development, staging, and production servers
• Backup copies: Keep offline archives for disaster recovery
• Format conversions: Download different formats for different systems
• Team distribution: Multiple developers or contractors working on the same license

Download methods available:

Full data vs. update files:

For quarterly updates, you have two options:

• Complete dataset: Full replacement files (recommended for simplicity)
• Transaction files: Differential updates showing only additions/deletions (advanced users)

Best practice: Most customers do full table replacements monthly or quarterly rather than applying transaction files. Test in staging first, keep previous month's data as backup, then use database table swapping for zero-downtime production updates.

Free technical support is included with every subscription. We help customers with data integration, troubleshooting, and optimization throughout their subscription period.

What we support:

• Import assistance: Help loading data into SQL Server, MySQL, PostgreSQL, or other databases
• Format guidance: Choosing the right format for your GIS platform or BI tool
• Field definitions: Explaining schema, field meanings, and join strategies
• Data questions: Understanding boundary changes, filler ZIPs, or USPS update cycles
• Performance optimization: Spatial indexing, query tuning, and large-dataset best practices
• Update procedures: Implementing quarterly refresh workflows
• Integration examples: Sample code and queries for common use cases

How to reach us:

What's NOT included:

• Custom development or consulting work (available separately)
• On-site training or implementation services
• Support for third-party software or platforms
• Debugging your application code (though we can provide guidance)

Enterprise customers: Need more hands-on assistance? We offer paid consulting services for custom integration, bulk processing workflows, and performance optimization projects. Contact us to discuss your requirements.

Complete package (all 8 formats):

• Compressed: ~750 MB download
• Uncompressed: ~1.22 GB total

Individual format sizes (uncompressed):

Database storage recommendations:

• Minimum: 500 MB for current quarter data only
• Recommended: 1-2 GB to maintain 2-3 quarterly versions for rollback capability
• With spatial indexes: Add 20-30% additional space for optimal query performance
• Historical archive: ~1.2 GB per year if keeping all 4 quarterly releases

Bandwidth considerations:

• Initial download: 750 MB compressed (one-time)
• Quarterly updates: Full downloads or differential transaction files available
• FTP automation: Schedule downloads during off-peak hours for minimal impact

Planning tip: Most customers download only the 1-2 formats they actually use rather than all 8. If you're just doing database imports, grab the appropriate SQL format (~140 MB) instead of the full package. You can always download other formats later if needed.

The ZIP field is your primary join key for linking boundary data with demographic information, sales data, customer databases, or census statistics.

Available join keys:

Example 1: Join with our ZIP Code Database

Combine boundary polygons with city names, area codes, time zones, and demographics:

SELECT 
    b.geom,
    b.ZIP,
    z.city,
    z.state_name,
    z.area_code,
    z.timezone,
    z.population,
    z.median_household_income
FROM zip_boundaries b
INNER JOIN zip_code_database z ON b.ZIP = z.zip_code;

Example 2: Join with customer data

Visualize customer distribution by ZIP Code on a map:

SELECT 
    b.geom,
    b.ZIP,
    b.NAME AS zip_city,
    COUNT(c.customer_id) AS customer_count,
    SUM(c.annual_revenue) AS total_revenue
FROM zip_boundaries b
LEFT JOIN customers c ON b.ZIP = c.zip_code
GROUP BY b.geom, b.ZIP, b.NAME;

Example 3: County-level aggregation

Roll up ZIP-level data to counties for regional analysis:

SELECT 
    b.COUNTYFIPS,
    b.COUNTYNAME,
    b.STATE,
    COUNT(DISTINCT b.ZIP) AS zip_count,
    SUM(b.TOTRESCNT) AS total_residences,
    c.median_income
FROM zip_boundaries b
INNER JOIN county_demographics c ON b.COUNTYFIPS = c.fips_code
GROUP BY b.COUNTYFIPS, b.COUNTYNAME, b.STATE, c.median_income;

Join best practices:

• Index join keys: Create indexes on ZIP, COUNTYFIPS, and STATEFIPS for optimal query performance
• Use INNER vs. LEFT JOIN appropriately: INNER for required matches, LEFT to preserve all boundaries
• Handle leading zeros: Ensure ZIP codes are padded to 5 digits (e.g., "00501" not "501")
• Watch for multi-county ZIPs: COUNTYFIPS contains only the primary county - contact us for multi-county tables if needed

Need demographic data? The Boundary Data and ZIP Code Database are separate products. Neither is bundled with the other. If you need city names, area codes, time zones, or demographics, consider purchasing our ZIP Code Database and JOIN the tables via the ZIP field.

ZIPTYPE classifies each ZIP Code by its delivery characteristics and geographic representation. Understanding these types helps you filter data appropriately for your use case.

The four ZIPTYPE values:

Filtering by ZIPTYPE - Common use cases:

-- Only street-delivery ZIPs with polygons
SELECT * FROM zip_boundaries WHERE ZIPTYPE = 'NON_UNIQUE';

-- Exclude filler ZIPs from analysis
SELECT * FROM zip_boundaries WHERE ZIPTYPE != 'FILLER';

-- Find all point-only ZIPs (PO Box + Unique)
SELECT * FROM zip_centroids WHERE ZIPTYPE IN ('PO_BOX', 'UNIQUE');

-- Get residential delivery areas only
SELECT * FROM zip_boundaries 
WHERE ZIPTYPE = 'NON_UNIQUE' AND TOTRESCNT > 0;

Pro tip: Most applications should filter to ZIPTYPE = 'NON_UNIQUE' for standard mapping and demographics analysis. Include PO_BOX and UNIQUE types only when you specifically need those point locations for geocoding or institutional targeting.

Every boundary record includes delivery statistics that help you understand the density, composition, and characteristics of each ZIP Code area. These are valuable analytics fields often overlooked by users.

Field definitions:

Key relationship:

TOTRESCNT = MFDU + SFDU

Total residential count always equals the sum of multifamily and single-family units.

Practical use cases:

1. Market segmentation by housing type:

-- Find urban/high-density areas (high multifamily ratio)
SELECT ZIP, NAME, TOTRESCNT, 
       ROUND((MFDU * 100.0 / TOTRESCNT), 1) AS pct_multifamily
FROM zip_boundaries
WHERE TOTRESCNT > 5000 
  AND (MFDU * 100.0 / TOTRESCNT) > 60
ORDER BY pct_multifamily DESC;

-- Suburban/single-family markets
SELECT ZIP, NAME, SFDU, 
       ROUND((SFDU * 100.0 / TOTRESCNT), 1) AS pct_single_family
FROM zip_boundaries
WHERE TOTRESCNT > 5000 
  AND (SFDU * 100.0 / TOTRESCNT) > 80;

2. Business targeting and B2B prospecting:

-- High business concentration ZIPs (commercial districts)
SELECT ZIP, NAME, STATE, BIZCNT, TOTRESCNT,
       ROUND((BIZCNT * 1.0 / TOTRESCNT), 2) AS business_to_residential_ratio
FROM zip_boundaries
WHERE BIZCNT > 1000
ORDER BY business_to_residential_ratio DESC;

3. Delivery route planning and logistics:

-- Calculate delivery density (addresses per square mile)
-- Requires calculating area from geometry
SELECT ZIP, NAME, TOTRESCNT, BIZCNT,
       ROUND(TOTRESCNT / (ST_Area(geom::geography) / 2589988.11), 0) AS residential_per_sq_mile
FROM zip_boundaries
WHERE TOTRESCNT > 100
ORDER BY residential_per_sq_mile DESC;

4. Lead scoring and prioritization:

-- Score ZIPs for residential service providers
SELECT ZIP, NAME, STATE,
       TOTRESCNT AS total_households,
       SFDU AS single_family_homes,
       -- Higher score = better target
       (SFDU * 2) + MFDU AS lead_score
FROM zip_boundaries
WHERE TOTRESCNT > 3000
ORDER BY lead_score DESC
LIMIT 100;

5. Market sizing and territory planning:

-- Territory summary by county
SELECT COUNTYNAME, STATE,
       COUNT(*) AS zip_count,
       SUM(TOTRESCNT) AS total_households,
       SUM(BIZCNT) AS total_businesses,
       AVG(TOTRESCNT) AS avg_households_per_zip
FROM zip_boundaries
GROUP BY COUNTYNAME, STATE
ORDER BY total_households DESC;

Data quality notes:

• Source: Derived from USPS carrier route data, updated quarterly
• Accuracy: Reflects deliverable addresses, not population or occupancy
• Zero values: Common in PO_BOX, UNIQUE, and FILLER ZIPTYPEs
• Business counts: Include both street addresses and PO Boxes
• Changes over time: Watch for significant increases/decreases indicating development or demographic shifts

RELVER (Release Version) is a critical versioning field that tracks which quarterly release each boundary record came from. Think of it as a timestamp for your data.

Format: YYYYMM

• 202508 = August 2025 release
• 202411 = November 2024 release
• 202502 = February 2025 release

Why this field is important:

Common use cases:

1. Verify data consistency:

-- Check which releases are in your database
SELECT DISTINCT RELVER, COUNT(*) AS record_count
FROM zip_boundaries
GROUP BY RELVER
ORDER BY RELVER DESC;

-- Should return one RELVER value if properly loaded
-- Multiple values indicate mixed/incomplete updates

2. Track quarterly changes:

-- Compare ZIPs added between releases
SELECT current.ZIP, current.NAME, current.STATE
FROM zip_boundaries current
LEFT JOIN zip_boundaries_archive previous 
  ON current.ZIP = previous.ZIP AND previous.RELVER = '202405'
WHERE current.RELVER = '202508'
  AND previous.ZIP IS NULL;

3. Maintain version history:

-- Archive structure for tracking changes over time
CREATE TABLE zip_boundaries_history (
    ZIP CHAR(5),
    NAME VARCHAR(40),
    RELVER VARCHAR(8),
    geom GEOMETRY,
    archived_date DATE,
    -- other fields...
    PRIMARY KEY (ZIP, RELVER)
);

-- Query historical boundary changes for specific ZIP
SELECT ZIP, RELVER, ST_Area(geom) AS area_sq_meters
FROM zip_boundaries_history
WHERE ZIP = '32504'
ORDER BY RELVER;

4. Blue-green deployment pattern:

-- Load new quarterly data into staging table
-- Verify RELVER before promotion to production
SELECT 
    MIN(RELVER) AS oldest_version,
    MAX(RELVER) AS newest_version,
    COUNT(DISTINCT RELVER) AS version_count
FROM zip_boundaries_staging;

-- If version_count = 1 and newest_version = expected, proceed with swap
-- Otherwise, investigate data quality issue

5. Automated update validation:

-- Post-update validation script
SELECT 
    CASE 
        WHEN COUNT(DISTINCT RELVER) = 1 THEN 'PASS'
        ELSE 'FAIL'
    END AS consistency_check,
    CASE 
        WHEN MAX(RELVER) >= '202508' THEN 'PASS'
        ELSE 'FAIL'
    END AS currency_check,
    MAX(RELVER) AS current_version,
    COUNT(*) AS total_records
FROM zip_boundaries;

Best practices:

• Always verify RELVER after loading: Ensure all records have same version
• Document your production version: Track which RELVER is live in each environment
• Keep previous quarter: Maintain last quarter's data for rollback capability
• Index the RELVER field: Improves performance on version-based queries
• Include in backups: RELVER helps identify backup data vintage

Bottom line: RELVER is your data version number. Use it religiously to track what you have, when to update, and how to maintain version consistency across your systems.