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

前言

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

图形获取区域坐标#

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

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

存储位置

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

开放地理空间联盟(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空间类型的数据同样可以建立索引,使用的关键词是 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 在使用函数作为查询字段的情况下依然可以使用索引,最后延伸了一些其他的空间处理函数。

到此这篇关于利用MySQL空间函数实现位置打卡的文章就介绍到这了,更多相关MySQL空间函数位置打卡内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

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

    前言 项目需求是跟用户当前位置判断是否在给定的地理位置范围内,符合位置限制才可以打卡,其中的位置范围是一个或多个不规则的多边形.如下图,判断用户是在清华还是北大. 图形获取区域坐标# 因为项目前端使用微信小程序的wx.getLocation获取地理位置,为了坐标的一致性,后台选取区域范围采用了腾讯地图的地理位置服务,在应用工具->绘制几何图形里,提供了点.线.多边形和圆形可以方便的选取看这里. 在官方提供的示例上稍加改动即可获取选定的位置坐标. 存储位置 取到坐标位置后,接着就是怎么存储? 开放

  • 利用MySQL加密函数保护Web网站敏感数据的方法分享

    如果您正在运行使用MySQL的Web应用程序,那么它把密码或者其他敏感信息保存在应用程序里的机会就很大.保护这些数据免受黑客或者窥探者的获取是一个令人关注的重要问题,因为您既不能让未经授权的人员使用或者破坏应用程序,同时还要保证您的竞争优势.幸运的是,MySQL带有很多设计用来提供这种类型安全的加密函数.本文概述了其中的一些函数,并说明了如何使用它们,以及它们能够提供的不同级别的安全. 双向加密 就让我们从最简单的加密开始:双向加密.在这里,一段数据通过一个密钥被加密,只能够由知道这个密钥的人来

  • 利用drone ci自动化部署静态博客的完整步骤

    目录 前言 准备 安装drone ci 1.docker-compose配置文件 2.docker-compose配置文件主要参数详解 drone server DRONE_SERVER_PROTO和DRONE_SERVER_HOST DRONE_RPC_SECRET DRONE_USER_CREATE DRONE_DATABASE_DRIVER DRONE_DATABASE_DATASOURCE DRONE_GITEE_CLIENT_ID和DRONE_GITEE_CLIENT_SECRET d

  • 利用java和sqlserver建立简易图书管理系统的完整步骤

    目录 1.所需功能: 2.流程: 3.环境及其语言: 4.数据库建立 5.操作: 数据库连接:sql.T3.Test1.java 选择需要的操作:sql.T3.Test1.java 1.游览所有图书 2.查找书籍 3.建立新图书 4借出图书 5. 归还图书删除某一图书 6.删除某一图书 释放连接: sql.T1.JDBCUtil.java 总结 1.所需功能: 游览所有图书.查找书籍.建立新图书,借出和归还图书,删除某一图书 2.流程: 数据库连接 选择需要的操作 释放连接 3.环境及其语言:

  • PHP编程之高级技巧——利用Mysql函数

    尽管PHP为我们提供了很多函数,但有些东西处理起来还是不很方便.譬如PHP提供的日期时间函数就很有限.Mysql为我们提供了不少此类的函数.是否可以利用Mysql函数来处理PHP程序呢?笔者做了以下的尝试. <?php     $data_time="1998-12-31 23:59:59";     $connect_id=mysql_connect('localhost');     $query_id=mysql_query("SELECT DATE_ADD('$

  • 如何利用Mysql计算地址经纬度距离实时位置

    目录 前言 思路 数据库地址表设计 需求实现 总结 前言 最近在做项目时候,遇到一个这样子的需求, 点到卡包里面卡券使用使用,需要展示卡券使用附近门店, 思路 数据库地址表设计 通用的区域街道地址表tz_sys_area 字段名称 类型 备注 area_id bigint 区域ID area_name varchar(32) 区域名称 parent_id bigint 所属父区域ID level int 层级 type char 区域类型0国家1省份直辖市2地市3区县 area_name var

  • Mysql CAST函数的具体使用

    目录 语法 参数说明 示例 练习案例 CAST函数用于将值从一种数据类型转换为表达式中指定的另一种数据类型 语法 CAST(value AS datatype) AS关键字用于分隔两个参数,在AS之前的是要处理的数据,在AS之后的是要转换的数据类型 参数说明 value: 要转换的值 datatype: 要转换成的数据类型 datatype参数取值 值 描述 DATE 将value转换成'YYYY-MM-DD'格式 DATETIME 将value转换成'YYYY-MM-DD HH:MM:SS'格

  • 利用JavaScript实现仿QQ个人资料卡效果

    目录 前言 思路 实现代码 HTML CSS 背景音乐 JavaScript 最终效果 前言 最近在学习前端的知识,无意间发现QQ 那个个人资料卡还挺好看的,就想着自己能不能照着原版搞出一个高仿出来,话不多时直接开始先睡上一觉,找找灵感,睡醒来又饿了,出去吃个饭,回来天tm都黑了,哈哈哈哈,金今天又是摆烂的一天,啊啊啊,不行至少今天这篇博客得写个前言部分…于是乎我们来到第二天,开始干(要干啥来着,幸亏昨天还写了个标题,要不然都忘了——HTML+CSS+JS实现仿QQ个人资料卡) 不多逼逼直接上干

  • iOS 基本动画、关键帧动画、利用缓动函数实现物理动画效果

    iOS基本动画/关键帧动画/利用缓动函数实现物理动画效果 先说下基本动画部分 基本动画部分比较简单, 但能实现的动画效果也很局限 使用方法大致为: #1. 创建原始UI或者画面 #2. 创建CABasicAnimation实例, 并设置keypart/duration/fromValue/toValue #3. 设置动画最终停留的位置 #4. 将配置好的动画添加到layer层中 举个例子, 比如实现一个圆形从上往下移动, 上代码: //设置原始画面 UIView *showView = [[UI

  • MySQL 字符串函数大全

    MySQL 字符串函数大全 对于针对字符串位置的操作,第一个位置被标记为1. ASCII(str) 返回字符串str的最左面字符的ASCII代码值.如果str是空字符串,返回0.如果str是NULL,返回NULL. mysql> select ASCII('2'); -> 50 mysql> select ASCII(2); -> 50 mysql> select ASCII('dx'); -> 100 也可参见ORD()函数. ORD(str) 如果字符串str最左面

随机推荐