United States Geographical Data: ZIP Codes

In a previous post, I gave a script to load state and county geographical data into SQL Server. Here, I extend that data to include ZIP code boundaries.

One thing that I have recently learned is that ZIP code data is really a collection of points, in particular, specific places where mail is delivered. These points can be abstracted in a series of lines along a delivery route, for instance, houses on a residential street. However, ZIP code geographic data is, strictly speaking, not a geographic area. It is possible to draw boundaries around the ZIP code delivery points, but these boundaries are only approximations, and different sources will draw the boundaries in different ways.

More specifically, what I present here are known as ZIP code tabulation areas (ZCTAs) used by the Census Bureau to approximate ZIP codes for census purposes. Here is source of the data.

One thing you may note is that the ZCTAs don’t cover all the possible area of the United States. For instance, large lakes and wilderness areas tend to not be included in any ZCTA.

As with the state and county data, the ZIP code boundaries can be loaded into a table with this definition:

CREATE TABLE [dbo].[PoliticalRegion](
	[PoliticalRegionId] [int] NOT NULL IDENTITY(1,1),
	[RegionName] [nvarchar](255) NOT NULL,
	[Type] [nvarchar] (30) NOT NULL,
	[ParentRegionName] [nvarchar] (255) NULL,
	[Boundaries] [geography] NOT NULL
);

Here is the ZIP code data. The file is quite large (41 MB). One way that I been able to get this data to load is via the command line:

sqlcmd -S serverName -d databaseName -E -i ZipCodeInserts.sql

Leave a Reply

Your email address will not be published. Required fields are marked *