A simple database that marries multiple datasets to aid in geolocating addresses via ZIP Code™. All of the data in the database are freely available online (see the Licensing section for links), but not all in the same place and not always easily cross-referenceable. This project is (currently) primarily for my own purposes, so it is quite limited in scope, but I wanted to share it in case anyone else could make use of it. As I have further need of it and as I have time and energy available, I will gradually expand its density and capabilities.
Simple ZIP Code data. Each ZIP Code appears only one time, so codes that cross municipal boundaries may return incorrect place names. Full documentation for the source data.
- zip5 char(5) CONSTRAINT pkey_county_fips PRIMARY KEY
- Five didgit ZIP Code
- city nvarchar(232) NOT NULL
- Official USPS city name
- state_abr nchar(2) NOT NULL
- Official USPS state abbreviation
- county_name nvarchar(232)
- County name
State and County codes. Full documentation for the source data.
- state_postal_abbr nchar(2)
- State postal abbreviation
- state_fips_code nchar(2)
- State FIPS code
- county_fips_code nchar(5)
- County FIPS code
- county_ns_code nchar(8) CONSTRAINT pkey_county_fips PRIMARY KEY
- County NS code
- county_name nvarchar(232)
- County name and legal/statistical area description
- fips_class nchar(2)
- FIPS class code
- functional_status nchar(1)
- Functional status
To be used for deriving County from ZIP Code (but not ZIP Code from County). Full documentation for the source data
- zip5 nchar(5)
- 5 digit USPS ZIP code
- county_code nchar(5)
- 5 digit unique 2000 or 2010 Census county GEOID consisting of state FIPS + county FIPS
- zip_preferred_city nvarchar(232)
- USPS preferred city name
- zip_preferred_state nchar(2)
- USPS preferred state address state
- residential_ratio numeric(20, 19)
- The ratio of residential addresses in the ZIP
- business_ratio numeric(20, 19)
- The ratio of business addresses in the ZIP
- other_ratio numeric(20, 19)
- The ratio of other addresses in the ZIP
- total_ratio numeric(20, 19)
- The ratio of all addresses in the ZIP
- CONSTRAINT pkey_zip_to_county PRIMARY KEY (zip5, county_code)
- zip_to_county.zip5
- zip_to_county.zip_preferred_city
- county_fips.county_name
- state_abbr
- county_fips.state_postal_abbr
- zip_to_county.zip_preferred_state
- zip_to_county.residential_ratio
- zip_to_county.business_ratio
- zip_to_county.other_ratio
- zip_to_county.total_ratio
The INSERT files use the SQL Server Utilities Statement GO to break the INSERTs into batches of 1,000 to accomodate SQL Server's Table Value Constructor INSERT limit. For other SQL flavors, the GO statements will need to be removed, and INSERT batches may need to be limited in another way.
The databse itself, including all SQL and related code, is released under the Public Domain Dedication and License v1.0 complemented by these Community Norms.
Other licenses and restrictions may apply to the data within the database (including data as represented within the relevant INSERT statements).
- Data in the following files are from U.S. Government sources and are assumed to be in the public domain per 17 U.S.C. § 105.
- Data in the following files are derived from SimpleMaps US Zip Codes Database and are released under the Creative Commons Attribution 4.0 International license.
"ZIP Code" is a trademark of the United States Postal Service
A project of 232 Software
