利用MySQL空间函数实现位置打卡的完整步骤

作者:古道 时间:2024-01-28 11:23:32 

前言

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

利用MySQL空间函数实现位置打卡的完整步骤

图形获取区域坐标#

因为项目前端使用微信小程序的wx.getLocation获取地理位置,为了坐标的一致性,后台选取区域范围采用了腾讯地图的地理位置服务,在应用工具->绘制几何图形里,提供了点、线、多边形和圆形可以方便的选取看这里。

在官方提供的示例上稍加改动即可获取选定的位置坐标。

利用MySQL空间函数实现位置打卡的完整步骤

存储位置

取到坐标位置后,接着就是怎么存储?

开放地理空间联盟(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 ,

建表语句 如下:


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感兴趣的可以看这里。

插入语句如下:


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)

查询判断


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空间函数实现位置打卡的完整步骤

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

用法如下:


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、关于图形相交的情况比较复杂,包含重叠、外相交等情况,具体可以看这里

总结

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

来源:https://www.cnblogs.com/gudao119/p/13510679.html

标签:mysql,空间函数,位置
0
投稿

猜你喜欢

  • python中isdigit() isalpha()用于判断字符串的类型问题

    2023-05-02 18:52:48
  • CSS背景图片的运用优化HTTP连接数

    2008-09-04 21:38:00
  • CentOS7安装Python3的教程详解

    2023-10-17 02:40:28
  • bootstrap3 兼容IE8浏览器!

    2023-07-02 05:18:58
  • SQL Server中选出指定范围行的SQL语句代码

    2024-01-28 08:39:14
  • django项目中使用云片网发送短信验证码的实现

    2021-09-20 18:35:23
  • Python实现利用163邮箱远程关电脑脚本

    2023-08-29 10:08:08
  • 详解Vue.js——60分钟组件快速入门(上篇)

    2024-05-03 15:12:29
  • PHP中round()函数对浮点数进行四舍五入的方法

    2023-11-23 21:35:24
  • python PyQt5/Pyside2 按钮右击菜单实例代码

    2023-03-11 15:03:19
  • 6个卓越Web设计细节

    2010-03-29 12:56:00
  • Python根据指定日期计算后n天,前n天是哪一天的方法

    2022-12-27 19:13:50
  • mybatis-plus的sql语句打印问题小结

    2024-01-25 11:23:42
  • PHP设计模式之装饰器模式定义与用法详解

    2023-09-11 18:41:48
  • Django+boostrap 美化admin后台的操作

    2022-02-26 16:16:21
  • yolov5模型配置yaml文件详细讲解

    2022-02-04 07:21:30
  • php计算给定日期所在周的开始日期和结束日期示例

    2023-10-11 12:49:12
  • flask框架视图函数用法示例

    2022-07-18 09:36:18
  • Vue Render函数原理及代码实例解析

    2023-07-02 16:34:38
  • mysql查询一天,查询一周,查询一个月的数据

    2011-01-29 16:22:00
  • asp之家 网络编程 m.aspxhome.com