国际地理地址应该如何在关系型数据库中存储？

Question

国际地理地址应该如何在关系型数据库中存储？

databasedatabase-designrelational-databaserdbmsstreet-address

66

如果要在关系表中存储国际地理地址，最灵活的模式是什么？每个地址部分都应该分成自己的字段，还是更像自由文本？

将不同格式的地址分开存储在不同的表中有意义吗？例如，为美国地址、加拿大地址、英国地址等创建一个表...

- Bob

1

http://schema.org/PostalAddress - 作为搜索引擎的标准使用。 - Ivan Rave

10个回答

30

注意不要过度分析地址格式，否则很可能会得到大多数用户需要绕开的规范，从而迫使他们使用错误的字段或只填写主要字段并忽略额外字段。

保持简单。

像BenAlabaster提到的StreetType在与英语或西班牙语等孤立语言不同的语言一起使用时会造成问题。

为了向您展示野外情况有多么糟糕：阿姆斯特丹的"Henriette Roland Holststraat"由"Henriette" + "Roland Holst" + "straat"组成，可以缩写为"Roland Holststraat"，或者"H.R.Holststr."或"Henriette Roland-Holst straat"的拼写错误取决于天气。除非你对地球上每个国家都有最新的街道注册表，否则你将一无所获。

最后，要小心，在一些多语言国家，名字可能会因语言不同而不同！例如，在布鲁塞尔，许多街道都有法语和荷兰语名称："Avenu du Port"和"Havenlaan"，取决于收件人的首选语言。（Google地图交替显示两个名称，以确保安全。）

您可以尝试设计各种巧妙的技巧，但销售代表会理解这个吗？

- Ruben

10

这里有一个趣闻，对于那些遇到这个问题的人来说：

我作为一个在多个洲（欧洲、亚洲、北美）生活和工作的人发言。根据我的经验和与我一起工作的人的经验，使用以下系统会更容易：

1. 提供三行文本框让我输入地址，并将这三行文本框中的内容原样传递给您的当地邮政服务。让我使用任何字符集，使用UTF-8或更好的字符集。 2. 如果您的系统需要我提供特定信息（例如邮政编码、州、省等），请单独询问。通过分析这些信息可以进行业务需求，但这些信息不应该与您的当地邮政服务共享（除非我也在上述第1点中输入了相同的信息）。 3. 有一个下拉菜单要求我指定上述第1点中提供的地址的分类位置，例如国家。 4. 如果您必须解析我在第1点中提供的信息，请使用我在第3点中的答案选择正则表达式。运行该正则表达式以解析第1点中的信息。尽可能使用正则表达式输出填充第2点中的用户界面元素。如果我更正了自动填充的信息，请使用我更改后的信息来改进您的正则表达式。同样，尽可能让我有机会查看并更正您的正则表达式的输出：没有人比我更清楚我想要传达什么。

我发现，像这样构建的系统最容易使用。特别是当我向一个您公司几乎没有功能内部知识的邮政系统发送邮件时。

如果您的公司确实具有关于特定邮政系统的内部知识，请使用我在第3点中选择的信息来通知您显示给我的视图。许多人知道美国邮政系统在包装上期望什么；如果我在第3点中选择了美国，请随意使视图看起来适合美国地址。如果我选择了您公司不知道的国家，请显示一个通用的三行文本框，让我自己输入，不要强制我使用ASCII。

让我们真实一点吧 - 构建一个完整的、百科全书式的所有全球邮政系统（公共和私人）的数据库，充其量是一个艰巨的任务，如果不是不可能的任务。例如，有些邮政系统只有当地的最后一英里承运人真正知道地址的位置。有时，在包装上向该承运人传递笔记非常有用。将每个边缘情况承运人的本地知识映射到您的数据库中确实是一项不可能的任务。

问问哥德尔吧。（然后问问自己，您是否试图使用公理系统来模拟一个论域，或者说除了某种算术（如集合论或关系代数）之外。）

- StudentsTea

9

那取决于你想用它来做什么。

如果地址分开，可以更容易地将其用于其他目的（例如与USPS数据进行验证或从UPS / FEDEX获取运费）。以下是我通常用于地址的内容：

地址行1 地址行2 地址行3 城市地区邮政编码县国家

针对编辑的回应：大多数情况下我认为没有必要。我列出的表格具有足够的字段（且足够普遍），适用于大多数国家/地区的地址。

- Stephen Wrighton

1

地址行1、2和3肯定足够通用，但是当涉及以编程方式解析地址时，你会遇到麻烦。考虑国际地址格式时，以编程方式解析地址并不是一项微不足道的任务。 - BenAlabaster

2

@Alix Axel - 对于那些国家，请将该字段留空。 - Stephen Wrighton

这个程序可以工作，但是它无法处理欧洲、加拿大和美国以外的邮政系统。 - Warren P

8

地址

作为对@BenAlabaster提供的优秀答案的完全相反，你可以简单地写：

address       TEXT(300)
postal_code   VARCHAR(15)
country_code  VARCHAR(2)

您的客户端表单布局仍然可以像您希望的那样复杂（或者使用多行输入，用户可以手动输入他们的地址）。然后，您可以在必要时添加地址中的换行符。

国家

您的国家表格如下所示：

country_code  VARCHAR(2)
country_name  VARCHAR(255)

此句话的英译中为：此外，您可以选择以下其中之一：

postal_code_required  TINYINT(1)
postal_code_regex     VARCHAR(255) NULL DEFAULT NULL

然后使用以下列表来设计您的国家表格：

- rybo111

3

为了覆盖国际地址，地址字段不应过于具体或过于抽象。

以下演示几乎涵盖了世界上所有的地址：

*****************************************************************
Type            Field name    Displayed name in your form         
*****************************************************************
INT             id (PK)
VARCHAR(100)    country       Country            
VARCHAR(100)    zip_code      Zip code
VARCHAR(100)    state         State, province or prefecture
VARCHAR(100)    city          City
VARCHAR(100)    street        Street address or PO Box
VARCHAR(100)    building      Apt, office, suite, etc. (Optional)
*****************************************************************

- Kai - Kazuya Ito

3

Ben Alabaster的答案评论：为了根据国家格式化地址，您可以使用一个格式化表格，该表格具有将每个国家的列顺序作为单独行的排序。

AddressFormat（CountryCode，FieldName，FieldOrder）

字段顺序可以编码为使用复杂的网格布局。

按国家分隔地址没有意义。随着国家数量增加，这将会变得混乱，并且如果您想要找到国际客户的所有地址，则会遇到麻烦。采用Ben建议的地址类型也可能导致模棱两可，当您拥有既有建筑号码又有公寓号码的地址时。我可能在一个公寓大楼中，在那里每座建筑都有不同的名称。这在印度非常普遍。

- bkm

2

我使用https://github.com/commerceguys/addressing库来格式化国际地址，它们使用以下元素：

Country
Administrative area
Locality (City)
Dependent Locality (in: BR, CN, IR, MY, MX, NZ, PH, KR, ZA, TH)
Postal code
Sorting code
Address line 1
Address line 2
Organization
Recipient

这并不能帮助你解析街道（名称、门牌号码等）。另外，如果你正在寻找多语言国家列表，请访问以下链接：https://github.com/umpirsky/country-list。

- Harald Ernst

0

唯一的方法是将它们分割成：

Name varchar,
Title varchar,
StreetAddress varchar,
StreetAddressLine2 varchar,
zipCode varchar,
City varchar,
Province varchar,
Country lookup

由于几乎每个国家都有自己的地址数据标准，而且每个国家的邮政编码格式也不同。
您可以从类似问题的我的帖子中获得一些小问题的样本。

这不应该让每个国家分开地址，因为有些国家只有少数地址约定。一些流行的约定包括在小村庄中没有街道，只有村名和编号，而在大城市的地址中有街道。我了解到，在匈牙利首都布达佩斯，有几条街道有相同的名称（您可以通过城市的区号来区分它们），而其他城市没有这样的地址（来自匈牙利的人可能会确认这是真的）。因此，地址格式的总数将是国家数量乘以该国的地址格式数量...可以使用不同的表格完成，但这将是可怕的工作。

- smok1

你为什么用了“Province”而不是“ZipCode”？此外，“StreetAddress”和“StreetAddressLine2”对于显示目的来说足够通用，但如果你必须以编程方式进行EDI或解析地址以进行QA（或出于任何其他原因），那么你将会遇到麻烦。 - BenAlabaster

这取决于你需要这些数据做什么。如果是向全球客户发送邮件，我的解决方案就可以了。如果是全球EDI，你可能需要像你回答这个问题那样的东西。然而，为了导航目的，你需要包含GIS数据和它们之间链接的附加数据结构（这样你就会知道地址1和地址2位于同一地点，即使它们有不同的街道名称等）。因此，在不知道上下文的情况下很难说哪种解决方案是可行的（不太复杂且足够准确）。 - smok1

0

我知道这是一个非常古老的话题，已经有了答案，但我也想发表一下我的看法。这完全取决于您的项目目标以及您希望目标用户如何输入地址。Ben的建议可以让您准确解析地址，但另一方面可能会导致更长（可能更令人沮丧）的用户数据输入过程。Stephen Wrighton的建议更简单，因此用户输入地址可能更容易。

我还看到过一些模型，只有一个“地址”列，可以捕获典型的街道号码、类型、街道名称、单元/公寓号码等，所有这些都在一个列中，同时保留城市、国家、地区等在其他列中。类似于Stephen的模型，但Address1、Address2和Address3都合并成一个列。

我的观点是，最灵活的模型往往是最不限制的，这取决于您对灵活性的理解。

- Shan Plourde

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- BenAlabaster · Accepted Answer

我会从我的博客文章 - 地址存储的一课（在archive.org上）中总结我的想法。

在我的当前项目中[我在物流公司工作]，我们正在存储国际地址。我已经研究了全世界的地址设计了数据库的这部分。有很多不同的格式。在西方世界，我们倾向于使用一个相当统一的格式 - 一些差异，但它们大多数是：

街道号码 - 数字
房屋或建筑名称 - [VarChar - 在英国，有些房屋/建筑物是用名称而不是编号标识的]
街道号码后缀 [VarChar，虽然在大多数情况下，Char（1）就足够了]
- A，B等
街道名称 [VarChar]
街道类型 [VarChar或Int如果您有StreetTypes表]
- 到目前为止，我在讲英语的世界中发现了262种独特的类型，可能还有更多，别忘了其他语言，例如Strasse，Rue等。
街道方向 [VarChar（2）]
- N，E，S，W，NE，SE，NW，SW
地址类型 [VarChar或Int如果您有AddressTypes表]
- 邮政信箱
- 公寓
- 建筑
- 地板
- 办公室
- 套房
- 等等...
地址类型标识符 [VarChar]
- 即，盒子号码，公寓号码，楼层号记住公寓号码和办公室有时具有字母数字信息 - 如1A
当地市政府 [VarChar或Int如果您有Municipalities表]

例如，如果您的小村庄/村落出现在城镇之前的地址中。

城市/城镇 [VarChar或Int（如果您有城市表）]
行政区划 [VarChar或Int（如果您有区域表）]
- 州（美国）
- 省（加拿大）
- 联邦区（墨西哥）
- 县（英国）
- 等等......
邮政区域 [VarChar]
- 邮编（美国）
- 邮政编码（加拿大，墨西哥）
- 邮政编码（英国）
国家 [VarChar或Int（如果您有国家表）]

这似乎覆盖了大多数国家，但字段的排序可能会显示不同。您可以在http://www.bitboost.com/ref/international-address-formats.html#Formats上找到显示格式的列表。

例如，在许多国家中，邮政编码位于城市名称之前，街道号位于街道名称之后。在加拿大，美国和英国，街道号位于街道名称之前，而邮编（或ZIP）在城市名称之后。

关于将地址分离到不同的国家的问题，我不建议这样做，因为它会在其他领域（例如报告）中使生活更加困难。我提供的格式覆盖了我们物流数据库中所有地址的美国，加拿大，墨西哥和英国，没有任何问题。它还涵盖了我们所有的欧洲，中国，日本和马来西亚地址。我不能代表其他国家，但我还没有存储过这些字段不支持的国家的地址。

我不建议采用其他人和许多数据库中看到的 Address1、Address2、Address3 格式，因为从包含字母数字混合的字符串中解析地址信息并不像首次看起来那么简单 - 特别是如果由于错误信息、输入错误、拼写错误等而导致数据输入不正确时。如果您分离字段，可以使用距离算法检查可能的意义，使用概率检查街道名称与邮政编码和街道号，或检查省市与街道名称等。当您有一个字符串表示您的整个街道地址时，请尝试执行其中任何操作都不是一件微不足道的事情。

对地址数据库进行 QA 是令人头疼的，这个领域简化生活的最简单方法是确保所有字段只容纳可以在输入时自动验证为正确的单个信息片段。概率、距离算法和正则表达式可以检查输入的有效性，并向用户提供反馈，以表明他们的错误是什么，并建议适当的更正。

要注意的一个警告是具有既是路名又是街道类型的名称的道路 - 如果您涵盖加拿大，您需要知道多伦多的“ Avenue Road”，它将使您陷入困境，如果您使用 Address1、2、3 格式。这种情况可能也发生在其他地方，尽管我不知道它们 - 这个单一实例足以让我尖叫 WTF?!