SQL 是否有针对世界所有地址的通用街道地址数据库设计？

Is there common street addresses database design for all addresses of the world?

提问by Arsen Mkrtchyan

I am a programmer and to be honest don't know street address structures of the world, just how in my country is structured :) so which is the best and common database design for storing street addresses? It should be so simple to use, fast to query and dynamic to store all street addresses of the world which is identifying just by one id
Thanks a lot

我是一名程序员，老实说，我不知道世界上的街道地址结构，我的国家的结构如何:) 那么哪个是用于存储街道地址的最佳和常见的数据库设计？使用起来应该如此简单，查询速度快，并且可以动态存储世界上仅通过一个 ID 标识的所有街道地址，非常
感谢

采纳答案by Edward Ross

It is possible to represent addresses from lots of different countries in a standard set of fields. The basic idea of a named access route (thoroughfare) which the named or numbered buildings are located on is fairly standard, except in China sometimes. Other near universal concepts include: naming the settlement (city/town/village), which can be generically referred to as a locality; naming the region and assigning an alphanumeric postcode. Note that postcodes, also known as zip codes, are purely numeric only in some countries. You will need lots of fields if you really want to be generic.

可以在一组标准字段中表示来自许多不同国家的地址。命名或编号建筑物所在的命名通道（通路）的基本概念是相当标准的，有时在 CN 除外。其他近乎通用的概念包括：命名聚居地（城市/城镇/村庄），通常可以称为地点；命名区域并分配字母数字邮政编码。请注意，邮政编码（也称为邮政编码）仅在某些国家/地区是纯数字的。如果你真的想要通用，你将需要很多字段。

The UPU Universal Postal Union provides address data for lots of countries in a standard format. Note that the UPU format holds all addresses (down to the available field precision) for a whole country, it is therefore relational. If storing customer addresses, where only a small fraction of all possible addresses will be stored, its better to use a single table (or flat format) containing all fields and one address per row.

万国邮联万国邮政联盟以标准格式提供许多国家的地址数据。请注意，万国邮联格式包含整个国家/地区的所有地址（低至可用字段精度），因此它是相关的。如果存储客户地址，其中只存储所有可能地址的一小部分，最好使用包含所有字段和每行一个地址的单个表（或平面格式）。

A reasonable format for storing addresses would be as follows:

存储地址的合理格式如下：

• Address Lines 1-4
• Locality
• Region
• Postcode (or zipcode)
• Country

• 地址行 1-4
• 地域性
• 地区
• 邮政编码（或邮政编码）
• 国家

Address lines 1-4 can hold components such as:

地址行 1-4 可以包含以下组件：

• Building
• Sub-Building
• Premise number (house number)
• Premise Range
• Thoroughfare
• Sub-Thoroughfare
• Double-Dependent Locality
• Sub-Locality

• 建筑
• 子楼
• 房号（门牌号）
• 前提范围
• 通道
• 次通路
• 双重依赖局部性
• 次区域

Frequently only 3 address lines are used, but this is often insufficient. It is of course possible to require more lines to represent all addresses in the official format, but commas can always be used as line separators, meaning the information can still be captured.

通常只使用 3 个地址线，但这通常是不够的。当然可能需要更多行来表示正式格式的所有地址，但逗号始终可以用作行分隔符，这意味着仍然可以捕获信息。

Usually analysis of the data would be performed by locality, region, postcode and country and these elements are fairly easy for users to understand when entering data. This is why these elements should be stored as separate fields. However, don't force users to supply postcode or region, they may not be used locally.

通常对数据的分析会按地区、地区、邮政编码和国家进行，这些元素在用户输入数据时很容易理解。这就是为什么这些元素应该存储为单独的字段。但是，不要强迫用户提供邮政编码或地区，它们可能不会在本地使用。

Locality can be unclear, particularly the distinction between map locality and postal-locality. The postal locality is the one deemed by a postal authority which may sometimes be a nearby large town. However, the postcode will usually resolve any problems or discrepancies there, to allow correct delivery even if the official post-locality is not used.

地域性可能不明确，尤其是地图地域性和邮政地域性之间的区别。邮政所在地是邮政当局认定的地方，有时可能是附近的大城市。但是，邮政编码通常会解决那里的任何问题或差异，即使不使用官方邮政所在地，也能正确投递。

回答by Mitch Wheat

Have a look at Database Answers. Specifically, this covers many cases:

看看数据库答案。具体来说，这涵盖了很多情况：

(All variable length character datatype)

（所有变长字符数据类型）

AddressId
Line1
Line2
Line3
City
ZipOrPostcode
StateProvinceCounty
CountryId
OtherAddressDetails

回答by andora

Ask yourself what is the main purposeof storing this data? Do you intend to actually send mail to the person at the address? Track demographics, populations? Be able to ask callers for their correct address as part of some basic authentication/verification? All of the above? None of the above?

问问自己存储这些数据的主要目的是什么？您是否真的打算将邮件发送给该地址的人？跟踪人口统计，人口？作为一些基本身份验证/验证的一部分，能够向呼叫者询问他们的正确地址吗？上述所有的？以上都不是？

Depending on your actual need, you will determine either a) it doesn't really matter, and you can go for a free-text approach, or b) structured/specific fields for all countries, or c) country specific architecture.

根据您的实际需要，您将确定 a) 这并不重要，您可以采用自由文本方法，或 b) 适用于所有国家/地区的结构化/特定字段，或 c) 特定国家/地区的架构。

回答by Emily

Sometimes the closest you can get to a street address is the city.

有时，离街道地址最近的是城市。

I once had a project to put all the Secondary Schools in India in Google Maps. I wrote a spiffy program using the Google API and thought it would be quite easy.

我曾经有一个项目，将印度的所有中学都放在 Google 地图中。我使用 Google API 编写了一个漂亮的程序，并认为这很容易。

Then I got the data from the client. Some school addresses were things like "Across from the market, next to the barber" or "Near old bus stand".

然后我从客户那里得到了数据。一些学校地址是“市场对面，理发店旁边”或“旧公交车站附近”。

It made my task much harder since, unfortunately, the Google API does not support that format.

这让我的任务变得更加困难，因为不幸的是，Google API 不支持这种格式。

回答by Jonathan Leffler

For international addresses, it is remarkably hard to find a way to format the information if it is broken down into fields. As a for instance, an Italian address uses:

对于国际地址，如果将信息分解为字段，则很难找到格式化信息的方法。例如，意大利地址使用：

<street address>
<zip> <town> <region>
<country>

Such as

如

Via Eroi della Repubblica
89861 Tropea VV
Italy

That is rather different from the order for US addresses - on the second line.

这与美国地址的顺序有很大不同 - 在第二行。

See also the SO questions:

另请参阅 SO 问题：

• How many address fields would you use for a UK database?
• Do you break up addresses into street / city / state / zip?
• How do you deal with duplicate street suffixes?
• Best practices for storing postal addresses in a database (RDBMS)?

• 您会为英国数据库使用多少个地址字段？
• 您是否将地址分解为街道/城市/州/邮政编码？
• 你如何处理重复的街道后缀？
• 在数据库 (RDBMS) 中存储邮政地址的最佳实践？

Also check out tag 'postal-code'.

还要检查标签“邮政编码”。

---

---

Edit: Reverse order of region and town - per UPU

编辑：地区和城镇的倒序- 每个万国邮联

回答by Hraban

Maybe this is useful: https://gist.github.com/259744For a project I collected a table of informations about all countries of the world, including ISO codes, top level domain, phone code, car sign, length and regex of zip. Country names and comments unfortunately only in German...

也许这很有用：https: //gist.github.com/259744对于一个项目，我收集了有关世界所有国家/地区的信息表，包括 ISO 代码、顶级域、电话代码、汽车标志、长度和正则表达式压缩。不幸的是，国家名称和评论只有德语...

回答by Abel Callejo

No, there are no standard addressing scheme. It usually varies from country-to-country. Even the Universal Postal Unionsaid on Adressing the world, an address for everyonethat there is none. The best solution for this is to use the 2/3-letter country code standards known as ISO 3166and treat everything else by country's standards.

不，没有标准的寻址方案。它通常因国家而异。就连万国邮政联盟在“ Adressing the world”上也说，“没有地址”给所有人。对此的最佳解决方案是使用称为ISO 3166 的2/3 字母国家/地区代码标准，并按照国家/地区标准处理其他所有内容。

However, if you really are desperate to use easily accessible tools for your project, you can try Google Place API.

但是，如果您真的很想为您的项目使用易于访问的工具，您可以尝试Google Place API。

回答by duffymo

No, absolutely not. If you compare the way US and Japanese addresseswork, you'll see that it's not possible.

不，绝对不是。如果您比较美国和日本地址的工作方式，您会发现这是不可能的。

UPDATE:

更新：

On second thought, anything can be done, but there's a trade-off.

再想一想，任何事情都可以做，但有一个权衡。

One approach is to model the problem with address and address_attribute tables, with a 1:m relationship between them, anything can be modeled. The address_attribute table would have a pk, a name, a value, and an fk that points back to its address parent's pk. It's almost like using a Map with name, value pairs.

一种方法是使用 address 和 address_attribute 表对问题进行建模，通过它们之间的 1:m 关系，可以对任何内容进行建模。address_attribute 表将有一个 pk、一个名称、一个值和一个指向其父地址 pk 的 fk。这几乎就像使用带有名称、值对的 Map 一样。

The trade-off is having to do a JOIN every time you want an address. You also have to interrogate the names of the address_attributes to figure out what you're dealing with each time.

权衡是每次需要地址时都必须执行 JOIN。您还必须询问 address_attributes 的名称以找出您每次处理的内容。

Another approach would be to do more comprehensive research on how addresses are modeled around the world. In an object-oriented world you might have the western Address class (street1/street2/city/state/zip) and others for Japan, China, as many as needed to tile the address space. Then you'd have a master Address table and child tables to the other types with a 1:1 relationship between them.

另一种方法是对世界各地的地址建模方式进行更全面的研究。在面向对象的世界中，您可能有西方的 Address 类 (street1/street2/city/state/zip) 和其他适用于日本、 CN 的类，根据需要平铺地址空间。然后，您将拥有一个主地址表和其他类型的子表，它们之间具有 1:1 的关系。

How does Amazon or eBay do it? They ship internationally. Do they have locale-specific UI features? I've only used the US locale.

亚马逊或 eBay 是如何做到的？他们运送国际。它们是否具有特定于语言环境的 UI 功能？我只使用了美国语言环境。

回答by jerryjvl

Depends on how free-form you are prepared to go with the fields. One free-form address field will obviously always do, but be of relatively little help narrowing down geography.

取决于您准备如何自由地使用这些字段。一个自由格式的地址字段显然总是可以的，但对缩小地理范围的帮助相对较小。

The problem you'll have is that there is too much variation in the level of geographical hierarchy across countries. Heck, some countries do not even have 'street addresses' everywhere.

您将遇到的问题是，不同国家/地区的地理等级级别差异太大。哎呀，有些国家甚至没有到处都有“街道地址”。

I recommend you don't try to make it too clever.

我建议你不要试图让它太聪明。

回答by Paulo Santos

Differently of other answers here, I believe it's possible to have an structured address database.

与这里的其他答案不同，我相信有一个结构化的地址数据库是可能的。

Just out of the hat, I can think of the following structure:

刚开始，我可以想到以下结构：

• Country
• Region (State / Province)
• Locality (City / Municipality)
• Sub-Locality (County / other sub-division of a locality)
• Street

• 国家
• 地区（州/省）
• 地区（市/市）
• 子地方（县/地方的其他子部门）
• 街道

But how to query it fast enough?

但是如何足够快地查询它呢？

One way I always think it can be accomplished is to ask for the ZIP Code (or Postal Code) which varies from country to country, but is solid within the country.

我一直认为可以实现的一种方法是询问邮政编码（或邮政编码），该编码因国家/地区而异，但在国内是固定的。

This way you can structure your data around the information provided by the postal offices around the world.

通过这种方式，您可以围绕世界各地的邮局提供的信息构建您的数据。