MySQL空间函数实现位置打卡

IT大咖说

发布于 2020-08-21 16:42:21

2.5K0

发布于 2020-08-21 16:42:21

文章被收录于专栏：IT大咖说

项目需求是跟用户当前位置判断是否在给定的地理位置范围内，符合位置限制才可以打卡，其中的位置范围是一个或多个不规则的多边形。如下图，判断用户是在清华还是北大。

图形获取区域坐标

因为项目前端使用微信小程序的wx.getLocation获取地理位置，为了坐标的一致性，后台选取区域范围采用了腾讯地图的地理位置服务，在应用工具->绘制几何图形里，提供了点、线、多边形和圆形可以方便的选取看这里（https://lbs.qq.com/webDemoCenter/glAPI/glEditor/toolDraw）。在官方提供的示例上稍加改动即可获取选定的位置坐标。

存储位置

取到坐标位置后，接着就是怎么存储？开放地理空间联盟(OGC)是一个由 250多家公司，机构和大学组成的国际联盟，参与开发公开可用的空间解决方案，这些解决方案可用于管理空间数据的各种应用程序。OGC发布了地理信息的 OpenGIS®Implementation 标准，该规范可从 OGC 网站http://www.opengeospatial.org/standards/sfs获得。为了遵循 OGC 规范，MySQL 将空间 extensions 实现为具有 Geometry Types 环境的 SQL 的子集，提供生成、存储、分析空间的功能。总之，MySQL可以满足我们的需求。 MySQL提供单个的存储类型 POINT、LINESTRING、POLYGON 对应几何图形点、线、多边形，GEOMETRY 可以存储三种中的任何一种。同时拥有存储多种类型的能力， MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION依次对应单个图形的复数。回到项目中，我们用到的是 POLYGON ， 建表语句 如下：

Copy
CREATE TABLE `polygon` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `polygon` polygon NOT NULL,
  PRIMARY KEY (`id`),
  SPATIAL KEY `d` (`polygon`)
) DEFAULT CHARSET=utf8;

插入数据 MySQL 支持将Well-Known 文本(WKT)格式和Well-Known 二进制(WKB)格式两种格式转换为object类型存储起来，我们使用更易于理解的WKT格式。对WKB感兴趣的可以看这里（https://www.docs4dev.com/docs/zh/mysql/5.7/reference/gis-data-formats.html#gis-wkb-format）。插入语句如下：

Copy
INSERT INTO `polygon` VALUES ('1', '清华大学', GeomFromText('POLYGON((
40.01169924229143 116.31565081888039,39.99304082299905 116.31616541796757,39.99343506780591 116.33297565023167,40.00237067000859 116.33743550702275,40.01340715321479 116.33057418815224,40.01169924229143 116.31565081888039))'));

INSERT INTO `polygon` VALUES ('2', '北京大学', GeomFromText('POLYGON((39.99711457525893 116.30450117461078,39.98673259872773 116.30535884106575,39.98673259872773 116.31702308311287,39.99963848242885 116.31598375134854,39.99711457525893 116.30450117461078))'));

需要注意的是腾讯地图返回的多边形的点不是闭合的，而polygon函数需要为了确定多边形是否闭合要求第一个点和最后一个点是一样的。如果不是闭合的polygon返回的结果将是NULL，插入语句就会执行失败。如果几何满足诸如此(非穷举)列表中的条件，则它在语法上是 well-formed：

线串至少有两个点
多边形至少有一个环
多边形环关闭(第一个和最后一个点相同)
多边形环至少有 4 个点(最小多边形是一个三角形，第一个和最后一个点相同)
集合不为空(除了GeometryCollection)

查询判断

Copy
SELECT * FROM polygon WHERE
	MBRWithin (ST_GeomFromText('POINT(39.991333490218544 116.30964748487895)'), polygon);
# 在北京大学

SELECT * FROM polygon WHERE
	MBRWithin (ST_GeomFromText('POINT(39.988967560246685 116.3286905102832)'), polygon);
# 不在北大

细心的同学可能发现了这里的查询语句里用的是函数，在以往的SQL里如果存在查询字段上使用函数必然导致索引失效、全表扫描，但是在空间数据上不会，先看 EXPLAIN 语句和结果：

可见MySQL空间类型的数据同样可以建立索引，使用的关键词是 SPATIAL 用法如下：

Copy
CREATE TABLE geom (g GEOMETRY NOT NULL);
CREATE SPATIAL INDEX g ON geom (g);

常用的空间计算函数

1、判断两点之间的距离 ST_Distance(g1，g2)，返回g1和g2之间的距离。如果任一参数是NULL或空几何，则 return value 为NULL。 2、图形1是否完全包含图形2 ST_Contains(g1，g2)，返回 1 或 0 以指示g1是否完全包含g2。还可以用ST_Within(g2,g1)达到相同的效果。 3、不相交 ST_Disjoint(g1，g2)，返回 1 或 0 以指示g1是否在空间上与(不相交)g2不相交。 4、关于图形相交的情况比较复杂，包含重叠、外相交等情况，具体可以看这里（https://www.docs4dev.com/docs/zh/mysql/5.7/reference/spatial-relation-functions-object-shapes.html）

总结

本文通过一个地理位置打卡的需求，使用 MySQL 自带的 Polygon 数据类型实现了空间数据的存储，用ST_Contains(g1，g2) 函数代入了后台预置的地理区域和前端获取到的用户地理位置可以得出用户是否在打卡范围内。其中还涉及到了 MySQL 在使用函数作为查询字段的情况下依然可以使用索引，最后延伸了一些其他的空间处理函数。

作者：古道

出处：https://www.cnblogs.com/gudao119/p/13510679.html

“IT大咖说”欢迎广大技术人员投稿，投稿邮箱：aliang@itdks.com