Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
247 views
in Technique[技术] by (71.8m points)

sql server - How to get the bounding coordinates for a US postal(zip) code?

Is there a service/API that will take a postal/zip code and return the bounding(perimeter) coordinates so I can build a Geometry object in a MS SQL database?

By bounding coordinates, I mean I would like to retrieve a list of GPS coordinates that construct a polygon that defines the US zip code.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

An elaboration of my comment, that ZIP codes are not polygons....

We often think of ZIP codes as areas (polygons) because we say, "Oh, I live in this ZIP code..." which gives the impression of a containing region, and maybe the fact that ZIP stands for "Zone Improvement Plan" helps the false association with polygons.

In actuality, ZIP codes are lines which represent, in a sense, mail carrier routes. Geometrically, lines do not have area. Just as lines are strings of points along a coordinate plane, ZIP code lines are strings of delivery points in the abstract space of USPS-designated addresses.

They are not correlated to geographical coordinates. What you will find, though, is that they appear to be geographically oriented because it would be inefficient for carriers to have a route completely irrelevant of distance and location.

What is this "abstract space of USPS-designated addresses"? That's how I am describing the large and mysterious database of deliverable locations maintained by the US Postal Service. Addresses are not allotted based on geography, but on the routes that carriers travel which usually relates to streets and travelability.

Some 5-digit ZIP codes are only a single building, or a complex of buildings, or even a single floor of a building (yes, multiple zip codes can be at a single coordinate because their delivery points are layered vertically). Some of these -- among others -- are "unique" ZIPs. Companies and universities frequently get their own ZIP codes for marketing or organizational purposes. For instance, the ZIP code "12345" belongs to General Electric up in Schenectady, NY. (Edit: In a previous version of Google Maps, when you follow that link, you'd notice that the placement marker was hovering, because it points to a ZIP code, which is not a coordinate. While most US ZIP codes used to show a region on Google Maps, these types cannot because the USPS does not "own" them, so to speak, and they have no area.)

Just for fun, let's try verifying an address in a unique ZIP code. Head over to SmartyStreets and punch in a bogus address in 12345, like:

Street: 999 Sdf sdf

ZIP Code: 12345

When you try to verify that, notice that... it's VALID! Why? The USPS will deliver a piece to the receptacle for that unique ZIP code, but at that point, it's up to GE to distribute it. Pretty much anything internal to the ZIP code is irrelevant to the USPS, including the street address (technically "delivery line 1"). Many universities function in a similar manner. Here's more information regarding that.

Now, try the same bogus address, but without a ZIP code, and instead do the city/state:

Street: 999 Sdf sdf

City: Schenectady

State: NY

It doesn't validate. This is because even though Schenectady contains 12345, where the address is "valid," it geometrically intersects with the "real" ZIP codes for Schenectady.

Take another instance: military. Certain naval ships have their own ZIP codes. Military addresses are an entirely different class of addresses using the same namespace. Ships move. Geographical coordinates don't.

ZIP precision is another fun one. 5-digit ZIP codes are the least "precise" (though the term "specific" might be more meaningful here, since ZIP codes don't pinpoint anything). 7- and 9-digit ZIP codes are the most specific, often down to block or neighborhood-level in urban areas. But since each ZIP code is a different size, it's really hard to tell what actual distances you're talking.

A 9-digit ZIP code might be portioned to a floor of a building, so there you have overlapping ZIP codes for potentially hundreds of addresses.

Bottom line: ZIP codes don't, contrary to popular belief, provide geographical or boundary data. They vary widely and are actually quite un-helpful unless you're delivering mail or packages... but the USPS' job was to design efficient carrier routes, not partition the population into coordinate regions so much.

That's more the job of the census bureau. They've compiled a list of cartographic boundaries since ZIP codes are "convenient" to work with. To do this, they sectioned bunches of addresses into census blocks. Then, they aggregated USPS ZIP code data to find the relation between their census blocks (which has some rough coordinate data) and the ZIP codes. Thus, we have approximations of what it would look like to plot a line as a polygon. (Apparently, they converted a 1D line into a 2D polygon by transforming a 2D polygon based on its contents to fit linear data -- for each non-unique, regular ZIP code.)

From their website (link above):

A ZIP Code tabulation area (ZCTA) is a statistical geographic entity that approximates the delivery area for a U.S. Postal Service five-digit or three-digit ZIP Code. ZCTAs are aggregations of census blocks that have the same predominant ZIP Code associated with the addresses in the U.S. Census Bureau's Master Address File (MAF). Three-digit ZCTA codes are applied to large contiguous areas for which the U.S. Census Bureau does not have five-digit ZIP Code information in its MAF. ZCTAs do not precisely depict ZIP Code delivery areas, and do not include all ZIP Codes used for mail delivery. The U.S. Census Bureau has established ZCTAs as a new geographic entity similar to, but replacing, data tabulations for ZIP Codes undertaken in conjunction with the 1990 and earlier censuses.

The USCB's dataset is incomplete, and at times inaccurate. Google still has holes in their data, too (the 12345 is a somewhat good example) -- but Google will patch it eventually by going over each address and ZIP code by hand. They do this already, but haven't made all their map data perfect quite yet. Naturally, access to this data is limited to API terms, and it's very expensive to raise these.

Phew. I'm beat. I hope that helps clarify things. Disclaimer: I used to be a developer at SmartyStreets. More information on geocoding with address data.

Even more information about ZIP codes.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...