Copyright 1997-2009 USNaviguide LLC. All rights reserved.

Important note to users and licensees:

All programs, scripts, routines, databases, documentation and all other materials not specifically marked as "open source," GPL or Apache license are property of USNaviguide LLC and cannot be released, reused, sold, given away, rented, leased or 
otherwise passed on to others not licensed to have possession. We regard copyright infringement as a serious matter
so use due diligence to prevent these materials from falling into the hands of those not licensed to use them.

Please read the TOS.txt for complete statement.

Tables included:

geozf_dump.zip - USPS Zip Code Polygon Database in tab delimited format - one polygon per record.
zip_list.zip - Zip code cross reference table which contains the point data (centroid) for each zip.
county.zip - fips state / fips county to county name table.
gestf.zip - fips state to USPS state code and state name table - also includes bounding box for each US state.

Record Layouts:

geozf_dump.dat

Note: this is a straight tab delimited file with no field headers. The data starts at the first record.

Field order:

Field        | Type   | Description
-------------|--------|-------------------------------------------------------------------------
Record Key   |  int   | Unique number for the record
FIPS State   |  int   | FIPS state numbers - see cross reference table gestf
Zip Code     |  int   | 5 digit zip code
Zip Name     |  text  | Official USPS name for the zip code
County Number|  int   | FIPS county number - see cross reference table county
Color Code   |  int   | Color code is a number from 1 to 8 to be used to color polygons
Exterior Ring|  int   | Record key of the exterior polygon - indicates this is a "donut" or interior polygon
Has Int. Ring|  int   | A number 0 - no interior rings or 1 - has interior rings
Polygon Field|  text  | A series of coordinates in latitude and longitude order that constitues the polygon

EXTRING and HASINTRING can be used to determine what type of polygon a record contains. If the EXTRING field
contains zero, then it is an exterior ring, if it has a value, then that value is the polygon ID of the
exterior ring and it is an interior ring or "hole." Exterior ring polygons that have an HASINTRING value of 1
also have interior rings. 

zip_list.zip - Zip code cross reference table which contains the point data (centroid) for each zip.

Note: this is a straight tab delimited file with no field headers. The data starts at the first record.

Field   | Type    | Description
--------|---------|-------------------------------------------------------------------------
zip     | integer | Zip Code - remember to zero fill for zips less than 10000
zipname | text    | Zip Code Name
fipsst  | integer | FIPS State code - see gestf for cross reference to USPS state code
county  | integer | USPS County of record
polpnt  | geometry| POINT geometry field - centroid of polygon or point for point zips
pointzip| integer | Point zip code indicator: 0 - polygon centroid, 1 - point zip code
swpoint | point   | South West corner of bounding box for polygons (pointzip 0 only)
nepoint | point   | North East corner of bounding box for polygons (pointzip 0 only)

Notes:
Contains one record per zip code.
The zipname field is the "preferred" zip code name from the USPS.
For point zip codes, the zip code only represents an approximate location. Exact locations cannot be determined.
No APO or AFO point zips are located in this file.
Fields with the value of NULL contain no data. This may have to be filtered by your input process.

# county.zip - fips state / fips county to county name table.

Field   | Type    | Description
--------|---------|-------------------------------------------------------------------------
state   | integer | FIPS State Code - see gestf for cross reference to USPS state code
county  | integer | FIPS County Number
name    | text    | County name - derived from Tiger database

Notes:
This file is a Postgres tab delimited dump.

# gestf.zip - fips state to USPS state code and state name table - also includes bounding box for each US state.

Field   | Type    | Description
--------|---------|-------------------------------------------------------------------------
fipsst  | integer | FIPS State code
uspsst  | text    | USPS State code
stname  | text    | Official state name
bbox    | box     | Bounding box for state

Notes:
Bounding box is not present for territories.
USPS state name is blank for FIPS states not processing mail through the USPS.

Shapefile versions are in the "shapes" directory, record layout is similar. Please ask if you need information on dealing with shapefiles.

For your convenience, here are PostgreSQL compatible record layouts and loading syntax:

-- Geo Table ST: States...

DROP TABLE gestf ;

CREATE TABLE gestf (
-- FIPS State Number
   FIPSST int2 primary key DEFAULT 0,
-- USPS State Code
   USPSST char(2) DEFAULT '',
-- State Name
   STNAME varchar(70) DEFAULT '',
-- Bounding Box
   BBOX box
) ;

grant all on gestf to public ;

-- To load:
-- COPY gestf (fipsst,uspsst,stname,bbox) FROM '<your file path and name>' ;Mac-Pro:2nd root# 


-- Geo Table zip_list...
-- Zip Code Cross Reference...

DROP TABLE zip_list ;

CREATE TABLE zip_list (
-- Zip Code
   zip integer,
-- Zip Code "Preferred Name"
   zipname character varying(70),
-- FIPS State Code
   fipsst smallint,
-- FIPS County Code
   county integer,
-- Point guaranteed to be on the surface of the polygon (Centroid)
   polpnt point,
-- Point Zip Code (0-area, 1-point zip, 2-not geographically identified)
   pointzip smallint DEFAULT 0,
-- Southwest point of bounding box (pointzip = 0 only)
   swpoint point,
-- Northeast point of bounding box (pointzip = 0 only)
   nepoint point
) ;

grant all on zip_list to public ;

-- To load:
-- COPY zip_list (zip, zipname, fipsst, county, polpnt, pointzip, swpoint, nepoint) FROM '<your file path and name>' ;


-- Geo Table Z: Real Zip Codes...

DROP TABLE geozf ;

CREATE TABLE geozf (
-- Record Key
   IDGEOZ int4 primary key,
-- FIPS State
   FIPSST int2 DEFAULT 0,
-- ZIP
   ZIP int4 DEFAULT 0,
-- Name of Place at center of Zip
   ZIPNAME varchar(70) DEFAULT '',
-- County Number
   COUNTY int4 DEFAULT 0,
-- Color Code (0-9)
   COLOR int DEFAULT 0,
-- Exterior Ring IDGEO3
   EXTRING int4 DEFAULT 0,
-- Has Interior Rings: 0-No Interior Rings, 1-Has Interior Ring - use EXTRING to access
   HASINTRING int2 DEFAULT 0,
-- Polygon Coordinates
   POLY polygon
) ;

grant all on geozf to public ;

create index geozf_zip on geozf (zip) ;

-- To load:
-- COPY geozf (idgeoz,fipsst,zip,zipname,county,color,extring,hasintring,poly) FROM '<your file path and name>' ;