I have a database that has a list of zip codes which has latitude, longitude, state, city, state fips code, time zone, etc. I'd like to extend this and write either a C# function or TSQL (SQL Server 2008) function that will take a street, city, state and zip and return the zip+4. I've searched high and low and there's many programs out there that can do it but none appear to actually allow me to see what they've done and enhance it for my own needs. Does anyone know of an open source solution that can do this or point me in the right direction so I can make an open source version?
So what do the extra numbers mean? These last 4 digits represent specific delivery routes within delivery areas. This extra detail means an even more precise matching to a more granular level. It could represent a handful of houses on one side of a street, or even a single building that receives a high volume of mail.
ZIP+4 Codes are 4-digit codes added to traditional ZIP Codes that provide faster routing. The last 4 digits of the ZIP Code represent delivery route segments within delivery areas and generally consist of 10-20 mailboxes.
The current postal codes in the United States range from 00001 – 99950.
Possibly the easiest solution to use is the USPS's address API (http://www.usps.com/webtools/htm/Address-Information.htm#_Toc131231396) The usage regarding scrubbing databases is a bit vague (and if you ask them about it, it seems they remain just as vague, perhaps on purpose) but once you get approved it's VERY easy to send the data you have and get back a fully verified, 100% compliant and up-to-date address. Once you get the data back from USPS, you can simply add/subtract/agument it as you need prior to stuffing back into the DB. For instance, you could Geocode from the verified address, or from your DB, then append that data into the data you received, inserting in a loop.
I'm using it on one of my applications to validate inbound inquiries and it has proven nearly 100% reliable for getting me what I need corrected.
One thing to note about the USPS free API is that their license says the data it returns must be used only for sending things via the USPS. We had a need for ZIP+4 data but we ship our stuff through other carriers, so this meant we could not use the free service.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With