Mapping between different territorial codes

Code systems

  • United States Postal Service (USPS)

    • ZIP codes

  • Federal Information Processing Standard (FIPS) codes

    • States

    • Counties

  • Social Security Administration (SSA) Codes

    • States

    • Counties

Mapping sources

Mapping between FIPS and SSA codes is available from National Bureau of Economic Research (NBER)

Mapping between FIPS and ZIP codes we have taken from United States Department of Housing and Urban Development (HUD) Office of Policy Development and Research (PD&R) ZIP crosswalk files

Python and SQL Code

Two utilities are provided to downloaded mapping files from sources and load them into the database:

  • Python module to download and ingest NBER FIPS <-> SSA mapping files: ssa2fips

  • Python module to download and ingest HUD ZIP to FIPS mappings: zip2fips

  • DDL for HUD ZIP to FIPS mappings: hud_zip2fips.ddl

  • SQL to create direct ZIP => FIPS lookup table and helper functions: zip2fips.sql

Tables and Functions

All tables and functions are in schema “public”.

Tables

  • ssa

  • hud_zip2fips

  • MATERIALIZED VIEW public.zip2fips for direct lookups

Functions

The functions below are defined in zip2fips.sql and used to map zip codes to county FIPS codes or to validate that a given combination of zip code and county code exists.

  • public.zip_to_fips(year int, zip int) RETURNS int. This function looks for all counties that intersected the provided zip code area in the given year. The intersecting counties are then sorted by the ratio of county residential addresses within the zip code area: the number of residential addresses in the intersection of the
    ZIP code area and the county divided by the total number of residential addresses in the entire ZIP. The county with the highest ratio is selected as the return value. The FIPS code for such county is returned as an integer (e.g., 12).

  • public.zip_to_fips3(year int, zip int) RETURNS varchar(3) - the same as the above, but return value is a 3-character string (VARCHAR(3)) padded by zeros (e.g., ‘012’).

  • public.is_zip_to_fips_exact(year int, zip int) RETURNS bool returns true if county fips code can be unambiguously inferred from the given zip code in the given year, false otherwise. In particular this function return false if a year is missing from mapping files.

  • public.validate_zip_fips(zip int, fips2 varchar, fips3 varchar) RETURNS bool returns true if a given combination of the codes is a valid combination, i.e. if there are exist (or ever existed) any residential, business or other addresses in the given state and county that have the given zip code.