MySQL数据库设计-存储单个和多个经纬度

Question

我有一个mysql数据库表，用于存储建筑物、活动和其他一些内容的位置。所有位置都存储在一个表中，并通过它们自己的多对多表与建筑物、活动等链接起来。这样我就可以在地图上显示点，还可以进行过滤等操作。

然而，问题出在某些事物只有一个位置，因此只有1个纬度和经度，但是像跑道这样的东西却有许多个纬度和经度位置，而像大型体育场馆可能会在其上方有一个多边形。这些也被存储为一系列纬度和经度列表，其中第一个和最后一个相同。

我在想应该如何在mysql数据库中存储这些数据。最初，我只有一个用于查找表的纬度、经度和id列。我是否应该有另一个查找表用于坐标或在将数据序列化后以某种方式放入数据库中，或者我应该将整个字符串存储在一个字段中lat1,long1 lat1,long1;lat2,long2;lat1,long1？

有什么建议吗？

- Paul M

Answer 1

我不会从一开始就将数据去规范化，通过将整个“序列化”多边形推送到单个字段中。

相反，我会有一个Polygons表（带有多边形ID和可能的每个多边形辅助信息，例如它是否是实际封闭的多边形或仅是折线 - 虽然这可能可以通过以下表格来表示，在某些多边形的最后一个点等于第一个点），以及一个PointsInPolygon表（带有点的坐标、多边形ID外键、多边形内顶点编号 - 这两个共同是唯一的）。

规范化（通常情况下）将使您的自由查询更加简单（包括在此情况下“X附近的多边形”，多边形中的点等）。同样，像其他种类的数据一样，您可以在确定某些特定查询确实需要优化时，稍后添加冗余非规范化值（代价是更新表格、完整性检查等）。就此而言，地理数据并没有与其他数据有极大区别。

- Alex Martelli

---

虽然这是“适当的，允许任何”方法，但我认为过度规范化是存在的。他似乎想要额外的位置数据来在客户端上可视化位置，而不是查询附近的位置或类似的内容。即使他确实想要查询位置，我仍然建议他将多边形存储为序列化数据，并使用单个纬度/经度对进行地理查询。 - Blixt

Alex，谢谢你的回答。我需要坐下来决定我们是要查询最近的位置等，还是Blixt解决方案对我们更好。选择真多啊！ - Paul M

@Paul M，是的，这确实很难——正常形式的数据库可以让你推迟决策和选择，但是去规范化肯定可以提供性能优势。我遵循Knuth（和Hoare）的“过早优化是编程中万恶之源”的原则——只有在有硬数据表明我需要优化时才进行优化。@Blixt，我尊重你的实用主义，但也允许我持有同样实用主义的立场：在我职业生涯的30年中，我经常遇到“坚如磐石的规格”被改变的情况——因此我尽可能保持我的架构具有灵活性，而规范化==最大的灵活性！-) - Alex Martelli

这是我选择的选项，因此我可以拥有最大的灵活性。这个应用程序将会非常庞大，所以我们希望它能够扩展。 - Paul M

@Alex，我尊重您的经验并相信您是正确的。但是根据我的经验，过度规范化会使本应简单的某些事情变得更加困难，因为一个原子对象突然成为几个相关对象的组合。您能否提供一些指导方针，帮助我们决定在哪里划线？ - Blixt

@Blixt，我的立场是，我总是可以稍后添加非规范化数据来优化已经通过分析证明需要这种优化的查询（当然，这意味着冗余，但这几乎等同于“非规范化”！）。Scott Ambler的《敏捷数据库技术》一书中有一个简短但有用的关于为了优化而非规范化的部分（你可以在谷歌图书搜索中阅读该部分的大部分内容，但这本书也值得购买，因为它还包含其他有用的内容;-)。 - Alex Martelli

Answer 2

由于您没有在位置上进行任何查找，并且正在使用（我假设）Google Maps API，最简单的解决方案可能是将一组lat/lon编码为JSON并存储在varchar列中。

您可以直接从数据库输出JSON供您的Google Maps API代码使用。我建议您使用一些简单的JSON结构，例如：["point",1.23456,2.34567]或["line",1.23456,2.34567,3.45678,4.56789]等等。

- Blixt

---

假设您没有在（多边形附近的x等）上执行任何查询，这绝对是最佳方法。 - RedBlueThing

感谢您的回答和对其他答案的反馈。这些数据主要用于在Google地图上显示。我们可能希望进行聚类，但我看不出我们需要查询位置。它将是全部或无！ - Paul M

感谢您的帮助和见解，Blixt真的很有用。我已经在另一个项目中使用了这种方法。但是，Alex回答了我提出的问题，所以我将把正确答案授予他。 - Paul M