An example of using Spatial SQL and a work flow for creating Zip Code boundaries.



Using USPS Zip+4, ZipMove and CityState data available for a fee and US Census Department's freely downloaded TIGER data, one can readily approximate Zip Code boundaries. There are actually no such thing as a Zip Code boundaries since USPS considers Zip Codes to be a collection of one or more linear and/or point entities.

Please reference https://ribbs.usps.gov/addressing/documents/tech_guides/pubs/AIS.PDF and http://www.census.gov/geo/www/tiger/tgrshp2010/tgrshp2010.html for more info.

Chapter 2 of the AIS.PDF describes the format of the data files to be used. The data comes in the form of a password protected .Zip file on a CD. Before it can be used it must be copied to the hard drive and unzipped with the proper password. Each file is supplied in a fixed format with a single character at the start of each record to indicate the record type.

A single application was written in Delphi (ObjectPascal) to decode the various record types and where appropriate, write them to separate CSV files, to be loaded into the GIS. A single “.ini” file contains the record format information allowing for record format changes without the need of a programmer. There is also a button to decode all of the Zip+4 files in a directory.




Once the data has been loaded into the GIS several queries are used to find the altered Zip Codes. An initial query is used to get summary information of changes.




The result of the query is a table like this:


Examining that output we see that there were 23 records in the table zm_12_10 with an effective date of change of 9/25/2010 where a building or a street segment moved from Zip Code 37013 to Zip Code 37211. From this we know that we need to load the Zip+4 data for the 3-digit Zip Codes 370 and 372. A three digit Zip Code is just a collection of all of the 5-digit zip codes that start with the same first three digits. Here is a simple script to do that:


First we show an input box to specify a file, create a filesystemobject to handle the file. Some code unzips the file if not already unzipped and then call the parser to prepare the fixed format file to be loaded as a CSV. Then the file is imported to a table and a temporary query is altered and run that adds a new column. The function ends after running a query to populate the new column with an amalgam of several fields listed in zip_fullname_base. To be complete the contents of zip_fullname_base are:


We run the script for both 3-digit Zip Codes 370 and 372. Next to find the county each is in:
We see that they both are in state 47 county 037. Because this is not always the case there is a script that allow reading from a table to load the necessary TIGER line data. This data is organized by county.


There is some code in the script to account for the different locations of the data files depending on what system I am running on. Old lines are removed. The table holding the FIP codes for the counties to load is queried and a test to see if the data has already been loaded is made. If not, it is loaded. Because the default is to load the data as text the important columns are converted to integer format. Then all of the lines are copied to a “working2” drawing/layer that already has proper line style/color formatting.


Here we see the Tiger lines for the county and the 2 Zip Codes highlighted.


Here we see the Tiger lines for Zip Code 37211. The formatting for the boundaries, the bold green lines, were altered to stand out better. You can also see the query used to highlight the lines.


Here we see the Tiger lines for Zip Code 37013.


Here we see the Tiger lines for both Zip Codes with a bolder formating. We also have lables for the Zip Codes. There is no apparent crossing of the boundaries, this quite possibly due to disparate vintages of data as I prepare this. But if we look along the junction of Zip Codes 37013 and 37217 we can yellow lines crossing over. This would suggest that the we should investigate this area some more.


We can also confirm the individual changes that were listed I zm_12_10. This query will populate a table of the just those changes for the Zip code in question.


Here we see the 23 records.

If we want we can query to show an individual street and range.


To see that the specified range of Sugarloaf Dr is entirely within 37211 as it should be.

At the end of the process we need to know that we have properly documented all of the changes. To that end there is a series of queries run. I had planned on scripting them but time never seemed to permit it.