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

Question

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

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

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

我现在需要执行一个 SQL 查询，该查询将返回这组坐标(视口(viewport))内的所有地理空间点。

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

标志表:

• 编号
• 坐标
• 姓名

示例行:

1 | [几何 - 25B] |坚韧AB

也可以使用 AsText(coordinates) 将坐标字段转换为文字点。然而，X 和 Y 函数会为您做这件事。

坐标列数据类型:POINT

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

数据库管理系统:MySQL
数据库管理系统版本:5.6.14

Answer 1

大概您的 geometry 列中的 POINT 数据中的 x 和 y 项是以纬度和经度表示的。

要在 MySQL 中高效地执行此查找，您需要做一些事情。

• MyISAM 表(或 MySQL 5.7 及更高版本以及 InnoDB 或 MyISAM)
• 几何列上的 NOT NULL 限定条件
• 空间索引ALTER TABLE flags ADD SPATIAL INDEX(坐标)
• 用于创建要搜索的矩形的文本表示的代码
• 在 SELECT 语句中使用 GeomFromText 和 MBRContains/MBRWithin 函数。

假设您的纬度/经度框是一个以 Winchester Cathedral (51.0606, -1.3131) 为中心、范围为 1 度的矩形.您需要围绕该点的边界框。此 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。

这是一些 documentation .

如果您的 flags 表包含少于几十万行，您可能会发现带有纬度和经度列(FLOAT 数据类型，索引)的普通表(不是空间表)执行如下很好，更容易开发和调试。

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