SQL 邮政编码到城市/州，反之亦然在数据库中？

Zip Code to City/State and vice-versa in a database?

提问by Erik Forbes

I'm new to SQL and relational databases and I have what I would imagine is a common problem.

我是 SQL 和关系数据库的新手，我认为这是一个常见问题。

I'm making a website and when each user submits a post they have to provide a location in either a zip code or a City/State.

我正在制作一个网站，当每个用户提交帖子时，他们必须提供邮政编码或城市/州的位置。

What is the best practice for handling this? Do I simply create a Zip Code and City and State table and query against them or are there ready made solutions for handling this?

处理此问题的最佳做法是什么？我是简单地创建一个邮政编码和城市和州表并对其进行查询，还是有现成的解决方案来处理这个问题？

I'm using SQL Server 2005 if it makes a difference.

如果它有所作为，我正在使用 SQL Server 2005。

I need to be able to retrieve a zip code given a city/state or I need to be able to spit out the city state given a zip code.

我需要能够检索给定城市/州的邮政编码，或者我需要能够根据邮政编码吐出城市州。

回答by Erik Forbes

You have a couple options. You can buy a bulk zip-code libraryfrom somebody which will list zip codes, cities, counties, etc. by state, or you can pay someone to access a web servicewhich will perform the same function on a more granular level.

你有几个选择。您可以从某个人那里购买一个批量邮政编码库，该库将按州列出邮政编码、城市、县等，或者您可以付费让某人访问 Web 服务，该服务将在更细粒度的级别上执行相同的功能。

Your best bet would be to go with the zip-code library option, as it'll cost you less than the web service and will provide better performance. How you query or pre-process this library is up to you. You mention SQL Server, so you'd probably want State, Zipcode, and City tables, and include the relevant relationships between them. You'll also need to have provisions for cities that span multiple zipcodes, or for zipcodes that have multiple cities - but none of these issues are insurmountable.

最好的选择是使用 zip-code library 选项，因为它比 Web 服务花费更少，并且会提供更好的性能。您如何查询或预处理此库取决于您。您提到了 SQL Server，因此您可能需要 State、Zipcode 和 City 表，并包含它们之间的相关关系。您还需要为跨越多个邮政编码的城市或具有多个城市的邮政编码提供规定 - 但这些问题都不是不可克服的。

As far as dealing with the vagarities of user input, you may consider enlisting the help of an address validation web service, although most of them require a full shipping address in order to validate.

至于处理用户输入的变化无常，您可以考虑寻求地址验证 Web 服务的帮助，尽管它们中的大多数都需要完整的送货地址才能进行验证。

Edit: looks like there's a SourceForge project offering free zip-code data, including lat/lon data, etc. Not sure how correct or current it is.

编辑：看起来有一个SourceForge 项目提供免费的邮政编码数据，包括纬度/经度数据等。不确定它的正确性或当前性。

Edit 2: After some cursory looking on that SourceForge project's site it looks like this is a dead project. If you use this data, you'll need to provide some allowance for zipcodes / cities that don't exist in your database. Purchased bulk libraries usually come with some sort of guarantee of updates, or a pricing plan for updates, etc., and are probably more reliable.

编辑 2：在 SourceForge 项目的网站上粗略查看后，它看起来像是一个死项目。如果您使用此数据，则需要为数据库中不存在的邮政编码/城市提供一些津贴。购买的批量库通常带有某种更新保证，或更新的定价计划等，并且可能更可靠。

回答by DavGarcia

Have a ZipCode table that is related to a CityState table. Some zip codes have multiple cities associated with them, so you may need to have the interface let the user select from the city they want or let them override the default.

有一个与 CityState 表相关的 ZipCode 表。某些邮政编码有多个与之关联的城市，因此您可能需要让界面让用户从他们想要的城市中进行选择或让他们覆盖默认值。

I use the paid service from ZipInfo.comsince I needed additional information such as lat/long, zip type and county. Zip codes also change several times as year as new zip codes are added or merged with others, so you will need to update your data a few times a year to stay consistent.

我使用ZipInfo.com的付费服务，因为我需要额外的信息，例如纬度/经度、邮编类型和县。随着新邮政编码的添加或与其他邮政编码的合并，邮政编码每年也会发​​生多次变化，因此您需要每年更新几次数据以保持一致。

回答by Bramha Ghosh

Depending on the details of what you're building, Yelp has a free neighborhoods APIthat may be able to get you what you need. Be sure to check their Terms of Use and stuff to make sure you stay in compliance.

根据您正在构建的内容的详细信息，Yelp 有一个免费的社区 API，可以为您提供所需的内容。请务必检查他们的使用条款和内容，以确保您遵守规定。

I know this isn't a db centric answer, but what you're doing may not be best handled in the database itself.

我知道这不是以数据库为中心的答案，但是您所做的事情可能无法在数据库本身中得到最佳处理。

回答by Muad'Dib

There are many free geo-coding webservices where you can get a zip from a city-state, and vice-versa. Take a look at the GeoNames webservice. You could do something like check your db, and then if what you are looking for is not there, grab it from the webservice and add it.

有许多免费的地理编码网络服务，您可以从中获取来自城邦的 zip，反之亦然。查看 GeoNames 网络服务。您可以执行一些操作，例如检查您的数据库，然后如果您要查找的内容不存在，请从网络服务中获取并添加它。

回答by Kluge

If the reason for the lookup is the user's convenience, here's an alternate approach that doesn't require licensing any third-party databases:

如果查找的原因是为了用户的方便，这里有一种不需要许可任何第三方数据库的替代方法：

Just lookup the city/state from your existing name/address table, provided the zip code matches. If somebody has previously made an entry for that zip code, then you'll find the city and state from that entry. IF no previous entry exists, then worst case the user has to entry in the city and state.

如果邮政编码匹配，只需从您现有的名称/地址表中查找城市/州。如果之前有人为该邮政编码输入过条目，那么您会从该条目中找到城市和州。如果不存在以前的条目，那么最坏的情况是用户必须输入城市和州。

This solution assumes your need is for convenience for the user. If you are more concerned about accurate validation of city, state, zip codes then you're better off licensing a verified database.

此解决方案假定您的需求是为了方便用户。如果您更关心城市、州、邮政编码的准确验证，那么您最好为经过验证的数据库授权。

回答by Ben Dunlap

My understanding is that the USPS web APIis free to use, but requires permission that depends on a number of factors, including the nature of the program that will be using it and the reason you need the data.

我的理解是USPS Web API可以免费使用，但需要获得许可，这取决于多种因素，包括将使用它的程序的性质以及您需要数据的原因。

If you qualify to use it, this would presumably be the most accurate source for the information you need.

如果您有资格使用它，这大概是您所需信息的最准确来源。

回答by alphadogg

Not sure I understand the question. Do you need to allow either and, later, return both?

不确定我理解这个问题。您是否需要允许其中之一，然后返回两者？

You'll have to be careful, even with a zip/city database that can be purchased, since some cities span multiple zips, so you can't always "calculate" in that direction. Similar issue in the opposite direction.

即使使用可以购买的邮编/城市数据库，您也必须小心，因为有些城市跨越多个邮编，因此您不能总是朝着那个方向“计算”。相反方向的类似问题。

回答by kemiller2002

There are libraries that you can buy and import that aren't that expensive. Your problem with them is going to be that you will have an ongoing maintenance cost with it (not a lot). Zip codes change all the time, meaning that in 6 mos or so, your data will be slightly out of date. You might want to look at interfacing with a service like google maps. Here we use a hybrid approach. We spend the amount of the money for updates to the data every 6 months, and if the zip code isn't in the table we verify it against google maps to make sure that information entered is still valid and it just hasn't been entered into our system. If it is valid, we update the system, if not we let the user know that a mistake was made.

您可以购买和导入一些价格不高的库。你对它们的问题将是你将有持续的维护成本（不是很多）。邮政编码一直在变化，这意味着在 6 个月左右的时间里，您的数据会稍微过时。您可能想查看与谷歌地图等服务的接口。这里我们使用混合方法。我们每 6 个月花费一笔钱更新数据，如果邮政编码不在表格中，我们会根据谷歌地图对其进行验证，以确保输入的信息仍然有效并且只是尚未输入进入我们的系统。如果有效，我们会更新系统，如果无效，我们会让用户知道发生了错误。

Another option would be to ping the USPS website. I believe they have a city/state look up page by zipcode.

另一种选择是 ping USPS 网站。我相信他们有一个按邮政编码的城市/州查询页面。

回答by Muhammad Bilal

For US States Tables, you can use following query, assuming you have StateIdand StateNamein the Statestable and you have StateIdauto increment.

对于美国州表，您可以使用以下查询，假设您有StateId和StateName在States表中并且您有StateId自动增量。

This query has 50 US States...

此查询有 50 个美国州...

INSERT INTO States
        ( StateName )
        VALUES
( 'Alabama'),
( 'Alaska'),
( 'Arizona'),
( 'Arkansas'),
( 'California'),
( 'Colorado'),
( 'Connecticut'),
( 'Delaware'),
( 'District of Columbia'),
( 'Florida'),
( 'Georgia'),
( 'Hawaii'),
( 'Idaho'),
( 'Illinois'),
( 'Indiana'),
( 'Iowa'),
( 'Kansas'),
( 'Kentucky'),
( 'Louisiana'),
( 'Maine'),
( 'Maryland'),
( 'Massachusetts'),
( 'Michigan'),
( 'Minnesota'),
( 'Mississippi'),
( 'Missouri'),
( 'Montana'),
( 'Nebraska'),
( 'Nevada'),
( 'New Hampshire'),
( 'New Jersey'),
( 'New Mexico'),
( 'New York'),
( 'North Carolina'),
( 'North Dakota'),
( 'Ohio'),
( 'Oklahoma'),
( 'Oregon'),
( 'Pennsylvania'),
( 'Puerto Rico'),
( 'Rhode Island'),
( 'South Carolina'),
( 'South Dakota'),
( 'Tennessee'),
( 'Texas'),
( 'Utah'),
( 'Vermont'),
( 'Virginia'),
( 'Washington'),
( 'West Virginia'),
( 'Wisconsin'),
( 'Wyoming');

回答by user54650

I got that beat. Here's a free one (zipped, csv):

我得到了那个节拍。这是一个免费的（压缩的，csv）：

Hosted At maphacks.com

托管在 maphacks.com

Headers are zip,city,state,latitude,longitude,timezone,dst

标题是邮编，城市，州，纬度，经度，时区，dst

Now, your potential loss is that you aren't paying for updates (which can bite you in the end, but they try to keep it somewhat updated)

现在，你的潜在损失是你没有为更新付费（这最终会咬你，但他们会尽量保持更新）