代码之家 › 专栏 › 技术社区 › Rodrick Chapman

在关系数据库中建模地理位置

database-design sql

Rodrick Chapman · 技术社区 · 16 年前

我正在设计一个联系人管理系统,并且遇到了一个关于以一致的方式建模地理位置的有趣问题。我希望能够记录与特定人员相关联的位置(工作、学校、家庭等的邮寄地址),我的想法是创建一个区域表,如下所示:

区域设置(ID、locationname、parentID) 自治地方(如国家,如美国)是自己的父母。这样,我就可以随意地在“政治单位”(国家、城市或国家、城市、大学)中筑巢。有些查询必然涉及递归。

对于我可能遇到的任何其他关于可预测问题的建议或建议,我将不胜感激。

8 回复 | 直到 10 年前

Paul Tomblin 16 年前

你可能想看看freebase.com,它是一个公开讨论“位置”是什么意思以及当一个位置包含在另一个位置时它意味着什么的网站。这些问题可以引起很多讨论。

例如,有明显的“地理嵌套”,但没有明显的逻辑嵌套。例如,在严格的地理意义上,梵蒂冈城是嵌套在意大利。但这不是政治上的套路。同样,如果您的用户位于一所大学的研究中心,但不位于该大学的财产上,您是否建立了这种关系的模型?

Kyle Cronin 16 年前

对我来说是个好办法。在阅读你的帖子时,我不清楚的一点是,“他们的父母”是什么意思——如果这意味着区域设置没有父母,你最好使用空值,而不要使用它的ID。

JPLemme 16 年前

我想你可能想得太多了。大多数系统只存储地址,可能还存储一个国家列表,这是有原因的。以下是一些需要注意的事项:

布朗克斯区的一个地址是否会将该行政区作为层级中的一个级别?在非法人区的地址是否会消除等级结构中的“城市”级别?你如何建立一个大学地址和一个不在一个地址中的地址的模型?最后,您将得到一个不规则的层次结构,这将迫使您在每次需要在应用程序中显示地址时遍历树。如果你有一个“地址簿”页面,那么性能会有很大的影响。
我不确定你是否只有一个等级。布朗大学在普罗维登斯,里和布里斯托尔,里有设施。唯一干净的解决方案是拥有两个校区的双重等级,每个校区在一个等级中属于各自的城市,但在另一个等级中都属于布朗大学。(大学从根本上不同于政治区域。你不应该把它们混在一起。)
邮政编码呢?一些邮政编码包含多个城镇,而另一些时候一个城市被分解成多个邮政编码。而且(很少)一些邮政编码甚至跨越州际线。(根据维基百科,至少…)
您将如何输入数据?如果考虑到虚空地址、特定街道的备用名称、不同的国际格式等,通过解析常规格式的地址来构建数据库可能会很困难,而且我认为按层次输入每个地址将是一个PITA。
听起来好像你在试图在你的应用程序中模拟整个世界。您真的想要或需要维护一个能够包含世界上每个城市、州、省、邮政编码和国家的表吗?(或者至少每一个你认识的人?)我能想到的唯一一件事就是这个方案会给你带来接近度,但如果这是你想要的,我会单独存储州和国家(也许还有邮政编码),并添加谷歌的纬度和经度数据。

为极端的悲观感到抱歉,但我自己也走了这条路。它在逻辑上美丽优雅,但在实践中却不太管用。

Andrew not the Saint 16 年前

这里有一个非常灵活的模式建议。立即警告:它可能过于灵活/复杂,无法满足您的实际需要。

位置 (locationid,locationname) --基本构建基块

区位群 (locationgroupid、locationgroupname、parentlocationgroupid) --这可以有效地封装多个层次结构。您有一个根节点,然后可以创建多个独立的分支。例如,您可以先按状态拆分,然后创建几个子层次结构,例如zip/city/XXXX

位置组位置 (locationID,locationGroupID) --下面介绍如何将位置与一个或多个层次结构链接。例如,你可以把你的房子和一个城市联系起来…您需要实现的是一个约束,您不能将一个位置与任何两个层次结构链接起来,其中一个层次结构是另一个层次结构的父级(因为关系已经是隐式的)。

andyuk 16 年前

我会仔细考虑这个,因为它可能不是一个必要的特性。为什么不使用文本字段,让用户输入地址呢?

记住 KISS principle (保持简单,愚蠢)。

Dr8k 16 年前

我同意其他的帖子,你需要在这里对你的要求非常小心。地理位置可能成为一个棘手的问题,这就是为什么地理信息系统如此复杂的原因。

如果你确定你只需要一个基本的继承结构,我有以下建议:

我支持前面的注释,即根级别的项不应将自己作为父级。根级项的父级应为空值。始终小心将数据放入一个没有意义的字段中(即“特殊”值表示没有数据)。这种做法很少必要,而且方式在devleoper社区过度使用。
考虑xpath/xml。这是为了记录继承结构以及在检索时处理/解析数据而考虑的事情。如果您使用的是MSSQLServer,那么select语句中的xpath表达式非常适合于返回记录的完整位置/继承路径等任务,因为代码很简单,结果也很快。

CAD bloke 16 年前

对于地理位置,您可能希望将地址解析为纬度、经度数组(可能使用谷歌地图等)以计算近似值等。地缘政治筑巢…我会用吻来回应。

如果您真的想对它建模,也许您需要更通用的类型…国家->州->县->自治区->地区->城市->郊区->街道或邮政信箱->号码->->设备等->机构(大学或雇主)->部门->分部-1->分部-n…你确定你不能接吻吗?

M.Torres 14 年前

我正在为全球用户建模一个应用程序,我也有同样的问题,但我认为这种方法可能已经在许多企业中使用。但为什么这个问题没有一个通用的解决方案呢?或者,这个问题有没有一个最好的解决方案可以作为起点,或者世界上任何人都需要从一开始就为它考虑一个解决方案? 不幸的是,我们在很多地方,任何时候都在做同样的事情。例如,谁没有创建多个用户、客户或产品的数据库?最糟糕的是,世界上所有的企业都成功了。我认为这可能有解决普遍问题的普遍方法。