USA: ZIP+4 Codes & Address Data
Overview
United States ZIP codes are five numerical digits long. ZIP+4 codes, also known as add-on or plus-four codes, include four additional digits that identify a geographic segment within a ZIP code delivery area.
Table of Contents
The first digit in a ZIP code represents a defined group of U.S. states. The second and third digits represent a region in that group, such as a large city.
To send mail, only the first five digits of the ZIP code are required. Including the full ZIP+4 code can expedite delivery times due to the specific geographic detail communicated.
While modern marketers know the value in using the ZIP code for demographic research, businesses noticed early that the ZIP code could provide an alternative way for them to determine market groups. The 1967 Annual Report even made note on the unforeseen business uses of the ZIP.
The below figure demonstrates the digits in the ZIP code.
USA_ZIP4CODE
ZIP Codes are not just numbers that identify a location. They have become social identifiers – giving information on the demographics of the people of a certain region. Companies use Zip Codes as important data structures that give them a visual representation of their audience. With this information, companies are making major marketing decisions including the closing or opening of stores, launching new products or services and pivoting sales data into regions. ZIP Codes are POWERFUL and as such, it is necessary for any business to have VALID and ACCURATE ZIP codes.
| Colum Name | Data Type | Description |
|---|---|---|
| ZIP-4 | NUMBER | A USA postal code is a five digit number that forms part of a postal address in the USA. ZIP+4 codes, also known as add-on or plus-four codes, include four additional digits that identify a geographic segment within a ZIP code delivery area |
| NUMBER | NUMBER | The number field represents the particular area such as house number, office number, building number. |
| STREET_ADDRESS | TEXT | This is the name of the street which derived from STREET_NAME + TYPE (e.g SQUAW VALLEY DR) |
| CITY_NAME | TEXT | Name of the city |
| STATE | TEXT | This is the province or state name within USA |
| LATITUDE | NUMBER | latitude of the internal point |
| LONGITUDE | NUMBER | longitude of the internal point |
| STREET_NAME | TEXT | Name of the street without its type (e.g SQUAW VALLEY) |
| TYPE | TEXT | Street types such as Avenue (Ave), Boulevard (Blvd), Drive(DR). There are 186 types present in the USA. |
| DIRECTION | TEXT | This is derived from the street name, such as the street location in the city. The values are E,W,N,S, SE,SW, NE, NW |
Sample use cases
Profiles of Zip Codes with and without Orders
Are the zip codes with orders different from the zip codes without orders?
Classifying and Comparing Zip Codes
Augment zip codes with census information. Wealthier zip codes place orders and less wealthy zip codes do not place orders. Extending this observation leads to the question: Among zip codes that place orders, do wealthier ones place more orders than less wealthy ones?
Wealthiest Zip Code in a State?
Augment zip codes with census information, administrative boundaries and geographic hierarchies (also available from Sonra). Wealth is spread unevenly across the United States. Relative wealth is often more important than absolute wealth, although actual income levels may differ considerably. This inspires a question: What is the wealthiest zip code in each state?
SAMPLE SQL QUERIES
Usage 1
Find an address with known ZIP code.
|
1 2 |
SELECT * FROM USA_POSTALCODE WHERE "ZIP-4" = '15217-2018' |
Usage 2
Find out the number of ZIP codes for state PA and city ‘BRIDGEVILLE’
|
1 2 3 4 5 6 7 |
SELECT STATE, CITY_NAME , COUNT("ZIP-4") AS NUMBER_OF_ZIP_CODE FROM USA_POSTALCODE WHERE STATE = 'PA' AND CITY_NAME = 'BRIDGEVILLE' GROUP BY STATE, CITY_NAME |
Usage 3
Find out all the ZIP codes for state PA, city ‘BRIDGEVILLE’ and street ’VANADIUM RD’
|
1 2 3 |
SELECT * FROM USA_POSTALCODE WHERE STATE = 'PA' AND CITY_NAME = 'BRIDGEVILLE' AND STREET_ADDRESS LIKE 'VANADIUM RD%' |
Usage 4
Find out the Geocoding for the address 12400,FALLEN LEAF DR, DALLAS, TX
|
1 2 3 |
SELECT LATITUDE, LONGITUDE FROM USA_POSTALCODE WHERE NUMBER = '12400' AND STREET_ADDRESS LIKE 'FALLEN LEAF DR%' AND CITY_NAME = 'DALLAS' AND STATE = 'TX' |
Usage 5
Find out the ZIP Code for the address 2099,WENDOVER ST, PITTSBURGH, PA
|
1 |
SELECT "ZIP-4" FROM USA_POSTALCODE WHERE NUMBER = '2099' AND STREET_ADDRESS LIKE 'WENDOVER ST%' AND CITY_NAME = 'PITTSBURGH' AND STATE = 'PA' |
Usage 6
Find out the address by geocoding
|
1 2 3 |
SELECT * FROM "LOCATION"."POSTCODE_USA"."USA_POSTALCODE" WHERE LATITUDE = '33.956921' AND LONGITUDE = '-118.085352' |
