选择所有在边界框内的地理空间点

Question

我在MySQL数据库中有一个名为“flags”的表，该表具有400,000行数据，由表示英国不同位置的地理空间点组成。

我正在创建的应用程序使用Google地图。地图上有一个按钮，应该可以切换地图上旗帜的可见性。现在我的工作是创建一个API，当传递一个边界框时，返回边界框内的所有标记（以便可以在地图上显示它们）。

传递的参数是视口当前位置的东北纬度/经度和西南纬度/经度。

现在，我需要执行一个SQL查询，以返回位于这些坐标集（视口）内部的所有地理空间点。

理想情况下，解决方案需要进行优化，因为要搜索的行很多。但是，应用程序确实强制用户在显示标志之前缩放到某个级别。

Flags表：

• id
• coordinates
• name

示例行：

1 | [GEOMETRY - 25B] | Tenacy AB

通过使用AsText(coordinates)，可以将coordinates字段转换为文字点。但是，X和Y函数会为您完成此操作。

coordinates列的数据类型为：POINT

我知道可以使用X和Y函数获取点的纬度/经度。例如，可以像这样检索点的纬度：X(coordinates)

DBMS：MySQL
DBMS版本：5.6.14

- jskidd3

---

展示“flags”表的定义将非常有帮助。您还可以展示该表的半打样本行。此外，由于英国跨越本初子午线，经度的符号（+ -）将很重要。 - O. Jones

@OllieJones 谢谢你，Ollie。我已经编辑了问题并添加了标志表结构。 - jskidd3

Answer 1

假设你的geometry列中的POINT数据中的x和y项是纬度和经度度数。

要在MySQL中高效地进行此查找，您需要几个条件：

• 一个MyISAM表（或MySQL 5.7及以上版本和InnoDB或MyISAM）
• 在您的geometry列上使用NOT NULL限定
• 一个空间索引 ALTER TABLE flags ADD SPATIAL INDEX (coordinates)
• 创建一个文本表示要搜索的矩形的代码
• 在SELECT语句中使用GeomFromText和MBRContains / MBRWithin函数。

假设您的纬/经度框是以位于Winchester Cathedral (51.0606, -1.3131)为中心延伸一度的矩形。您需要在该点周围绘制一个边界框。这个MySQL查询将生成一个LINESTRING（文本），用于沿着该边界框对角线走的线。

SELECT 
       CONCAT('LINESTRING(',
              latitude-0.5,' ',longitude-0.5,
              ',', 
              latitude+0.5 ,' ',longitude +0.5,
              ')') AS box
   FROM (
      SELECT 51.0606 AS latitude, -1.3131 AS longitude
   ) AS coord

这个查询会得到以下结果：

LINESTRING(50.5606 -1.8131,51.5606 -0.8131)

你还可以使用主机语言中的字符串处理来生成类似的文本字符串。您需要的格式如下。

 LINESTRING(lat1 long1, lat2 long2) 

那么您可以按照以下方式使用它来搜索您的空间表：

SELECT whatever, whatever 
  FROM flags
 WHERE MBRContains(
        GeomFromText( 'LINESTRING(50.5606 -1.8131,51.5606 -0.8131)' ),
        flags.coordinates)     

这将利用空间索引并找到每一行 flags，其坐标位于对角线框的范围内。

这里是一些文档。

如果您的flags表包含的行数少于几十万行，您可能会发现具有纬度和经度列（FLOAT数据类型，带有索引）的普通表（而不是空间表）的性能表现也很好，并且更易于开发和调试。

我已经写了一个有关该技术的教程。http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

- O. Jones

---

我无法感谢你的足够。非常棒的答案，谢谢！也许过几天我还需要你的帮助，因为你似乎在MySQL地理空间方面非常有知识。我在你的Google Plus上看到了你的电子邮件地址，如果过几天我给你发送另一个Stack问题的链接，你介意吗？非常感谢你的帮助（如果你还没有猜到的话）！ - jskidd3

@OllieJones 看起来 MySQL 使用 X/Y 作为经度/纬度，而不是 Stack Overflow 上通常使用的纬度/经度。Point(x y) 实际上是 Point(y x)。 - kouton

@kouton，也许你在想PostgreSQL。据我所知，MySQL没有将POINT(x y)中的x和y分配任何地理语义。如果有的话，也许你可以提供一个参考。 - O. Jones

@OllieJones MySQL 存储 WKT，显然应符合 lng、lat。即使 PHPMyAdmin 也是这样假定的，并在其地图上不正确地绘制多边形/点。http://spatialreference.org/ref/epsg/4326/ - kouton

谢谢你。像这样在边界框内查找点是非常困难的。 - joe

@O.Jones 我想你指的是你的另一篇文章，同样很有趣：https://www.plumislandmedia.net/mysql/using-mysqls-geospatial-extension-location-finder/ - Pato

Answer 2

你可以使用Quadkey。它可以减少维度，使空间搜索更容易。我写了一个PHP类Hilbert-curve @ phpclasses.org。您还可以从Microsoft Bing地图平铺中了解有关Quadkey的信息。基本上，Quadkey有助于找到x、y、z坐标处的瓦片。来源：http://msdn.microsoft.com/en-us/library/bb259689.aspx。

- Micromega