mysql数据库锁 栅栏,如何使用MySQL查找多边形地理围栏中包含的点

与其他数据库(如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特性”。