PostgreSQL 9.5是否有邮编-州匹配扩展？PostGIS能否实现该功能？

Great question! Let's break down your options for correcting zip code-state mismatches in PostgreSQL 9.5, including whether PostGIS is a fit for your needs.

Does PostGIS Work for This? #

Absolutely! PostGIS has dedicated tools for US geographic data via the postgis_tiger_geocoder extension, which is perfect for validating zip code-state pairs. Here's how to use it:

1. Enable required extensions (ensure PostGIS is installed on your server first):

   CREATE EXTENSION postgis;
   CREATE EXTENSION postgis_tiger_geocoder;
   

2. Load US TIGER/Line data: This official dataset includes zip code boundaries and their associated states. Generate a shell script to download and load data for the states you care about:

   -- Generate script for NY and AL (adjust the array for your needs)
   SELECT loader_generate_script(ARRAY['ny', 'al'], 'sh');
   

   Run the generated script to populate the Tiger schema with geographic data.
3. Fix mismatched records: Use the zip_to_state function to get the correct state for a zip code, then update your table:

   -- Verify the correct state for a zip code
   SELECT zip_to_state('10314'); -- Returns 'NY'
   
   -- Update mismatched entries
   UPDATE your_address_table a
   SET state = zip_to_state(a.zip_code)
   WHERE zip_to_state(a.zip_code) IS NOT NULL
     AND a.state != zip_to_state(a.zip_code);
   

PostGIS is ideal if you plan to handle additional geospatial tasks (like full address geocoding or distance calculations) down the line.

Alternative Solutions for PostgreSQL 9.5 #

If you don't need full geospatial capabilities, these lighter options might be better suited:

• Custom Zip Code-State Mapping Table:
  This is the simplest, most performant approach for just fixing mismatches. Create a table with official US zip-to-state mappings (public datasets for this are easy to find), then use it to correct your data:

  -- Create the mapping table
  CREATE TABLE us_zip_state (
      zip_code VARCHAR(5) PRIMARY KEY,
      state_abbr VARCHAR(2) NOT NULL
  );
  
  -- Import your zip-state data (adjust the file path as needed)
  COPY us_zip_state FROM '/path/to/zip_state_data.csv' WITH (FORMAT csv, HEADER);
  
  -- Fix mismatched records
  UPDATE your_address_table t
  SET state = z.state_abbr
  FROM us_zip_state z
  WHERE t.zip_code = z.zip_code
    AND t.state != z.state_abbr;
  

  This method requires no extra extensions and works seamlessly with PostgreSQL 9.5.

• pg_trgm for Fuzzy Matching (Optional):
  If you have typos in zip codes (e.g., '1031' instead of '10314'), the pg_trgm extension can help with fuzzy matching to find the closest valid zip code. Enable it with:

  CREATE EXTENSION pg_trgm;
  

  Pair this with your mapping table to handle partial or misentered zip codes.

Recommendation #

• Choose PostGIS + postgis_tiger_geocoder if you anticipate needing geospatial features beyond zip-state validation.
• Use a custom mapping table if you only need to fix mismatches—it's faster, simpler, and avoids the overhead of a full geospatial extension.

内容的提问来源于stack exchange，提问作者pinklotus