[MySQL] mysql地理位置服务geometry字段类型

概述这个字段类型是mysql5.7新增的功能,主要就是解决坐标存储和距离计算的常见问题 创建表:CREATE TABLE `service` ( `id` bigint(20) NOT NULL AUTO

这个字段类型是MysqL5.7新增的功能,主要就是解决坐标存储和距离计算的常见问题

创建表:
CREATE table `service` (
`ID` bigint(20) NOT NulL auto_INCREMENT,
`name` varchar(128) NOT NulL DEFAulT '',
`content` varchar(128) NOT NulL DEFAulT '',
`tel` varchar(20) NOT NulL DEFAulT '',
`location` geometry NOT NulL,
PRIMARY KEY (`ID`),
KEY `location` (`location`(32))
) ENGINE=InnoDB auto_INCREMENT=1 DEFAulT CHARSET=utf8

插入坐标
insert into service (name,content,tel,location)values("陶士涵",'牛逼','18898989898',ST_GeomFromText('POINT(116.28828 40.053257)'));
读取坐标
select *,astext(location) from service;
查询距离
SELECT name,(st_distance (location,point(116.282459,40.047955) ) *111195) AS distance FROM service ORDER BY distance;
判断距离
SELECT name,astext(location),FLOOR(st_distance (location,40.047955) ) *111195) AS distance FROM service having distance < 1000 ORDER BY distance;

 

总结

以上是内存溢出为你收集整理的[MySQL] mysql地理位置服务geometry字段类型全部内容，希望文章能够帮你解决[MySQL] mysql地理位置服务geometry字段类型所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。