邮政编码到城市/州，反之亦然在数据库中? [英] Zip Code to City/State and vice-versa in a database?

问题描述

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

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

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

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?

我使用的是 SQL Server 2005，如果它有所不同的话.

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

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

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.

推荐答案

您有几个选择.您可以从列出邮政编码、城市、县的人那里购买批量邮政编码库等，或者您可以花钱请人访问网络服务，该服务将在更细化.

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

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

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.

至于处理用户输入的变化无常，您可以考虑寻求地址验证网络服务，尽管其中大多数需要完整的送货地址才能进行验证.

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.

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

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.

编辑 2:在 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.

这篇关于邮政编码到城市/州，反之亦然在数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！