TOP

[MySQL] mysql地理位置服务geometry字段类型
2019-09-04 00:57:11 】 浏览:35
Tags:MySQL mysql 地理位置 服务 geometry 类型

这个字段类型是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,content,tel, (st_distance (location,point(116.282459,40.047955) ) *111195) AS distance FROM service ORDER BY distance;
判断距离
SELECT name,content,tel,astext(location),FLOOR(st_distance (location,point(116.282459,40.047955) ) *111195) AS distance FROM service having distance < 1000 ORDER BY distance;

 


[MySQL] mysql地理位置服务geometry字段类型 https://www.cppentry.com/bencandy.php?fid=85&id=249939

】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇php获取指定日期的前一天,前一月.. 下一篇php json_encode()函数返回对象和..