与其他数据库(如Postgres)相比,MySQL在地理空间方面的应用有点晚,但随着5.6和5.7版本的发布,许多新的GIS功能最终得以实现。例如,在5.7中,我们得到了:

  • ST_Distance_Sphere可计算球体上两点之间的距离。
  • InnoDB空间索引
  • GeoJSON集成,引用RFC的话,“一种基于JSON的地理空间数据交换格式。”

有很多有趣的事情我们现在可以完成使用这些新的情势。今天我将展示一个非常简单的例子,说明如何使用多边形和点进行搜索。这里的目标是回答“这一点在这个城市吗?”?”.

定义点

首先,让我们创建一个带有名称和一组坐标的“places”表。但是,我将使用一个点,而不是将纬度和经度存储为两个不同的浮动。

CREATE TABLE `places` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(256) DEFAULT NULL,
  `coordinates` point DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后让我们在巴黎和纽约增加几个地方进行基本测试。存储新点的方法非常前卫:

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Eiffel Tower", POINT(48.858271, 2.293795));

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Pere Lachaise", POINT(48.861131, 2.394683));

INSERT INTO `places` ( `name`, `coordinates`)
VALUES ("Brooklyn", POINT(40.711089, -73.948391));

过滤

让我们试着回答这个问题“我的数据库中的哪些地方在巴黎?”?”.

用ST_Distance_Sphere过滤

首先,让我们尝试使用MySQL的ST_Distance_Sphere来查找两点之间的距离。让我们使用卢浮宫的坐标(48.861105,2.335337),它位于巴黎市中心。我们可以很容易地用ST_distance_Sphere计算距离。

SELECT name,
ST_Distance_Sphere(coordinates, POINT(48.861105, 2.335337))
FROM places

结果如下

Eiffel Tower    4629.968479838098
Pere Lachaise   6598.960247905806
Brooklyn    8500637.22140377

返回的距离以meters为单位,所以一切都能查出来。如果我们想看到距离卢浮宫不到10公里的所有点,我们可以:

SELECT name FROM places
WHERE ST_Distance_Sphere(coordinates, POINT(48.861105, 2.335337)) < 10000

这是一个相当好的近似在巴黎,但这并不完美。如果我想知道一个点是否真的在巴黎,而不仅仅是靠近巴黎市中心呢?

使用多边形和ST_包含

首先让我们定义一个包围巴黎的基本形状。我将在这里使用的坐标是一个非常粗略的近似值,我只定义了一个矩形,但在这个例子中可以使用:

SET @paris = ST_GEOMFROMTEXT(
  'POLYGON(
(48.89 2.27, 48.89 2.42, 48.81 2.42, 48.81 2.27, 48.89 2.27)
  )'
);

如果你仔细看,你会发现最后一个点和第一个点是一样的,我们有5个点。这是因为我们需要“close”多边形。
那么我们可以用巴黎包含的所有多边形:

SELECT name FROM places
WHERE ST_CONTAINS(@paris, coordinates)

当然,我可以跳过定义@paris并在查询中直接调用POLYGON的步骤。

总结

Geospacial函数很有趣,而且由于我们现在可以在InnoDB中使用空间索引,因此它们实际上可以在生产中使用。在本文中,我不会深入讨论性能,但我建议您阅读Percona文章“MySQL 5.7中的新GIS特性”。

Tags: mysql, 地理围栏

Related Posts:

Leave a Comment