ZIP*Data II:Database Layouts: Difference between revisions

From Melissa Data Wiki
Jump to navigation Jump to search
← Older edit
No edit summary
No edit summary
 
(8 intermediate revisions by the same user not shown)
Line 10: Line 10:
{|class="alternate01 sortable" cellspacing="0"
{|class="alternate01 sortable" cellspacing="0"
!Field Name
!Field Name
!Data Type
!Length
!style="border-right:0px;"|Definition
!style="border-right:0px;"|Definition
|-
|-
|ZIP
|ZIP||Char||5||The ZIP code for the record.
|The ZIP code for the record.
|-
|-
|FIPS
|FIPS||Char||5||The county FIPS code.
|The county FIPS code.
|-
|-
|CountyName
|CountyName||Varchar||26||The name of the county.
|The name of the county.
|-
|-
|Percentage
|Percentage||Decimal||(6,2)||The percentage of deliverable addresses that reside in a county FIPS code for a ZIP Code.
|The percentage of deliverable addresses that reside in a county FIPS code for a ZIP Code.
|-
|-
|Cnt
|Cnt||Varchar||5||The number of records used to determine the percentage.
|The number of records used to determine the percentage.
|}
|}


Line 34: Line 31:
{|class="alternate01 sortable" cellspacing="0"
{|class="alternate01 sortable" cellspacing="0"
!Field Name
!Field Name
!Data Type
!Length
!style="border-right:0px;"|Definition
!style="border-right:0px;"|Definition
|-
|-
|ZIP
|ZIP||Char||5||ZIP Codes identify specific geographic delivery areas. A ZIP code can represent an area within a state, one which crosses state boundaries, or a single building.
|ZIP Codes identify specific geographic delivery areas. A ZIP code can represent an area within a state, one which crosses state boundaries, or a single building.
|-
|-
|State
|State||Char||2||The USPS® standard 2-letter state or territory abbreviation.
|The USPS® standard 2-letter state or territory abbreviation.
|-
|-
|City
|City||Char||30||The city, community, station or other name by which a 5-digit ZIP area is known.
|The city, community, station or other name by which a 5-digit ZIP area is known.
|-
|-
|CityAbbreviation
|CityAbbreviation||Char||13||The abbreviated city name.
|The abbreviated city name.
|-
|-
|Latitude
|Latitude||Decimal||(7,4)||The geographic coordinate of a point measured in degrees north or south of the equator.
|The geographic coordinate of a point measured in degrees north or south of the equator.
|-
|-
|Longitude
|Longitude||Decimal||(9,4)||The geographic coordinate of a point measured in degrees east or west of the Greenwich meridian.
|The geographic coordinate of a point measured in degrees east or west of the Greenwich meridian.
|-
|-
|CountyFips
|CountyFips||Char||5||A Federal Information Processing Standard number is assigned to each county in the United States. FIPS numbers begin with the 2-digit state code and are followed by the 3-digit county code.
|A Federal Information Processing Standard number is assigned to each county in the United States. FIPS numbers begin with the 2-digit state code and are followed by the 3-digit county code.
|-
|-
|CountyName
|CountyName||Char||25||The name of the county.
|The name of the county.
|-
|-
|DominantAreaCode
|DominantAreaCode||Char||3||The 3-digit telephone area code.
|The 3-digit telephone area code.
|-
|-
|ZipType
|ZipType||Char||1||Defines the ZIP Code for delivery purposes.
|Defines the ZIP Code for delivery purposes.
{|class="alternate01" cellspacing="0"
{|class="alternate01" cellspacing="0"
!Code
!Code
!style="border-right:0px;"|Definition
!style="border-right:0px;"|Definition
|-
|-
|P
|P||A ZIP code used only for P.O. Boxes.
|A ZIP code used only for P.O. Boxes.
|-
|-
|U
|U||A unique* ZIP code.
|A unique* ZIP code.
|-
|-
|M
|M||Identifies an APO/FPO ZIP Code.
|Identifies an APO/FPO ZIP Code.
|-
|-
|Blank
|Blank||A standard ZIP Code.
|A standard ZIP Code.
|}
|}
*These are assigned to specific organizations and not to a city as a whole. Unique ZIP's are assigned to organizations such as Reader's Digest, educational institutions and government facilities.
*These are assigned to specific organizations and not to a city as a whole. Unique ZIP's are assigned to organizations such as Reader's Digest, educational institutions and government facilities.
|-
|-
|LastLineIndicator
|LastLineIndicator||Char||1|| An "L" in this field indicates that the city name is the official USPS name for the ZIP Code. (Only one record per ZIP Code is coded with an "L".)
| An "L" in this field indicates that the city name is the official USPS name for the ZIP Code. (Only one record per ZIP Code is coded with an "L".)
|-
|-
|FacilityCode
|FacilityCode||Char||1||This code refers to the Postal Facility Code associated with a particular ZIP Code.
|This code refers to the Postal Facility Code associated with a particular ZIP Code.
{|class="alternate01" cellspacing="0"
{|class="alternate01" cellspacing="0"
!Code
!Code
!style="border-right:0px;"|Definition
!style="border-right:0px;"|Definition
|-
|-
|A
|B||Branch
|Airport Mail Facility (AMF)
|-
|-
|B
|C||Community Post Office (BMF)
|Branch
|-
|-
|C
|N||Community Name, Former Postal Facility or Place Name
|Community Post Office (BMF)
|-
|-
|D
|P||Post Office
|Area Distribution Center
|-
|-
|E
|U||Urbanization
|Sectional Center Facility (SCF)
|-
|-
|F
|X||Unapproved Mailing Name
|Delivery Distribution Center
|}
|-
|-
|G
|FinanceNumber||Char||6||The number that refers to the main Post Office that serves a city. It is also used with multi-coded cities. All ZIP Codes in a multi-coded city are assigned the same Finance Number by the USPS.
|General Mail Facility
|-
|-
|K
|colspan="4"|<b>CBSA</b>
|Bulk Mail Center
|-
|-
|M
|CBSA||Char||5||The Core Based Statistical Area (CBSA) code for the ZIP Code.
|Money Order Unit
|-
|-
|N
|CBSATitle||Varchar||50||The official U.S. Census Bureau name for the CBSA.
|Community Name, Former Postal Facility or Place Name
|-
|-
|P
|CBSAStatus||Char||1||
|Post Office
{|class="alternate01" cellspacing="0"
!Code
!style="border-right:0px;"|Definition
|-
|-
|S
|1||Metropolitan Statistical Area
|Station
|-
|-
|U
|2||Micropolitan Statistical Area
|Urbanization
|-
|X
|Unapproved Mailing Name
|}
|}
|-
|-
|FinanceNumber
|CBSADivision||Char||5||The numeric code for the division within the CBSA containing the ZIP Code. Some CBSA’s are broken into parts known as divisions.
|The number that refers to the main Post Office that serves a city. It is also used with multi-coded cities. All ZIP Codes in a multi-coded city are assigned the same Finance Number by the USPS.
|-
|-
|CBSA
|CBSADivisionTitle||Varchar||50||The official U.S. Census Bureau’s official name for the division within the CBSA containing the ZIP Code.
|The Core Based Statistical Area (CBSA) code for the ZIP Code.
|-
|-
|CBSATitle
|CBSADivisionStatus||Varchar||1||  
|The official U.S. Census Bureau name for the CBSA.
|-
|CBSAStatus
|
{|class="alternate01" cellspacing="0"
{|class="alternate01" cellspacing="0"
!Code
!Code
!style="border-right:0px;"|Definition
!style="border-right:0px;"|Definition
|-
|-
|1
|1||Metropolitan Statistical Area
|Metropolitan Statistical Area
|-
|-
|2
|2||Micropolitan Statistical Area
|Micropolitan Statistical Area
|}
|}
|-
|-
|CBSADivision
|Organization||Char||28||The organization name.
|The numeric code for the division within the CBSA containing the ZIP Code. Some CBSA’s are broken into parts known as divisions.
|-
|-
|CBSADivisionTitle
|colspan="4"|<b>Residential/Business Deliveries</b>
|The official U.S. Census Bureau’s official name for the division within the CBSA containing the ZIP Code.
|-
|-
|CBSADivisionStatus
|ResidentialDeliveries||Varchar||7||The number of active residential mail deliveries to residence addresses.
|
|-
{|class="alternate01" cellspacing="0"
|ResidentailPOBoxes||Varchar||7||The number of active residential mail deliveries made to PO boxes.
!Code
|-
!style="border-right:0px;"|Definition
|BusinessDeliveries||Varchar||7||The number of active business mail deliveries made to business addresses.
|-
|-
|1
|BusinessPOBoxes||Varchar||7||The number of active business mail deliveries made to PO Boxes.
|Metropolitan Statistical Area
|-
|-
|2
|colspan="4"|<b>Population</b>
|Micropolitan Statistical Area
|}
|-
|-
|Organization
|TotalPopulation||Varchar||7||Total Population for a ZIP Code.
|The organization name.
|-
|-
|ResidentialDeliveries
|PopWhite||Varchar||7||The number of people in the population of White/Caucasian decent.
|The number of active residential mail deliveries to residence addresses.
|-
|-
|ResidentailPOBoxes
|PopAfricanAmerican||Varchar||7||The number of people in the population of Black/African American decent.
|The number of active residential mail deliveries made to PO boxes.
|-
|-
|BusinessDeliveries
|PopAmericanIndianAlaskaNative||Varchar||7||The number of people in the population of American Indian or Alaskan Native decent.
|The number of active business mail deliveries made to business addresses.
|-
|-
|BusinessPOBoxes
|PopAsian||Varchar||7||The number of people in the population of Asian decent.
|The number of active business mail deliveries made to PO Boxes.
|-
|-
|TotalPopulation
|PopPacificIslander||Varchar||7||The number of people in the population of Pacific Islander decent.
|Total Population for a ZIP Code.
|-
|-
|PopWhite
|PopOther||Varchar||7||The number of people in the population of Other decent.
|The number of people in the population of White/Caucasian decent.
|-
|-
|PopAfricanAmerican
|PopMultipleRace||Varchar||7||The number of people in the population of Multiple Race decent.
|The number of people in the population of Black/African American decent.
|-
|-
|PopAmericanIndianAlaskaNative
|PopHispanic||Varchar||7||The number of people in the population of Hispanic/Latino decent.
|The number of people in the population of American Indian or Alaskan Native decent.
|-
|-
|PopAsian
|colspan="4"|<b>Age</b>
|The number of people in the population of Asian decent.
|-
|-
|PopPacificIslander
|MedianAge||Varchar||7||The median age of the population.
|The number of people in the population of Pacific Islander decent.
|-
|-
|PopOther
|MedianAgeMale||Varchar||7||The median age of the male portion of the population.
|The number of people in the population of Other decent.
|-
|-
|PopMultipleRace
|MedianAgeFemale||Varchar||7||The median age of the female portion of the population.
|The number of people in the population of Multiple Race decent.
|-
|-
|PopHispanic
|colspan="4"|<b>Education</b>
|The number of people in the population of Hispanic/Latino decent.
|-
|-
|MedianAge
|EduNineOrLess||Varchar||7||The number of people over 18 years of age in the population who have an education level under grade 9.
|The median age of the population.
|-
|-
|MedianAgeMale
|EduSomeHighSchool||Varchar||7||The number of people over 18 years of age in the population who have attended some of high school (between grades 9 and 12) but not graduated.
|The median age of the male portion of the population.
|-
|-
|MedianAgeFemale
|EduHighSchoolGraduate||Varchar||7||The number of people over 18 years of age in the population who have graduated high school.
|The median age of the female portion of the population.
|-
|-
|EduNineOrLess
|EduSomeCollegeNoDiploma||Varchar||7||The number of people over 18 years of age in the population who have attended college but not received a degree.
|The number of people over 18 years of age in the population who have an education level under grade 9.
|-
|-
|EduSomeHighSchool
|EduAssociatesDegree||Varchar||7||The number of people over 18 years of age in the population who have attended college and received an associate's degree.
|The number of people over 18 years of age in the population who have attended some of high school (between grades 9 and 12) but not graduated.
|-
|-
|EduHighSchoolGraduate
|EduBachelorsDegree||Varchar||7||The number of people over 18 years of age in the population who have attended college and received a bachelor's degree.
|The number of people over 18 years of age in the population who have graduated high school.
|-
|-
|EduSomeCollegeNoDiploma
|EduGraduateDegree||Varchar||7||The number of people over 18 years of age in the population who have attended college and received a graduate degree.
|The number of people over 18 years of age in the population who have attended college but not received a degree.
|-
|-
|EduAssociatesDegree
|colspan="4"|<b>Income</b>
|The number of people over 18 years of age in the population who have attended college and received an associate's degree.
|-
|-
|EduBachelorsDegree
|MedianHouseholdIncome||Varchar||7||The median household income of the population.
|The number of people over 18 years of age in the population who have attended college and received a bachelor's degree.
|-
|-
|EduGraduateDegree
|PerCapitaIncome||Varchar||7||The median income per person.
|The number of people over 18 years of age in the population who have attended college and received a graduate degree.
|-
|-
|MedianHouseholdIncome
|colspan="4"|<b>Home Value</b>
|The median household income of the population.
|-
|-
|PerCapitaIncome
|MedianHomeValue||Varchar||7||The median home value in the ZIP Code.
|The median income per person.
|-
|-
|MedianHomeValue
|colspan="4"|<b>Time Zone</b>
|The median home value in the ZIP Code.
|-
|-
|Time Zone
|TimeZone||Varchar||4||A code representing the ZIP Code time zone.
|The number of hours past UTC Time Standard.
{|class="alternate01" cellspacing="0"
{|class="alternate01" cellspacing="0"
!Hours
!Code
!style="border-right:0px;"|Time Zone
!style="border-right:0px;"|Time Zone
|-
|-
|4
|AKST||Alaska Standard Time
|Atlantic
|-
|-
|5
|AST||Atlantic Standard time
|Eastern
|-
|-
|6
|CHST||Chamorro Standard Time
|Central
|-
|-
|7
|CST||Central Standard Time
|Mountain
|-
|-
|7
|EST||Eastern Standard Time
|Arizona
|-
|-
|8
|HAST||Hawaiian-Aleutian Standard Time
|Pacific
|-
|-
|9
|HST||Hawaii Standard time
|Alaska
|-
|-
|10
|MHT||Marshall Islands Time
|Hawaii-Aleutian
|-
|-
|11
|MST||Mountain Standard Time / Mountain Time
|Samoa
|-
|-
|13
|PST||Pacific Standard Time / Pacific Time
|Marshall Islands
|-
|-
|14
|PWT||Palau Time (standard time)
|Guam
|-
|-
|15
|SST||Samoa Standard Time
|Palau
|}
|}


Latest revision as of 20:42, 14 August 2024

← ZIP*Data II

ZIP*Data II Navigation
Introduction
Database Layouts
Distance Calculation
Abbreviations and Codes



It is recommend that if you are going to import this data into another format you make every field a text field.

ZIP/County Database (ZIPCnty.csv)

The ZIP/County database can be used to determine which counties have addresses in a given ZIP Code, based on the FIPS Code.

Field Name Data Type Length Definition
ZIP Char 5 The ZIP code for the record.
FIPS Char 5 The county FIPS code.
CountyName Varchar 26 The name of the county.
Percentage Decimal (6,2) The percentage of deliverable addresses that reside in a county FIPS code for a ZIP Code.
Cnt Varchar 5 The number of records used to determine the percentage.


ZIP Code Database (ZIPData2.csv)

The ZIP Code database contains the core ZIP Code data for the U.S. and Puerto Rico.

Field Name Data Type Length Definition
ZIP Char 5 ZIP Codes identify specific geographic delivery areas. A ZIP code can represent an area within a state, one which crosses state boundaries, or a single building.
State Char 2 The USPS® standard 2-letter state or territory abbreviation.
City Char 30 The city, community, station or other name by which a 5-digit ZIP area is known.
CityAbbreviation Char 13 The abbreviated city name.
Latitude Decimal (7,4) The geographic coordinate of a point measured in degrees north or south of the equator.
Longitude Decimal (9,4) The geographic coordinate of a point measured in degrees east or west of the Greenwich meridian.
CountyFips Char 5 A Federal Information Processing Standard number is assigned to each county in the United States. FIPS numbers begin with the 2-digit state code and are followed by the 3-digit county code.
CountyName Char 25 The name of the county.
DominantAreaCode Char 3 The 3-digit telephone area code.
ZipType Char 1 Defines the ZIP Code for delivery purposes.
Code Definition
P A ZIP code used only for P.O. Boxes.
U A unique* ZIP code.
M Identifies an APO/FPO ZIP Code.
Blank A standard ZIP Code.
  • These are assigned to specific organizations and not to a city as a whole. Unique ZIP's are assigned to organizations such as Reader's Digest, educational institutions and government facilities.
LastLineIndicator Char 1 An "L" in this field indicates that the city name is the official USPS name for the ZIP Code. (Only one record per ZIP Code is coded with an "L".)
FacilityCode Char 1 This code refers to the Postal Facility Code associated with a particular ZIP Code.
Code Definition
B Branch
C Community Post Office (BMF)
N Community Name, Former Postal Facility or Place Name
P Post Office
U Urbanization
X Unapproved Mailing Name
FinanceNumber Char 6 The number that refers to the main Post Office that serves a city. It is also used with multi-coded cities. All ZIP Codes in a multi-coded city are assigned the same Finance Number by the USPS.
CBSA
CBSA Char 5 The Core Based Statistical Area (CBSA) code for the ZIP Code.
CBSATitle Varchar 50 The official U.S. Census Bureau name for the CBSA.
CBSAStatus Char 1
Code Definition
1 Metropolitan Statistical Area
2 Micropolitan Statistical Area
CBSADivision Char 5 The numeric code for the division within the CBSA containing the ZIP Code. Some CBSA’s are broken into parts known as divisions.
CBSADivisionTitle Varchar 50 The official U.S. Census Bureau’s official name for the division within the CBSA containing the ZIP Code.
CBSADivisionStatus Varchar 1
Code Definition
1 Metropolitan Statistical Area
2 Micropolitan Statistical Area
Organization Char 28 The organization name.
Residential/Business Deliveries
ResidentialDeliveries Varchar 7 The number of active residential mail deliveries to residence addresses.
ResidentailPOBoxes Varchar 7 The number of active residential mail deliveries made to PO boxes.
BusinessDeliveries Varchar 7 The number of active business mail deliveries made to business addresses.
BusinessPOBoxes Varchar 7 The number of active business mail deliveries made to PO Boxes.
Population
TotalPopulation Varchar 7 Total Population for a ZIP Code.
PopWhite Varchar 7 The number of people in the population of White/Caucasian decent.
PopAfricanAmerican Varchar 7 The number of people in the population of Black/African American decent.
PopAmericanIndianAlaskaNative Varchar 7 The number of people in the population of American Indian or Alaskan Native decent.
PopAsian Varchar 7 The number of people in the population of Asian decent.
PopPacificIslander Varchar 7 The number of people in the population of Pacific Islander decent.
PopOther Varchar 7 The number of people in the population of Other decent.
PopMultipleRace Varchar 7 The number of people in the population of Multiple Race decent.
PopHispanic Varchar 7 The number of people in the population of Hispanic/Latino decent.
Age
MedianAge Varchar 7 The median age of the population.
MedianAgeMale Varchar 7 The median age of the male portion of the population.
MedianAgeFemale Varchar 7 The median age of the female portion of the population.
Education
EduNineOrLess Varchar 7 The number of people over 18 years of age in the population who have an education level under grade 9.
EduSomeHighSchool Varchar 7 The number of people over 18 years of age in the population who have attended some of high school (between grades 9 and 12) but not graduated.
EduHighSchoolGraduate Varchar 7 The number of people over 18 years of age in the population who have graduated high school.
EduSomeCollegeNoDiploma Varchar 7 The number of people over 18 years of age in the population who have attended college but not received a degree.
EduAssociatesDegree Varchar 7 The number of people over 18 years of age in the population who have attended college and received an associate's degree.
EduBachelorsDegree Varchar 7 The number of people over 18 years of age in the population who have attended college and received a bachelor's degree.
EduGraduateDegree Varchar 7 The number of people over 18 years of age in the population who have attended college and received a graduate degree.
Income
MedianHouseholdIncome Varchar 7 The median household income of the population.
PerCapitaIncome Varchar 7 The median income per person.
Home Value
MedianHomeValue Varchar 7 The median home value in the ZIP Code.
Time Zone
TimeZone Varchar 4 A code representing the ZIP Code time zone.
Code Time Zone
AKST Alaska Standard Time
AST Atlantic Standard time
CHST Chamorro Standard Time
CST Central Standard Time
EST Eastern Standard Time
HAST Hawaiian-Aleutian Standard Time
HST Hawaii Standard time
MHT Marshall Islands Time
MST Mountain Standard Time / Mountain Time
PST Pacific Standard Time / Pacific Time
PWT Palau Time (standard time)
SST Samoa Standard Time

In the U.S., the time of each zone is advanced by one hour every year beginning at 2a.m. on the second Sunday in March. On the first Sunday in November, the time is turned back one hour. States and territories exempted from this observance are Arizona, Hawaii, and Puerto Rico.

Counties which are split between time zones are Gulf, FL; Alieutian Islands, AK; Idaho, ID; Cherry, NE; McKenzie, ND; Morton, ND; Sioux, ND; Jones, SD; and Stanley, SD

Retrieved from "http://wiki.melissadata.com/index.php?title=ZIP*Data_II:Database_Layouts&oldid=20159"